Recovery in PostgreSQL 9

Recovery of all databases

Recovery of a complete database server, including all of its databases, is an important feature. This recipe covers how to do that in the simplest way possible.

Getting ready

Find a suitable server on which to perform the restore.

Before you recover onto a live server, always take another backup. Whatever problem you thought you had could be just about to get worse.

How to do it...

LOGICAL (from custom dump -F c):

Restore of all databases means simply restoring each individual database from each dump you took. Confirm you have the correct backup before you restore:
```
pg_restore --schema-only -v dumpfile | head | grep Started
```

Reload globals from script file as follows:
```
psql -f myglobals.sql
```

Reload all databases. Create the databases using parallel tasks to speed things along. This can be executed remotely without needing to transfer dumpfile between systems. Note that there is a separate dumpfile for each database.
```
pg_restore -d postgres -j 4 dumpfile
```

LOGICAL (from script dump created by pg_dump –F p):

As above, though with this command to execute the script. This can be executed remotely without needing to transfer dumpfile between systems.

Confirm you have the correct backup before you restore. If the following command returns nothing, then the file is not timestamped, and you'll have to identify it in a different way:
```
head myscriptdump.sql | grep Started
```

Reload globals from script file as follows:
```
psql -f myglobals.sql
```

Reload all scripts like the following:
```
psql -f myscriptdump.sql
```

LOGICAL (from script dump created by pg_dumpall):

We need to follow the procedure, which is shown next.

Confirm you have the correct backup before you restore. If the following command returns nothing, then the file is not timestamped, and you'll have to identify it in a different way:
```
head myscriptdump.sql | grep Started
```

Find a suitable server, or create a new virtual server.

Reload script in full
```
psql -f myscriptdump.sql
```

PHYSICAL:

Restore the backup file onto the target server.

Extract the backup file into the new data directory.

Confirm that you have the correct backup before you restore.

$ cat backup_label
START WAL LOCATION: 0/12000020 (file 000000010000000000000012)
CHECKPOINT LOCATION: 0/12000058
START TIME: 2010-06-03 19:53:23 BST
LABEL: standalone

Check all file permissions and ownerships are correct and links are valid. That should already be the case if you are using the postgres userid everywhere, which is recommended.

Start the server

That procedure is so simple. That also helps us understand that we need both a base backup and the appropriate WAL files.

If you used other techniques, then we need to step through the tasks to make sure we cover everything required as follows:

Shutdown any server running in the data directory.

Restore the backup so that any files in the data directory that have matching names are replaced with the version from the backup. (The manual says delete all files and then restore backup—that might be a lot slower than running an rsync between your backup and the destination without the –-update option). Remember that this step can be performed in parallel to speed things up, though it is up to you to script that.

Check that all file permissions and ownerships are correct and links are valid. That should already be the case if you are using the postgres userid everywhere, which is recommended.

Remove any files in pg_xlog/.

Copy in any latest WAL files from a running server, if any.

Add in a recovery.conf and set its file permissions correctly also.

Start the server.

The only part that requires some thought and checking is which parameters you select for the recovery.conf. There's only one that matters here, and that is the restore_command.

restore_command tells us how to restore archived WAL files. It needs to be the command that will be executed to bring back WAL files from the archive.

If you are forward-thinking, there'll be a README.backup file for you to read to find out how to set the restore_command. If not, then presumably you've got the location of the WAL files you've been saving written down somewhere.

Say, for example, that your files are being saved to a directory named /backups/pg/servername/archive, owned by the postgres user.

On a remote server named backup1, we would then write this all on one line of the recovery.conf as follows:

restore_command = 'scp backup1:/backups/pg/servername/archive/%f %p'

How it works...

PostgreSQL is designed to require very minimal information to perform a recovery. We try hard to wrap all the details up for you.

Logical recovery: Logical recovery executes SQL to re-create the database objects. If performance is an issue, look at the recipe on recovery performance.

Physical recovery: Physical recovery re-applies data changes at the block level so tends to be much faster than logical recovery. Physical recovery requires both a base backup and a set of archived WAL files.

