Upgrading Slony-I 2.0.x to latest version 2.1.x

Posted on by Raghav
Slony-1 2.1 has very good fixes and new features like adding Bulk tables, improvement on WAIT FOR with Merge Set/Move Set, support for TRUNCATE on replicating tables and many more. Am using Slony-I 2.0.7, so thought of upgrading it to latest version. Upgrading Slony-I is very simple and it can be achievable in few steps. My upgrade procedure assumes there is already Master/Slave setup with Slony 2.0.7.

Backup Plan:
1. Backup the existing slony schema (_slonyschema) of master/slave
2. Backup the OLD Slony Binaries
3. Backup all initially creates slony configuration files.

Upgrade Procedure:
1. Stop all running slon proces on all nodes.
2. Install new Version of Slony 2.1.x binaries.
3. Execute SLONIK upgradation script
4. Start slony with new binaries on all nodes.

Link: http://slony.info/documentation/2.1/slonyupgrade.html

Current PostgreSQL & Slony version:

repdb=# select substr(version(),1,26) as "PostgreSQL-Version",_myrep.slonyversion();
     PostgreSQL-Version     | slonyversion
----------------------------+--------------
 PostgreSQL 9.1.3 on x86_64 | 2.0.7
(1 row)

Install/Configure Latest version of Slony-I 2.1.x source

 wget http://main.slony.info/downloads/2.0/source/slony1-2.1.0.tar.bz2
 ./configure --prefix=/opt/PostgreSQL/9.1/bin --with-pgconfigdir=/opt/PostgreSQL/9.1/bin
 make
 make install

After installation, you can find three executables slon, slonik & slon_logshipper under "/opt/PostgreSQL/9.1/bin/bin".  

-bash-4.1$ ./slon -v
slon version 2.1.0

Upgradation Script:

## Upgrade script

cluster name = myrep;
node 1 admin conninfo='host=localhost dbname=postgres user=postgres port=5432';
node 2 admin conninfo='host=localhost dbname=repdb user=postgres port=5433';
UPDATE FUNCTIONS (  ID = 1 );
UPDATE FUNCTIONS (  ID = 2 );

Note: Update all the nodes with UPDATE FUNCTIONS. I have two nodes Master(5432) and Slave(5433).

Execute the script:

-bash-4.1$ slonik upgrade_207_201.slonik

Start the slony process with new binaries and check for the changes.

postgres=# select substr(version(),1,26) as "PostgreSQL-Version",_myrep.slonyversion();
     PostgreSQL-Version     | slonyversion
----------------------------+--------------
 PostgreSQL 9.1.3 on x86_64 | 2.1.0
(1 row)

You can see my slony version has been upgraded to latest. You can also perform health check on the schema with a function provided by Slony-I in their documenation. Health Check function should return TRUE, else somewhere your PG & Slony catalogs are damaged.
Function link: http://slony.info/documentation/2.1/function.slon-node-health-check.html

postgres=# select node_health_check();
 node_health_check
-------------------
 t
(1 row)

–Raghav