0

We have a number of PostgreSQL 9.0 servers. We use binary replication to have a host standby instance of those. The only problem is that is someone drops the master, with or without intentions, this will cascade to the replicas as well. I'm looking at the possible ways to avoid this. One possible option in seems to be Point in Time Recovery. I'm just wondering what could be a good design for this. Any ideas? Let's assume the master is compromised and we lose everything we have there. How can we avoid losing the replica or at least have a way to bring it back if it's dropped?

1
  • When you say "drops the master", I presume you mean "drops a critical table/database on the master server" ? If so, your options are (a) periodic dumps and (b) PITR. – Craig Ringer Aug 15 '13 at 6:11
2

First, repeat after me:

A replica is not a backup

That is your first problem. Do not use replication as a substitute for backups. Replication is a useful part of a disaster recovery strategy. It is not a useful disaster recovery strategy.

For PostgreSQL 9.0.x I would recommend that you take a look at barman, an open source backup and archive manager for PostgreSQL by 2ndQuadrant. This helps manage some of the details so you don't have to. (Full disclosure: I am a principal consultant for 2ndQuadrant.)

This being said, barman is only helpful if you are relatively familiar already with the base backup/wal shipping approach (which is what you want so you can roll back human errors like this). This is necessary for PITR to work.

In this approach you set up two things. The first is a base backup (see the pg_basebackup documentation for how). Note you cannot use a logical backup (i.e. from pg_dump for this). The second is to set up continuous archiving so that you can take your WAL segments and use them for PITR. The specifics here depend highly on your environment and so you want to take a look at the docs and design something that works for you. True to the UNIX way, these are largely tools for building your backup and recovery solution. There isn't much more that can be said about this without something more specific, but start there and build a solution for your business out from it.

1
  • BTW, there's also a "delayed replication" feature in the works that may be interesting for this. It's designed to let a hot standby replica intentionally lag a few hours behind the master, applying WAL only on a delay. It might make 9.4, though I doubt it. It's no substitute for proper backups (as you note, PITR is the best solution here) but would certainly be useful in this user's situation. – Craig Ringer Dec 13 '13 at 3:38

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.