0

We are planning to implement disaster recovery (DR) for a Postgres DB. What I understood from my basic research:

  1. pg_basebackup shall be used instead of pg_dumpall (Performance perpective) or like to hear any other alternate approach...
  2. You don't need the standby server and can be move to any physical volume in compressed format
  3. Backed up can be restored cp command and with postgres.conf parameter we can do the the point in time recovery

I would like to know the best practices which can be followed while doing the backups and what could be the failure scenarios while restoring which can be avoided?

1
  • I think using one of the ready made tools for this is a much better approach. Look into barman, pg_backrest or pg_probackup - they have already solved all those problems (and then some)
    – user1822
    Jul 6, 2021 at 6:40

1 Answer 1

1

Fundamentally, a DR scenario might fall into two basic categories:

  • You want a "hot standby" solution, wherin in the case of your primary database failing you can switch over to the standby server. PostgreSQL achieves this through it's hot standby functionality - which also provides you with a secondary server that you can shuttle read only queries to in some situations (https://www.postgresql.org/docs/13/hot-standby.html)

  • You want to recover to a point in time from a backup (to handle the case where, for example, data was accidentally modified or deleted that you wish to restore.) In this case, you would need to create a "base backup" using pg_basebackup and then archive all the subsequent write-ahead log files (WAL files) in order to restore to a specific transaction or point in time.

The pg_dump/dumpall mechanisms are limited to backing up and restoring data to/at the point in time that you made the backup (i.e., you cannot advance "forward" from that point)

No third party software is required to accomplish either of these tasks - and it's fairly straightforward to setup both cases (it's all built into postgresql/provided by postgresql utils.) Third party utilities exist to try and make it easier though.

Tools that can assist you in these backup/replication scenarios (barman being one for pure DR, and repmgr being another for streaming replication.)

Your Answer

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

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