6/26/2023 0 Comments Postgresql xlog positionThis step is not mandatory but is extremely important for a robust replication setup. When configuring streaming replication, you have the option to enable WAL archiving. Looking at the above diagram, you might wonder, what happens when the communication between the WAL sender and the WAL receiver fails? The following diagram represents streaming replication: In the primary server, another process exists, named WAL sender, and is in charge of sending the WAL registries to the standby server as they happen. In practice, a process called WAL receiver, running on the standby server, will connect to the primary server using a TCP/IP connection. This works by transferring WAL records (a WAL file is composed of WAL records) on the fly (record-based log shipping) between a primary server and one or several standby servers without waiting for the WAL file to be filled. Streaming replication allows you to stay more up-to-date than is possible with file-based log shipping. Then, in version 9.0 (back in 2010), streaming replication was introduced. We can represent this file-based log shipping method with the picture below: However, such a low setting will substantially increase the bandwidth required for file shipping). So, there is a window for data loss (you can tune this by using the archive_timeout parameter, which can be set to as low as a few seconds. This replication implementation has the downside: if there is a major failure on the primary servers, transactions not yet shipped will be lost. PostgreSQL implements file-based log shipping by transferring WAL records one file (WAL segment) at a time. This means that the WAL records are directly moved from one database server to another to be applied. The first replication method (warm standby) that PostgreSQL implemented (version 8.2, back in 2006) was based on the log shipping method. Now that we’ve covered the WAL, let’s review the history of replication in PostgreSQL. This parameter requires a restart, so it can be hard to change on running production databases if you have forgotten that.įor further information, you can check the official documentation here or here. Archive adds logging required for WAL archiving hot_standby further adds information needed to run read-only queries on a standby server logical adds information necessary to support logical decoding. The default value is minimal, which writes only the information needed to recover from a crash or immediate shutdown. The wal_level determines how much information is written to the WAL. One parameter that you need to set up when configuring all your PostgreSQL installations is the wal_level. The number of WAL files contained in pg_wal will depend on the value assigned to the parameter checkpoint_segments (or min_wal_size and max_wal_size, depending on the version) in the nf configuration file. They have a unique incremental name in the following format: “00000001 00000000 00000000”. These files have a default size of 16MB (you can change the size by altering the –with-wal-segsize configure option when building the server). The WALs are stored in the pg_wal directory (or pg_xlog in PostgreSQL versions < 10) under the data directory. The insert position is a Log Sequence Number (LSN), a byte offset into the logs, increasing with each new record. Each WAL record will be appended into a WAL file. Using WAL results in a significantly reduced number of disk writes because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction.Ī WAL record will specify the changes made to the data, bit by bit. Any changes that have not been applied to the data pages can be redone from the REDO logs. REDO logs contain all changes made in the database, and they are used for replication, recovery, online backup, and point-in-time recovery ( PITR). The WALs are the REDO logs in PostgreSQL. Write-Ahead Log (WAL)Ī Write-Ahead Log is a standard method for ensuring data integrity, and it is automatically enabled by default. So, let’s quickly review a little bit about write-ahead logs. When talking about replication, we will be talking a lot about WALs. In this blog, we will review a little bit of the history of PostgreSQL’s built-in replication features and deep dive into how streaming replication works. It is a topic that you probably have seen over and over but never gets old. Knowledge of replication is a must for anybody managing databases.
0 Comments
Leave a Reply. |