Postgresql dump permission denied

Question

I get this error while trying to dump database, i entered

linuxuser $ sudo su postgres
linuxuser $ [sudo] password for linuxuser:...
$ pg_dump -h localhost mydb >tempfile
$ sh: cannot create tempfile: Permission denied

What the problem? i've just installed fresh postgresql.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

1. In the Finder, create a new folder. Select it.
   In this example, I created a folder named postgres_backups.
2. Choose File > Get Info.
3. Open the disclosure triangle for the Sharing & Permissions section.
4. Click the Plus button to add another item to the list of users.
   A list of users appears in a "sheet" dialog.
5. Select the postgres user from the list.
6. In the Privilege column, for the new postgres row, change the popup menu to Read & Write.
7. 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.

Answer 5

first thing you have to do is do not switch to user postgres. The use this command for backup:

pg_dump -U username -h localhost dbname > /db.sql

Answer 6

I wrestled with this "Permission Denied" issue while trying to backup my PSQL database on an Ubuntu machine for a long time, and tried user access rights, superuser status, folder properties--lots of stuff. Finally, something worked that was ridiculously simple. In the command:

pg_dump -U username -O dbname > 'filename.sql'

Make sure you have quotes (single or double quotes seem to work) around the filename which follows the greater than (">") sign. The examples above do not have quotes around the filename. Once I tried that, I no longer received Permission Denied errors.