0

For a small startup that doesn't have much expected traction in the very beginning. We are currently working with only one postgres database without replications and at risk of failures. I've done some research and realized that the primary methods everyone uses nowadays are either master-master or master-slave replications. My question is in general for small guys like us, what would be the urgency and need to have these replications in place for the additional cost of servers? Would we be able to survive on a single server? As well, would master-master or master-slave suit us better?

Thanks!

2
  • Your DB server shouldn't be "prone to failures" at all, if you mean that it actually fails sometimes. If you mean "at risk of failure" then sure. Everything is. It's always a matter of balancing risk. Personally I think you should have at minimum PITR backups (look at PgBarman) and nightly pg_dump backups. Work on high availability when you need it. Apr 20, 2014 at 3:08
  • Yes I meant at risk of failure because there is only one instance live. To be honest this is the first time I have heard of PITR and I looked briefly at the website of PgBarman. I would like to know if there is a typical industrial guideline of cost between having PITR backups vs database replication. And if I'm in the wrong direction, I would like to know if database replication is for data recovery or is it simply for high availability?
    – gtr32x
    Apr 20, 2014 at 3:12

1 Answer 1

4

There are really several different things in play here:

  • Recovery to the current state if the DB server is lost. Synchronous replication is your only option here, but it comes with some risks - if the replica goes down or fails the master will stall, and if there's much latency then performance will suffer due to the need for the replica to confirm before commits are accepted. Setting synchronous_commit = off for transactions that aren't crucial can help.

  • Recovery to a recent state if the DB server is lost. Asynchronous replication and is what you want here. It doesn't guarantee to be up to date to the latest committed transaction, but has less performance impact.

  • Recovery to some previous point in time to cope with accidental deletions, corruption, etc. Your two tools here are dumps and PITR (wal archiving), with different advantages. PITR lets you recover to a finer-grained point in time ("what was in my customer table just before Bob dropped it?"), but doesn't protect as effectively against corruption of database storage and can require more disk space. PgBarman helps automate PITR, as does the tool WAL-E. Dumps can be more compact and are portable between PostgreSQL versions and architectures, but are coarser grained snapshots of the DB state - you can't have an incremental dump in Pg.

  • High availability. HA isn't so much for protection against data loss as to maintain uptime. In PostgreSQL it's implemented with replication and failover. repmgr is one tool that can help you manage this, in combination with something like PgBouncer or HAProxy for connection routing.

Personally I think your minimum requirement is to use PgBarman or WAL-E to archive WAL for PITR, and do nightly dumps as additional redundancy for backup purposes.

Look into synchronous replication if you cannot afford to possibly risk losing a transaction (e.g. if you're processing financials/payments).

Investigate HA if you reach the point where downtime becomes unacceptable.

1
  • Really appreciate the detailed answer! I will go the path of PITR first and HA later if needed.
    – gtr32x
    Apr 21, 2014 at 5:02

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.