This is the second part of the topic, the more historical version is described in the previous part, and here is the vitality of wal in PostgreSQL which born replication、logical replication and more performance related configure, let’s continue to redo it.

1. Replication(V9.0)

Replication is implemented here, and many corresponding GUC are added for replication. Corresponding to warm standby, replication can also be called hot standby, which helps to achieve the data synchronization using WAL record between the primary and the standby.

Most GUCs appear as [GUC6] in the image, will not talk about all of them but pick some important.

WAL_LEVEL

The current version of wal supports three levels: minimal, archive and hot standby.

WAL_SENDER_DELAY

The wal sending process sends the wal logs generated by the master to the standby machine every other period of time, this parameter is used to configure the time interval.

WAL_KEEP_SEGMENTS

For some reasons, if the synchronization delay between the master and the standby is too long, it will cause the wal segments of the master to be removed before they are sent to the standby. In this case, the standby machine cannot synchronize the masterdata. Therefore, by configuring the wal ‘keep_segments’ parameter on the master, the WAL logs generated by the master will not be removed immediately, and the backup can have enough time complete data synchronization.

HOT_STANDBY

Configure whether to connect to this standby machine for query.

MAX_STANDBY_ARCHIVE_DELAY && MAX_STANDBY_STREAM_DELAY

When a wal redo operation conflicts with the currently executing query, you need to decide whether to wait for the query to complete redo or cancel the query to execute redo.This parameter sets a time to allow the query to continue executing. After this time, the query will be cancelled, and to finish the redo.

MAX_STANDBY_ARCHIVE_DELAY is used for file level wal transfer, that is, warm standby.

MAX_STANDBY_STREAM_DELAY is used for replication, or hot standby.

STANDBY_MODE

Define whether this is a PITR or a standby.

2. Standby synchronous commit(V9.1)

SYNCHRONOUS_COMMIT

This is not a new parameter, but an optional value of ‘local’ has been added to this parameter.

On: wal record writes to the disk in the local and standby before the transaction completes the commit state.

Local: wal record writes to the disk in the local,before the transaction completes the commit state.

Off: asynchronous commit

SYNCHRONOUS_STANDBY_NAMES

Point which is the synchronous standby.

HOT_STANDBY_FEEDBACK

The standby machine sends the query being executed by the standby machine to the master to inform the master not to clean up the related death tuples.

3. Cascade replication(V9.2)

The feature of version 9.2 is that there is a cascade replication. A standby machine can obtain wal logs from its upstream server, and can also deliver wal logs to the downstream server. There is no special GUC for cascading replication, we just need to configure the upstream server as a standby machine.

Here are two new GUCs, which have no related to cascade replicate.

PAUSE_AT_RECOVERY_TARGET

If the recovery target is specified in the PITR, when the recovery target is reached, the startup process stops redo, and the database is still in the recovery state. You can connect to the database to see whether the current database state meets your expectations. If not, it provides an opportunity for you to reconfigure recovery.conf to continue recovery. Of course, this requires the ‘pause_at_recovery_target parameter’ to be configured as true, otherwise, redo will immediately enter a readable and writable state when it reaches the recovery target.

SYNCHRONOUS_COMMIT

This parameter comes again. This time, it provides a ‘remote_write’ option. ‘remote_write’ is more rigorous than on, when wal record is applied to the synchronous standby machine, the transaction will be considered completed.

4. Small changes(V9.3)

Deleted the ‘replication_timeout’ parameter, added the ‘wal_sender_timeout’ alternative parameter, and added the ‘wal_receiver_timeout’ parameter

5. Slot and Logical(V9.4)

Slot is implemented so that the sending server can just save the appropriate wal file.

The ‘logical’ level for wal appears, with the built-in wal log parsing plugin test_decoding and the tool pg_recvlogical.

WAL_LEVEL

Support ‘logical’ wal level.

WAL_LOG_HINTS

If some pages have unimportant page data changes, they also follow the full page write mechanism.

MAX_REPLICATION_SLOTS

Each replication standby machine can be configured to use a replication slot, which records the application of wal records of the corresponding standby machine, and the wal segments saved for the standby by the master are not cleaned.

This is the max value for number of slot.

PRIMARY_SLOT_NAME

Configure the slot to be used on the standby machine, and name the slot.

RECOVERY_MIN_APPLY_DELAY

After receiving the wal log, the standby machine will delay a period of time to complete the redo operation of the wal record. Synchronous replication is not affected by this configuration.

Consider that if you execute a delete operation without conditional filtering on the master, at this time, the delete operation has not been synchronized to the standby machine, emergency measures can be taken to remedy the data.

6. Small changes(V9.5)

WAL_COMPRESSION

Whether to compress full page write data in wal log.

MAX_WAL_SIZE && MIN_WAL_SIZE

Soft limit of wal segments size in wal directory. When the size of wal segment in wal directory is smaller than ‘min_wal_segment’, checkpoint will not touch them; when the size of wal segments in wal directory is larger than ‘max_wal_segment’, checkpoint will remove the old wal segment until the size of wal segment met ‘max_wal_size’; otherwise, checkpoint will rename the unused wal segment to the name of future wal segment.

ARCHIVE_MODE

Add “always” option, whether a standby machine continues to archive the wal segments it receives.

RECOVERY_TARGET_ACTION

After the recovery, there are three options for database action: pause, promote, and shutdown. This parameter replaces the ‘pause_at_recovery_target’ parameter.

Pause means to stop redo, but the database is still in recovery mode at this time. This parameter allows you to view the current state of the database. If the current state does not meet your expectations, you can stop the database and modify recovery.conf, start the database and continue redo .And use the pg_xlog_replay_resume() command to exit the recovery mode of the database.

Promote: after the recovery, directly promote the database to exit the recovery mode.

Shutdown: This is equivalent to adding an additional shutdown operation on the basis of pause.

TRACK_COMMIT_TIMESTAMP

Record the commit time for the committed transaction.

WAL_RETRIEVE_RETRY_INTERVAL

This is a waiting time. When the standby machine has replayed all the wal logs, the walreceiver process detects whether there is a new wal log interval.

7. Multi synchronous standby(V9.6)

The biggest highlight of version 9.6 is to support multiple synchronous standby machines. In addition, the wal level (minimum, replica, logical) has been modified.

WAL_LEVEL

Wal level supports three types: minimal, replica and logical.

SYNCHRONOUS_COMMIT

Add the ‘remote_apply’ option. Wal record applied on the synchronous standby specified by ‘synchronous_standby_names’ before the master transaction is completed.

WAL_WRITE_FLUSH_AFTER

When a transaction is submitted asynchronously, the wal writer process will be notified to swipe the wal cache, but this swipe only refers to the swiping into the operating system cache.

When the size of wal records that are flushed into the operating system cache but have not completed the hard disk synchronization is greater than the value of ‘wal_write_flush_after’, the process of synchronizing wal cache to the hard disk will be triggered once.

SYNCHRONOUS_STANDBY_NAMES

Support multiple synchronous standby, by new string.

8. Logical Replication(V10.0)

In version 10.0, the user’s visible xlog tends to be changed to wal. The most intuitive thing is to change the name of the generated directory of wal from pg_xlog to pg_wal. In addition, many functions related to wal are also changed from xlog to wal. In version 9.4, wal log at logical level has been implemented. Since 10.0, the PostgreSQL kernel has officially added the built-in use of logical wal.

RECOVERY_TARGET_LSN

Specify a stop LSN point for the PITR.

MAX_LOGICAL_REPLICATION_WORKES

Maximum number of logical replication workers.

MAX_SYNC_WORKERS_PER_SUBSCRIPTION

When creating a subscription, the subscripter can choose to copy all the data in all tables of the publisher. This parameter refers to the workers.

9. Replication configuration tuning(V12)

In the stream copy, PITR, or warm standby functions, the recovery.conf configuration file is no longer used, and all relevant parameters are transferred to the postgresql.conf configuration file. Use an empty recovery.signal file or standby.signal to mark this as a recovery process.

