How To Easily Migrate A PostgreSQL Server With Minimal Downtime

PostgreSQL is a great database server, but when your dataset is rather large, migrating a server by using pg_dump can be a rather long process. In this tutorial we will discuss a way to migrate a entire server with as little downtime as possible. To achieve this, we will be using the PostgreSQL built in features for PITR (Point in time recovery).

Assumptions

This tutorial assumes you have 2 PostgreSQL 8.3 servers running on a Debian / Ubuntu based Linux distribution. The database versions need to be the same. You cannot use this to migrate from 8.3 to 8.4.

The destination PostgreSQL server's data directory will be lost!

Preperation

Preperation is key, especially to avoid as much downtime as possible. Therefore, read this entire tutorial before proceeding with running any of the commands. If you do not fully understand what the command is supposed to do; don't run it. It is possible to destroy your current PostgreSQL database. Always have a backup on hand!

The first steps: Configure the source postgresql server

Before proceeding: Make sure the destination's PostgreSQL server is NOT running!

The first step is to prepare the source database server. As stated earlier we will be using PITR (Point In Time Recovery) for this. This creates WAL files, which will be copied to the destination database server. Each WAL file is (by default) 16MB in size. This can add up in disk space if your database is frequently updated. Keep this in mind.

We will need to set up PITR archiving on the source server. Log in to the source server and create the archive directory. Substitute /archive for the directory you want.

mkdir /archive; chown postgres.postgres /archive;

Next, edit the file /etc/postgresql/8.3/main/postgresql.conf:

vim /etc/postgresql/8.3/main/postgresql.conf

Add the following lines:

archive_mode = on # for pgpoolII
archive_command = 'cp -i %p /archive%f 

Next, run the following commands to initiate postgres in backup mode.

# sudo /etc/init.d/postgresql-8.3 reload;
# sudo su postgres;
# psql;
postgres=# SELECT pg_start_backup('backup');
postgres=# \q

If you recieved an error after the backup command; fix this first before continuing!

Next, copy the files in /var/lib/postgresql/8.3/main to the destination server. I prefer to use rsync for this.

rsync --progress -azv --delete /var/lib/postgresql/8.3/main/ root@<destinationserver>:/var/lib/postgresql/8.3/main

When this is finished, we will need to stop postgresql's backup mode;

# sudo su postgres;
# psql;
postgres=# SELECT pg_stop_backup();
postgres=# \q

Destination server

Now we need to configure the destination server. First, create another archive directory. Again, substitute /archive/ for your desired directory. For ease I recommend keeping these directories the same.

# sudo mkdir /archive; sudo chown postgres.postgres /archive;
# sudo apt-get install postgresql-contrib-8.3
# sudo vim /var/lib/postgresql/8.3/main/recovery.conf

Paste the following line into the newly created file:

restore_command = '/usr/lib/postgresql/8.3/bin/pg_standby -l -d -s 2 -t /tmp/pgsql.trigger /archive %f %p %r 2>>standby.log'

Next, we will need to rsync the /archive directories across. This can be done from either the destination or source server. The following assumes the destination server:

# sudo rsync --progress -azv --delete root@<sourceserver>:/archive /archive

When finished, start postgresql..

# sudo /etc/init.d/postgresql-8.3 start

When you want to switch over, all you have to do is re-rsync the /archive directory and create a trigger file to start postgresql into normal mode. Please note that from the moment you create the trigger file, new data from the source server will not be added to the destination server.

# sudo rsync --progress -azv --delete root@<sourceserver>:/archive /archive
# sudo su -c 'touch /tmp/pgsql.trigger' postgres

Stand back, relax and enjoy a job well done

Assuming everything went alright, the new server is now up and running to the point that you created the trigger file. Now you can take a step back and enjoy a job well done.