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.
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.
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
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.