PostgreSQL – Migrating Database from 9.1 to Ubuntu 14.04

postgresqlpostgresql-9.1postgresql-9.3Ubuntu

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.

Best Answer

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.

Related Solutions

PostGIS Extension – Difficulty on Ubuntu 14.04

But that is 9.1. - Is pgadmin connected to the wrong postgresql?

Yes.

It appears that you're connected to your 9.1 server, suggesting that 9.1 is still running.

Do pg_lsclusters to see what PostgreSQL installs you have and their status.

Each runs on a different port. If you want to connect to 9.3 you need to check what port it runs on and connect PgAdmin to that port.

Postgresql – Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

Best Answer

Related Solutions

PostGIS Extension – Difficulty on Ubuntu 14.04

Postgresql – Authentification failure for PostgreSQL server (9.3)

Related Question