Which pgdump format is best for small storage size and fast restore?

Question

This a first time foray in PostgreSQL backups (db dumps) and I've been researching the different pgdump formats, other pgdump options, and pgdumpall. For a Postgres beginner looking at taking an hourly dump (will overwrite previous dump) of two databases that contain table triggers and two different schemas in each db, what would be the backup format and options to easily achieve the following:

1. Small file size (single file per db or ability to choose which db to restore)
2. Easy to restore as clean db (with & without same db name[s])
3. Easy to restore on different server (user maybe different)
4. Triggers are disabled on restore and re-enabled after restore.

Include example commands to backup and restore.

Any other helpful pgdump/pgrestore suggestions welcome.

Goren

Never disable triggers - they do not affect backup anyway but your database could become inconsistent if you disable them. Do not overwrite previous dump - always have more than one backup. – Bottali

Answer 1

This command will create a small dmp file which includes only structure of the database - tables, columns, triggers, views etc.. (This command will just take few minutes)

pg_dump -U "dbuser" -h "host" -p "port" -F c -b -v -f ob_`date +%Y%m%d`.dmp dbname

**ex:** pg_dump -U thames -h localhost -p 5432 -F c -b -v -f ob_`date +%Y%m%d`.dmp dbname

This command will take the backup of complete database

pg_dump -h localhost -U "dbuser" "dbname" -Fc > "pathfilename.backup"

**ex:** pg_dump -h localhost -U thames thamesdb - Fc > "thamesdb.backup"

and for restore you can use:

pg_restore -i -h localhost -U "user" -d "dbname" -v "dbname.backup"

**ex:** pg_restore -i -h localhost -U thames -d thamesdb -v "thamesdb.backup"

to take backup of selected tabels(uses regular expressions) here

pg_dump -t '(A|B|C)'

for full details you can visit pgdump help page there are many options out there

Mclean

Answer 2

If you want to take your backups hourly, I would think you should be using log archiving instead of pg_dump.

Kovacev

could you explain in more detail and what would need to happen to backup and also restore using log archiving? – Goren

@Kovacev that would be great if u could explain – Mclean

I don't think I can do a better job than the docs already do: postgresql.org/docs/current/static/continuous-archiving.html – Kovacev