The third chapter of PostgreSQL Replication Understanding Instant Recovery (2)

3.2 Archiving transaction logs

After looking at the pictures, we can look at how to get these things into working condition. When it comes to filing in a timely manner, the first thing you need to do is archive xlog. PostgreSQL provides all of the archive-related options through postgresql.conf. Let's take a step-by-step look at what needs to be done in postgresql.conf to start the archive:

1. First, you should set the Archive_mode to ON.

2. The second step, you should configure your archive command. The archive command is a simple shell command with two parameters:

1.%p: This is a placeholder for the xlog that should be archived, including its full path (source).

2.%f: This variable holds the name of the Xlog without a path pointing to it.

Now let's set up the archive. To do this, we should create a location to store the Xlog. Ideally, the xlog you want to archive is not stored on the same hardware as the DB instance. For this example, let's assume we're going to apply a archive to/archive. The following changes need to be made in postgresql.conf:

Wal_level = Archive

# Minimal, archive, or Hot_standby

# (change requires restart)

Archive_mode = On

# allows archiving to being done

# (change requires restart)

Archive_command = ' cp%p/archive/%f '

# command to archive a logfile segment

# placeholders:%p = path of file to archive

#%f = file name only

Once these changes have been made, the archive is ready and you only need to restart the database to activate the archive.

Before we restart the DB instance, we want you to focus on the wal_level. There are currently three different wal_level settings to choose from:

Minimal

Archive

Hot_standby

So far, in the case of a single node, the amount of transaction log generated is insufficient to synchronize the entire second instance. There are some optimizations in PostgreSQL that let Xlog write 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 is created or emptied in the same transaction).

To replay the transaction log, archive is necessary. The difference between archive and Hot_standby is that archive does not need to know the currently running transaction. This information is critical in the case of convective replication.

[Restart can be done by directly using pg_ctl–d/data_directory–m fast restart or through a standard init script.] ]

The easiest way to check our archive work is to create some useless data in the database. The following code snippet shows a way to easily produce 1 million rows of data:

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 just created a series of numbers. It is important that 1 million rows of data will depart a considerable amount of xlog traffic. You will see a large number of files making it an 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

These files can be easily used in future replay operations.

[If you want to save storage, you can also assume these XLOG files.] Only need to add gzip to your archive_commmand. ]

Original address: http://www.cnblogs.com/songyuejie/p/4743370.html

The third chapter of PostgreSQL Replication Understanding Instant Recovery (2)