2

• What are ideal practices for taking PostgreSQL logical backup using pg_dump?

• Is it ideal to take backup from a standby/slave node? If replication lag is less than 200ms

• Is it ideal to take backup from standby/slave node, and is there any specific configuration we need to change?

• Which method is a good way for taking backups logical backup or physical backup? where DB is getting updated frequently. As a backup is taken for disaster recovery which method is the faster and better backup and disaster recovery(restore).

updated
Our current database size is 5GB and replication is on hot standby mode.
We are running the Backup script on slave node but it takes remote backup from the master node every 30 minutes.
The reason I created this question is to understand when the backup is running some COPY statements takes 6 mins to complete, even though it will not affect other transactions on DB, is there any other issues occurs if a statement is taking more time.

Tibin Geo k k
  • 53
  • 10
  • 1
    How big is your PG database? What is HW config of your instance? How big is rate of changes on database over presumed time of backup? We went from parallel pg_dump backup on DB up to ~300GB to pg-barman/ pg_basebackup backup on DB ~4.5TB. So please add more info into your question so you can get more useful answer. – JosMac Jan 10 '18 at 12:43
  • pg_dump is not really a "backup tool". It creates a "snapshot" of the data at a specific point in time. If you want to backup for disaster recovery, you should consider backup tools like pgbarman or pgbackrest –  Jan 11 '18 at 09:39
  • @a_horse_with_no_name what if I want to take only logical backups as I want backup only of 1 DB, can I achieve it by tools like pg-barman. – Tibin Geo k k Jan 11 '18 at 09:47
  • @JosMac I have updated my question added more info – Tibin Geo k k Jan 15 '18 at 08:10
  • pg-barman can backup only whole PostgreSQL instance – JosMac Jan 15 '18 at 09:09

1 Answers1

4

I thought about what you wrote and here are some ideas for you:

  1. If you need backup which will really be consistent to some point in time then you must use pg_basebackup or pg_barman (internally uses pg_basebackup) - explanation is in 1. link below. Latest pg_basebackup 10 streams WAL logs so you backup also all changes done during backup. Of course this backup takes only the whole PG instance. On the other hand it does not lock any table. And if you do it from remote instance then it causes only small CPU load on PG instance and disk IO is not as big as some texts suggests. See links 4 about my experiences. Restoration is quite simple - see link 5.
  2. If you use pg_dump you must understand that you have no guarantee that your backup is really consistent to the point in time - again see link 1. There is a possibility to use snapshot of the database (see links 2 and 3) but even with it you cannot count on 100% consistency. We used pg_dump only on our analytical database which loads new only 1x per day (yesterdays partitions from production database). You can speed it with parallel option (works only for directory backup format). But downside is much higher load on PG instance - higher CPU usage, much higher disk IO. Even if you run pg_dump remotely - in such case you save only disk IO for saving of backup files. Plus pg_dump needs to place read lock on tables so it can collied either with new inserts or with replication (when taken on replica). But when your database reaches hundreds of GBs then even parallel dump can takes hours and in that moment you would need to switch to pg_basebackup anyway.
  3. pg_barman is "comfortable version" of pg_basebackup + it allows you to prevent data loss even when your PG instance crashes very badly. Setting it to work requires more changes but it is definitely worth it. You will have to set WAL log archiving (see link 6) and if you PG is <10 you will have to set "max_wal_senders" and "max_replication_slots" (which you need for replication anyway) - everything is in pg-barman manual although description is not exactly great. pg_barman will stream and store WAL records even between backups so this way you can be sure that data loss in case of very bad crash will be almost none. But making it work can take many hours because descriptions are not exactly good. pg-barman does both backup and restoration with its commands.

Your database is 5GB big so any backup method will be quick. But you have to decide if you need point in time recovery and almost zero data loss or not - so if you will invest time to setting pg-barman or not.

Links:

  1. PostgreSQL, Backups and everything you need to know
  2. Review for Paper: 14-Serializable Snapshot Isolation in PostgreSQL - about snapshots
  3. Parallel dumping of databases - example how to use snapshot
  4. pg_basebackup experiencies
  5. pg_basebackup - restore tar backup
  6. Archiving WAL logs using script
JosMac
  • 2,164
  • 1
  • 17
  • 23
  • Thank you for this answer, 1 clarification I wanted to make is that, In your 2nd point you said `pg_dump needs to place read lock on tables so it can collied either with new inserts`. Is `pg_dump` is creating the lock on transactions while taking the backup?, if we have a 1 table which takes 10 minutes to take the backup ( `COPY` statement), will the table be blocked for other transactions, will this affects application if app is writing/reading from that table at same time?> – Tibin Geo k k Jan 17 '18 at 11:28
  • I tested it to and pg_dump starts COPY cmd which places `AccessShareLock` on the whole table to prevent changes of structure, drop of table etc. Test showed what docu says - https://www.postgresql.org/docs/current/static/explicit-locking.html - it does not interfere with update, delete, insert on usual db instance. Different problem is with replication on replica - when COPY is taking too long you will see "conflict with recovery" error - https://stackoverflow.com/questions/14592436/postgresql-error-canceling-statement-due-to-conflict-with-recovery But consistency to PIT problem still remains – JosMac Jan 19 '18 at 10:24