Restoring a database export (Simple)

Once a backup is taken, we need to know how to use it to restore the database to working order. Once again, PostgreSQL provides the pg_restore utility to do all of the hard work.

Getting ready

Please refer to the Getting a basic export (Simple) recipe on preparing a sample database. The pg_restore tool gains the most functionality with the custom export format, so we will use that for the following example. These commands should produce a simple SQL export of our databases. We will give the backup a .pgr extension, indicating that it is a PostgreSQL backup file, as shown in the following command:

$> pg_dump -Fc -f sample_backup.pgr sample
$> pg_dumpall -g -f globals.sql

Once these files are safely stored elsewhere, revert the database to a fresh install.

The normal procedure to do this is a bit complex, so for now, we can cheat a little. Simply drop the sample database with the following command, and we can continue:

$> dropdb sample

How to do it...

The pg_restore tool is not quite analogous to pg_dump. It is more of a sophisticated backup playback engine. Since we are working with a partial export, there are a few extra steps to fully restore everything as follows:

1. Again, start by obtaining our global objects:

   $> psql -f globals.sql postgres
   

2. Next, create the sample database:

   $> createdb sample
   

3. Finally, use the following restoration command:

   $> pg_restore -d sample sample_backup.pgr
   

How it works...

There is a bit of new material here. We started by using the psql utility to execute commands in the globals.sql file. Remember, output of pg_dumpall is just in SQL format, so we can use PostgreSQL's default SQL execution command. We can connect to the postgres database, since it always exists as a root for new database installations. This creates the global objects such as users and groups that we always want to preserve.

We then needed the sample database to exist, so we used createdb, another PostgreSQL utility we have used before. This time, it provides a target for pg_restore. By using the -d flag, our backup is restored directly into the sample database instead of any preexisting defaults. The last parameter is similar to how we specify a database name with pg_dump or psql. But for pg_restore, the last unnamed parameter is assumed to be a database backup to restore.

There's more...

That was admittedly much more complicated than simply using pg_dumpall to export everything, and psql to restore it including database names. However, now we are using much more powerful tools and gaining even further flexibility.

Parallel database restore

Since we are using PostgreSQL Version 8.4 or higher, the pg_restore utility includes the ability to execute parts of a backup file in parallel. While data is restoring in one table, indexes could be created in another. We could have restored our sample database using the following command:

$> pg_restore -j 4 -d sample sample_backup.pgr

This would invoke four restore jobs (-j) simultaneously. With enough CPUs, restores finish several times faster than the default linear process. Index and primary key creation are very CPU intensive.

Database targeting

Note how we always specify the restore database. We could just as easily restore the database twice with different names each time! Each database is independent of the other. The following command lines show how we can restore the database twice:

$> createdb sample
$> createdb extra
$> pg_restore -d sample sample_backup.pgr
$> pg_restore -d extra sample_backup.pgr

This is a perfect tool to experiment with production data safely or to restore an old backup next to a production database, and transfer data between them.

Partial database restores

Even though our export is of the entire sample database, we could restore only portions of it, or only the schema, or only the data. Much like pg_dump, all these options are available, and pg_restore is smart enough to ignore irrelevant parts of a source backup. The following command would only restore the pgbench_tellers table:

$> pg_restore -d sample -t pgbench_tellers sample_backup.pgr

Note

Remember to create your databases with createdb before restoring them!