Restore Postgres Database from pg_data directory

Question

I have a broken VM that won't boot with an old postgresql database on it (used to run PostgreSQL 8.4). I have access to the file system (and the pg_data directory).

How can I extract the data (or restore the database) from this data directory?

Is it as simple as copying the contents of this directory into a working 8.4 pg_data directory?

Solution

Actually it is basically that simple. Here are the steps I took to get this working:

1) Archive the data directory (/var/lib/postgres/8.4/data) into a tar.gz file.

2) Move the file to a working workstation (my desktop, running a Debian-based distribution of Linux)

3) Install the PostgreSQL APT repository and install postgresql-8.4 (or version which was on the broken server) using the instructions found at the PostgreSQL Linux downloads for Ubuntu.

4) Extract the contents of the tar.gz file into the main directory for the "new" PostgreSQL 8.4 installation (/var/lib/postgresql/8.4/main/).

5) Modify the postgresql.conf to change the port = 5432 to port = 5433. This allows us to control which version of PostgreSQL we connect to using the port number (assuming we have the latest stable version on our workstation, such as 9.1). So 9.1 will stay on the default 5432, and 8.4 will be on 5433.

6) Modify the ownership of the extracted data directory so postgres is the owner: chown -R postgres:postgres /var/lib/postgresql/8.4/main/*

7) Start the postgres service: service postgresql start (you'll see both versions start up)

8) su as postgres and connect using port 5433, and the database name that was on the old server: psql -p 5433 DatabaseName