1

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.

Improve this question
1
  • 1
    For a database of that size, I would rather use WAL archiving for backups. Or solutions like pgbackrest, barman or pg_probackup
    – user1822
    Nov 28, 2019 at 9:23

1 Answer 1

Reset to default
1

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

Improve this answer
4
  • 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, 2019 at 4:18
  • Can you update your post with the steps that you executed?
    – richyen
    Dec 16, 2019 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, 2019 at 3:38
  • tried above steps after making following changes in pg_hba.conf file, # replication privilege. local replication all trust #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident
    – JineshJK
    Dec 17, 2019 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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