With the recent PostgreSQL security update, I was forced to perform emergency maintenance on all my PostgreSQL servers to install the update. To remind myself of the process, I did a quick search and it turns out I could not find great instructions on installing “Point Releases” of PostgreSQL.

Here are my step by step instructions, for the next time I need to do this.

At LabKey, we run lots of different versions of PostgreSQL(for testing, etc) and I always install PostgreSQL from source. The instructions below will be for upgrading v9.0.

One of the great things about upgrading to a new point release, is that you are not required perform the usual methodology of

  1. Backup database
  2. Stop database server
  3. Install new version of database
  4. Restore database backup from Step #1

You can simply install the new version and start the server up and it should work. The methodology I used is

1. Backup the databases

[Optional] For safe keeping, backup the database using pg_dump

/usr/local/postgresql-9.0/bin/pg_dumpall -U postgres -W -f /backup/postgresql_backup_for_upgrade_to_9.0.13.out

2. Build the new version

sudo su - 
cd /usr/local/src
wget http://ftp.postgresql.org/pub/source/v9.0.13/postgresql-9.0.13.tar.gz
tar xzf postgresql-9.0.13.tar.gz
cd postgresql-9.0.13/
./configure --prefix=/usr/local/postgresql-9.0/
make

3. Shutdown PostgreSQL server

/etc/init.d/postgresql-9.0 stop

Backup the installation

For safe keeping, make a backup of the installed binaries, etc

If you have small databases or your PGDATA directory is not located in the PostgreSQL install directory , you can run

cd /usr/local
cp -Rp postgresql-9.0 postgresql-9.0_backup

If your databases are large, then you can run

cd /usr/local
tar --exclude="./postgresql-9.0/data" -czf postgresql_backup_for_upgrade_to_9.0.13.tar.gz postgresql-9.0 

Upgrade PostgreSQL to 9.0.13 and start the server

cd /usr/local/src/postgresql-9.0.13/
make install 
/etc/init.d/postgresql-9.0 start

That is it. All done.