Postgres backup as tar.gz

Question

I'm trying to backup Postgres with pg_dump.

pg_dump $POSTGRES_DB -U postgres > $POSTGRES_DB-$(date +%d-%m-%y_%H-%M).sql

What is the way to backup it as tar.gz?

Answer 1

Some options:

---Custom format which is compressed
pg_dump -Fc $POSTGRES_DB -U postgres > $POSTGRES_DB-$(date +%d-%m-%y_%H-%M).out

---Postgres tar format compressed with gzip
---This will need to be uncompressed first then fed to pg_restore as 
---pg_restore does not understand compressed tar format.
pg_dump -Ft $POSTGRES_DB -U postgres | gzip > $POSTGRES_DB-$(date +%d-%m-%y_%H-%M).tar.gz

---Postgres plain text compressed with gzip
pg_dump $POSTGRES_DB -U postgres | gzip > $POSTGRES_DB-$(date +%d-%m-%y_%H-%M).tar.gz

--Alternate plain text compression where -Z is the level of compression(0..9). 
pg_dump $POSTGRES_DB -U postgres -Z 5 > $POSTGRES_DB-$(date +%d-%m-%y_%H-%M).gz

For more detail on what the options mean see pg_dump. The -Fc option is probably the most useful, in my opinion, as it allows you more options on what to restore. You can restore everything or portions using the options shown here pg_restore.