13

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

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Mostafa Jamareh
  • 1,389
  • 4
  • 22
  • 54
  • This looks like a permission issue. Are you able to manually create a file like "/home ..../someWhere/db.sql" from command prompt? – Jayadevan Jan 30 '14 at 14:02
  • 2
    thanks @Jayadevan , yes i could to create file manually using `touch` command – Mostafa Jamareh Jan 30 '14 at 18:10
  • 2
    @MostafaJamareh Are you able to create the file using `touch` **using the same sudo command** though? What is "user_name"? *Why* are you `sudo`ing to another user to do the dump? – Craig Ringer Jan 31 '14 at 00:44
  • 1
    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 – Jayadevan Jan 31 '14 at 04:55
  • @CraigRinger what is "user_name"? , it is a postgresql user_name , i want to connect to postgres user to dump – Mostafa Jamareh Feb 01 '14 at 05:35
  • @Jayadevan yes i could create myFile.txt from psql – Mostafa Jamareh Feb 01 '14 at 05:36
  • thanks @Jayadevan it worked when i executed the `pg_dump` command from `psql` – Mostafa Jamareh Feb 01 '14 at 05:39
  • @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**? – Craig Ringer Feb 01 '14 at 07:50
  • @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` – Mostafa Jamareh Feb 01 '14 at 08:18
  • @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. – Craig Ringer Feb 01 '14 at 08:41

4 Answers4

16

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).

Greg
  • 6,571
  • 2
  • 27
  • 39
10

try pg_dump from psql command-line as below:

postgres=# \! pg_dump dbName -f /home ..../someWhere/db.sql
  • 2
    I never had to do this before on any machine. It "just worked." What changed? How do I change it back? – JosephK Oct 01 '17 at 02:36
1

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.

Guardian
  • 383
  • 4
  • 17
1

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.

Goulven
  • 777
  • 9
  • 20