Archiving the transaction log

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

The first thing you have to do when it comes to Point-in-time Recovery is 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:

1. First of all, you should turn archive_mode on.
2. In the second step, you should configure your archive_command. The archive_command is a simple shell, and it needs just two parameters to operate properly:
   • %p: This is a placeholder representing the XLOG file that should be archived, including its full path (source).
   • %f: This variable holds the name of XLOG without the path pointing to it.

Let's 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 copy an archive to /archive. The following changes have to be made to postgresql.conf:

wal_level = archive
        # minimal, archive, hot_standby, or logical
        # (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 these changes have been made to postgresql.conf, archiving is ready for action. To activate these change, restarting the database server is necessary.

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

• minimal
• archive
• hot_standby
• logical

The amount of transaction log produced in the case of wal_level = minimal is by far not enough to synchronize an entire second instance. In wal_level=minimal, there are some optimizations in PostgreSQL that allow XLOG writing to be skipped. 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 the currently running transactions. However, for streaming replication, as will be covered in the next chapters, this information is vital.

If you are planning to use logical decoding, wal_level must be set to logical to make sure that the XLOG contains even more information, which is needed to support logical decoding. Logical decoding requires the most verbose XLOG currently available in PostgreSQL.

Tip

Restarting can either be done through pg_ctl –D /data_directory –m fast restart directly or through a standard init script.

The easiest way to check whether our archiving works is to create some useless data inside the database. The following snippet shows that 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 one 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 /archive/
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.

Tip

If you want to save storage, you can also compress those XLOG files. Just add gzip to your archive_command. This complicates things a little, but this little complexity certainly benefits users.