Restore Postgresql Database From Data Directory Files

By Kevin Waterson 24496 12 Comments

So, you have no backup SQL dump from pg_dump. All you have is the old data directory and you need to restore the files from that old directory, possibly on another drive, or you have a achive, or whatever. This tutorial will show how to restore a PostgreSQL database from the old data directory files. There are many steps, but each of them is small.

The process begins with locating your postgresql config file. This tutorial uses ubuntu 14.04 and PostgreSQL 9.3, so the config file is located in /etc/postgresql/9.3/main/postgresql.conf.

With your favourite editor, open this file. You will need to be root.

sudo vim /etc/postgresql/9.3/main/postgresql.conf

Find the line which contains the data directory.

data_directory = '/var/lib/postgresql/9.3/main' # use data in another directory

Comment out this line, and below it put this line

# data_directory = '/var/lib/postgresql/9.3/main' # use data in another directory
data_directory = '/media/backups/var/lib/postgresql/9.3/main'

Now the postgresql server needs to be restarted, so..

$ sudo service postgresql restart

Quite likely, you will get an error like the one below. This is OK.

* Restarting PostgreSQL 9.3 database server
* Error: pid file is invalid, please manually kill the stale server process.

To counter this, the PID for the postgres application must be found.

ps aux | grep postgres

The above command will give a list of postgres services running such as this.

postgres  5294  0.0  0.1 252012 21304 ?        S    12:35   0:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres  5296  0.0  0.0 252148  8964 ?        Ss   12:35   0:00 postgres: checkpointer process
postgres  5297  0.0  0.0 252012  4872 ?        Ss   12:35   0:00 postgres: writer process
postgres  5298  0.0  0.0 252012  3576 ?        Ss   12:35   0:00 postgres: wal writer process
postgres  5299  0.0  0.0 252872  6548 ?        Ss   12:35   0:00 postgres: autovacuum launcher process
postgres  5300  0.0  0.0 107824  3868 ?        Ss   12:35   0:00 postgres: stats collector process
kevin     5621  0.0  0.0  15952  2276 pts/14   S+   13:04   0:00 grep --color=auto postgres

The important number required is the number in the second column of the first line. This is the parent process, and once killed, the rest will cascade. The PID on your machine will be different from this number.

sudo kill 5294

When this process is killed, the of the processes will die also, like vampire minions once the master is killed. this can be checked again with the ps command.

ps aux | grep postgres

Great, now the postgresql server can be restarted without promlems.

$ sudo service postgresql restart

The result should be a single like such as this:

* Restarting PostgreSQL 9.3 database server

Now the database is running with the old database directory, and all the databases are now available. To access them can be done with the postgres user.

sudo su postgres

Now the database is accessible as you would connect to any database.

psql db_name

To be able to restore this database, a dump needs to be created using pg_dump.

$ pg_dump -U postgres db_name -f /tmp/db_name.sql

Great! You are saved. Now that a dump is required, the process needs to be reversed to put the dump file into the new database installation.

So, first the data_directory needs to be restored in the postgresql.conf config file. Again, using your favourite editor, open this file and delete the line with the backup data_directory and uncomment the original line.

data_directory = '/var/lib/postgresql/9.3/main' # use data in another directory

Now the postgresql database needs to be restarted for the change to take effenct.

$ sudo service postgresql restart

Ooops!

* Restarting PostgreSQL 9.3 database server
* Error: pid file is invalid, please manually kill the stale server process.

Once again, the parent process needs to be killed to allow the service to restart.

$ ps aux | grep postgres

$ ps aux | grep postgres
postgres  5683  0.0  0.1 252016 21112 ?        S    13:11   0:00 /usr/lib/postgresql/9.3/bin/postgres -D /media/kevin/44329ff4-bbee-45d6-832a-73dfbdd883bd/var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres  5685  0.0  0.0 252016  3356 ?        Ss   13:11   0:00 postgres: checkpointer process
postgres  5686  0.0  0.0 252016  4644 ?        Ss   13:11   0:00 postgres: writer process
postgres  5687  0.0  0.0 252016  3356 ?        Ss   13:11   0:00 postgres: wal writer process
postgres  5688  0.0  0.0 252876  6068 ?        Ss   13:11   0:00 postgres: autovacuum launcher process
postgres  5689  0.0  0.0 107844  3528 ?        Ss   13:11   0:00 postgres: stats collector process
kevin     5792  0.0  0.0  15948  2172 pts/15   S+   13:26   0:00 grep --color=auto postgres

As before, the column required is the second column on the first line, then we kill the process with this number.

$ kill 5683

And finally, restart the database with the original configuration.

$ sudo service postgresql restart

Now that the database is available as a simple dump file, the database can be restored as with any database restoration.

Firstly, a database needs to be created and your user name.

Connect to the database server as the postgres user.

psql template1

In this example, the user kevin is created.

create user kevin password 'secret_password';

Then, a new database is created.

CREATE DATABASE my_db_name;

Add the user to the pg_hba.conf file. Add this line.

$ sudo vim /etc/postgresql/9.3/main/pg_hba.conf

local my_db_name kevin md5

And once again, restart the postgresql server.

$ sudo service postgresql restart

The final step, is to import the dump file back into the new database.

$ pg_dump -U kevin forge -f /tmp/db_name.sql

OK, so a lot of steps, but none of them too complex. You can now use your database as you so lovingly used to.