2

I'm looking into PostgreSQL replication solutions. I know two varieties of these solutions

  1. Low level - which involves streaming log-shipping, hot-standby features available in PostgreSQL 9.x
  2. High level - Slony, Londiste that work at SQL query level

My database is not very busy and huge (at least now). But I want to avoid downtimes due to Amazon EC2 failures (like the one that happened recently). My solution is to maintain a slave in a different Availability zone that will remain in sync with my master database instance. That way I can failover to it when master goes down. One point to consider is that, this will require continuous replication of data from master to slave, which will be network traffic across EC2 availability zones. It's not free. It costs 1 cent per GB right now, but after reading some calculations in the PostgreSQL cookbook, I learnt that costs can grow very high even if DB traffic is low. For instance in the "Hot physical backup and continuous archiving" chapter of the "PostgreSQL 9 Administration Cookbook" I read this:

If archive_timeout is set to 30 seconds, we will generate a minimum of 2*60*24 = 2880 files per day, each 16 MB in size, so a total volume of 46 GB per day (minimum)

[and that I assume with minimum traffic on the DB]

My only requirement is that, each write SQL query that executes on the Master server, should be replayed on the Slave server. If this is done on event callback, then that will be perfect because there will be data transfer between master and slave only when DB is being modified and not every 30 seconds or so even if no changes to DB have occured.

Therefore I thought Londiste might be the solution for me, but I am not 100% sure that it works that way.

What do you suggest?

2 Answers 2

4

After a week of research I believe Streaming Log Shipping and Hot Standby configuration of PostgreSQL instances serves my needs of instant replication (minimum window of data loss), yet low network traffic. I've written a detailed blog post on how I set it up.

There might be other solutions too, using 3rd party tools like pgpool, but I didn't have much success with them.

2
  • I cannot seem to get to your blog. Is it still up? Sep 14, 2017 at 8:40
  • SSL compression, if enabled, can also significantly reduce the transfer requirements - by ~70% in my experience. Newer versions of OpenSSL may need it to be explicitly enabled by configuring compilation with zlib-dynamic (rather than no-zlib), and/or by cutting out the setting of SSL_OP_NO_COMPRESSION in ssl_lib.c. It is scheduled to be removed eventually (and hopefully replaced with something on PostgreSQL's end), but until then it can be useful if you are on a bandwidth budget and are willing to balance that against the security concern (rather theoretical IMO, given enough traffic). Feb 11, 2018 at 22:43
2

Have a look at pgpool. We're using it in production at my work and so far we're very satisfied. You obviously still want to make backups, since it doesn't protect you against SQL query screwups, but it does the synchronization/replication thing beautifully.

2
  • That seems like the right tool. I'm trying it now. Thanks.
    – Jayesh
    Jul 20, 2012 at 5:46
  • The reason we're using it is because the application using the database is so security sensitive that we can't put it on a SAN disk. In this case, we've got the database on local disks on two separate machines in separate server halls, and using pgpool we can have a redundant environment without having to use any standard clustering software. It sounds very like what you will be doing.
    – Jenny D
    Jul 20, 2012 at 6:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .