How do I downgrade PostgreSQL?

In my ubuntu 12.04 x64 I installed PostgreSQL 8.4. It got automatically upgraded to 9.1.1. Due to some inconsistencies, I want to downgrade it to 8.4. How can I do this using the terminal?

Solution 1:

Ubuntu does not automatically upgrade PostgreSQL 8.4 to 9.1. What probably happened is that 9.1 is now installed alongside the old 8.4, and the client tools you are using prefer to connect to the 9.1 instance. Try pg_lsclusters to see what is really running on what ports they are on.

Solution 2:

I am not sure downgrading is the best thing to do here. After all there are reasons why upgrades are made. I do understand that there are cases in which upgrading is not obvious.

If you do want to downgrade there are two options and I strongly advice the second option.

Manually install an earlier version of PostgreSQL

Run the following commands (taken from http://colekcolek.com/2012/02/23/downgrade-postgresql-8-2-ubuntu-11-10/):

wget http://ftp.postgresql.org/pub/source/v8.4.9/postgresql-8.4.9.tar.gz
tar zxvf postgresql-8.4.9.tar.gz
cd postgresql-8.4.9
./configure --without-readline --without-zlib
make
su
make install

You will be responsible yourself for maintaining the postgres install.

Use an APT Repository

There is a repository that could help you here. In terms of maintenance that is definitely the better option. Here is an extensive quote from http://www.postgresql.org/download/linux/ubuntu/:

PostgreSQL Apt Repository

If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

To use the apt repository, follow these steps:

1. Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
2. Import the repository signing key, and update the package lists
   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

For more information about the apt repository, including answers to frequent questions, please see the apt page on the wiki.

There is also a PPA, but I think it is deprecated in favour of the repository: sudo add-apt-repository ppa:pitti/postgresql. More info on the same page.

Also read: How do I set which PostgreSQL version is to be used by default?