PostgreSQL Upgradation

Its always a challenging task when moving from one version to another version on new Server. My presentation below is to upgrade the Old version of PostgreSQL 8.3 to PostgreSQL 9.0.4 on NEW SERVER. Basically, steps are very simple for upgradation, but need to take some extra care when bouncing the new server before and after restoration. Latest PostgreSQL, has lot of fixes in it, so it is recommended to use the new binaries for entire upgradation process.

Step 1 (On New Server PG 9.0.x):

First step in PostgreSQL is to set SHMMAX/SHMALL at OS-level, because shared_buffers purely depends on these setting, below script will give you the figure how much should be the SHMMAX/SHMALL on the basis of Server's Memory. I have taken the script, written by Greg Smith, which is very handy in setting the SHMMAX/SHMALL.

For better understanding on shmmax/shmall, here is the link
http://www.puschitz.com/TuningLinuxForOracle.shtml#SettingSharedMemory

vi shmsetup.sh
#!/bin/bash
# Output lines suitable for sysctl configuration based 
# on total amount of RAM on the system.  The output 
# will allow up to 50% of physical memory to be allocated 
# into shared memory.
# On Linux, you can use it as follows (as root): 
# 
# ./shmsetup >> /etc/sysctl.conf 
# sysctl -p
#
# Early FreeBSD versions do not support the sysconf interface 
# used here.  The exact version where this works hasn't 
# been confirmed yet.

page_size=`getconf PAGE_SIZE` 
phys_pages=`getconf _PHYS_PAGES`

if [ -z "$page_size" ]; then 
  echo Error:  cannot determine page size 
  exit 1 
fi

if [ -z "$phys_pages" ]; then 
  echo Error:  cannot determine number of memory pages 
  exit 2 
fi

shmall=`expr $phys_pages / 2` 
shmmax=`expr $shmall \* $page_size` 

echo \# Maximum shared segment size in bytes 
echo kernel.shmmax = $shmmax 
echo \# Maximum number of shared memory segments in pages 
echo kernel.shmall = $shmall
:wq

Execute the script :
#chmod +x shmsetup.sh
#./shmsetup.sh

Step 2 (On New Server PG 9.0.x):

Install the latest version of PostgreSQL 9.0.4 on the new server. Link below for 32bit and 64 bit:
http://get.enterprisedb.com/postgresql/postgresql-9.0.4-1-linux.bin
http://get.enterprisedb.com/postgresql/postgresql-9.0.4-1-linux-x64.bin

Most of the cases, as well recommended, to keep the "pg_xlog" in different mount point. You can create a new cluster with initdb command by selecting different "pg_xlog" mount point:-

$initdb -D DATA_DIRECTORY -X PG_XLOG_LOCATION

Note: initdb command will not create the 'pg_log' directory under new cluster, you need to create it explicitly.

After installation and creation of the cluster set the environment variables like PGDATA, PATH, PGDATABASE, PGPORT, PGUSER etc., in ".bash_profile" under postgres user.

Step 3 (On Old Server PG 8.3.x):

As I said, use the new binaries for all the commands you are executing on this server. If you dont have the new binaries on this server, install a copy of new binaries with source installation to any new location without overriding the existing binaries.

Download:-
http://wwwmaster.postgresql.org/redir/198/h/source/v9.0.4/postgresql-9.0.4.tar.gz

#tar xvf postgresql-9.0.4.tar.gz
#cd postgresql-9.0.4
#./configure --prefix=/usr/pg904
#make
#make install
New binaries location will be "/usr/pg904/"

Step 4 (On Old Server PG 8.3.x):

Intial step would be taking dump of global objects like users, tablespaces, etc., using pg_dumpall.

$ /usr/pg904/bin/pg_dumpall -p $PGPORT -g > /pg83_backups/global_dump.sql

Step 5 (On Old Server PG 8.3.x):

Take the dump of all the databases in a cluster using below command. Also generate logs for each dump to analyze if any issue arises in the dumps

$ usr/pg904/bin/pg_dump -Fc -v -U PGUSER -p PGPORT DBNAME -f /pg83_backups/dbname.dmp  >> /pg83_backups/dbname.log 2>>/pg83_backups/dbname.log

if the database is bigger, run in nohup

$ nohup usr/pg904/bin/pg_dump -Fc -v -U PGUSER -p PGPORT DBNAME -f /pg83_backups/dbname.dmp >> /pg83_backups/dbname.log 2>>/pg83_backups/dbname.log &

Step 6 (On Old Server PG 8.3.x):

Move all the dumps(/pg83_backups) to new server.

