28

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>

1
  • 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): > -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 Oct 22, 2012 at 14:57

1 Answer 1

27

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.

1
  • 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. Sep 29, 2020 at 19:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .