Postgresql dump permission denied
Write into directory where postgres user has write access. For instance /tmp
.
$ pg_dump -h localhost mydb >/tmp/tempfile
In your attempt postgres user tries to create a file in some random directory belonging to the other user.
postgres
User
As the other correct answers said, the folder in which you are trying save the backup does not have permissions assigned to the postgres
user (operating system user account). The postgres
user is the one running the backup utility. This user account was created during the Postgres installation process. You may have used a different name, but the default is postgres
.
Folder With Permissions
The solution is to either find or create a folder where the postgres
user has read-write permissions.
Mac OS X
In Mac OS X (Mountain Lion), I am able to create such a folder in the Finder.
- In the Finder, create a new folder. Select it.
In this example, I created a folder namedpostgres_backups
. - Choose
File
>Get Info
. - Open the disclosure triangle for the
Sharing & Permissions
section. - Click the Plus button to add another item to the list of users.
A list of users appears in a "sheet" dialog. - Select the
postgres
user from the list. - In the
Privilege
column, for the newpostgres
row, change the popup menu toRead & Write
. - Close the
Get Info
window. Done.
Now you can direct your Postgres backup files to that folder.
By the way, I use the pgAdmin app to do backups and restores. Control+click on the desired database and choose Backups…
. The pgAdmin app was probably bundled with your Postgres installation.
backup and restore can be done by any unpriviledged user that knows the postgres superuser password by changing permissions on the working directory:
% mkdir backup
% chmod 0777 backup
% su postgres
[enter password]
$ cd backup
$ pg_dump mydb >tempfile
$ exit
"tempfile" will be owned by postgres and same group as the user
sudo su postgres doesn't change the current directory so you're still in linuxuser's home directory and postgres has no permission to write into it. Change to a different directory