PITR postgresql

If the standard methods can't be used (for ex, if the database is too big for a complete dump), another method can be used: WAL archiving.

This page describes how to use WAL archiving and PITR on an example database.
Backup ¶

The setup procedure is the following:

* Configure PITR recovery by defining an archive_command
* Restart the cluster to start WAL archiving

To make a backup:

* Connect to the database and run the pg_start_backup('label') function
* Backup the entire cluster (without stopping it)
* Connect to the database and run the pg_stop_backup() function

(Intro) WAL files ¶

At all times, postgresql maintains a WAL (write-ahead log) in the pg_xlog subdirectory of the cluster. These files are primarily used for crash-safety purposes: each operation on the database is logged in a WAL file (with a default limit of 16 MB). By default, WAL files are recycled. However, if postgresql is configured to continuously log all operations, these files can be used for backups, acting exactly like incremental backups. This has some benefits:

* Backups does not need to be perfectly consistent: you need a backup of the cluster, and some WAL files
* A complete dump of the database is not needed
* Incremental
* Continuous
* Point in time recovery: it is possible to restore the database at its state at any time since the backup

However, there are some drawbacks:

* Additional complexity
* Needs lots of space on disk
* More writes on disk (can impact performance)
* Works on the entire cluster

PITR is generally not needed: you should prefer using dumps when possible. PITR can be used for large databases, continuous backups, or high-availability.

Before trying to use PITR, you should practise, and test on a non-critical database.
Enabling WAL archiving ¶

We will configure postgresql to archive WAL files when they are complete, instead of recycling them.
(Option) Creating a test cluster ¶

Since we don't want to crash our production server, we'll create a test cluster.

# mkdir /bigstuff/pgPITR
# chown postgres:postgres /bigstuff/pgPITR
# su - postgres

$ export PGDATA=/bigstuff/pgPITR
$ /usr/lib/postgresql/8.2/bin/initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /bigstuff/pgPITR ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /bigstuff/pgPITR/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

/usr/lib/postgresql/8.2/bin/postgres -D /bigstuff/pgPITR
or
/usr/lib/postgresql/8.2/bin/pg_ctl -D /bigstuff/pgPITR -l logfile start

Add archive_command ¶

Edit your configuration file to add archive_command. This command can do anything you want (from a simple copy to a complex script), just remember to return 0.

For our test, we will copy files to a directory in /tmp. Stop the server, and edit the configuration:

$ vi $PGDATA/postgresql.conf
archive_command = 'cp %p /tmp/wals/%f'

Restart the server.

PostgreSQL will now work as usuel, creating WAL files in its pg_xlog subdirectory. However, when they are complete, it will copy them to the /tmp/wals/ directory.
(Option) Create a test database ¶

$ /usr/lib/postgresql/8.2/bin/pg_ctl -l /tmp/pg.log start
server starting

$ createdb test
CREATE DATABASE

$ psql test
[...]
test=# BEGIN WORK;
BEGIN
test=# CREATE TABLE dummy1 AS SELECT * FROM pg_class, pg_attribute;
SELECT
test=# COMMIT;
COMMIT
test=# select pg_size_pretty(pg_relation_size('dummy1'));
pg_size_pretty
----------------
97 MB
(1 row)
test=# \q

At this point, PostgreSQL should have archiving some files:

$ ls -al /tmp/wals/
total 98402
drwxr-xr-x 2 postgres postgres 288 Jan 6 15:00 .
drwxrwxrwt 24 root root 2304 Jan 6 14:59 ..
-rw------- 1 postgres postgres 16777216 Jan 6 15:00 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Jan 6 15:00 00000001000000000000000A
-rw------- 1 postgres postgres 16777216 Jan 6 15:00 00000001000000000000000B
-rw------- 1 postgres postgres 16777216 Jan 6 15:00 00000001000000000000000C
-rw------- 1 postgres postgres 16777216 Jan 6 15:00 00000001000000000000000D
-rw------- 1 postgres postgres 16777216 Jan 6 15:00 00000001000000000000000E

Backup the cluster ¶

You need to backup the cluster directory. You can use any method you want (tar, cpio, bacula, etc). to backup the cluster directory ($PGDATA).

Note: you don't need to stop the cluster or take extra precautions.

For our example, we'll just create a tar archive. Before doing so, we just need to inform postgresql that we will create a backup using the command pg_start_backup.

$ psql test
[...]
test=# SELECT pg_start_backup('full backup');
pg_start_backup
-----------------
0/F74F118
(1 row)

test=# \q

$ tar -cvzf /tmp/pgdata.tgz $PGDATA

$ psql test
[...]
test=# SELECT pg_stop_backup();
pg_stop_backup
----------------
0/F74F118
(1 row)

test=# \q


The argument of pg_start_backup is simply a label that helps you. This will be stored in file $PGDATA_backup_label.
(option) create some tables ¶

