postgresql where does the output of pg_dump go

Question

I am trying to backup a db of postgresql and I want to use pg_dump command.
I tried :

psql -U postgres
postgres-# pg_dump test > backup.sql

But I don't know where the output file goes.
Any help will be appreciated

For Mac Os Kindly check this answer [here][1] is well explained. [1]: https://stackoverflow.com/a/50823671/4683950 — Espoir Murhabazi, Jun 22 '18 at 09:44

score 69 · Answer 1 · answered Aug 26 '18 at 19:16

I'm late to this party, but I feel that none of the answers are really correct. Most seem to imply that pg_dump writes a file somewhere. It doesn't. You are sending the output to a file, and you told the shell where to write that file.

In your example pg_dump test > backup.sql, which uses the plain or SQL format, the pg_dump command does not store any file anywhere. It just sends the output to STDOUT, which is usually your screen, and it's done.

But in your command, you also told your shell (Terminal, Command prompt, whatever) to redirect STDOUT to a file. This has nothing to do with pg_dump but is a standard feature of shells like Bash or cmd.exe.

You used > to redirect STDOUT to a file instead of the screen. And you gave the file name: "backup.sql". Since you didn't specify any path, the file will be in your current directory. This is probably your home directory, unless you have done a cd ... into some other directory.

In the particular case of pg_dump, you could also have used an alternative to the > /path/to/some_file shell redirection, by using the -f some_file option:

-f file
--file=file

Send output to the specified file. This parameter can be omitted for file based output formats, in which case the standard output is used.

So your command could have been pg_dump test -f backup.sql, asking pg_dump to write directly to that file.

But in any case, you give the file name, and if you don't specify a path, the file is created in your current directory. If your prompt doesn't already display your current directory, you can have it shown with the pwd command on Unix, and cd in Windows.

TLDR (for me): Running `pg_dump my_database` didn't create a file, it just output the content of my database in my terminal. I should have piped the output to a file, for example: `pg_dump my_database > backup.sql` — Matt, Jul 16 '21 at 18:30

score 39 · Accepted Answer · edited Jan 07 '19 at 17:29

39

Go to command prompt and directory postgresql\9.3\bin.

Example

.

..
    c:\Program files\postgresql\9.3\bin> pg_dump -h localhost -p 5432 -U postgres test > D:\backup.sql
...

After above command enter User "postgres" password and check D:\ drive for backup.sql file

edited Jan 07 '19 at 17:29

Simeon Leyzerzon

18,658
9
54
82

answered Jul 24 '14 at 10:21

Sarfaraz Makandar

5,933
16
59
84

score 25 · Answer 3 · edited Jul 28 '18 at 13:35

In my situation (PostgreSQL 9.1.21, Centos 6.7), the command

runuser -l postgres -c 'pg_dump my_database > my_database.sql'

saved the file here:

/var/lib/pgsql/my_database.sql

Not sure if that is true for other Linux dists, CentOS and/or pgl versions. According to the answer post by the asker of this question, this is true, but other users said the backup file was in the current directory (a situation different of most people reading this thread, for obvious reasons). Well, I hope this can help other users with the same problem.

P.s.: if that's not the path for your situation, you can try (in Linux) to find it using the below command (as stated by @Bohemian in the comments of this question), but this can take a while:

find / -name 'my_database.sql'

EDIT: I tried to run the analogous command in Ubuntu 12.04 (it works on Ubuntu 18.04):

sudo -u postgres pg_dump my_database > my_database.sql

And in this case the file was saved in the current directory where I ran the command! So both cases can happen in Linux, depending of the specific dist you are working

On Ubuntu 18.04 this command dump the file just where you execute it. It works perfect. — mrroot5, Jul 28 '18 at 08:22
sudo -u postgres pg_dump s2s_mall > my_database.sql worked for me, I can see DB_bkp on current Dir... Thanks!!!! — S.Yadav, Aug 13 '18 at 10:46
@Brian Hellekin: " I hope this can help other users with the same problem." Yes, it helped me! I am on Ubuntu 18.04 and found at /var/lib/pgadmin/storage/my username. Thanks much! p.s. — Malik A. Rumi, Mar 11 '19 at 19:39

score 7 · Answer 4 · answered Jun 15 '16 at 19:28

7

If you are not specifying fully qualified paths, like:

pg_dump your_db_name > dbdump

then in Windows it stores dumps in current user's home directory. I.e.:

C:\Users\username

answered Jun 15 '16 at 19:28

WelcomeTo

811
8
11

Same in Ubuntu: running pg_dump -U postgres dbname > dbfile from under a root resulted into root/dbfile – Anatoly Alekseev Jan 17 '23 at 11:16

IvanM · Answer 5 · 2021-10-03T12:54:03.153

7

For Linux default dump path is:

/var/lib/postgresql/

edited Oct 03 '21 at 12:54

answered Mar 17 '19 at 19:38

IvanM

2,913
2
30
30

score 4 · Answer 6 · answered Nov 24 '20 at 08:51

4

If you use linux (except centos)

sudo su - postgres
pg_dump your_db_name > your_db_name.sql
cd /var/lib/postgresql
ls -l

Here your'll see your_db_name.sql file

answered Nov 24 '20 at 08:51

Galaxy IT

696
6
7

score 3 · Answer 7 · answered Aug 06 '19 at 17:46

In pgadmin 4 for a Mac, assuming dump is successful you can click on "More Details" you will see a box that says "Running command:" in that box you will see /Applications/pgAdmin 4.app/Contents/SharedSupport/pg_dump --file "path/to/file" where path to file is the destination of storage.

score 2 · Answer 8 · answered May 14 '21 at 07:22

2

After doing

psql -U postgres

Using the command

\! pg_dump -U postgres humaine > C:\Users\saivi\OneDrive\Desktop\humaine_backup1.sql

The output file would go where the path at the right is specified

answered May 14 '21 at 07:22

Vikranth

1,538
1
14
23

score 1 · Answer 9 · answered Jan 04 '20 at 16:48

1

In the server (Ubundu/Centos) the path of backup file will be /var/lib/pgadmin/storage/

answered Jan 04 '20 at 16:48

Shiju S S

51
1
9

Nurullah gümüş · Answer 10 · 2023-08-20T09:49:20.253

1

$ pg_dump --dbname="postgresql://[username]:[password]@[host]:[port]/[db_name]" -F t -O -x -R > file.sql

edited Aug 20 '23 at 09:49

answered Aug 20 '23 at 09:48

Nurullah gümüş

11
2

1

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? **If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient.** Can you kindly [edit] your answer to offer an explanation? – Jeremy Caney Aug 21 '23 at 00:55
including the full connection string (username, password, port, etc.) was extremely useful to me – jackRoark Aug 24 '23 at 17:14

score 1 · Answer 11 · answered Aug 30 '23 at 15:23

1

Just open pgAdmin, right-click your db and select "backup...". Specify filepath there.

answered Aug 30 '23 at 15:23

D H

51
5

score 0 · Answer 12 · answered Jan 23 '23 at 15:57

Below is the OS specification.

NAME="Ubuntu"
VERSION="20.04 LTS (Focal Fossa)"

I am using following command to take the backup of postgresql database.

pg_dump -U postgres -Fc <db_name> > /var/lib/postgresql/backup-20230123.dump

If storage file path has been provided explicitly, in that case, the database dump will be generated to that place only.

For windows, provide folder path where you want to download the dump.

postgresql where does the output of pg_dump go

12 Answers12

Linked