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

Communicant

Why don't you do it then run find / -name backup.sql – Crouch

For Mac Os Kindly check this answer [here][1] is well explained. [1]: https://mcmap.net/q/270496/-where-does-the-file-output-of-pg_dump-go-on-macos – Pants

Keep in mind that you should not be running the pg_dump command from within the postgres command line if you are trying to write to a file. Run it as mentioned below in https://mcmap.net/q/268532/-postgresql-where-does-the-output-of-pg_dump-go. If you simply run pg_dump -h localhost -p 5432 -U postgresUser dbName > backup.sql then the backup.sql file will be saved to the directory you are running the command from (so if you are in a Linux terminal simply enter ls to see the directory contents). – Externalization

Answer 1

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

Psychographer

^^' what if you don't have a D: drive ? – Farmyard

Answer 2

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.

Junkman

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 – Topaz

Answer 3

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

Prognosis

On Ubuntu 18.04 this command dump the file just where you execute it. It works perfect. – Calci

sudo -u postgres pg_dump s2s_mall > my_database.sql worked for me, I can see DB_bkp on current Dir... Thanks!!!! – Krick

@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. – Henricks

Answer 4

For Linux default dump path is:

/var/lib/postgresql/

Whippletree

Answer 5

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

Papist

Same in Ubuntu: running pg_dump -U postgres dbname > dbfile from under a root resulted into root/dbfile – Transformation

Answer 6

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

Dish

Answer 7

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.

Obeng

Answer 8

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

Garthgartner

Answer 9

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

Hartsfield

Answer 10

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

Beshrew

Answer 11

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

Metamathematics

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? – Pallet

including the full connection string (username, password, port, etc.) was extremely useful to me – Snelling

Answer 12

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.

Sutlej