0

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.

Community
  • 1
kasperd
  • 241
  • 2
  • 4
  • 11
  • @joanolo It very clearly told me that I needed postgres-xc. I will investigate further why it is telling me that I need postgres-xc. – kasperd Dec 10 '16 at 17:14
  • Another approach:

    pg_dump needs the old database to be running; but most probably you should be able to restrict connections to "just the one that is doing the dump". You might be able to do it in your app. If not, most probably can be done by modifying pg_hba.conf; setting all access methods to "reject" except for one single privileged local user [let's call it pg_dump_privileged_user] you should have created beforehand for the purpose of using pg_dump.

     – joanolo Dec 10 '16 at 17:18
  • I think pg_upgrade should be an executable located where all the PostgreSQL executables are located. So, you may check there. In any case, I don't think it's safe to use it to transfer data between two different OSs. At least, I don't think this was part of the original purpose, and I am not sure the data is stored in a way that is binary compatible between OSs. – joanolo Dec 10 '16 at 17:20
  • @joanolo Turns out there is a /usr/lib/postgresql/9.3/bin/pg_upgrade but it is not in my PATH which is why it couldn't find it. I guess the postgres-xc contains a different pg_upgrade command which would be in a directory in my current PATH. So it seems that error message was misleading. Whether that fully solves my problem remains to be seen. – kasperd Dec 10 '16 at 17:24
  • @joanolo If the database could be set in read only mode while performing the dump, that might be ideal. I don't know if there is such a thing as a read only mode for PostgreSQL. – kasperd Dec 10 '16 at 17:26
  • @joanolo Calling Debian and Ubuntu two different OS may be an exaggeration in this context. As far as I can tell there is a larger difference between different versions of the same distribution than there is between similar versions of the same distribution. – kasperd Dec 10 '16 at 17:31
  • WRT to "readonly mode", it seems there is a possibility. Check for default_transaction_read_only at https://www.postgresql.org/docs/9.6/static/runtime-config-client.html, and check also https://www.postgresql.org/message-id/5fe0d78bda51d7176940fb8e78739b54@biglumber.com

    (I've never used this feature myself... I guess I would really make some trials to make sure you cannot UPDATE or INSERT anything).

    Debian and Ubuntu: they may be similarly based, but you can have lots of little diffs. I think pg_upgrade was thought for upgrading between 2 versions of DB within same machine.

     – joanolo Dec 10 '16 at 17:33
  • 1
    @joanolo Looks like using pg_dump and running the output through psql does the right thing with respect to triggers. And it is not as slow as the other answer had me thinking. Now I just need to figure out whether I would rather trust a binary or a textual dump. And I need to verify that it correctly handles all the unusual characters that the users have entered. – kasperd Dec 10 '16 at 18:22
  • 1
    @joanolo default_transaction_read_only appears to work. But I do wonder whether transactions started before the change might still be committing changes after a reload. Might be safer to do a full restart of the database and not just a reload. – kasperd Dec 10 '16 at 21:18

1 Answers1

0

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.

kasperd
  • 241
  • 2
  • 4
  • 11