After taking a look at the big picture, we can take a look and see how things can be put to work.

The first thing you have to do when it comes to Point-In-Time-Recovery is to archive the XLOG. PostgreSQL offers all the configuration options related to archiving through postgresql.conf.

Let us see step by step what has to be done in postgresql.conf to start archiving:

Let us set up archiving now. To do so, we should create a place to put the XLOG. Ideally, the XLOG is not stored on the same hardware as the database instance you want to archive. For the sake of this example, we assume that we want to apply an archive to /archive. The following changes have to be made to postgresql.conf:

wal_level = archive
        # minimal, archive, or hot_standby
        # (change requires restart)
archive_mode = on
        # allows archiving to be done
        # (change requires restart)
archive_command = 'cp %p /archive/%f'
        # command to use to archive a logfile segment
        # placeholders: %p = path of file to archive
        #               %f = file name only

Once those changes have been made, archiving is ready for action and you can simply restart the database to activate things.

Before we restart the database instance, we want to focus your attention on wal_level. Currently three different wal_level settings are available:

The amount of transaction log produced in the case of just a single node is by far not enough to synchronize an entire second instance. There are some optimizations in PostgreSQL, which allow XLOG-writing to be skipped in the case of single-node mode. The following instructions can benefit from wal_level being set to minimal: CREATE TABLE AS, CREATE INDEX, CLUSTER, and COPY (if the table was created or truncated within the same transaction).

To replay the transaction log, at least archive is needed. The difference between archive and hot_standby is that archive does not have to know about currently running transactions. For streaming replication, however, this information is vital.

The easiest way to check if our archiving works is to create some useless data inside the database. The following code snippets shows a million rows can be made easily:

test=# CREATE TABLE t_test AS SELECT * FROM generate_series(1, 1000000);
SELECT 1000000
test=# SELECT * FROM t_test LIMIT 3;
generate_series
-----------------
               1
               2
               3
(3 rows)

We have simply created a list of numbers. The important thing is that 1 million rows will trigger a fair amount of XLOG traffic. You will see that a handful of files have made it to the archive:

iMac:archivehs$ ls -l
total 131072
-rw-------  1 hs  wheel  16777216 Mar  5 22:31 000000010000000000000001
-rw-------  1 hs  wheel  16777216 Mar  5 22:31 000000010000000000000002
-rw-------  1 hs  wheel  16777216 Mar  5 22:31 000000010000000000000003
-rw-------  1 hs  wheel  16777216 Mar  5 22:31 000000010000000000000004

Those files can be easily used for future replay operations.