There is a file named backup_label in the data directory of the base backup. This tells us to retrieve a .backup file from the archive that contains the start and stop WAL locations of the base backup. Recovery then starts to apply changes from the starting WAL location, and must proceed as far as the stop address for the backup to be valid.

After recovery completes, the recovery.conf file is renamed to recovery.done to prevent the server from re-entering recovery.

The server log records each WAL file restored from the archive, so you can check progress and rate of recovery. You can query the archive to find out the name of the latest archived WAL file to allow you to calculate how many files to go.

The restore_command should return 0 if a file has been restored and non-zero for failure cases. Recovery will proceed until there is no next WAL file, so there will eventually be an error recorded in the logs.

If you have lost some of the WAL files, or they are damaged, then recovery will stop at that point. No further changes after that will be applied, and you will likely lose those changes; that would be the time to call your support vendor.

There's more...

You can start and stop the server once recovery has started without any problem. It will not interfere with the recovery.

You can connect to the database server while it is recovering and run queries, if that is useful. That is known as Hot Standby mode.

Recovery to a point in time

If your database suffers a problem at 15:22 p.m. and yet your backup was taken at 04:00 a.m. you're probably hoping there is a way to recover the changes made between those two times. What you need is known as "point-in-time recovery".

Regrettably, if you've made a backup with pg_dump at 04:00 a.m. then you won't be able to recover to any other time than 04:00. As a result, the term point-in-time recovery (PITR) has become synonymous with the physical backup and restore technique in PostgreSQL.

Getting ready

If you have a backup made with pg_dump, then give up all hope of using that as a starting point for a point in time recovery. It's a frequently asked question, but the answer is still "no"; the reason it gets asked is exactly why I'm pleading with you to plan your backups ahead of time.

First, you need to decide what the point of time is that to which you would like to recover. If the answer is "as late as possible", then you don't need to do a PITR at all, just recover until end of logs.

How to do it...

How do you decide to what point to recover? The point where we stop recovery is known as the "recovery target". The most straightforward way is to do this based upon a timestamp.

In the recovery.conf, you can add (or uncomment) a line that says the following:

recovery_target_time = '2010-06-01 16:59:14.27452+01'

or similar. Note that you need to be careful to specify the time zone of the target, so that it matches the time zone of the server that wrote the log. That might differ from the time zone of the current server, so check.

After that, you can check progress during a recovery by running queries in Hot Standby mode.

How it works...

Recovery works by applying individual WAL records. These correspond to individual block changes, so there are many WAL records to each transaction. The final part of any successful transaction is a commit WAL record, though there are abort records as well. Each transaction completion record has a timestamp on it that allows us to decide whether to stop at that point or not.

You can also define a recovery target using a transaction id (xid), though finding out which xid to use is somewhat difficult, and you may need to refer to external records if they exist.

The recovery target is specified in the recovery.conf and cannot change while the server is running. If you want to change the recovery target, you can shutdown the server, edit the recovery.conf, and then restart the server. Be careful though, if you change the recovery target and recovery is already passed the point, it can lead to errors. If you define a recovery_target_timestamp that has already passed, then recovery will stop almost immediately, though this will be later than the correct stopping point. If you define a recovery_target_xid that has already passed, then recovery will just continue to the end of logs. Restarting recovery from the beginning using a fresh restore of the base backup is always safe.

Once a server completes recovery, it will assign a new "timeline". Once a server is fully available, we can write new changes to the database. Those changes might differ from changes we made in a previous "future history" of the database. So we differentiate between alternate futures using different timelines. If we need to go back and run recovery again, we can create a new server history using the original or subsequent timelines. The best way to think about this is that it is exactly like a Sci-Fi novel—you can't change the past but you can return to an earlier time and take a different action instead. But you'll need to be careful not to confuse yourself.

There's more...

pg_dump cannot be used as a base backup for a PITR. The reason is that a log replay contains the physical changes to data blocks, not logical changes based upon Primary Keys. If you reload a pg_dump the data will likely go back into different data blocks, so the changes wouldn't correctly reference the data.

