Future Imperfect

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
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
#
if ($ENV{"SLONYNODES"}) {
    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     => 'node1.postgres81.com',
             dbname   => 'myDatabaseName',
             port     => 5432,
             user     => 'postgres',
             password => 'xxxx');

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

}

$SLONY_SETS = {

    "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',
                        'aged_debt_id_seq'

#...
   },

};

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

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

 

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

./slon_kill

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.