1

We have PostgreSQL 9.3.9 streaming replication between Master DB and Slave DB. Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both while replicating? Am I always will get persistant and ready to work or restore Slave DB while replicating?

  • 1
    There's no such thing as "9.*" when it comes to replication. You need a major version match, e.g. 9.4 => 9.4, or 9.3 => 9.3, etc. It'll refuse to replicate otherwise. Please show the exact output of the SELECT version() command on both master and replica. – Craig Ringer Oct 7 '15 at 7:42
  • There are the same versions of course both on master and slave. We have now 9.3.9. I've just meant versions of PostgreSQL from 9th that have ability to replicate out-of-box. – m1848 Oct 7 '15 at 8:07
  • OK. The reason I ask is that there were some nasty bugs in 9.3 related to multixacts, but they're fixed by 9.3.9. So if you were asking about a specific incident where you'd experienced corruption the version was extremely relevant. That is the only incidence I can think of where I've encountered replication corruption. – Craig Ringer Oct 7 '15 at 8:13
  • 2
    Can you elaborate on this question? Are you asking about a specific incident? Or more about whether certain things can happen, hypothetically, in a "how does it work" manner? What exactly do you mean by "loss of all xlog files". On the master? How would that happen without losing the master entirely? I think you need to explain these scenarios a fair bit more, and be more detailed about what you want to know. (Please edit the question then comment here when done) – Craig Ringer Oct 7 '15 at 8:15
4

Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both while replicating?

Yes, there are.

Anything that corrupts the transaction logs on the master before they're archived or streamed to the replica will do it - and I've seen that happen personally. The root cause was administrator error, in that the admin kill -9'd the postmaster, deleted postmaster.pid and then started a new postmaster up. Nonetheless, the corrupt WAL was replicated.

Memory errors that corrupt shared memory, flip a bit, etc could do it.

Severe filesystem problems affecting the transaction logs could do it.

A nasty PostgreSQL bug could do it. We actually had one of those in 9.3 too, though it's fixed prior to 9.3.9. To learn more than you ever wanted to about it, search for "multixact wrap-around".

This is why I strongly recommend that you keep point-in-time recovery using WAL archives and periodic base backups. The pgbarman utility helps with this.

Periodic text-format dumps can also be a good idea as extra insurance.

Am I always will get persistant and ready to work or restore Slave DB while replicating?

No, because some kinds of corruption can be replicated to the slave.

The most obvious one is if you DROP TABLE critical_production_data; the system will obediently replicate that to the slave before you can even say "Oh sh...".

Point in time recovery is important.

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.