WAL doesn't contain enough information to reconstruct all SQL fully that produced those changes. Later feature additions to PostgreSQL may add the required information to WAL.

Recovery of a dropped/damaged table

You may drop or even damage a table in some way. Tables could be damaged for physical reasons, such as disk corruption, or they could also be damaged by running poorly specified UPDATEs/DELETEs, which update too many rows or overwrite critical data.

It's a common request to recover from this situation from a backup.

How to do it...

The methods differ, depending upon the type of backup you have available. If you have multiple types of backup, you have a choice.

LOGICAL (from custom dump -F c):

If you've taken a logical backup using pg_dump into a custom file, then you can simply extract the table you want from the dumpfile like the following:

pg_restore -t mydroppedtable dumpfile | psql

or connect direct to the database using –d.

The preceding command tries to re-create the table and then load data into it. Note that pg_restore -t option does not dump out any of the indexes on the table selected. That means we need a slightly more complex procedure than it would first appear, and the procedure needs to vary depending upon whether we are repairing a damaged table or putting back a dropped table.

To repair a damaged table we want to replace the data in the table in a single transaction. There isn't a specific option to do this, so we need to do the following:

Dump the table to a script file as follows:

pg_restore -t mydroppedtable dumpfile > mydroppedtable.sql

Edit a script named restore_mydroppedtable.sql with the following code:
```
BEGIN;
TRUNCATE mydroppedtable;
i mydroppedtable.sql
COMMIT;
```

Then, run it using the following:
```
psql -f restore_mydroppedtable.sql
```

If you've dropped a table then you need to:
- Create a new database in which to work, name it restorework, as follows:
```
CREATE DATABASE restorework;
```
- Restore the complete schema to the new database as follows:
```
pg_restore --schema-only -d restorework dumpfile
```

Now, dump just the definitions of the dropped table into a new file, which will contain CREATE TABLE, indexes, other constraints and grants. Note that this database has no data in it, so specifying –-schema-only is optional, as follows:
```
pg_dump -t mydroppedtable --schema-only restorework >
mydroppedtable.sql
```

Now, recreate the table on the main database as follows:
```
psql -f mydroppedtable.sql
```

Now, reload just the data into database maindb as follows

pg_restore -t mydroppedtable --data-only -d maindb dumpfile

If you've got a very large table, then the fourth step can be a problem, because it builds the indexes as well. If you want you can manually edit the script into two pieces, one before the load ("pre-load") and one after the load ("post-load"). There are some ideas for that at the end of the recipe.

LOGICAL (from script dump):

The easy way to restore a single table from a script is as follows:

Find a suitable server, or create a new virtual server.

Reload the script in full, as follows:
```
psql -f myscriptdump.sql
```

From the recovered database server, dump the table, its data, and all the definitions of the dropped table into a new file as follows:
```
pg_dump -t mydroppedtable -F c mydatabase > dumpfile
```

Now, recreate the table into the original server and database, using parallel tasks to speed things along. This can be executed remotely without needing to transfer dumpfile between systems.
```
pg_restore -d mydatabase -j 2 dumpfile
```

The only way to extract a single table from a script dump without doing all of the preceding is to write a custom Perl script to read and extract just the parts of the file you want. That can be complicated, because you may need certain SET commands at the top of the file, the table, and data in the middle of the file, and the indexes and constraints on the table are near the end of the file. It's complex; the safe route is the one already mentioned.

PHYSICAL:

To recover a single table from a physical backup, we need to:

Find a suitable server, or create a new virtual server.

Recover the database server in full, as described in previous recipes on physical recovery, including all databases and all tables. You may wish to stop at a useful point in time.

From the recovered database server, dump the table, its data, and all the definitions of the dropped table into a new file as follows:
```
pg_dump -t mydroppedtable -F c mydatabase > dumpfile
```

Now, recreate the table into the original server and database using parallel tasks to speed things along. This can be executed remotely without needing to transfer dumpfile between systems as follows:
```
pg_restore -d mydatabase -j 2 dumpfile
```