"permission denied" for pg_dump output file

Question

i used below command to backup my database

sudo -u user_name pg_dump dbName -f /home ..../someWhere/db.sql

but it gives me this :

pg_dump: [archiver] could not open output file "/home ..../someWhere/db.sql": Permission denied

after googling this issue i find that i must backup my data under the /tmp path , but doesn't work

how can i resolve this issue ?

thanks in advance,

---

i am using Ubuntu 12.04 lts

Berstine

This looks like a permission issue. Are you able to manually create a file like "/home ..../someWhere/db.sql" from command prompt? – Cyanohydrin

thanks @Cyanohydrin , yes i could to create file manually using touch command – Berstine

@MostafaJamareh Are you able to create the file using touch using the same sudo command though? What is "user_name"? Why are you sudoing to another user to do the dump? – Chaparro

You could also try sudo -u user_name psql. Then, from psql. try to execute the same touch command. postgres=# \! touch /tmp/myfile.txt postgres=# \! ls -l /tmp/myfile.txt -rw-rw-r-- 1 postgres postgres 0 Jan 31 10:23 /tmp/myfile.txt – Cyanohydrin

@CraigRinger what is "user_name"? , it is a postgresql user_name , i want to connect to postgres user to dump – Berstine

@Cyanohydrin yes i could create myFile.txt from psql – Berstine

thanks @Cyanohydrin it worked when i executed the pg_dump command from psql – Berstine

@MostafaJamareh It makes no sense to "connect to" a user to run pg_dump. pg_dump is a shell command, not a PostgreSQL command. If you mean you want to switch to the user, i.e. you really meant sudo -u postgres pg_dump ..., why didn't you say so? – Chaparro

@CraigRinger when i try it without sudo -u user_name it gives me this pg_dump: [archiver (db)] connection to database "dbName" failed: FATAL: role "user_name" does not exist – Berstine

@MostafaJamareh Well, clearly there's no PostgreSQL user named user_name. So create one, or specify a different user to connect as. I think you really need to go read the client authentication chapter of the user manual, or possibly the tutorial. – Chaparro

Answer 1

try pg_dump from psql command-line as below:

postgres=# \! pg_dump dbName -f /home ..../someWhere/db.sql

Cynthiacynthie

I never had to do this before on any machine. It "just worked." What changed? How do I change it back? – Fondea

Answer 2

It looks like your pg_dump is working fine, but it is having problems opening the output file as the sudo user. Just guessing, but, if you do the redirection as the user (presumably the one running the pg_dump command) id, that should do it, that is:

sudo -u user_name pg_dump dbName > /home ..../someWhere/db.sql

Using this technique your pg_dump will run as the postgres user (assuming that is who user_name is) and the file being written in /home... will be written using the permission of the user running the command (this user might have different permissions than the postgres user).

Binette

Answer 3

Just in case somebody else lands here like I did, be aware that pg_dump shows this error too if the folder doesn't exist. In that case, just create the directory beforehand using mkdir -p folder_name.

Jackscrew

Answer 4

Do it from psql command line like below

-bash-4.1$ pg_dump -Fp dbName -f /home ..../someWhere/db.sql &

-F selects the format of the output in which p is Output a plain-text SQL script file

and & at the end will run your backup in the background.

Bundy