Postgresql – How to take backup of multiple DB in a specific point of time

backuppostgresqlpostgresql-9.6snapshot

My test setup is having 3 databases in postgresql-9.6 instance, 2 DBs consumed by 2 live Web apps.
I am using pg_dump to take hot backup of all databases. In order to keep CPU load under threshold, backup script perform pg_dump in sequence.

pg_dump -U postgres -Fc <db-1> > <db-1>.dump
pg_dump -U postgres -Fc <db-2> > <db-2>.dump
pg_dump -U postgres -Fc <db-3> > <db-3>.dump

All three database share information like User details, Subscription, Task state, etc.
As the backup is taken in different time period, in the event of Data recovery, the database will end up with inconsistent data across all three DB. One of the DB size is 180GB+ and the dump time for this DB alone would take 30 min.

How to take backup of all databases from a specific time period.

I am not sure how to use the Snapshot feature in postgresql.

Best Answer

You can try using the --snapshot method (https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/), but it might be a bit more complicated than you'd like.

My personal recommendation is to create a cluster-wide backup with pg_basebackup (or a third-party app that basically does the same thing, like barman, pg_backrest, etc.)

Related Question