Migrating PostgreSQL 9.1 database to Ubuntu 14.04
The message from the command-not-found package telling me I needed to install postgres-xc turned out to be misleading. In fact pg_upgrade is part of the default install but it is not in the default PATH rather it is located in /usr/lib/postgresql/9.3/bin/pg_upgrade.

Because pg_upgrade was not in PATH a search through available packages was performed and the postgres-xc package happens to have a /usr/bin/pg_upgrade executable. That was however not the pg_upgrade I was looking for.

Alas pg_upgrade would not work unless I was able to bring up a PostgreSQL instance serving the files in /var/lib/postgresql/9.1 which I wasn't able to do with the packages available in Ubuntu 14.04 repositories.

So I concluded that the answer to my question most likely was: No, converting the files in /var/lib/postgresql/9.1 is not possible using software from the Ubuntu 14.04 repository.

What I did in the end was to use pg_dump on the Debian system where the database originated. My concern about changes being made while pg_dump was running were addressed by setting default_transaction_read_only in /etc/postgresql/9.1/main/postgresql.conf. To ensure that no currently running transaction would commit after I started pg_dump, I restarted PostgreSQL between enabling default_transaction_read_only and starting pg_dump.

This approach had the added benefit of at least having the system keep running in read-only mode while the migration was ongoing, thus minimising the user visible downtime.

Before I started this manoeuvre I verified the process using the output of a pg_dump command performed on the running database.

In particular I verified that:


Database triggers were migrated correctly.
Unusual unicode characters in the data were migrated correctly.


I decided to use the textual output from pg_dump to be imported by using psql rather than the binary output format, because that allowed me to easily perform a few manual sanity checks on the file being migrated between the two databases.