21. Using Slony-I for PostgreSQL Upgrades

A number of people have found Slony-I useful for helping perform upgrades between major PostgreSQL releases ( e.g. which mandates running initdb to create a new database instance) without requiring a substantial downtime.

The "simple" way that one might imagine doing such an upgrade would involve running pg_dump on the database running the older version, and then submit the results into a psql session connected to a database instance running the newer version. Unfortunately, the time consumed from start to end, for this approach, may be prohibitive. For a database containing 40GB of data with numerous indices, the process required involves:

• Stop all applications that might modify the data

• Start the pg_dump, and load that into the new database

• Wait 40 hours for the dump/load to complete

• Point "write" applications to the new database

And note that this led to a 40 hour outage.

Slony-I offers an opportunity to replace that long outage with one a few minutes or even a few seconds long. The approach taken is to create a Slony-I replica in the new version. It is possible that it might take much longer than 40h to create that replica, but once it's there, it can be kept very nearly up to date.

When it is time to switch over to the new database, the procedure is rather less time consuming:

• Stop all applications that might modify the data

• Lock the set against client application updates using LOCK SET

• Submit the Slonik command MOVE SET to shift the origin from the old database to the new one

• Point the applications at the new database

This procedure should only need to take a very short time, likely bound more by how quickly you can reconfigure your applications than anything else. If you can automate all the steps, it might take less than a second. If not, somewhere between a few seconds and a few minutes is likely.

Note that after the origin has been shifted, updates now flow into the old database. If you discover that due to some unforeseen, untested condition, your application is somehow unhappy connecting to the new database, you could easily use MOVE SET again to shift the origin back to the old database.

If you consider it particularly vital to be able to shift back to the old database in its state at the time of the changeover, so as to be able to outright roll back the whole change, and would like as well the ability to shift back to the old version (with any updates since the changeover), the following steps would accomplish that:

• Prepare two Slony-I replicas of the database:

  • One running the new version of PostgreSQL

  • One running the old version of PostgreSQL

  Thus, you have three nodes, one running the new version of PostgreSQL, and the other two the old version.

• Once they are roughly "in sync", stop all applications that might modify the data

• Allow them to get in sync, then stop the slon daemon that has been feeding the subscriber running the old version of PostgreSQL

  You may want to use UNINSTALL NODE to decommission this node, making it into a standalone database, or merely kill the slon, depending on how permanent you want this all to be.

• Then use MOVE SET to shift the origin, as before.

Supposing a "small" disaster strikes, you might recover back to the node running the old database that has been seeing updates come through; if you run into larger problems, you would have to abandon the two nodes and go to the one which had been shut off.

This isn't to say that it is routine to have the sorts of problems that would mandate such a "paranoid" procedure; people worried about process risk assessments can be reassured if you have choices like this.