When executing a pg_dump which should I use and why?

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


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

  • 1
    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): > -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

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.

Your Answer

