Skip to content

PostgreSQL upgrade from 8.1 to 9.1 using slony1 – Part 2


Before you start the upgrade of your progresql services you should already have your Postgresql 9.x servers configured and installed including any additional streaming replicated servers already streaming and running OK, however you will only be dealing with the Read/Write server (node 2 here) . I would also recommend some configuration adjustments to checkpoint segments for the initial loading.

Compile slony1 on both server-node-1 (8.x) & server-node-2 (9.x)

cd /home/somedirectory/
tar -xvf slony1-1.2.22.tar.bz2
mkdir /var/log/slony
chown -R postgres:postgres /var/log/slony
chown -R postgres:postgres /var/lib/pgsql/
cd slony1-1.2.22
# node 1 (8.x)
./configure --prefix=/var/lib/pgsql/data/ --with-perltools
# node 2 (9.x)
./configure --with-pgconfigdir=/usr/pgsql-9.1/bin --prefix=/var/lib/pgsql/9.1/data/ --with-perltools
make clean
make install


Note that –prefix= could vary on both nodes, now that Slony is installed

On node 2 (9.x) if you haven’t initialised the DB:

service postgresql-9.1 initdb

you should also create the new database with same credentials as node 1s database

su - postgres
/usr/bin/createdb --encoding UTF8 myDatabaseName

echo "alter user postgres with password 'xxxx';" | psql
echo "create user myDBUser nocreatedb createuser;" | psql
echo "alter user myDBUser with password 'xxxxx';" | psql
echo "create language plpgsql;" | psql


# again ,these commands will vary and you may or may not wish to use them at all , I prefer to set a postgres password for example.

Now on node-1 (8.1)


su - postgres
my-database.postgres:~> pg_dumpall -v --globals-only > useraccts.sql
my-database.postgres:~> pg_dump -s myDatabaseName > myDatabaseName.schema

transfer the two files created above to your node 2 (9.1) and put them somewhere sensible like /var/lib/pgsql/9.1/

On node-2 (9.1) Load the user accounts and schemas that you copied over into the clean database:

su - postgres
cd /var/lib/pgsql/9.1
psql < useraccts.sql
su - myDatabaseName
psql < myDatabaseName.schema

# Now this is the time to review your schema and indexes to ensure names and values are consistent , you may need to alter _seq numbered names for example. This is usually related to creating and renaming things during the lifespan of the old Database.

Ensure /var/lib/pgsql/9.1/data/etc/slon_tools.conf (on node 2) and /var/lib/pgsql/data/etc/slon_tools.conf (node 1) are the same.

This file is the key to the whole process and it may take several attempts to complete the replication properly by adjusting the file.If the replication failed , I found that it is better to start from a clean database on Node 2 and try again.

Here is an example sample with indexes used as keytables (dont ask!) and seq…

#My system MyDatabase
    require $ENV{"SLONYNODES"};
} else {

    $CLUSTER_NAME = 'replication';
    $LOGDIR = '/var/log/slony';
    # $APACHE_ROTATOR = '/usr/local/apache/bin/rotatelogs';
    # SYNC check interval (slon -s option)
    # $SYNC_CHECK_INTERVAL = 1000;
    # Which node is the default master for all sets?
    $MASTERNODE = 1;

    add_node(node     => 1,
             host     => '',
             dbname   => 'myDatabaseName',
             port     => 5432,
             user     => 'postgres',
             password => 'xxxx');

    add_node(node     => 2,
             host     => '',
             dbname   => 'MyDatabaseName',
             port     => 5432,
             user     => 'postgres',
             password => 'xxxx');



    "set1" => {

        "set_id" => 1,
        "table_id"    => 1,
        "sequence_id" => 1,

        "keyedtables" => {
                        'account' => 'account_idx1',
                        'aged_debt' => 'aged_debt_idx1'


        # Sequences that need to be replicated should be entered here.
        "sequences" => ['account_id_seq',



if ($ENV{"SLONYSET"}) {
    require $ENV{"SLONYSET"};

# Please do not add or change anything below this point.


Obviously you’ll need to replace with your own tables and database names etc, also ensure that the /etc/hosts is set so that names of the host resolve OK on both nodes.

On node-1 (8.x)

cd /var/lib/pgsql/data/bin
./slonik_init_cluster > /tmp/init.txt
cat /tmp/init.txt | ./slonik
./slon_start 1 --nowatchdog

node-2 (9.x)

cd /var/lib/pgsql/9.1/data/bin
./slon_start 2 --nowatchdog

node-1 (8.1)

cd /var/lib/pgsql/data/bin
./slonik_create_set set1 > /tmp/createset.txt
cat /tmp/createset.txt | ./slonik
./slonik_subscribe_set 1 2 > /tmp/subscribe.txt
cat /tmp/subscribe.txt | ./slonik

And now we wait …
logging occurs in /var/log/slony/slony1/node1 and /var/log/slony/slony1/node2
on node 2 grep for “copied”
#.e.g tail -F ats-2012-01-09_11:18:29.log | grep copied

you can also check the replication view st_lag_num_events on the primary node .

You can either leave it running for ever . Or once your happy with the upgrade , stop any applications using 8.x then kill slony1 and then stop 8.x postgresql service

# once complete

on node 1 (8.x)

cd /var/lib/pgsql/data/bin
./slonik_uninstall_nodes | ./slonik

on node 1 and 2


And your ready to switch you applications to the new server and start them up , with minimal downtime!

# Additional Note if you want to rerun things you should be able run ./slon_kill and then echo “drop schema _replication cascade;” | psql (for example)¬† , to start again as its all pretty safe.

Ipad 2 is just a speak and spell


It is amazing the convergence of technology and the incredible reduction in component pricing that has come together to bring forth the wonders of iPad to the masses. From the incredible advances in flash technology for storage to the ARM based processor (via Acorn/BBC) and touch technology and all the tools and SDKs that allow you to build all the apps you could shake a infra-red touch screen at, and the embedded OS that it is sat on.
It makes me incredibly happy then that using these evil products to know that in twenty years time people may look back at the iPad as if they were holding the equivalent of a speak and spell. The things we are amazed at now will be ‘so 2011‘. My fledglings often tell me how awful things of my youth are with the words ‘thats so 90s‘ , I’m sure they will get the same treament when the table(t)s are turned and I can then roll over happily were ever I maybe laying.
I cant begin to fathom the advancements we will make in another 30 years of components that will produce some exciting future gadgets. I’d like to see what will happen with bendable/fold-able screens and tablets in the coming years for example. I need one to fold away an embarrassing app that I may or may not be playing in a public place.

A speak and spell yesterday

Here is a link to an article to a break down of all the differences between the two systems.

PostgreSQL upgrade from 8.1 to 9.1


Been trying for a while to upgrade a really old database with a really new one, with no downtime , this I managed successfully with Slony-I . with slony 2 its is not possible to go from such an old version of PostgreSQL unfortunately, However Slony 1 worked great  pretty much first time. The only fiddly and long process is setting up the slon_tools.conf file to identify all the table and sequence you need to replicate on your new shiny 9.1 release.I originally successfully replicated to 9.0 as well BTW.

The PostgreSQL 9 release allowed me to also test its own streaming replication , this meant that actually both master & slave of PostgreSQL 9.x needs to be up and running before migrating and replicating from 8.1 with slony 1. Got that tip from @DevrimGunduz himself no less.

Its actually not that difficult , Luckily for me If I mess up (not that I did as it happens!) I have all the power of VMWare to go back to an older snapshot.