Remastering without restarting

Thanks to Streaming-Only Remastering, PostgreSQL 9.3 has been a boon to high-availability setups and maintenance  You can re-arrange your replicas however you like; remastered, in a tree, in a ring, whatever.  However, there's been one wart on this free reconfiguration of Postgres replication clusters: if you want to change masters, you have to restart the replica.

This doesn't sound like a big deal, until you think about a cluster with load balancing to 16 read-only replicas.  Every one of those you restart breaks a bunch of application connections.   Looking at how timeline switch works, it didn't seem like there was even a good reason for this; really, the only thing which seemed to be blocking it was that primary_conninfo comes from recovery.conf, which only gets read on startup.  I'd hoped that the merger of recovery.conf and postgresql.conf would solve this, but that patch got punted to 9.5 due to conflicts with SET PERSISTENT.

So, I set out to find a workaround, as well as proving that it was only the deficiencies of recovery.conf preventing us from doing no-restart remastering.  And I found one, thanks to the question someone asked me at pgDay NYC.

So, in the diagram above, M1 is the current master.  M2 is a replica which is the designated failover target.  R1 and R2 are additional replicas.  "proxy" is a simple TCP proxy; in fact, I used a python proxy written in 100 lines of code for this test.  You can't use a Postgres proxy like pgBouncer because it won't accept a replication connection.

Remastering time!

1. Shut down M1
2. Promote M2
3. Restart the proxy, now pointing to M2

And the new configuration:

 

But: what happened to R1 and R2?  Did they remaster without restarting?  Yes, indeedy, they did!

LOG:  entering standby mode
LOG:  redo starts at 0/21000028
LOG:  consistent recovery state reached at 0/210000F0
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 0/22000000 on timeline 6
LOG:  replication terminated by primary server
DETAIL:  End of WAL reached on timeline 6 at 0/2219C1F0.
FATAL:  could not send end-of-streaming message to primary: no COPY in progress
FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "172.31.11.254" and accepting
                TCP/IP connections on port 9999?

LOG:  fetching timeline history file for timeline 7 from primary server
LOG:  started streaming WAL from primary at 0/22000000 on timeline 6
LOG:  replication terminated by primary server
DETAIL:  End of WAL reached on timeline 6 at 0/2219C1F0.
LOG:  new target timeline is 7
LOG:  restarted WAL streaming at 0/22000000 on timeline 7

Not only does this provide us a new remastering workaround for high-availability configurations on 9.3, it also shows us that as soon as we get around to merging recovery.conf with postgresql.conf, restarting to remaster can be eliminated.

Replication Auto-Failover at SFPUG coming up

One of the things we've been lacking in Postgres world for a while is a utility which can smoothly handle auto-failover in a replicated cluster, and integrates with the users' other high availability server management tools.  That is, while our built-in replication handles failover and supports all the configurations you could want, it's been left up to the user to implement their own failover management tools.  Well, I've taken a crack at changing this situation: introducing HandyRep.

Now, HandyRep is still somewhat of a work in progress (consider it a version 0.8), but I'll be talking about it Tuesday at SFPUG.  We'll have live video of the presentation and demo, starting at around 7:15pm PST.

The goals of the HandyRep project are:

• Supports configurable automated failover logic for high availability
• Supports manual failover, replication monitoring, node provisioning, and remastering (with 9.3).
• Written entirely in Python, for easy hackability
• All functionality is accessible as an embeddable Python library.
• All functionality accessible via a REST interface when using the included Flask application with a web server.
• Supports user-supplied plugins to let it interface with applications in your environment.
• Can be run from a 3rd-party server (such as a pgbouncer server)
• Designed to minimize the per-node installation/configuration requirements. No "agent" is installed on the nodes.
• Highly configurable to support  whatever the user's PostgreSQL configuration and file arrangement is on each node.
• Supports multiple replicas and remastering (requires 9.3)
• Integrates 3rd-party connection failover, such as pgbouncer, HAProxy, or BigIP.
• Integrates with configuration management utilities, such as Puppet, Chef, CFEngine and SaltStack.
• Integrates with archiving utilities such as WAL-E and Barman
• Licensed under the same terms as PostgreSQL itself.

Now, not all of these goals are satisfied yet -- there's still a lot of plugin-writing to do.  However, we've made a good start and expect to be deploying HandyRep in production before the end of the month.  Tune in to SFPUG to find out more!