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.
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.
The current version of wal supports three levels: minimal, archive and hot standby.
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.
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.
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.
Define whether this is a PITR or a standby.
2. Standby synchronous commit(V9.1)
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
Point which is the synchronous standby.
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.
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.
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.
Support ‘logical’ wal level.
If some pages have unimportant page data changes, they also follow the full page write mechanism.
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.
Configure the slot to be used on the standby machine, and name the slot.
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)
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.
Add “always” option, whether a standby machine continues to archive the wal segments it receives.
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.
Record the commit time for the committed transaction.
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 supports three types: minimal, replica and logical.
Add the ‘remote_apply’ option. Wal record applied on the synchronous standby specified by ‘synchronous_standby_names’ before the master transaction is completed.
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.
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.
Specify a stop LSN point for the PITR.
Maximum number of logical replication workers.
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.
When creating the wal segment, initialize all the wal segment data to 0 to ensure that the wal segment has applied for space.
Whether to reuse the old wal segment.
Replace ‘trigger_file’ parameter.
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.
Movead.Li is kernel development of Highgo Software. Since joining into Highgo Software in 2016, Movead takes the most time on researching the code of Postgres and is good at ‘Write Ahead Log’ and ‘Database Backup And Recovery’. Base on the experience Movead has two open-source software on the Postgres database. One is Walminer which can analyze history wal file to SQL. The other one is pg_lightool which can do a single table or block recovery base on base backup and walfiles or walfiles only.
Now he has joined the HighGo community team and hopes to make more contributions to the community in the future.