How do I copy databases from Postgres 8.4 to Postgres 9.1?

Question

My trac server recently started having issues. It was running a very outdated version of Ubuntu, and I was unable to properly use apt-get to correct the issues I was having. So instead I backed up the machine and did a clean install of Ubuntu 12.04.

Now I have a new server with Postgres 9.1 properly installed and running. I want to get my old Postgres 8.4 database from the backup copied over and setup on the new install. How do I go about doing this?

---

What I have done so far is to copy the old Postgres 8.4 data and bin directories from the backup (/var/lib/postgresql/8.4/main and /usr/lib/postgresql/8.4/bin) to /tmp/postgres.old and /tmp/postgres-bin.old on the new install. I stopped Postgres 9.1 using service postgres stop and ran the command:

sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_upgrade \
  --old-datadir=/tmp/postgres.old/main/ \
  --new-datadir=/var/lib/postgresql/9.1/main/ \
  --old-bindir=/tmp/postgres-bin.old/postgresql/8.4/bin \
  --new-bindir=/usr/lib/postgresql/9.1/bin

As described in the Postgres documentation. This seemed like it would work great, except I received the error:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?


unable to connect to old postmaster started with the command: "/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl" -w -l "/dev/null" -D "/tmp/postgres.old/main" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1
Failure, exiting

The issue seems to be that it's trying to start the old server, and that doesn't work because it's so very old and not installed properly. Trying to start it manually reveals the problem:

root@mission:/tmp# sudo -u postgres /tmp/postgres-bin.old/postgresql/8.4/bin/postgres 
/tmp/postgres-bin.old/postgresql/8.4/bin/postgres: error while loading shared libraries: libssl.so.0.9.8: cannot open shared object file: No such file or directory

So, How can I restore old Postgres 8.4 databases into a new Postgres 9.1 install, and update them?

Answer 1

It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default data directory is). From there, I'd do a pg_dump on the database(s) in question and then restore them to the new server. That would probably give you the cleanest environment.

So, here are what I see as your options:

Using pg_upgrade

On The New Server (pg_upgrade)

1. Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt.
2. Attempt to start the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/
3. If that works, stop the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/
4. Now try your pg_upgrade script

Without Using pg_upgrade

1. Create a VM or use an old, decommed server and install postgresql 8.4
2. Initialize your server using initdb (or pg_createcluster)
3. Copy your backed up data over that which was just created
4. Start postgresql
5. Backup the data using pg_dump
6. Copy the dump file to the "new server"
7. Restore to a new database on the new server

Answer 2

Had a similar issue today. Had to migrate two databases from a 8.2.14 version to 9.3.2 plus switching operating systems from Suse to Ubuntu. I tried several ways, in the end the 'easiest' won. I created a dump with pg_dump but using inserts instead of copies and created the database from the dump file:

pg_dump 'database' -f 'filename' -C -D -U 'user' -W

Before importing the file into the new system I checked paths which were used within some functions and corrected the path information. As far as I can see it seems to work fine after the import now.