If your PostgreSQL instance is running on an Ubuntu LTS system that you need to upgrade to the most recent release, say from precise to trusty – because, well, sooner or later you must – you need to consider what is going to happen to your database.

Note
The upgrade process described in this article is similar to what you would have to do if you were upgrading from Trusty to Xenial, the newest Ubuntu LTS release.

Ubuntu attempts to make the process of upgrading to the newest distribution release easy and hassle-free. In fact, it is the case in many situations but not when there is PostgreSQL running in the system. If you just go ahead and try to run do-release-upgrade command, which is the officially recommended way to upgrade your Ubuntu LTS distribution to the newest release, you will end up seeing this error message: 

...

Get:72 http://us.archive.ubuntu.com trusty-backports/universe Translation-en [34.6 kB] 
Fetched 23.3 MB in 6s (0 B/s)

Checking package manager
Reading package lists... Done
Building dependency tree
Reading state information... Done
Building data structures... Done
Calculating the changes
Calculating the changes
Could not calculate the upgrade

An unresolvable problem occurred while calculating the upgrade.

This can be caused by:

* Upgrading to a pre-release version of Ubuntu
* Running the current pre-release version of Ubuntu
* Unofficial software packages not provided by Ubuntu

If none of this applies, then please report this bug using the
command 'ubuntu-bug ubuntu-release-upgrader-core' in a terminal.

Not very helpful, is it? Well, clearly you need to troubleshoot. Where do you start?

Examine /var/log/dist-upgrade/main.log and look for ERROR messages. This is what you will see: 

...
2016-02-09 07:19:01,392 DEBUG blacklist expr '^postgresql-.*[0-9]\.[0-9].*' matches 'postgresql-plperl-9.3'
2016-02-09 07:19:01,393 DEBUG The package 'postgresql-plperl-9.3' is marked for removal but it's in the removal blacklist
2016-02-09 07:19:01,462 ERROR Dist-upgrade failed: 'The package 'postgresql-plperl-9.3' is marked for removal but it is in the removal blacklist.'
...

That's more specific. Basically, it says if any postgresql-* package is installed abort the mission. This means that what was supposed to be an easy and hassle-free, one-command-only upgrade has become your duty to figure out how to do it. Yeah, the promise of simplicity that Ubuntu broadcasts far and wide in its marketing campaigns clearly has its terms and conditions that may apply. Anyway, roll up your sleeves. We gotta do some manual work here.

It is not a problem with do-release-upgrade per se. APT itself is configured on Ubuntu LTS to not do anything with PostgreSQL packages, like, for example, automatically removing postgresql-* packages:

/etc/apt/apt.conf.d/01autoremove-postgresql
// File installed by postgresql-common. Currently not updated automatically,
// but might be in future releases.
//
// We mark all PostgreSQL packages as NeverAutoRemove because otherwise apt
// would remove the old postgresql-x.y package when the "postgresql" meta
// package changes its dependencies to a new version, rendering the old
// database cluster inaccessible. As access to the cluster might depend on
// other modules (like datatypes), we use a pretty wide pattern here. We might
// tighten this to match only actually used PostgreSQL versions in the future.

APT
{
 NeverAutoRemove
 {
   "^postgresql-";
 };
};

It's a good thing, if you think about it. You don't want a OS upgrade tool, or even a package manager to mess with your database.

So, before you even attempt to run do-release-upgrade and hope it lives up to Ubuntu's promise of making your life easier, you need to plan upgrade of your PostgreSQL. As a new Ubuntu LTS release will most likely mean a new version of PostgreSQL, you need to carefully put together a plan for the upgrade.

It means you should really start by reading release notes of the new PostgreSQL version (available on the main page of postgresql.org) and see if you will need to do anything special to make sure your data isn't lost during the upgrade, and that any other requirement is met.

Once you have an idea of what's coming in the new version and what actions you may need to take to ensure that your upgrade is completed successfully, you can start working on getting your PostgreSQL server ready for the do-release-upgrade.

In most simple cases that don't require you to do anything special about PostgreSQL itself, it will come down to a number of steps:

• Back up your data
• apt-get remove postgresql-*
• do-release-upgrade
• Update PGDG repository configuration (precise->trusty)
• apt-get update
• apt-get -s dist-upgrade
• Restore your data 

Back Up Your Data

You need to back up your cluster/databases and globals. You can read more about backups here. This time we're taking a look at how to use pg_dump. You can also upgrade by using pg_upgrade utility which we'll cover next time. For example, using pg_dumpall something like this will create a backup for globals and for every database in your cluster:

/usr/lib/postgresql/9.3/bin/pg_dumpall -g -Upostgres -p 5434 --file=globals.sql;
/usr/lib/postgresql/9.3/bin/psql -p 5434 -AtU postgres -c "SELECT datname FROM pg_database \
                         WHERE NOT datistemplate"| \
