# Lesson 04: Replication / Physical Replication > Native Markdown source page: Replication / Physical Replication --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** No matching course-page link was captured in the source inventory. > **Archive:** [DBA3-handouts-16-en-20260917.zip](https://edu.postgrespro.com/16/DBA3-handouts-16-en-20260917.zip) > **PDF pages:** 39 ## Page 1: Replication / Physical Replication {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal) ### Use of Course Materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.ru ### Disclaimer Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Topics {#source-page-002} - Replication Use Cases - How Physical Replication Works - WAL Record Delivery - Limitations and Considerations - Synchronous and Asynchronous Replication - Replication Monitoring - Common Issues and Troubleshooting ## Page 3: Replication Use Cases {#source-page-003} **Replication** - the process of synchronizing multiple copies of a database cluster on - different servers - A fundamental mechanism serving multiple goals - reliability - when a failure occurs, - the system must remain operational - scalability - load balancing across servers ### Speaker notes A single server managing databases may not meet the requirements of the system. There are two main challenges that require multiple servers to address. First, reliability: one physical server is a possible point of failure. If the server fails, the system becomes unavailable. Secondly, performance. One server may not be able to handle the load. Often, it's preferable to scale horizontally rather than vertically, distributing the load across multiple servers. This can be due to both hardware costs and the need for different configurations tailored to different workload types (such as OLTP and reporting). In PostgreSQL, distributed systems follow a "shared nothing" architecture: multiple servers operate independently without sharing memory or storage. Therefore, when servers need to work with the same data, that data must be kept synchronized between them. This synchronization mechanism is called replication. ## Page 4: Leader→Replica {#source-page-004} **WAL entries are transmitted to the replica and applied there** - data flows in one direction only - the entire cluster is replicated; selective replication is not supported - The replica is an exact copy of the leader - the same major server version - fully compatible architectures and platforms - Replica is read-only ### Speaker notes During physical replication, servers have assigned roles: one is the leader and others are replicas. The leader sends write-ahead log records to the replica, which then applies them to its data files. The replica does not “understand” the changes, just applies them mechanically without any interpretation of what the changes mean, but it is faster than re-executing the original SQL statements. However, binary compatibility between servers is critical: they must run on the same hardware and software and use matching major versions of PostgreSQL. MIN Since the WAL is shared across the entire cluster, only the cluster as a whole can be replicated. There's no way to filter which log entries get replicated. Replica does not generate records, just applies them. Therefore, replicas are read-only: no data modification operations allowed. ## Page 5: Streaming Replication {#source-page-005} ![Source slide page 5: Streaming Replication](images/page-005.png){#source-figure-005 caption="Source slide page 5: Streaming Replication"} ### Speaker notes If there is a recovery.signal file, the server is restored from the backup: replays the WAL records, restores consistency and completes the recovery. Starting the replica is similar, but the recovery does not complete: the replica continues to read and apply records from the leader. The signal for this is the standby.signal file. In the topic "Base Backup," we considered two ways to deliver WAL records from the server to the client: streaming and file-based. Replication uses the same two mechanisms. Streaming replication is the most common approach by far. The wal receiver process on the replica connects to the leader over the replication protocol, using the connection string from the primary\_conninfo parameter, and reads the stream of WAL records. With streaming replication, the replica lag is minimal and can be reduced to zero if needed. The received records are written to disk as WAL segments, just as they are on the leader. Next, the startup process – the same one that handles crash recovery – applies records to data files, only now it works continuously. The leader starts a wal sender process to handle this connection. Streaming replication can use a replication slot when necessary. The slot name is stored in the primary\_slot\_name parameter. ## Page 6: Streaming Replication Configuration {#source-page-006} Since our configuration will not include a WAL archive, it is essential to use a replication slot at every stage – otherwise, given sufficient delay, the primary may delete the required file segments before they are committed, forcing you to take a new backup and redo the entire setup. Create a slot: ```sql α=> SELECT pg_create_physical_replication_slot('replica'); ``` ```text pg_create_physical_replication_slot ------------------------------------- (replica,) (1 row) ``` Examine the slot we just created: ```sql α=> SELECT * FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]-------+--------- slot_name | replica plugin | slot_type | physical datoid | database | temporary | f active | f active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase | f conflicting | ``` The slot is not initialized right away (restart\_lsn and wal\_status are empty). Take a standalone backup into the beta server’s data directory using the slot we created. When using the -R option, the tool adds the files needed to start the future replica to the backup. ```sh student$ sudo pg_ctlcluster 16 beta stop ``` ```text Cluster is not running. ``` ```sh postgres$ rm -rf /var/lib/postgresql/16/beta ``` ```sh postgres$ pg_basebackup \ --checkpoint=fast \ --pgdata=/var/lib/postgresql/16/beta \ -R --slot=replica ``` Check the slot again: ```sql α=> SELECT * FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]-------+---------- slot_name | replica plugin | slot_type | physical datoid | database | temporary | f active | f active_pid | xmin | catalog_xmin | restart_lsn | 0/2000000 confirmed_flush_lsn | wal_status | reserved safe_wal_size | two_phase | f conflicting | ``` ## Page 7: Page 7 {#source-page-007} Once the backup completes, the slot is initialized and the primary retains all WAL files starting from the beginning of the backup (restart\_lsn, wal\_status). Compare with the line in backup\_label: ```sh postgres$ head -n 1 /var/lib/postgresql/16/beta/backup_label ``` ```text START WAL LOCATION: 0/2000028 (file 000000010000000000000002) ``` The postgresql.auto.conf file was generated by pg\_basebackup because of the -R option. This file contains the connection information for the primary and the replication slot name: ```sh postgres$ cat /var/lib/postgresql/16/beta/postgresql.auto.conf ``` ```sql # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=''/var/run/postgresql'' port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable' primary_slot_name = 'replica' ``` By default, the replica will be a hot standby, meaning it can serve queries during recovery. If this is not necessary, the replica can be warm instead (hot\_standby = off). The tool also created the standby.signal file. Its presence instructs the server to enter continuous recovery mode. ```sh postgres$ ls -l /var/lib/postgresql/16/beta/standby.signal ``` ```text -rw------- 1 postgres postgres 0 Sep 16 23:57 /var/lib/postgresql/16/beta/standby.signal ``` The WAL records needed to bring the replica to a consistent state will be read from the standalone base backup. Then it will enter continuous recovery mode and begin receiving and replaying the WAL stream. ```sh student$ sudo pg_ctlcluster 16 beta start ``` ## Page 8: Replication with a WAL archive {#source-page-008} ![Source slide page 8: Replication with a WAL archive](images/page-008.png){#source-figure-008 caption="Source slide page 8: Replication with a WAL archive"} ### Speaker notes Another way to deliver WAL records is to give the replica access to the WAL archive, just as you would during regular recovery from archive (this mechanism is covered in the "Backup" module). If the replica can't retrieve the next WAL record via the replication protocol for any reason (such as a connection loss), it will attempt to read it from the archive using the command specified in the restore\_command parameter. When the connection is restored, the replica will automatically switch back to streaming replication. In fact, replication can work with just the archive, without streaming replication. But in this case: ● the replica is forced to lag behind the leader by the time it takes to fill the WAL segment; ● the leader has no awareness of the replica's existence, which can lead to problems in certain situations. ## Page 9: Processes {#source-page-009} **Leader Replica** - wal sender - wal receiver - startup - archiver - archiver - (archive\_mode = always) - checkpointer - checkpointer - (checkpoints) - (restartpoints) - background writer - background writer - wal writer - autovacuum ### Speaker notes Different sets of processes run on the leader and on the replica. Beyond the wal sender (on the leader) and wal receiver and startup processes (on the replica) we've already covered, there are others. Common processes: ● background writer – writes dirty pages from the buffer cache to disk (on the replica, pages are read into the buffer cache in the same way to apply WAL records, then written either in the background or during page eviction). ● checkpointer – performs restartpoints (See “WAL Archive”). If a failure occurs during recovery, the replica can resume from the last restartpoint rather than starting over from the beginning. Processes running only on the leader: ● wal writer – the replica doesn't generate WAL records; it only receives them from the leader. ● autovacuum launcher/worker — the replica doesn't perform vacuum operations; when vacuum runs on the primary, it's propagated to the replica through WAL. There is also a process that may or may not be present depending on the configuration: ● archiver — runs only on the leader when archive\_mode = on, but runs on both the leader and replica when archive\_mode = always (covered in more detail in the "Switchover to Replica" module). ## Page 10: Replica Processes {#source-page-010} Compare the processes on the primary: ```sh student$ ps f -C postgres -o pid,command | grep alpha ``` ```text 73709 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/alpha -c config_file=/etc/postgresql/16/alpha/postgresql.conf 73710 \_ postgres: 16/alpha: checkpointer 73711 \_ postgres: 16/alpha: background writer 73713 \_ postgres: 16/alpha: walwriter 73714 \_ postgres: 16/alpha: autovacuum launcher 73715 \_ postgres: 16/alpha: logical replication launcher 73758 \_ postgres: 16/alpha: student student [local] idle 74265 \_ postgres: 16/alpha: walsender postgres [local] streaming 0/3000060 ``` And on the replica: ```sh student$ ps f -C postgres -o pid,command | grep beta ``` ```text 74260 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/beta -c config_file=/etc/postgresql/16/beta/postgresql.conf 74261 \_ postgres: 16/beta: checkpointer 74262 \_ postgres: 16/beta: background writer 74263 \_ postgres: 16/beta: startup recovering 000000010000000000000003 74264 \_ postgres: 16/beta: walreceiver streaming 0/3000060 ``` ## Page 11: Archive Cleanup {#source-page-011} ![Source slide page 11: Archive Cleanup](images/page-011.png){#source-figure-011 caption="Source slide page 11: Archive Cleanup"} ### Speaker notes At restartpoints, the checkpointer process will delete unneeded WAL segments from the local pg\_wal directory. If the archive is used to synchronize just one replica, you can also clean it automatically. The archive\_cleanup\_command parameter specifies the command that will be executed at the end of each restartpoint. For example, you can set it to run the pg\_archivecleanup utility (the server will set the restartpoint segment name as %r): archive\_cleanup\_command = 'pg\_archivecleanup /path/to/archive %r' ONFIG-WAL-ARCHIVE-RECOVERY ## Page 12: Replica usage {#source-page-012} **Allowed** - read-only queries (select, copy to, cursors) - setting server parameters (set, reset) - transaction management (begin, commit, rollback...) - creating a backup (pg\_basebackup) - Not allowed - any changes (insert, update, delete, truncate, nextval...) - locks expecting changes (select for update...) - DDL commands (create, drop...), including creating temporary tables - maintenance commands (vacuum, analyze, reindex...) - access control (grant, revoke...) ### Speaker notes We've already mentioned that replicas don't allow any data modifications. More specifically, the following operations are not allowed: ● modifications to tables, materialized views, and sequences; ● locks (since these require modifying data pages); ● DDL commands, including creating temporary tables; ● commands such as VACUUM and ANALYZE; ● access control commands. A replica can execute read queries when hot\_standby = on. Setting server parameters and transaction management commands will also work. For example, you can start a (reading) transaction with a specific isolation level. Additionally, replicas can be used for creating backups. ## Page 13: Replica usage {#source-page-013} Run several commands on the primary server: ```sql α=> CREATE DATABASE replica_physical; ``` ```sql CREATE DATABASE ``` ```sql α=> \c replica_physical ``` ```text You are now connected to database "replica_physical" as user "student". ``` ```sql α=> CREATE TABLE test(s text); ``` ```sql CREATE TABLE ``` ```sql α=> INSERT INTO test VALUES ('Hello, world!'); ``` ```sql INSERT 0 1 ``` Check the replica: ```sh student$ psql -p 5433 ``` ```sql β=> \c replica_physical ``` ```text You are now connected to database "replica_physical" as user "student". ``` ```sql β=> SELECT * FROM test; ``` ```text s --------------- Hello, world! (1 row) ``` No changes can be done on replica directly: ```sql β=> INSERT INTO test VALUES ('Replica'); ``` ```sql ERROR: cannot execute INSERT in a read-only transaction ``` In general, you can tell a replica apart from the primary using the function: ```sql β=> SELECT pg_is_in_recovery(); ``` ```text pg_is_in_recovery ------------------- t (1 row) ``` ## Page 14: Limitations {#source-page-014} Isolation and MVCC - large number of savepoints may slow down query execution - Serializable isolation level is not supported - Triggers and locks - triggers and advisory locks do not work - Creating backups on a replica - the full\_page\_writes parameter must be enabled on the leader beforehand - delays caused by waiting for checkpoints and segment switches - Order of changes - tablespace directories – create on replica first - max\_... parameter values – increase on replica first ### Speaker notes Replication has several limitations and considerations. If the leader is running a transaction with more than 64 nested transactions (that is, it is actively using savepoints), snapshot creation on the replica will be suspended, preventing query execution for a while. The Serializable isolation level is not supported on replicas. Triggers won't fire on the replica since they've already executed on the leader: only their results get replicated. Advisory locks won't work either. When performing backups from a replica, there's no way to fully control the primary server – specifically, you can't trigger checkpoints or enable the full\_page\_writes parameter. Therefore, you need to enable this parameter on the leader in advance, and then wait for a checkpoint to occur, which can significantly delay the process. Tablespace creation is replicated, but if the OS user who started PostgreSQL does not have the privileges to create the required directory, an error will occur. The best approach is to first create the directories on the leader and all replicas, then execute the CREATE TABLESPACE command. If you increase a resource allocation parameter on the leader, replication may stop. This affects max\_connections, max\_prepared\_transactions, max\_locks\_per\_transaction, max\_wal\_senders and max\_worker\_processes. ## Page 15: Monitoring {#source-page-015} ![Source slide page 15: Monitoring](images/page-015.png){#source-figure-015 caption="Source slide page 15: Monitoring"} ### Speaker notes During replication, issues may arise that should be caught by properly configured monitoring. The key data is WAL positions. There are five main steps: 1) record created on leader 2) record transmitted by wal sender 3) record received by wal receiver 4) record synchronised to disk (flush) 5) record applied by startup process The leader usually tracks all five steps. The first one is tracked through the pg\_current\_wal\_lsn function, while the others come from the pg\_stat\_replication view. The replica passes replication status to the leader each time it writes to disk, but at least once every wal\_receiver\_status\_interval (default is 10 seconds). Information about the replication slot can be found in the pg\_replication\_slots view. Some of the replication progress information can also be obtained from the replica: in the pg\_stat\_wal\_receiver view and via pg\_last\_wal\_receive\_lsn and pg\_last\_wal\_replay\_lsn functions. Both functions pg\_is\_in\_recovery and pg\_get\_wal\_replay\_pause\_state return replication status information. The log\_replication\_commands parameter enables logging of replication protocol commands to the server log. ## Page 16: Replication Monitoring {#source-page-016} The replica is currently in recovery mode and applying WAL records: ```sql β=> SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(); ``` ```text pg_is_in_recovery | pg_get_wal_replay_pause_state -------------------+------------------------------- t | not paused (1 row) ``` The current max\_connections value on the primary: ```text α=> SHOW max_connections; ``` ```text max_connections ----------------- 100 (1 row) ``` If the number of users has grown, we’ll want to increase this limit: ```sql α=> ALTER SYSTEM SET max_connections = 150; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 alpha restart ``` How’s the replica doing? ```sql β=> SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(); ``` ```text pg_is_in_recovery | pg_get_wal_replay_pause_state -------------------+------------------------------- t | paused (1 row) ``` Replication has stopped. The reason is in the log: ```sh student$ tail -n 7 /var/log/postgresql/postgresql-16-beta.log ``` ```sql 2026-09-16 23:58:21.189 MSK [74263] WARNING: hot standby is not possible because of insufficient parameter settings 2026-09-16 23:58:21.189 MSK [74263] DETAIL: max_connections = 100 is a lower setting than on the primary server, where its value was 150. 2026-09-16 23:58:21.189 MSK [74263] CONTEXT: WAL redo at 0/3432EE8 for XLOG/PARAMETER_CHANGE: max_connections=150 max_worker_processes=8 max_wal_senders=10 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=replica wal_log_hints=off track_commit_timestamp=off 2026-09-16 23:58:21.189 MSK [74263] LOG: recovery has paused 2026-09-16 23:58:21.189 MSK [74263] DETAIL: If recovery is unpaused, the server will shut down. 2026-09-16 23:58:21.189 MSK [74263] HINT: You can then restart the server after making the necessary configuration changes. 2026-09-16 23:58:21.189 MSK [74263] CONTEXT: WAL redo at 0/3432EE8 for XLOG/PARAMETER_CHANGE: max_connections=150 max_worker_processes=8 max_wal_senders=10 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=replica wal_log_hints=off track_commit_timestamp=off ``` Follow the recommendation and increase the value on the replica. ```sql β=> ALTER SYSTEM SET max_connections = 150; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 beta restart ``` Check the replica: ```sh student$ psql -p 5433 -d replica_physical ``` ```sql β=> SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(); ``` ## Page 17: Page 17 {#source-page-017} ```text pg_is_in_recovery | pg_get_wal_replay_pause_state -------------------+------------------------------- t | not paused (1 row) ``` Replication continues. To avoid replication stopping, limits should be increased on the replica first. The state of the replication can be checked on the primary server: To access this information, a user must be a member of the pg\_read\_all\_stats group role or a superuser. ```sh student$ psql -d replica_physical ``` ```text α=> \drg student|pg_monitor ``` ```text List of role grants Role name | Member of | Options | Grantor ------------+------------------------+--------------+---------- pg_monitor | pg_read_all_settings | INHERIT, SET | postgres pg_monitor | pg_read_all_stats | INHERIT, SET | postgres pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres student | pg_checkpoint | INHERIT, SET | postgres student | pg_create_subscription | INHERIT, SET | postgres student | pg_monitor | INHERIT, SET | postgres student | pg_read_server_files | INHERIT, SET | postgres student | pg_write_server_files | INHERIT, SET | postgres (8 rows) ``` The pg\_monitor role is a member of the pg\_read\_all\_stats role and inherits its privileges. ```sql α=> SELECT * FROM pg_stat_replication \gx ``` ```text -[ RECORD 1 ]----+------------------------------ pid | 75323 usesysid | 10 usename | postgres application_name | 16/beta client_addr | client_hostname | client_port | -1 backend_start | 2026-09-16 23:58:27.895802+03 backend_xmin | state | streaming sent_lsn | 0/3437440 write_lsn | 0/3437440 flush_lsn | 0/3437440 replay_lsn | 0/3437440 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2026-09-16 23:58:30.809823+03 ``` > [!NOTE] > **Note** > Note the \*\_lsn and \*\_lag fields – they show the replica’s lag at different stages. All positions are currently in sync, with zero lag. Now let’s insert a large number of rows into the table to observe replication in action. ```sql α=> INSERT INTO test SELECT 'Just a line' FROM generate_series(1,1_000_000); ``` ```sql INSERT 0 1000000 ``` ```sql α=> SELECT *, pg_current_wal_lsn() from pg_stat_replication \gx ``` ## Page 18: Page 18 {#source-page-018} ```text -[ RECORD 1 ]------+------------------------------ pid | 75323 usesysid | 10 usename | postgres application_name | 16/beta client_addr | client_hostname | client_port | -1 backend_start | 2026-09-16 23:58:27.895802+03 backend_xmin | state | streaming sent_lsn | 0/7640000 write_lsn | 0/75E0000 flush_lsn | 0/75E0000 replay_lsn | 0/75806F8 write_lag | 00:00:00.098318 flush_lag | 00:00:00.098318 replay_lag | 00:00:00.196214 sync_priority | 0 sync_state | async reply_time | 2026-09-16 23:58:35.839864+03 pg_current_wal_lsn | 0/79151A8 ``` A small lag has appeared. Check the replica: ```sql β=> SELECT count(*) FROM test; ``` ```text count --------- 1000001 (1 row) ``` All rows have been delivered successfully. Check the replication status one more time: ```sql α=> SELECT *, pg_current_wal_lsn() from pg_stat_replication \gx ``` ```text -[ RECORD 1 ]------+------------------------------ pid | 75323 usesysid | 10 usename | postgres application_name | 16/beta client_addr | client_hostname | client_port | -1 backend_start | 2026-09-16 23:58:27.895802+03 backend_xmin | state | streaming sent_lsn | 0/79171B8 write_lsn | 0/79171B8 flush_lsn | 0/79171B8 replay_lsn | 0/79171B8 write_lag | 00:00:00.000609 flush_lag | 00:00:00.008241 replay_lag | 00:00:00.009082 sync_priority | 0 sync_state | async reply_time | 2026-09-16 23:58:36.077635+03 pg_current_wal_lsn | 0/79171B8 ``` All positions are now in sync. ## Page 19: Replication Modes {#source-page-019} Asynchronous - synchronous\_commit - = off - local - Synchronous - commit with callback from the synchronous replica - provides reliability but not consistency - synchronous\_commit - = remote\_write - = on - remote\_apply - synchronous\_standby\_names = - replica list - FIRST N (replica list) - ANY N (replica list) ### Speaker notes There are two transaction commit modes. The faster asynchronous mode carries a risk of losing already committed changes if a crash occurs. In synchronous mode, the COMMIT command doesn't complete until the record reaches disk. If you have multiple servers, you can still improve reliability by waiting for the replica to confirm that it has commited a record. In this case, the data will not be lost even if the disk fails. Synchronous replication is enabled using the same synchronous\_commit parameter that controls synchronous commits. Different values of this parameter let you control the level of reliability. With remote\_write, the leader waits for confirmation that the replica has received the record (though there's still a chance of losing changes if the replica crashes before writing the data to disk). With on, the leader waits for confirmation that the record has been written to the replica's disk (this is the reliable mode; however, an application querying the replica might not yet see the changes). Finally, the remote\_apply value makes the primary wait until the replica has actually applied the record. Each next mode introduces progressively longer delays. The leader can synchronize with one or multiple replicas. You can specify a list of replicas in priority order or set up quorum-based synchronization, where the leader waits for confirmation from any N of the available replicas. ## Page 20: Segment Loss {#source-page-020} **The leader might delete a WAL file that the replica still needs** - if the replica didn't collect it yet - (i.e. because it was down for a while) - Solution 1. Replication slot - makes the leader depend on the replica, requires monitoring - max\_slot\_wal\_keep\_size = −1 - Solution 2. WAL archive - no slot required ### Speaker notes Several challenges can arise during replication, each with different possible solutions. PostgreSQL periodically removes WAL files that are no longer needed for recovery. The leader might delete a file containing data that hasn't yet been transmitted to the replica. This isn't an issue when a write-ahead log archive is available, since the replica can simply read the required data from the archive if streaming replication fails, then switch back to receiving data from the stream once it catches up with the leader. Without the archive, the replica won't be able to continue recovery and will need to be reconstructed from a backup. To prevent this, you can use a replication slot. However, this creates a dependency. If the replica stops collecting WAL records, they will accumulate on the leader, potentially flooding the file system. To prevent this, you can limit the accumulated volume by setting the max\_slot\_wal\_keep\_size. If this limit is exceeded, the slot will stop, and the server will be able to delete the WAL files again. In any case, slots are yet another item you will have to monitor. ## Page 21: Impact of the Replication Slot {#source-page-021} Stop the replica. ```sh student$ sudo pg_ctlcluster 16 beta stop ``` Limit the WAL size to two segments. ```sql α=> ALTER SYSTEM SET min_wal_size = '32MB'; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM SET max_wal_size = '32MB'; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 alpha reload ``` The slot is inactive, but it retains the LSN of the last record received by the replica: ```sql α=> SELECT active, restart_lsn, wal_status FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]---------- active | f restart_lsn | 0/79171B8 wal_status | reserved ``` Let’s insert more rows into the table. ```sql α=> INSERT INTO test SELECT 'Just a line' FROM generate_series(1,1_000_000); ``` ```sql INSERT 0 1000000 ``` The LSN in the slot has not changed: ```sql α=> SELECT active, restart_lsn, wal_status FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]---------- active | f restart_lsn | 0/79171B8 wal_status | extended ``` Create a checkpoint, which should clean up the WAL since its size exceeds the allowed limit. ```text α=> CHECKPOINT; ``` ```text CHECKPOINT ``` What is the WAL size now? ```sql α=> SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir(); ``` ```text pg_size_pretty ---------------- 96 MB (1 row) ``` The checkpoint did not remove the segments – they are being retained by the slot, even though the size limit has been exceeded. If a slot is left unattended, it can exhaust all available disk space. In most cases, keeping the main server running smoothly is more important than keeping the replica in sync. To prevent WAL from growing out of control, you can limit the amount retained by the slot: ```sql α=> ALTER SYSTEM SET max_slot_wal_keep_size = '16MB'; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 alpha reload ``` ```text α=> CHECKPOINT; ``` ```text CHECKPOINT ``` Now the checkpoint cleans up the WAL: ```sql α=> SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir(); ``` ## Page 22: Page 22 {#source-page-022} ```text pg_size_pretty ---------------- 32 MB (1 row) ``` But the slot no longer guarantees the availability of WAL records (wal\_status=lost): ```sql α=> SELECT active, restart_lsn, wal_status FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]----- active | f restart_lsn | wal_status | lost ``` And the replica cannot synchronize: ```sh student$ sudo pg_ctlcluster 16 beta start ``` ```sh student$ sudo tail -n 2 /var/log/postgresql/postgresql-16-beta.log ``` ```sql 2026-09-16 23:58:46.203 MSK [76622] FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000007 has already been removed 2026-09-16 23:58:46.204 MSK [76619] LOG: waiting for WAL to become available at 0/7002000 ``` In such cases, you can synchronize the replica using the archive by setting the restore\_command parameter. If that’s not an option, you’ll need to reconfigure replication from scratch by creating a new base backup. ## Page 23: Conflicts: a Problem {#source-page-023} **WAL records incompatible with queries on the replica** - vacuum removes tuples that are still needed by queries on the replica - exclusive locks on objects - buffer pinning - Monitoring - pg\_stat\_database\_conflicts ### Speaker notes Another problem occurs when when the replica is used to run queries. The leader can perform an action that conflicts with a query running on the replica: ● Vacuum may remove a tuple that's being queried, or it may need to acquire a lock that conflicts with the query. ● A WAL record may aquire an exclusive lock on an object (for example, when dropping a table). ● Buffer pinning may cause conflicts sometimes. You can monitor such conflicts using the pg\_stat\_database\_conflicts view. ## Page 24: Conflicts: Solutions {#source-page-024} **Solution 0: Avoid conflicts** - avoid running commands that acquire locks - disable file truncation at end of vacuuming: retention setting - vacuum\_truncate = off - Solution 1: Delay applying conflicting records - max\_standby\_streaming\_delay - max\_standby\_archive\_delay - except deadlocks, DROP TABLESPACE, DROP DATABASE ### Speaker notes One way to handle conflicts is to avoid operations on the leader that would result in exclusive locks. For example, you can avoid running commands like DROP TABLE, TRUNCATE, LOCK, DROP INDEX, DROP TRIGGER, and similar operations, or run them only when you know there are no active queries on the replica. Some implicit locks can be avoided by disabling the corresponding functionality on the leader. For instance, if vacuum frees up pages at the end of a file, shrinking the file size requires an exclusive lock on the table. Then, you can disable file truncation by setting the table storage parameter vacuum\_truncate = off. However, vacuum cannot be disabled or postponed indefinitely, so this approach won't help avoid conflicts when vacuum removes tuples that are still needed by queries. Another approach is to delay the application of conflicting records on the replica. This method works with both streaming replication and archive-based replication; it delays the application of records related to both vacuum operations and exclusive locks. You can set the maximum application delay (measured from when the record is received). If the conflicting query hasn't finished by that time, it will be terminated. However, conflicts caused by rare causes (database or tablespace deletion, deadlocks) are not delayed and are resolved immediately by terminating the query. ## Page 25: Conflicts: Solutions {#source-page-025} **Solution 2: feedback via the replication protocol** - a snapshot on the replica will prevent tuple removal - in the same manner as - hot\_standby\_feedback = on - only for vacuum conflicts with queries on the replica - Monitoring - pg\_stat\_replication.backend\_xmin - pg\_replication\_slots.xmin ### Speaker notes Another approach is to defer conflicting operations on the leader. To enable this, enable replication protocol feedback. This method only works for vacuum operations (exclusive locks cannot be deferred) and only with streaming replication (if the replica switches to retrieving records from the archive, it essentially becomes invisible to the leader). Each data snapshot includes a transaction horizon – a transaction ID value that marks the boundary between tuples that are no longer needed and those that must be retained. The minimum horizon across all snapshots on the replica determines what can be vacuumed. This topic is covered in detail in the "Data Snapshots" section of the DBA2 course. With feedback enabled, the leader knows which tuples the replica needs. In this case, from the vacuum perspective, a query running on the replica is treated exactly the same as a query running on the leader itself. Feedback allows you to track the vacuum horizon on the leader side. When a replication slot is not in use, the horizon can be monitored through the pg\_stat\_replication.backend\_xmin field, while with a slot it appears in pg\_replication\_slots.xmin. Keep in mind that when feedback is enabled, even an inactive slot will prevent vacuum from proceeding, which can cause bloating. The practice assignments explore this effect. ## Page 26: Takeaways {#source-page-026} **The replication mechanism works by streaming WAL records to the replica and applying them there** - primary mode: streaming replication - can be supplemented with WAL archiving - The replica is an exact copy of the leader - does not generate its own WAL records and operates in read-only mode - A complex mechanism that requires proper configuration and monitoring - configurations vary significantly depending on your specific requirements ## Page 27: Practice {#source-page-027} 1. Configure streaming replication between two servers in synchronous mode, with a slot, without feedback. 2. Verify that replication works as intended. Make sure that when the replica is stopped, commits on the leader are not completed. 3. Simulate a scenario where a query running on the replica gets terminated because the leader has vacuumed tuples that the query still needs. 4. Disable deferral of conflicting changes and verify that queries are canceled immediately. 5. Enable feedback and verify that vacuum on the leader doesn't interrupt query execution. 6. Stop the replica and verify that the replication slot prevents vacuum from removing tuples. Drop the slot to allow vacuum to resume. ### Speaker notes 1. For synchronous mode, set on the leader: ● synchronous\_commit = on ● synchronous\_standby\_names = '"16/beta"' 3. Start a transaction with Repeatable Read isolation level on the replica and periodically query the table. Meanwhile, on the leader, modify that table and run vacuum. Keep in mind that the max\_standby\_streaming\_delay parameter defaults to 30 seconds. 4. Set the max\_standby\_streaming\_delay parameter to zero. 5. Enable the hot\_standby\_feedback parameter on the replica. The maximum notification interval is controlled by the wal\_receiver\_status\_interval parameter (defaults to 10 seconds). 6. After completing the tasks, delete the database you created and reset the configuration. Remember to disable the synchronous commit mode, otherwise a stopped replica will prevent transactions from committing. Don't set synchronous\_commit to off. Instead, set synchronous\_commit = "local" or reset the synchronous\_standby\_names parameter. ## Page 28: 1. Synchronous Physical Streaming Replication {#source-page-028} ```sql α=> CREATE DATABASE replica_physical; ``` ```sql CREATE DATABASE ``` Create and start the replica the same way as in the demonstration. ```sql α=> \c replica_physical ``` ```text You are now connected to database "replica_physical" as user "student". ``` Backup: ```sh student$ sudo pg_ctlcluster 16 beta stop ``` ```text Cluster is not running. ``` ```sh postgres$ rm -rf /var/lib/postgresql/16/beta ``` ```sh postgres$ pg_basebackup \ --pgdata /var/lib/postgresql/16/beta \ --checkpoint=fast \ -R --create-slot --slot=replica ``` ```sh student$ sudo pg_ctlcluster 16 beta start ``` For the main server to find the replica, the cluster\_name parameter has already been set in the main configuration: ```sh student$ psql -p 5433 -c 'SHOW cluster_name;' ``` ```text cluster_name -------------- 16/beta (1 row) ``` Another option is to add the application\_name attribute to the connection string: ```text primary_conninfo='user=student port=5432 application_name=16/beta' ``` Now enable synchronous replication mode on the primary. ```sql α=> ALTER SYSTEM SET synchronous_commit = on; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM SET synchronous_standby_names = '"16/beta"'; ``` ```sql ALTER SYSTEM ``` If all replicas are synchronous, you can set a value that matches any name: ```text synchronous_standby_names = '*' ``` However, the primary cannot distinguish a replica from, say, the pg\_receivewal utility. With this configuration, it will attempt to synchronize with any client connected via the replication protocol, which can result in commits waiting indefinitely. It’s better to list names explicitly. ```sh student$ sudo pg_ctlcluster 16 alpha reload ``` ### 2. Physical Replication Verification ```sql α=> SELECT * FROM pg_stat_replication \gx ``` ## Page 29: Page 29 {#source-page-029} ```text -[ RECORD 1 ]----+------------------------------ pid | 98849 usesysid | 10 usename | postgres application_name | 16/beta client_addr | client_hostname | client_port | -1 backend_start | 2026-09-17 00:11:28.62954+03 backend_xmin | state | streaming sent_lsn | 0/3000C50 write_lsn | 0/3000C50 flush_lsn | 0/3000C50 replay_lsn | 0/3000C50 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2026-09-17 00:11:31.677795+03 ``` sync\_state: sync means that replication is running in the synchronous mode: application\_name: 16/beta – the name by which the primary finds the replica. Stop the replica. ```sh student$ sudo pg_ctlcluster 16 beta stop ``` ```text α=> BEGIN; ``` ```text BEGIN ``` ```sql α=> CREATE TABLE test(id integer); ``` ```sql CREATE TABLE ``` ```text α=> COMMIT; ``` The commit waits for the synchronous replica... ```sh student$ sudo pg_ctlcluster 16 beta start ``` After the replica starts, the commit completes. ```text COMMIT ``` ### 3. Query Cancellation Due to Vacuum on the Primary ```sql α=> INSERT INTO test VALUES (1); ``` ```sql INSERT 0 1 ``` ```sh student$ psql -p 5433 -d replica_physical ``` When applying a WAL record on the replica is delayed beyond deadlock\_timeout due to a conflict with a running query, we want a message to be written to the server log. ```text β=> \dconfig deadlock_timeout ``` ```text List of configuration parameters Parameter | Value ------------------+------- deadlock_timeout | 1s (1 row) ``` To do this, set the log\_recovery\_conflict\_waits parameter. ```sql β=> ALTER SYSTEM SET log_recovery_conflict_waits = on; ``` ```sql ALTER SYSTEM ``` Also reduce the maximum time streaming replication can be paused before a query on the replica is canceled. ```sql β=> ALTER SYSTEM SET max_standby_streaming_delay = '9s'; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 beta reload ``` Start a transaction with the Repeatable Read isolation level: the first statement will create a data snapshot that all statements in the transaction will use. ## Page 30: Page 30 {#source-page-030} ```text β=> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` ```text BEGIN ``` ```sql β=> SELECT * FROM test; ``` ```text id ---- 1 (1 row) ``` On the primary, update the only row and run vacuum. The first tuple will be removed in the process. ```sql α=> UPDATE test SET id = 2; ``` ```sql UPDATE 1 ``` ```sql α=> VACUUM test; ``` ```sql VACUUM ``` After 5 seconds, the query on the replica still completes – the replica is delaying the application of the conflicting WAL record: ```sql β=> SELECT * FROM test; ``` ```text id ---- 1 (1 row) ``` And the server log already contains a message about waiting for the conflict to be resolved: ```sh postgres$ tail -3 /var/log/postgresql/postgresql-16-beta.log ``` ```text 2026-09-17 00:11:39.371 MSK [99295] LOG: recovery still waiting after 1024.875 ms: recovery conflict on snapshot 2026-09-17 00:11:39.371 MSK [99295] DETAIL: Conflicting process: 99381. 2026-09-17 00:11:39.371 MSK [99295] CONTEXT: WAL redo at 0/301B8A0 for Heap2/PRUNE: snapshotConflictHorizon: 738, nredirected: 1, ndead: 0, nunused: 0, redirected: [1->2], dead: [], unused: []; blkref #0: rel 1663/16409/16410, blk 0 ``` Five seconds later, the same query is aborted – the tuple from the snapshot no longer exists. ```sql β=> SELECT * FROM test; ``` ```text FATAL: terminating connection due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost ``` ### 4. Disable the Delay of Conflicting WAL Record Application Set the delay for applying conflicting changes to zero. ```sh student$ psql -p 5433 -d replica_physical ``` ```sql β=> ALTER SYSTEM SET max_standby_streaming_delay = 0; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 beta reload ``` Start a new transaction... ```text β=> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` ```text BEGIN ``` ```sql β=> SELECT * FROM test; ``` ```text id ---- 2 (1 row) ``` On the primary, update the row and run vacuum... ## Page 31: Page 31 {#source-page-031} ![Source slide page 31: Page 31](images/page-031.png){#source-figure-031 caption="Source slide page 31: Page 31"} ```sql α=> UPDATE test SET id = 3; ``` ```sql UPDATE 1 ``` ```sql α=> VACUUM test; ``` ```sql VACUUM ``` The query on the replica is canceled immediately: ```sql β=> SELECT * FROM test; ``` ```text FATAL: terminating connection due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost ``` ### 5. Feedback Set up feedback and, to avoid long waits, a short notification interval. ```sh student$ psql -p 5433 -d replica_physical ``` ```sql β=> ALTER SYSTEM SET hot_standby_feedback = on; ``` ```sql ALTER SYSTEM ``` ```sql β=> ALTER SYSTEM SET wal_receiver_status_interval = '1s'; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 beta reload ``` Start a new transaction... ```text β=> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` ```text BEGIN ``` ```sql β=> SELECT * FROM test; ``` ```text id ---- 3 (1 row) ``` On the primary, update the row and run vacuum... ```sql α=> UPDATE test SET id = 4; ``` ```sql UPDATE 1 ``` ```sql α=> VACUUM VERBOSE test; ``` ```sql INFO: vacuuming "replica_physical.public.test" INFO: finished vacuuming "replica_physical.public.test": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 0 removed, 2 remain, 1 are dead but not yet removable removable cutoff: 740, which was 1 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 16 hits, 0 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 188 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` Thanks to feedback, vacuum cannot remove the old tuple (tuples: 0 removed, 2 remain, 1 are dead but not yet removable). ```sql β=> SELECT * FROM test; ``` ```text id ---- 3 (1 row) ``` ## Page 32: Page 32 {#source-page-032} ```text β=> COMMIT; ``` ```text COMMIT ``` ```sql α=> VACUUM VERBOSE test; ``` ```sql INFO: vacuuming "replica_physical.public.test" INFO: finished vacuuming "replica_physical.public.test": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 1 removed, 1 remain, 0 are dead but not yet removable removable cutoff: 741, which was 0 XIDs old when operation ended new relfrozenxid: 740, which is 1 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 17 hits, 0 misses, 0 dirtied WAL usage: 3 records, 0 full page images, 308 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` Once the transaction on the replica completes, the primary can run vacuum (tuples: 1 removed, 1 remain, 0 are dead but not yet removable). The WAL records are replicated, but they won’t cause any conflicts. ### 6. Effect of a Replication Slot on Vacuum When the Replica Is Stopped Cancel synchronization and stop the replica: ```sql α=> ALTER SYSTEM RESET synchronous_standby_names; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 alpha reload ``` ```sh student$ sudo pg_ctlcluster 16 beta stop ``` Update the table row once more: ```sql α=> UPDATE test SET id = 5; ``` ```sql UPDATE 1 ``` The slot is inactive, but it retains the minimum xmin of the replica’s data snapshots: ```sql α=> SELECT active, xmin FROM pg_replication_slots; ``` ```text active | xmin --------+------ f | 741 (1 row) ``` This xmin value is less than the minimum xmin of local data snapshots: ```sql α=> SELECT min(backend_xmin::text::numeric) FROM pg_stat_activity; ``` ```text min ----- 742 (1 row) ``` Therefore, the slot will hold back vacuum (tuples: 0 removed, 2 remain, 1 are dead but not yet removable). ```sql α=> VACUUM VERBOSE test; ``` ```sql INFO: vacuuming "replica_physical.public.test" INFO: finished vacuuming "replica_physical.public.test": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 0 removed, 2 remain, 1 are dead but not yet removable removable cutoff: 741, which was 1 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 16 hits, 0 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 188 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` Delete the slot. ```sql α=> SELECT pg_drop_replication_slot('replica'); ``` ## Page 33: Page 33 {#source-page-033} ```text pg_drop_replication_slot -------------------------- (1 row) ``` Now vacuum will run successfully (tuples: 1 removed). ```sql α=> VACUUM VERBOSE test; ``` ```sql INFO: vacuuming "replica_physical.public.test" INFO: finished vacuuming "replica_physical.public.test": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 1 removed, 1 remain, 0 are dead but not yet removable removable cutoff: 742, which was 0 XIDs old when operation ended new relfrozenxid: 741, which is 1 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 17 hits, 0 misses, 0 dirtied WAL usage: 3 records, 0 full page images, 308 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` ### Cleanup Drop the database and reset configuration. ```sql α=> \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql α=> DROP DATABASE replica_physical; ``` ```sql DROP DATABASE ``` ```sql α=> ALTER SYSTEM RESET synchronous_commit; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM RESET synchronous_standby_names; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 alpha restart ``` ## Page 34: Practice+ {#source-page-034} **1. Configure streaming physical replication together with WAL archiving, using the virtual machine's network interface for the replication stream. 2. Verify that streaming replication is working through the interface. 3. Simulate a network failure by disabling the virtual machine's network interface. 4. Ensure that streaming replication has stopped and the replica is receiving changes from the WAL archive, but with lag. 5. Restart the virtual machine's network interface, verifying that streaming replication has automatically recovered.** ### Speaker notes 1. You can check the status of network interfaces with the command: ip a You can check the availability of the network address with the command: ping -c1 Edit the pg\_hba.conf file to allow connections to the alpha server through any network interfaces, and configure it to listen on all network interfaces. The wal\_receiver process ends if there is no network connection within the wal\_receiver\_timeout time. Specify a small value. 3. Simulate network failure by turning off the network interface: sudo ip l set enp0s3 down 4. Verify that after switching to the next WAL segment, the replica receives the changes made before switching. 5. You can switch the network interface back on with the command: sudo ip l set enp0s3 up After completing the exercises, drop the database you created and reset the configuration parameters to their defaults. ## Page 35: 1. Physical Streaming Replication and WAL Archive {#source-page-035} The primary virtual machine network interface is enp0s3: ```sh student$ ip a ``` ```text 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host noprefixroute valid_lft forever preferred_lft forever 2: enp0s3: mtu 1500 qdisc fq_codel state UP group default qlen 1000 link/ether 08:00:27:4f:a4:65 brd ff:ff:ff:ff:ff:ff inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic noprefixroute enp0s3 valid_lft 78573sec preferred_lft 78573sec inet6 fd17:625c:f037:2:a7c5:76e6:796c:1d04/64 scope global temporary dynamic valid_lft 86031sec preferred_lft 14031sec inet6 fd17:625c:f037:2:a00:27ff:fe4f:a465/64 scope global dynamic mngtmpaddr valid_lft 86031sec preferred_lft 14031sec inet6 fe80::a00:27ff:fe4f:a465/64 scope link valid_lft forever preferred_lft forever ``` Verify that the interface is accessible. ```sh student$ ping -c1 10.0.2.15 ``` ```sql PING 10.0.2.15 (10.0.2.15) 56(84) bytes of data. 64 bytes from 10.0.2.15: icmp_seq=1 ttl=64 time=0.031 ms ``` ```text --- 10.0.2.15 ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.031/0.031/0.031/0.000 ms ``` Allow connections to the alpha server from IPv4 addresses belonging to the server’s interface networks. ```sh postgres$ sed -i.bak 's/127\.0\.0\.1\/32/samenet/' /etc/postgresql/16/alpha/pg_hba.conf ``` Enable the server to listen on all interfaces and configure WAL archiving. ```sql α=> ALTER SYSTEM SET listen_addresses = '*'; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM SET archive_mode = on; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'; ``` ```sql ALTER SYSTEM ``` Directory for the WAL archive. ```sh postgres$ mkdir /var/lib/postgresql/archive ``` ```sh student$ sudo pg_ctlcluster 16 alpha restart ``` Backup: ```sh student$ sudo pg_ctlcluster 16 beta stop ``` ```text Cluster is not running. ``` ```sh postgres$ rm -rf /var/lib/postgresql/16/beta ``` ```sh postgres$ pg_basebackup --pgdata=/var/lib/postgresql/16/beta --checkpoint=fast ``` Prepare the configuration for the replica. The wal\_receiver\_timeout parameter defines how long the replica waits before terminating an idle connection. This allows the replica to detect a dropped network connection more quickly. ```sh postgres$ cat << EOF | tee /etc/postgresql/16/beta/conf.d/rest.conf restore_command = 'cp /var/lib/postgresql/archive/%f %p' primary_conninfo = 'user=postgres password=postgres host=10.0.2.15 port=5432' wal_receiver_timeout = '2s' EOF ``` ```text restore_command = 'cp /var/lib/postgresql/archive/%f %p' ``` ## Page 36: 2. Replication Check {#source-page-036} ```text primary_conninfo = 'user=postgres password=postgres host=10.0.2.15 port=5432' wal_receiver_timeout = '2s' ``` ```sh postgres$ touch /var/lib/postgresql/16/beta/standby.signal ``` ```sh student$ sudo pg_ctlcluster 16 beta start ``` ```sh student$ psql ``` ```sql α=> CREATE DATABASE replica_physical; ``` ```sql CREATE DATABASE ``` ```sql α=> \c replica_physical ``` ```text You are now connected to database "replica_physical" as user "student". ``` ```sql α=> CREATE TABLE test(s text); ``` ```sql CREATE TABLE ``` ```sql α=> INSERT INTO test VALUES ('Hello, world!'); ``` ```sql INSERT 0 1 ``` The replica receives WAL records via the stream... ```sh student$ psql -p 5433 ``` ```sql β=> SELECT pid, status, written_lsn, flushed_lsn, latest_end_lsn, sender_host FROM pg_stat_wal_receiver \gx ``` ```text -[ RECORD 1 ]--+---------- pid | 101744 status | streaming written_lsn | 0/3486380 flushed_lsn | 0/3486380 latest_end_lsn | 0/3486380 sender_host | 10.0.2.15 ``` ...and reports received records to the primary without delay: ```sql α=> SELECT * FROM pg_stat_replication \gx ``` ```text -[ RECORD 1 ]----+------------------------------ pid | 101745 usesysid | 10 usename | postgres application_name | 16/beta client_addr | 10.0.2.15 client_hostname | client_port | 55376 backend_start | 2026-09-17 00:12:33.618001+03 backend_xmin | state | streaming sent_lsn | 0/3486380 write_lsn | 0/3486380 flush_lsn | 0/3486380 replay_lsn | 0/3486380 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2026-09-17 00:12:40.460627+03 ``` ```sql β=> \c replica_physical ``` ```text You are now connected to database "replica_physical" as user "student". ``` The table has been replicated: ```sql β=> SELECT * FROM test; ``` ```text s --------------- Hello, world! (1 row) ``` ## Page 37: 3. Network Failure {#source-page-037} Disable the network interface to simulate a network failure. ```sh student$ sudo ip l set enp0s3 down ``` The network is unavailable. ```sh student$ ping -c1 10.0.2.15 ``` ```text ping: connect: Network is unreachable ``` The wal receiver process has stopped: ```sql β=> SELECT * FROM pg_stat_wal_receiver \gx ``` ```text (0 rows) ``` On the primary, insert a row... ```sql α=> INSERT INTO test VALUES ('Network failure.'); ``` ```sql INSERT 0 1 ``` ...but the replica doesn’t receive it: ```sql β=> SELECT * FROM test; ``` ```text s --------------- Hello, world! (1 row) ``` Streaming replication has stopped: ```sql α=> SELECT sent_lsn, write_lsn FROM pg_stat_replication \gx ``` ```text -[ RECORD 1 ]-------- sent_lsn | 0/34863F0 write_lsn | 0/3486380 ``` ### 4. Archiving Archiving on the primary, however, continues to function. Switch the WAL segment: ```sql α=> SELECT pg_walfile_name(pg_current_wal_lsn()), pg_switch_wal(); ``` ```text pg_walfile_name | pg_switch_wal --------------------------+--------------- 000000010000000000000003 | 0/3486458 (1 row) ``` ```sql α=> INSERT INTO test VALUES ('WAL segment switched.'); ``` ```sql INSERT 0 1 ``` The segment has been archived: ```sql α=> SELECT * FROM pg_stat_archiver \gx ``` ```text -[ RECORD 1 ]------+------------------------------ archived_count | 4 last_archived_wal | 000000010000000000000003 last_archived_time | 2026-09-17 00:12:50.518266+03 failed_count | 0 last_failed_wal | last_failed_time | stats_reset | 2026-07-15 23:46:12.428787+03 ``` Now the replica can apply the records from the new segment, and we will see the row that was inserted before the switch. ```sql β=> SELECT * FROM test; ``` ## Page 38: 5. Network Recovery {#source-page-038} ```text s ------------------ Hello, world! Network failure. (2 rows) ``` Let’s insert another row into the table on the primary. ```sql α=> INSERT INTO test VALUES ('Network still down...'); ``` ```sql INSERT 0 1 ``` It doesn’t reach the replica. ```sql β=> SELECT * FROM test; ``` ```text s ------------------ Hello, world! Network failure. (2 rows) ``` Now restore the network interface: the outage has been resolved. ```sh student$ sudo ip l set enp0s3 up ``` ```sh student$ ping -c1 10.0.2.15 ``` ```sql PING 10.0.2.15 (10.0.2.15) 56(84) bytes of data. 64 bytes from 10.0.2.15: icmp_seq=1 ttl=64 time=0.018 ms ``` ```text --- 10.0.2.15 ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.018/0.018/0.018/0.000 ms ``` Streaming replication is working again: ```sql α=> SELECT * FROM pg_stat_replication \gx ``` ```text -[ RECORD 1 ]----+------------------------------ pid | 103734 usesysid | 10 usename | postgres application_name | 16/beta client_addr | 10.0.2.15 client_hostname | client_port | 49664 backend_start | 2026-09-17 00:12:59.483625+03 backend_xmin | state | streaming sent_lsn | 0/4000150 write_lsn | 0/4000150 flush_lsn | 0/4000150 replay_lsn | 0/4000150 write_lag | 00:00:00.095116 flush_lag | 00:00:00.102971 replay_lag | 00:00:00.103039 sync_priority | 0 sync_state | async reply_time | 2026-09-17 00:12:59.616749+03 ``` On the primary, add another row. ```sql α=> INSERT INTO test VALUES ('Network restored!'); ``` ```sql INSERT 0 1 ``` The replica has received all the rows. ```sql β=> SELECT * FROM test; ``` ## Page 39: Cleanup {#source-page-039} ![Source slide page 39: Cleanup](images/page-039.png){#source-figure-039 caption="Source slide page 39: Cleanup"} ```text s ----------------------- Hello, world! Network failure. WAL segment switched. Network still down... Network restored! (5 rows) ``` Drop the database we created and reset configuration. ```sql α=> \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql α=> DROP DATABASE replica_physical; ``` ```sql DROP DATABASE ``` ```sql β=> \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql β=> ALTER SYSTEM RESET listen_addresses; ``` ```sql ALTER SYSTEM ``` ```sql β=> ALTER SYSTEM RESET archive_mode; ``` ```sql ALTER SYSTEM ``` ```sql β=> ALTER SYSTEM RESET archive_command; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 beta stop -m immediate --skip-systemctl-redirect ``` ```sql α=> ALTER SYSTEM RESET listen_addresses; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM RESET archive_mode; ``` ```sql ALTER SYSTEM ``` ```sql α=> ALTER SYSTEM RESET archive_command; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 alpha restart ``` ```sh postgres$ rm -f /etc/postgresql/16/beta/conf.d/rest.conf ```