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
- Backup database
- Stop database server
- Install new version of database
- 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.