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?
Postgresql – Disaster Recovery for PostgreSQL 9.0
disaster recoverypostgresql
Best Answer
First, repeat after me:
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_basebackupdocumentation for how). Note you cannot use a logical backup (i.e. frompg_dumpfor 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.