27

When executing a pg_dump which should I use and why?

pg_dump -U <user> <database> | gzip -c > backup.gz

or

pg_dump -F c -f backup.tar.gz -U <user> <database>

d4v3y0rk
  • 373
  • 1
  • 3
  • 8
  • 2
    In any case, don't use the `-d` option because it's deprecated since 8.4 and it has never meant to select the database to dump, it meant this instead (from [PG-8.3 doc](http://www.postgresql.org/docs/8.3/static/app-pgdump.html)): > -d > --inserts Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that > can be loaded into non-PostgreSQL databases. Also, it's not a good idea to suffix the filename of a dump in custom format (`-Fc`) with .tar.gz because it's **not** a gzipped tarfile. The custom dumps can only be processed – Daniel Vérité Oct 22 '12 at 14:57

1 Answers1

26

According to the pg_dump documentation the custom format is:

the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default

You can also select the compression level with the -Z option.

For simplicity I would definitely go for pg_dump -F c over piping to gzip.

pkhamre
  • 6,120
  • 3
  • 17
  • 27
  • 5
    I have a very small database now that is dumped with `-Fc`. It's only about 30k but running gzip on it compresses down to 20k. Not sure if that's overhead that would be insignificant in a larger database. – Joseph Sheedy Sep 29 '20 at 19:37