LazyAdminPostgres82to83

From JQuantLib

This article quickly demonstrates how databases hosted on a Postgres 8.2 cluster can be migrated to a Postgres 8.3 cluster. Richard Gomes

Overview

In Debian installations, PostgreSQL can be easily upgraded with minimum downtime of your production environment. In a nutshell, the procedure consists on:

Backup your databases
Installing new database version
Migrate your cluster
Setup your new environment
Setup tablespaces
Setup symbolic links
Restore your databases
New installation checklist
Drop your old cluster
Uninstall the old database version
Reconfigure new instance to port 5432

Procedure

In this tutorial, we will migrate our databases from 8.2.x to 8.3.x

Backup your databases

Every successful system maintenance starts by a good backup.

# cd to your temporary directory
cd /home/postgres/tmp
# backup all databases :: this is an example
for db in IMDNA barc cddna_UAT_2117 community communitydna cstech datademo dbjobdemo wallets ;do
  echo Backup $db
  pg_dump -p 5433 > $db.backup
done

Installing a new database version

Verify which packages repository contains the version of PostgreSQL you need. At the moment, postgresql-8.3 packages are only available in Backports repository. In order to get access to it, please configure your /etc/apt/sources.list as described at [http://www.backports.org/dokuwiki/doku.php?id=instructions http://www.backports.org/dokuwiki/doku.php?id=instructions]

Simulate installation

apt-get -t etch-backports install postgresql-8.3 postgresql-contrib-8.3 postgresql-doc-8.3 -V --simulate

Verify the list of recommended packages which appear.
New functionalities in PostgreSQL may need additional system services or libraries to work properly or even to be enabled.

Install the new version of PostgreSQL

apt-get -t etch-backports install postgresql-8.3 postgresql-contrib-8.3 ostgresql-doc-8.3

The above command will install another database instance which will run in parallel with your current instance and will listen on port 5433

Migrate your cluster

I've tried to migrate the cluster and it said that the new 8.3 cluster already exists. Seems like your existing cluster is automagically migrated by Debian installation process. I'm not sure, though.

Setup your new environment

This step consists of creating an application administrator user, configuring any special parameters you have configured on your current database instance, such like listen addresses, memory parameters, etc.

Creating a database administrator user

# switch to postgres user
su - postgres
# create application administrator user
createuser --no-superuser --createdb --createrole --pwprompt sa

Test connectivity

Try to connect to your new database server via pgadmin3, using the sa user you've just created. Remember that your new database server is running at port 5433.

Review your configuration settings

Compare configuration files with the ones of your current running database and make the necessary changes. Remember to keep the original versions renamed as .OLD, for instance. This will help you someday when you upgrade again, so that you can easily identify (and remember) the changes you've done.

cd /etc/postgresql/8.3/main

Compare these files:

environment
pg_hba.conf
pg_ident.conf
postgresql.conf
start.conf

Setup tablespaces

In order to keep the installation as standard as possible, whilst taking advantage of a large disk array, we've created symbolic links in our current database installation. For your reference, observe the listing below:

$ ls -al /var/lib/postgresql/8.2/main
lrwxrwxrwx 1 postgres postgres    19 2008-01-15 08:45 base -> /dbarray/data1/base
lrwxrwxrwx 1 postgres postgres    21 2008-01-14 16:39 data_a -> /dbarray/data1/data_a
lrwxrwxrwx 1 postgres postgres    21 2008-01-14 16:39 data_b -> /dbarray/data2/data_b
lrwxrwxrwx 1 postgres postgres    21 2008-01-15 08:46 global -> /dbarray/data2/global
lrwxrwxrwx 1 postgres postgres    22 2008-01-15 08:43 pg_clog -> /dbarray/data1/pg_clog
lrwxrwxrwx 1 postgres postgres    27 2008-01-15 08:47 pg_multixact -> /dbarray/data2/pg_multixact
lrwxrwxrwx 1 postgres postgres    26 2008-01-15 08:48 pg_subtrans -> /dbarray/data2/pg_subtrans
lrwxrwxrwx 1 postgres postgres    24 2008-01-15 08:49 pg_tblspc -> /dbarray/data1/pg_tblspc
lrwxrwxrwx 1 postgres postgres    26 2008-01-15 08:49 pg_twophase -> /dbarray/data1/pg_twophase
lrwxrwxrwx 1 postgres postgres    21 2008-01-14 16:43 pg_xlog -> /dbarray/logs/pg_xlog

We are showing only the files you need to care about.

What we will do now in our new database instance is:

create tablespaces data_a and data_b for the new database instance
move directories to the disk array and create symbolic links

Recognizing the battle field

# go to new database instance work directory
cd /var/lib/postgresql/8.3/main
ls -al /var/lib/postgresql/8.3/main

drwx------  5 postgres postgres 4096 2008-05-12 10:33 base
drwx------  2 postgres postgres 4096 2008-05-12 11:54 global
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_clog
drwx------  4 postgres postgres 4096 2008-05-12 10:33 pg_multixact
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_subtrans
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_tblspc
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_twophase
drwx------  3 postgres postgres 4096 2008-05-12 10:33 pg_xlog

We are showing only the files you need to care about.

Create directories for tablespaces

cd /var/lib/postgresql/8.3/main
mkdir data_a data_b
ls -al cd /var/lib/postgresql/8.3/main

drwx------  5 postgres postgres 4096 2008-05-12 10:33 base
drwx------  5 postgres postgres 4096 2008-05-12 10:50 data_a
drwx------  5 postgres postgres 4096 2008-05-12 10:50 data_b
drwx------  2 postgres postgres 4096 2008-05-12 11:54 global
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_clog
drwx------  4 postgres postgres 4096 2008-05-12 10:33 pg_multixact
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_subtrans
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_tblspc
drwx------  2 postgres postgres 4096 2008-05-12 10:33 pg_twophase
drwx------  3 postgres postgres 4096 2008-05-12 10:33 pg_xlog

Create tablespaces

psql -p 5433
CREATE TABLESPACE data_a OWNER sa LOCATION '/var/lib/postgresql/8.3/main/data_a'
CREATE TABLESPACE data_b OWNER sa LOCATION '/var/lib/postgresql/8.3/main/data_b'
\q

Setup symbolic links

Shutdown your new database instance

# 8.3 is our new instance!
/etc/init.d/postgresql-8.3 stop

Copy directories to disk array

su - postgres
mkdir -p /dbarray/8.3/data1
mkdir -p /dbarray/8.3/data2
mkdir -p /dbarray/8.3/logs
cd /var/lib/postgresql/8.3/main
cp -r base         /dbarray/8.3/data1/base
cp -r data_a       /dbarray/8.3/data1/data_a
cp -r data_b       /dbarray/8.3/data2/data_b
cp -r global       /dbarray/8.3/data2/global
cp -r pg_clog      /dbarray/8.3/data1/pg_clog
cp -r pg_multixact /dbarray/8.3/data2/pg_multixact
cp -r pg_subtrans  /dbarray/8.3/data2/pg_subtrans
cp -r pg_tblspc    /dbarray/8.3/data1/pg_tblspc
cp -r pg_twophase  /dbarray/8.3/data1/pg_twophase
cp -r pg_xlog      /dbarray/8.3/logs/pg_xlog

Keep old directories, just in case

cd /var/lib/postgresql/8.3/main
for dir in base data_a data_b global pg_clog pg_multixact pg_subtrans pg_tblspc pg_twophase pg_xlog ;do
  mv $dir $dir.OLD
done

Create symbolic links

cd /var/lib/postgresql/8.3/main
ln -s /dbarray/8.3/data1/base
ln -s /dbarray/8.3/data1/data_a
ln -s /dbarray/8.3/data2/data_b
ln -s /dbarray/8.3/data2/global
ln -s /dbarray/8.3/data1/pg_clog
ln -s /dbarray/8.3/data2/pg_multixact
ln -s /dbarray/8.3/data2/pg_subtrans
ln -s /dbarray/8.3/data1/pg_tblspc
ln -s /dbarray/8.3/data1/pg_twophase
ln -s /dbarray/8.3/logs/pg_xlog

Start your new database instance

# 8.3 is our new instance!
/etc/init.d/postgresql-8.3 start

Migrate your databases

# cd to your temporary directory
cd /home/postgres/tmp
# restore all databases :: this is an example
for db in IMDNA barc cddna_UAT_2117 community communitydna cstech datademo dbjobdemo wallets ;do
  echo Restore $db ...
  pgsql -p 5433 -d $db -f $db.backup
done

New installation checklist

Verify that your new database instance, with migrated databases is working properly.

Configure applications to access your new 8.3 database instance
Play with applications and test functionalities;

Drop your old cluster

This procedue will permanently remove your PostgreSQL 8.2 instance!
If you are plenty sure that your new database instance is working properly, cross your fingers and...

pg_dropcluster --stop 8.2 main

Now see what happened:

cd /var/lib/postgresql
ls -al

total 24
drwxr-xr-x  3 postgres postgres 4096 2008-05-12 13:49 .
drwxr-xr-x 57 root     root     4096 2008-04-26 07:45 ..
drwxr-xr-x  3 root     root     4096 2008-05-12 10:33 8.3
-rw-------  1 postgres postgres 4371 2008-05-12 13:46 .bash_history
-rw-------  1 postgres postgres 1168 2008-05-12 12:04 .psql_history

Uninstall the old database version

This procedue will permanently remove PostgreSQL 8.2!
If you've already dropped your old database cluster, you can safely...

apt-get -t etch-backports remove postgresql-8.2 postgresql-contrib-8.2 postgresql-doc-8.2
/home/root/bin/dpkg_purge

Reconfigure new instance to port 5432

cd /etc/postgresql/8.3/main
cp -p postgresql.conf postgresql.conf.OLD
/etc/init.d/postgresql-8.3 stop
sed 's/5433/5432/' < postgresql.conf.OLD > postgresql.conf
/etc/init.d/postgresql-8.3 start
netstat -an | fgrep 543

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     19482654 /var/run/postgresql/.s.PGSQL.5432

Richard Gomes 18:47, 12 May 2008 (UTC)