Step 7 (On New Server PG 9.0.x):

As per our STEP 2, New Server will have the latest binaries of PG 9.0.4 and cluster, to speed up the restoration process we need to tune some of the settings in $PGDATA/postgresql.conf file before and after.

Before restoration settings in postgresql.conf file(memory settings my differ as per the available RAM on the box):-

Memory Settings:
---------------
shared_buffers= (as per the shmmax settings, Maximum 8 gigs on 64 bit, 4 gigs on 32 bit)
work_mem= (in between 40MB - 100 MB)
maintenance_work_mem = (in between 1GB - 5 GB)

Checkpoints Settings:
--------------------
checkpoint_segments=(in between 128 - 256)
checkpoint_timeout=(default is 15mns make to 1h)

Autovacuum settings:
-------------------
autovacuum=off
track_counts=off

Sync to Disk:
------------
fsync=off
full_page_writes=off
synchronous_commit=off

Background Writer settings:
--------------------------
bgwriter_delay=(default 200ms, change to 50ms)

Changes demands to restart the cluster. 

$pg_ctl -D $PGDATA restart
or
$pg_ctl -D $PGDATA stop -m f
$pg_ctl -D $PGDATA start

Step 8 (On New Server PG 9.0.x):

First restoration is the global object.

$PGPATH/psql –d DBNAME -p $PGPORT -U $PGUSER -f /pg83_backups/global_dump.sql

Step 9 (On New Server PG 9.0.x):

Restoring the database can be done parallelly, means from PG 8.4 onwards we have an option -j will create multiple connection to PostgreSQL parallelly and fasten the restoration process.

http://www.postgresql.org/docs/current/static/app-pgrestore.html

Option -j, depends on number of CPUs the NEW Server has, for example if I have 4 core, I can go with -j 4. Each core can spawn one extra process with pg_restore. Use this option as per your CPU cores, you can also get the number of processors information with this command:
$ cat /proc/cpuinfo | grep -i processors | wc -l

Start restoring each database with the dumps to new server, if the database is bigger, run in nohup. Also generate the logs on restore for further analysis on the restoration.

$PGPATH/pg_restore –d DBNAME -Fc –v -p $PGPORT -U PGUSER /pg83_backups/dbname.dmp >>/pg83_backups/restore_dbname.log 2>>/pg83_backups/restore_dbname.log 
or
nohup $PGPATH/pg_restore –d DBNAME -Fc –v -p $PGPORT -U PGUSER /pg83_backups/dbname.dmp >>/pg83_backups/restore_dbname.log 2>>/pg83_backups/restore_dbname.log &

While, restoration is in progress you can monitor in two ways at OS-level using "top -cu postgres" or "ps -ef | grep postgres", at DB-level using "select * from pg_stat_activity".

Step 10 (On New Server PG 9.0.x):

Important step, after successfull restoration, it is recommended to update the catalogs with ANALYZE command.

$$PGPATH/vacuumdb -p $PGPORT -a -Z -v >>/pg83_backups/analyze.log 2>>/pg83_backups/analyze.log 
or
$nohup /usr/local/pgsql/bin/vacuumdb -p 5433 -a -Z -v >>/pg83_backups/analyze.log 2>>/pg83_backups/analyze.log &

Step 11 (On New Server PG 9.0.x):

After ANALYZE, you need to change the settings to normal or as per the demand of application by editing the $PGDATA/postgresql.conf file.

Memory Settings:
---------------
shared_buffers= (as per the shmmax settings, Maximum 8 gigs on 64 bit, 4 gigs on 32 bit)
work_mem= (in between 5MB - 40MB)
maintenance_work_mem = (in between 1GB -- 2 GB)

Checkpoints Settings:
--------------------
checkpoint_segments=(in between 64 - 128)
checkpoint_timeout=(default)

Autovacuum settings:
-------------------
autovacuum=on
track_counts=on

Sync to Disk:
------------
fsync=on
full_page_writes=on
synchronous_commit=on

Background Writer settings:
--------------------------
bgwriter_delay=(50ms)

Step 12 (On New Server PG 9.0.x):

After the above changes restart the cluster.

$pg_ctl -D $PGDATA restart
or
$pg_ctl -D $PGDATA stop -m f
$pg_ctl -D $PGDATA start

You also need to do some changes in $PGDATA/pg_hba.conf file for allowing application connections. Always keep a copy of $PGDATA/*.conf files(PG 8.3.x) on New Server for doing any changes to .conf files.

Do post your comments or suggestions, which are greatly appreciated.

Regards
Raghav