Migrating PostgreSQL 9.1 database to Ubuntu 14.04 I want to migrate a database from PostgreSQL 9.1 installed on an old Debian system to Ubuntu 14.04. The database is making use of triggers and it is a requirement that the triggers be migrated as well, but without having the triggers executed on every row as it is migrated to the new database causing the new database to contain a garbled version of the original data.

The PostgreSQL version in the Ubuntu 14.04 repository is 9.3.

Is there any way I can convert the data from /var/lib/postgresql/9.1 on the old system to be usable on PostgreSQL 9.3?

What I have tried so far

I found an older answer on how to migrate from 9.1 to 9.3. But I did not have much luck using it.

Using pg_dump will as far as I understand require the old database to be running as the data is being dumped, which could lead to updates written during the dump to be missing from the new database.

Using pg_upgrade requires both PostgreSQL versions to be installed simultaneously. But 9.1 is not available in the Ubuntu 14.04 repositories. I considered replicating the old system into a chroot on the new machine, but I don't know if that will play well with how pg_upgrade will create hardlinks.

There is another hurdle which so far prevented me from testing pg_upgrade. When I try to run pg_upgrade I am told that the command is in the postgres-xc package which is not installed.

If I try to install postgres-xc it tells me that postgres-xc depends on postgres-xc-client.

If I try installing postgres-xc-client I am told that doing so will uninstall posgres-9.3. So it appears it is not even possible to have pg_upgrade and PostgreSQL 9.3 installed at the same time.
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.