while read f;
  do /usr/lib/postgresql/9.3/bin/pg_dump -Upostgres -p 5434 --format=c --file=$f.sqlc $f;
done;

In this example, we use PostgreSQL 9.3 that was installed on Ubuntu Precise from PGDG repository. Upon successful completion of do-release-upgrade Ubuntu Trusty will have PostgreSQL 9.3 installed as its default PostgreSQL version from official Ubuntu repositories. In our simple test setup all data survived do-release-upgrade just fine and PostgreSQL works as expected, without any problems, after the upgrade of operating system. However, in most real-life scenarios you will probably be making a transition from a lower to a higher PostgreSQL version. In which case you would need to upgrade by dumping and reloading your data.

Remove PostgreSQL Packages

This must sound kinda scary, but the way to successfully run do-release-upgrade is by making sure PostgreSQL doesn't get in its way. You literally need to remove PostgreSQL packages before attempting to run do-release-upgrade.

Just to be safe, make sure you first back up postgresql.conf, pg_hba.conf and other important configuration files. In fact, create a copy of entire /etc/postgresql/9.3/ and /var/lib/postgresql/9.3. This will include data directory, which may be in gigabytes or terabytes. Well, you must have backups of your database anyway. Just make sure the backups are recent, in working state and there's a way to restore them in case APT messes up your data.

Once you're absolutely positive that you have backups of your entire database cluster and configuration, remove all PostgreSQL packages from the system:

$ sudo apt-get remove postgresql-* 

Note that apt-get remove will not delete your configuration, or data. At least it shouldn't. That's what apt-get purge does. The only reason we recommend to take backups is because it is a good practice and you don't really want to rely on APT and learn one day that an obscure bug or a change in its policy of doing the 'remove' action results in data loss.

Run do-release-upgrade

Here's a checklist to go over before running do-release-upgrade:

Ensure /boot has enough disk space.

If it doesn't, do-release-upgrade will fail and display this error message:

"Not enough free disk space
The upgrade has aborted. The upgrade needs a total of 54.2 M free
space on disk '/boot'. Please free at least an additional 13.9 M of
disk space on '/boot'. Empty your trash and remove temporary packages
of former installations using 'sudo apt-get clean'. "

3rd party software may break do-release-upgrade

PostgreSQL is one example. You will see do-release-upgrade notify you that PGDG APT source list was disabled in a typically non-specific fashion: 

"Third party sources disabled
Some third party entries in your sources.list were disabled. You can
re-enable them after the upgrade with the 'software-properties' tool
or your package manager."

Open additional SSH session on port 1022 before do-release-upgrade starts upgrading packages

If you lose your current SSH session you can retain access to your system in the second window.

Run do-release-upgrade

When you're ready run: 

$ sudo do-release-upgrade

Update PGDG Repository Configuration

Assuming do-release-upgrade went fine and you're using PGDG repositories, you will need to uncomment a line with repository source in /etc/apt/sources.list.d/pgdg.list and change precise-pgdg to trusty-pgdg. Your pgdg.list file would look then like this:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

Resynchronize The Package Index Files

At this point you should be running Ubuntu Trusty LTS and you need to run:

$ sudo apt-get update

Install Updates and Upgrades

You will probably realize that postgresql-* packages in PGDG are newer version than those installed during do-release-upgrade. So, in case you want to make sure you're using the most recent PGDG version of PostgreSQl run: 

$ sudo apt-get -s dist-upgrade

Drop -s after making sure that proposed changes look good.

Restore Your Data

Finally restore your data using the newer version of PostgreSQL. For the sake of example let's assume that you wanted to upgrade to PostgreSQL 9.5 once you're running trusty.

Essentially, to start using the new PostgreSQL version you would:

• apt-get install postgresql-9.5
• Restore your globals

 Here an example of you could restore globals:

/usr/lib/postgresql/9.5/bin/psql -U postgres -p 5435 < /home/admin/do-release-upgrade/db-backups/globals.sql 

• Restore databases:

Consider this example

for i in databaseA databaseB databaseC;do /usr/lib/postgresql/9.5/bin/psql -U postgres -p 5435 -c "CREATE DATABASE $i;"; done 

for i in databaseA databaseB databaseC;do /usr/lib/postgresql/9.5/bin/pg_restore -U postgres -p 5435 --dbname=$i $i.sqlc;done

Port number may be different on your system and these examples are merely a guideline.

Although it's a complete set of actions required to upgrade your database various factors unique to your setup may introduce other steps that need to be carried out in order to successfully upgrade PostgreSQL. There's no one-size-fits-all upgrade strategy and this post demonstrates just one of the simplest scenarios you may encounter.

If your case is particularly complex to the point where you would rather have us double-check your upgrade plan or actually perform it for you, feel free to contact us and we'll figure out the best path to upgrade your system running PostgreSQL to the latest release.