Blog

Update Postgres Major Version in Debian Jessie

Tags: , , , , , ,
You are currently viewing Update Postgres Major Version in Debian Jessie
Keep calm

Postgresql update is not a trivial task.

According to PostgreSQL Versioning policy:

A major release is numbered by increasing either the first or second part of the version number, e.g. 9.1 to 9.2. Minor releases are numbered by increasing the third part of the version number, e.g. 9.2.3 to 9.2.4

Upgrading to a minor version is done through the upgrade process of Debian.

For major versions, the process is much more complex. This post describes the upgrading from 9.4 to 9.5 in a Debian (Jessie) server.

First things first

First think carefully if you really need to upgrade. Large databases could be very slow and big downtime will take place.

Actually, you need to upgrade

  • when significant security issues are fixed with this version
  • when this major version includes new features, which are necessary for your application

If you DO NOT NEED to upgrade (for a period of time, e.g. 1 year or more)

hold Postgresql updates using apt-mark (see documentation)

apt-mark hold postgresql

REMARK: remove the hold with

apt-mark unhold postgresql

If you really NEED to upgrade

  • Take a full backup (using pg_dumpall or pg_dump)
  • Create a detailed upgrade plan and execute it in TEST server
  • Finally, upgrade production server

The Classical Approach

The user manual covers this topic in depth. See here.

There are two options available. You can upgrade:

  • using pg_dump

                    pg_dump -Fc -U [ROLE] [myDB1] -f /path/to/myDB1.dump

    and pg_restore

                    pg_restore -C -d myDB1 /path/to/myDB1.dump

    OR

  • using pg_upgrade in-place

If you are in doubt, select the first method. This will work in all cases. Additionally, it is the only method which works in case new Postgresql version includes changes in system tables.

The Debian Way

STEP 1 – Inform Users for DOWNTIME

Your application must be unavailable during update. So, inform users accordingly.

STEP 2 – Update Debian as usual

Use

apt-get update && apt-get -V upgrade

More details here.

Sometimes, you may get a message like:

The following packages have been kept back:
postgresql (9.4+170.pgdg80+1 => 9.5+172.pgdg80+1)
postgresql-contrib (9.4+170.pgdg80+1 => 9.5+172.pgdg80+1)

In this case (or similar), just use:

apt-get install postgresql-9.5

STEP 3 – Remove new cluster

Remove the new version cluster, which created during common Debian update (in STEP 2):

pg_dropcluster --stop 9.5 main

STEP 4 – UPGRADE with pg_upgradecluster

In Debian there is a tool available to perform the upgrade. This is a Perl script, named pg_upgradecluster. It will upgrade an existing PostgreSQL cluster to a new major version. Data and configuration files of the old version are copied to the new cluster. This utility also reconfigures the old database cluster to use a different network port.

Use this command:

pg_upgradecluster -v 9.5 9.4 main

you will get something like:

Stopping old cluster...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop operation
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Redirecting start request to systemctl
Creating new cluster 9.5/main ...
config /etc/postgresql/9.5/main
data   /var/lib/postgresql/9.5/main
locale en_US.UTF-8
socket /var/run/postgresql
port   5433
Disabling connections to the new cluster during upgrade...
Redirecting start request to systemctl
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database myDB1...
Analyzing database myDB1...
Fixing hardcoded library paths for stored procedures...
Upgrading database myDB2...
Analyzing database myDB2...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database myDB3...
Analyzing database myDB3...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Stopping target cluster...
Redirecting stop request to systemctl
Stopping old cluster...
Redirecting stop request to systemctl
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Redirecting start request to systemctl
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

        pg_dropcluster 9.4 main

STEP 5 – remove old cluster

When you are sure that new cluster works normally, use this command to remove old cluster:

pg_dropcluster --stop  9.4 main

this will remove

/etv/postgresql/9.4
/var/lib/postgresql/9.4
/var/run/postgresql/9.4-main.pid

You may also remove the binaries.