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.


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.)

  • I tried steps mentioned in that link, but it throws error for me. I am not sure what logic I am missing here. ERROR: invalid snapshot identifier: "0015EBDE-1" – JineshJK Dec 16 '19 at 4:18
  • Can you update your post with the steps that you executed? – richyen Dec 16 '19 at 7:16
  • Steps that I followed, 1. BEGIN; 2. SELECT pg_export_snapshot(); 3. BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; 4. SET TRANSACTION SNAPSHOT '000003F1-1'; 5. COMMIT; 6. psql "replication=database dbname=dbname" After 4th step I got an error as follows, "ERROR: current transaction is aborted, commands ignored until end of transaction block" After 5th step I got an error as follows, "psql: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "root", SSL off" – JineshJK Dec 17 '19 at 3:38
  • tried above steps after making following changes in pg_hba.conf file, # replication privilege. local replication all trust #host replication postgres ident #host replication postgres ::1/128 ident – JineshJK Dec 17 '19 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.