WAL_INIT_ZERO

When creating the wal segment, initialize all the wal segment data to 0 to ensure that the wal segment has applied for space.

WAL_RECYCLE

Whether to reuse the old wal segment.

PROMOTE_TRIGGER_FILE

Replace ‘trigger_file’ parameter.

Summary

This blog shows the growth process of PostgreSQL’s wal. Some functions are slowly added to PostgreSQL, and there are more and more GUC appear. Of course, some parameters are discarded. I want to introduce the meaning and reasons for each parameter in detail at first, but this will be a larger blog. Later, I will indirectly write some short blogs to introduce the meaning, origin, and implementation of each parameter. Finally, a table with all the parameters regarding when they were added or removed is summarized as below.

GUC	Appear	Disappear
CHECKPOINT_SEGMENTS	7.1	9.5
CHECKPOINT_TIMEOUT	7.1	–
WAL_FILES	7.1	7.3
CHECKPOINT_WARNING	7.4	–
CHECKPOINT_COMPLETION_TARGET	8.4	–
MAX_WAL_SIZE	9.5	–
MIN_WAL_SIZE	9.5	–
WAL_WRITE_FLUSH_AFTER	9.6	–
COMMIT_DELAY	7.1	–
COMMIT_SIBLINGS	7.1	–
WAL_DEBUG	7.1	–
FSYNC	7.1	–
WAL_FILES	7.1	–
WAL_SYNC_METHOD	7.1	–
WAL_BUFFERS	7.1	–
SYNCHRONOUS_COMMIT	8.3	–
WAL_WRITE_DELAY	8.3	–
ARCHIVE_COMMAND	8	–
ARCHIVE_TIMEOUT	8.2	–
ARCHIVE_MODE	8.3	–
MAX_REPLICATION_SLOTS	9.4
FULL_PAGE_WRITE	8.2	–
WAL_LEVEL	9	–
VACUUM_DEFER_CLEAN_AGE	9	–
WAL_LOG_HINTS	9.4	–
WAL_COMPRESSION	9.5	–
TRACK_COMMIT_TIMESTAMP	9.5	–
WAL_INIT_ZERO	12	–
WAL_RECYCLE	12	–
MAX_WAL_SENDERS	9	–
WAL_SENDER_DELAY	9	–
WAL_KEEP_SEGMENTS	9	–
SYNCHRONOUS_STANDBY_NAMES	9.1	–
REPLICATE_TIMEOUT	9.1	9.3
WAL_SENDER_TIMEOUT	9.3	–
RESTORE_COMMAND	8	–
RECOVERY_TARGET_TIME	8	–
RECOVERY_TARGET_XID	8	–
RECOVERY_TARGET_INCLUSIVE	8	–
RECOVERY_TARGET_TIMELINE	8	–
HOT_STANDBY	9	–
MAX_STANDBY_ARCHIVE_DELAY	9	–
MAX_STANDBY_STREAM_DELAY	9	–
ARCHIVE_CLEANUP_COMMAND	9	–
RECOVERY_END_COMMAND	9	–
STANDBY_MODE	9	12
PRIMARY_CONNINFO	9	–
TRIGGER_FILE	9	12
WAL_RECEIVER_STATUS_INTERVAL	9.1	–
HOT_STANDBY_FEEDBACK	9.1	–
RECOVERY_TARGET_NAME	9.1	–
PAUSE_AT_RECOVERY_TARGET	9.2	9.5
WAL_RECEIVER_TIMEOUT	9.3	–
PRIMARY_SLOT_NAME	9.4	–
RECOVERY_MIN_APPLY_DELAY	9.4	–
WAL_RETRIEVE_RETRY_INTERVAL	9.5	–
RECOVERY_TARGET_ACTION	9.5	–
RECOVERY_TARGET_LSN	10	–
MAX_LOGICAL_REPLICATION_WORKES	11	–
MAX_SYNC_WORKERS_PER_SUBSCRIPTION	12	–