postgresql where does the output of pg_dump go

Dariush Jafari picture Dariush Jafari · Jul 24, 2014 · Viewed 72.7k times · Source

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

Answer

mivk picture mivk · Aug 26, 2018

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.