Using pg upgrade on Ubuntu/Debian

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Upgrading to version 9.1)
m (Prerequisites: remove port number coloring from 9.2 example)
Line 18: Line 18:
  # sudo pg_lsclusters
  # sudo pg_lsclusters
  Version Cluster  Port Status Owner    Data directory                    Log file
  Version Cluster  Port Status Owner    Data directory                    Log file
  <span style="color:blue">9.1</span>    <span style="color:green">main</span>      <span style="color:purple">5432</span> down  postgres /var/lib/postgresql/9.1/main      /var/log/postgresql/postgresql-9.1-main.log
  <span style="color:blue">9.1</span>    <span style="color:green">main</span>      5432 down  postgres /var/lib/postgresql/9.1/main      /var/log/postgresql/postgresql-9.1-main.log
  <span style="color:red">9.2</span>    <span style="color:green">main</span>      <span style="color:darkcyan">5433</span> down  postgres /var/lib/postgresql/9.2/main      /var/log/postgresql/postgresql-9.2-main.log
  <span style="color:red">9.2</span>    <span style="color:green">main</span>      5433 down  postgres /var/lib/postgresql/9.2/main      /var/log/postgresql/postgresql-9.2-main.log
== Upgrading to version 9.2 or later ==
== Upgrading to version 9.2 or later ==

Revision as of 11:02, 29 October 2012

Instructions for how to convert/upgrade a PostgreSQL database cluster using pg_upgrade on Ubuntu or Debian. For the sake of example, I'm upgrading from version 9.1 to 9.2, but it should work with any version. Replace any version numbers on the command lines.



These instructions are experimental!

  • This way of upgrading is not yet supported by Ubuntu upstream. Do it at your own risk. I have tested this on Ubuntu 12.04.
  • PostgreSQL 9.2 is still a testing release (currently at RC 1). Do not run it in production!

When in doubt, use the old but slower pg_upgradecluster method.


First you need to install relevant packages: postgresql-VER and postgresql-server-dev-VER. If you're using contrib extensions, you also need postgresql-contrib-VER, and possibly other modules like postgresql-plpython-VER...

sudo apt-get install postgresql-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1 \
                     postgresql-9.2 postgresql-contrib-9.2 postgresql-server-dev-9.2

By default, Ubuntu creates a database cluster named "main" with each installed version. We will use these. Verify that these are configured:

# sudo pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5432 down   postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log
9.2     main      5433 down   postgres /var/lib/postgresql/9.2/main       /var/log/postgresql/postgresql-9.2-main.log

Upgrading to version 9.2 or later

First you need to stop the relevant database clusters. To stop all clusters, run:

sudo /etc/init.d/postgresql stop

(If you don't want to stop all clusters, you can also use pg_ctlcluster to manage them one by one)

Do the upgrade... (For extra performance, you can use the --link option to pg_upgrade; please read pg_upgrade documentation first)

cd /tmp
sudo -H -u postgres /usr/lib/postgresql/9.2/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.1/bin \
   -B /usr/lib/postgresql/9.2/bin \
   -d /var/lib/postgresql/9.1/main \
   -D /var/lib/postgresql/9.2/main \
   -o '-c config_file=/etc/postgresql/9.1/main/postgresql.conf' \
   -O '-c config_file=/etc/postgresql/9.2/main/postgresql.conf -F'

See if it worked...

sudo pg_ctlcluster 9.2 main start
sudo -u postgres psql --cluster 9.2/main
postgres=# select version();
PostgreSQL 9.2rc1 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
postgres=# \l+
... list of databases ...

Upgrading to version 9.1

The above instructions don't work when you upgrade to PostgreSQL 9.1. You have to jump through some hoops.

Note that I'm now using version numbers 9.0 and 9.1:

# sudo pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
9.0     main      5432 down   postgres /var/lib/postgresql/9.0/main       /var/log/postgresql/postgresql-9.0-main.log
9.1     main      5433 down   postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

First you have to symlink the configuration files to respective PostgreSQL data directories.

ln -s /etc/postgresql/9.1/main/postgresql.conf /var/lib/postgresql/9.1/main/
ln -s /etc/postgresql/9.0/main/postgresql.conf /var/lib/postgresql/9.0/main/

And now you have to run the pg_upgrade script with your respective configured port numbers:

cd /tmp
sudo -H -u postgres /usr/lib/postgresql/9.1/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.0/bin \
   -B /usr/lib/postgresql/9.1/bin \
   -d /var/lib/postgresql/9.0/main \
   -D /var/lib/postgresql/9.1/main \
   -p 5432 \
   -P 5433

Afterwards, you may delete the unnecessary config file links (but it's safe to leave them there if you want)

rm /var/lib/postgresql/9.1/main/postgresql.conf
rm /var/lib/postgresql/9.0/main/postgresql.conf

What next

Don't forget to merge your configuration changes in postgresql.conf, pg_hba.conf and other files.

If you upgraded from 9.0 or earlier and you're using contrib modules or other addons, it is recommended to migrate them to proper extensions. For example:

CREATE EXTENSION hstore SCHEMA public FROM unpackaged;


New cluster database "XXX" is not empty

The new cluster version already contains some data. For example, if you want to delete all data in the 9.2 version cluster, run:

pg_dropcluster 9.2 main
pg_createcluster 9.2 main

Where do I get PostgreSQL 9.2?

On Ubuntu, you can simply install 9.2 from the semi-official "PPA" repository:

sudo apt-get install python-software-properties
sudo apt-add-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2 postgresql-contrib-9.2 postgresql-server-dev-9.2

Warning: Support for the current major version in the PPA may be dropped when a new major version is released. If you want to use this PPA, be prepared to always upgrade to the newest major version soon after it's released.

Personal tools