Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
was the 9.1 service actually running on the server at the time? –  swasheck Jun 20 '12 at 21:06
1  
at the time of the failure on the first machine, or at the time of the consistency check? –  jcolebrand Jun 20 '12 at 21:06
1  
well, 9.1 and 8.4 can't be running at the same time unless the port is changed (presumably on the 9.1 instance) and they can't access the same files at the same time. when 9.1 was installed, did apt attempt to replace 8.4? –  swasheck Jun 20 '12 at 21:11
    
@swasheck This is a clean install of Ubuntu. Postgres 8.4 is not installed, I only restored it's files from backup. Only 9.1 is properly installed. 8.4 won't run at all. –  Josh Jun 20 '12 at 21:12
    
@Josh ... let's chat –  swasheck Jun 20 '12 at 21:14

2 Answers 2

up vote 5 down vote accepted

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
share|improve this answer
    
The new dependencies are not likely to match the old binaries. But why not install the full postgresql-8.4 package for ubuntu 12 instead? It's only the data directory that would need to be copied into /var/lib/postgresql/8.4/main/. The old binaries are not needed. –  Daniel Vérité Jun 20 '12 at 22:36
    
@DanielVérité very true. In the end, the user had a full backup of the server so he was able to start postgresql 8.4 and create a dump –  swasheck Jun 20 '12 at 22:39
    
Yes. I had a full backup of the old server which I mounted on the new server via NFS. I was then able to chroot to that mount, start postfix, and use pg_dump to export the databases I needed. Thanks swasheck! –  Josh Jun 22 '12 at 13:23

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.