Hacker News Reader

Comment by agilord | Hacker News Reader

I am a big fan of postgresql, but I am always having issues with the cluster configuration part, e.g. failovers, automatic new master election and all these crazy administration about the wal shipping. Was there any improvement on this either in the code base or by a 3rd party? What do people use to handle larger postgresql clusters?

As a contrast, I really like Riak's ability to just work with one node down...

View on HN

The one big improvement happening in 9.3 is that synchronizing with a new master has become much easier. In earlier releases you had to more or less either use file based wal archive logging or you would have to rsync your whole data directory over, now the built-in replication protocol can handle this for you. We also got pg_standby now which is all you need to set up a new slave.

But yeah - if you need automatic failover and master election, you still need third-party tools. Some have had success with pgPool as a out-of-the-box solution (I haven't. I had severe reliability issues with pgPool. You might be more lucky), others produce their own scripts.

The process isn't complicated, it just requires you reading a lot of manpages and thinking ahead, but once you got the process down, postgres itself is reliable enough that its (admittedly limited) tools just work (which is a very good thing).

As long as Postgres doesn't do master-master replication, failover will always be a complicated topic to deal with.

In 9.3 they also made failover faster.

Try this: http://www.repmgr.org/

They do support automatic failover too. The docs are in the Github repo somewhere.

I agree - one of the areas it would be great to have some development work done around is making replication administration simpler and easier.

One feature which would be really nice to have is the ability to do a manual switchover, ie making the existing master into a replica and an existing replica into the new master.

Another poster mentioned repmgr which looks good but hasn't had a release in sometime ( https://github.com/2ndQuadrant/repmgr/blob/master/HISTORY ) with 2 new Postgres releases since, although there does seem some sporadic work on a new beta.

That is something that is listed for 9.3. You can have a M1 with R1, R2, R3.

Now you change R1 to master, and take M1 "offline", afterwards turn M1 back on, and have it become a slave to R1.

See http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3...

I am not sure whether it is exactly what you want, but I have just learned that there is something called postgres-xc.

Postgres-XC is a niche market mostly useful for things like BI setups and highly scalable transactional clusters. It's a very complex solution to very complex problems. If you need it, you know about it.

Reply on news.ycombinator.com