[bmurphy]

Close. Postgres has traditionally focused on letting the community provide replication solutions, but we've been screaming so long for one that "just works" that they're finally getting around to building it.

I wouldn't say it covers 95% of the use cases. In fact, I find it very limiting, but if all you need is to make sure your database is backed up it's a great step in the right direction.

That being said, the topology limitations suck. If you lose your master server, you are running in a degraded environment until you can rebuild ALL slaves from scratch. This really sucks if you have a very large database that takes a long time to rebuild.

Postgres 9.0 replication is a great first step, but it's only a step. There's still a lot of work to be done.

[wiredfool]

  If you lose your master server, you are running in a
  degraded environment until you can rebuild ALL slaves from
  scratch. This really sucks if you have a very large
  database that takes a long time to rebuild.

I just tested this, and it's not quite true.

Assume you have 1 master, and 2 slaves(1=new master/2=additional slave) each replicating directly from the master. If they're in sync, then if you:

  * shutdown the master
  * shutdown slave1/new master
  * remove slave1's recovery.conf file
  * assign slave1 old master's ip address
  * start slave1 as new master

then slave2 will follow and you'll still have a replicated set. If, instead of doing the shutdown/remove recovery.conf/startup dance, you touch the trigger file, a new timeline is created and the slave2 isn't going to follow that timeline. I haven't found a way to make a slave follow to a new timeline yet.

[bmurphy]

Yes, that's exactly what I was referring to, you're getting a new timeline. You need to rebase the slaves and you have no real-time backup for a few hours. Sucks. You have to be very careful, and there are all sorts of ways you can break it. It's not something I'd want to rely on right now when under fire.

[wiredfool]

Looks like there's at least one person who's looking at doing something about the timeline issue: http://blog.tapoueh.org/articles/blog/_Back_from_PgCon2010.h... .

I'd bet that there are some pretty basic failure modes in the timeline preserving case where the slaves could be out of sync when one was promoted, and then that out of syncness could be propagated. I suspect that a missing transaction on the new master would trigger some sort of duplicate transaction oid issue, but a missing transaction on the slave would be more likely to go unnoticed.

I've just tested a case where I killed the master when I was running transactions against it, and it seems to work as well as the shutdown case. Bringing up the slave is where the timeline gets incremented.

I suppose I could throw one of the replication connections through a ip delay and intentionally introduce replication lag..

edit: Looks like http://www.postgresql.org/docs/9.0/static/warm-standby.html#... , section on monitoring gives the pg_current_xlog_location on the primary and the pg_last_xlog_receive_location on the slaves, which would at least tell one if the slaves are in sync, and if not, which one is the farthest ahead.

So, the process could be:

  * master dies. either gracefully or not.
  * all slaves are queried
  * most advanced one gets shutdown, promoted by ip and removing recovery.conf then started
  * all the other slaves should track those changes and bring themselves up to date.
  * Resync the old master as a new slave.

[bmurphy]

I just ran the same test. Set up a 2 slave cluster, killed slave 2, then killed the master. Ran pg_last_xlog_receive_location on each slave, picked the one that was furthest along in the timeline, promoted it to be the new master and the other slave caught up as expected.

That's great. That's the first time I've been able to get this to work smoothly. I'm sure there are still ways to break it but this gives me hope.