Restore Postgres Database from pg_data directory

directory restore postgresql-8.4
lightningmanic picture lightningmanic · Jun 18, 2013 · Viewed 11k times · Source

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?

Answer

lightningmanic picture lightningmanic · Jun 19, 2013

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

Related questions

How do I find all files containing specific text on Linux?

I'm trying to find a way to scan my entire Linux system for all files containing a specific string of text. Just to clarify, I'm looking for text within the file, not in the file name. When I was looking …

Read More
How do I list all files of a directory?

How can I list all files of a directory in Python and add them to a list?

Read More
How to change permissions for a folder and its subfolders/files in one step?

I would like to change permissions of a folder and all its sub folders and files in one step (command) in Linux. I have already tried the below command but it works only for the mentioned folder: chmod 775 /opt/lampp/…

Read More