Create one more table:

test=# BEGIN;
BEGIN
test=# CREATE TABLE dummy2 AS SELECT * FROM pg_class, pg_attribute;
SELECT
test=# COMMIT;
COMMIT

If you want to test PITR, create a table dummy3:

test=# BEGIN;
BEGIN
test=# CREATE TABLE dummy3 AS SELECT * FROM pg_class, pg_attribute;
SELECT
test=# COMMIT;
COMMIT
test=# BEGIN;
BEGIN

wait some time (remember the exact time !), then drop it:

test=# DROP TABLE dummy3;
DROP TABLE
test=# COMMIT;
COMMIT
test=# \q

(option) simulate disaster ¶

.. or just wait for it to happen ..

Here, we'll just kill brutally the server:

$ kill -9 $(head -1 $PGDATA/postmaster.pid)

Recovery ¶

At this point, your database must be stopped.

The procedure is the following:

* If possible, get data from the damaged cluster
* Restore the complete cluster backup from the archive
* Clean up the restored cluster (remove pg_xlog files and the pid file)
* If possible, copy WAL files from the damaged cluster to the restored cluster
* Create the $PGDATA/recovery.conf file
* Start the cluster
* Watch the logs, then verify the restored data

Old cluster ¶

First, we need to try to recover data from the old cluster. If it is not possible, you will still be able to recover data from you backup, but you will maybe lost data from the most recent (unarchived) WAL file.

Here, we will just rename it:

$ mv $PGDATA $PGDATA.old

Restore the cluster ¶

Restore the cluster using your favorite method.

For our example, we'll use the tar archive:

$ (cd / && tar xvzf /tmp/pgdata.tgz)
$ chmod 0700 $PGDATA

Clean up the restored directory:

$ rm $PGDATA/pg_xlog/0*
$ rm $PGDATA/postmaster.pid

Restore WAL files from the old cluster ¶

If available, you can copy the WAL files from the pg_xlog subdirectory of the damaged cluster:

$ cp -a $PGDATA.old/pg_xlog $PGDATA/

Create a recovery file ¶

Create a new file $PGDATA/recovery.conf. The only required line is restore_command:

restore_command = 'cp /tmp/wals/%f %p'
recovery_target_time = '2008-01-06 15:18:00 CET'

The recovery target time allows you to recover the most recent data (if you do not specify recovery_target_time) or at a precise time.

If you removed WAL files after archiving them from the directory (/tmp/wals), you should restore them as well.
Start the database, and witness the miracle ¶

Start the database:

$ /usr/lib/postgresql/8.2/bin/pg_ctl -l /tmp/pg.log start
server starting

PostgreSQL will detect the file recovery.conf, and start automatic recovery:

$ tail -f /tmp/pg.log
LOG: database system was interrupted at 2008-01-06 15:01:57 CET
LOG: starting archive recovery
LOG: restore_command = "cp /tmp/wals/%f %p"
LOG: recovery_target_time = 2008-01-06 15:31:00+01
cp: cannot stat `/tmp/wals/00000001.history': No such file or directory
LOG: restored log file "00000001000000000000000F.0074F118.backup" from archive
LOG: restored log file "00000001000000000000000F" from archive
LOG: checkpoint record is at 0/F74F118
LOG: redo record is at 0/F74F118; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 0/618; next OID: 24582
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: automatic recovery in progress
LOG: redo starts at 0/F74F168
LOG: restored log file "000000010000000000000010" from archive
LOG: restored log file "000000010000000000000011" from archive
LOG: restored log file "000000010000000000000012" from archive
LOG: restored log file "000000010000000000000013" from archive
LOG: restored log file "000000010000000000000014" from archive
LOG: restored log file "000000010000000000000015" from archive
LOG: restored log file "000000010000000000000016" from archive
LOG: restored log file "000000010000000000000017" from archive
LOG: restored log file "000000010000000000000018" from archive
LOG: restored log file "000000010000000000000019" from archive
LOG: restored log file "00000001000000000000001A" from archive
LOG: restored log file "00000001000000000000001B" from archive
LOG: restored log file "00000001000000000000001C" from archive
cp: cannot stat `/tmp/wals/00000001000000000000001D': No such file or directory
LOG: could not open file "pg_xlog/00000001000000000000001D" (log file 0, segment 29): No such file or directory
LOG: redo done at 0/1CFFFD70
LOG: restored log file "00000001000000000000001C" from archive
LOG: archive recovery complete
LOG: database system is ready

If you specified the correct recovery_target_time, you will be able to restore your database just at the moment before dropping the table dummy3:

test=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | dummy1 | table | postgres
public | dummy2 | table | postgres
public | dummy3 | table | postgres
(3 rows)

After recovery, PostgreSQL will rename recovery.conf to recovery.done.