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).



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 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.

Share this page:

5 Comment(s)

Add comment


From: Grant

One more thing:

In order to make the down time even shorter, remember that rsync will not copy files it already has, so, it is very reasonable to do the rsync before you shut down the original server, even several times if needed, until it is only working on the one, newest file.   Then shut down the DB, one more rsync, which will grab just that last file, which is much quicker, then bring up the remote machine.

From: Anonymous

There was an announcement in postgresql-announce about ChronicDB that can apply updates immediately without downtime. The are doing live connection migration so you don't even need to shutdown the application.

From: jpenny

Another option is bucardo.

 It is a replication server, but once the replication is complete, the master could be turned off.

From: spybubble review

The are doing live connection migration so you don't even need to shutdown the application.

From: Chris Bell

I'm guessing

archive_command = 'cp -i %p /archive%f

 should be

archive_command = 'cp -i %p /archive/%f'