How-to get the latest PostgreSQL version installed on Debian – the proper way

PostgreSQL poweredPostgreSQL maintains its own package repositories for binary packages for the most common Linux distributions and operating systems, which allows you to stay up to date through your familiar package manager. If you are running Debian 6 (Squeeze), you may be using the default PostgreSQL 8.4 and if you are running Debian 7 (Wheezy) it ships with PostgreSQL 9.1. Every major version of PostgreSQL comes with significant performance improvements and feature upgrades.

Repository

To update your Debian machine to the latest PostgreSQL version all you need to do is to create an additional apt-get source list /etc/apt/sources.list.d/pgdg.list and insert the following line for Debian 6:
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

or the following line for Debian 7:
deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main

Furthermore you will have to import the PostgreSQL repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
apt-key add -

Upgrade

After updating your package repositories, and upgrading
apt-get update ; apt-get upgrade
PostgreSQL should be upgraded and you should have two database server versions running. In my case 8.4 with all the previous data and 9.2 with no data.

Migration

To migrate the database content from the old version to the new database system (8.4 and 9.2 on my machine) you switch to the postgres user and erase the database that was created during installation of the new database system. Afterwards a pg_upgrade command will dump the data of the old server and import it into the new server.
su postgres
pg_dropcluster --stop 9.2 main
pg_upgradecluster 8.4 main

Finally you can uninstall your old version of PostgreSQL.

References:
http://www.postgresql.org/download/linux/debian/
http://www.postgresql.org/download/

Leave a Reply