How To Easily Migrate A PostgreSQL Server With Minimal Downtime

Want to support HowtoForge? Become a subscriber!
 
Submitted by dratone (Contact Author) (Forums) on Wed, 2010-03-31 16:51. :: Debian | Other

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.


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Chris Bell (not registered) on Thu, 2010-12-02 03:54.

I'm guessing

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

 should be

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

Submitted by spybubble review (not registered) on Mon, 2010-08-16 16:22.
The are doing live connection migration so you don't even need to shutdown the application.
Submitted by jpenny (not registered) on Sat, 2010-04-10 00:12.

Another option is bucardo.

 http://bucardo.org/wiki/Bucardo

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

Submitted by Grant (not registered) on Wed, 2010-04-07 19:51.

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.

Submitted by Anonymous (not registered) on Fri, 2010-07-30 01:24.
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.