Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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.

share|improve this answer
    
That seems like the right tool. I'm trying it now. Thanks. –  Jayesh Jul 20 '12 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 '12 at 6:45
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.