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.