# Lesson 16: Replication / Overview of Logical Replication > Native Markdown source page: Replication / Overview of Logical 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:** [dba1_16_replica_overview_logical.html](https://edu.postgrespro.com/16/dba1-16-en/dba1_16_replica_overview_logical.html) > **Archive:** [DBA1-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA1-handouts-16-en.zip) > **PDF pages:** 23 ## Page 1: Replication / Overview of Logical Replication {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova 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} - Logical Replication - WAL Levels - Publications and Subscriptions - Conflict Detection and Resolution - Replica Usage ## Page 3: Logical Replication {#source-page-003} **Publisher** - reads its own WAL records - decodes them into table row changes - sends to subscribers - Subscriber - receives WAL records from the replication stream - applies the changes to its own tables - Features - publication-subscription: data flow is possible in both directions - requires protocol-level compatibility - can replicate individual tables ### Speaker notes In physical replication only one server (the primary) makes changes and generates WAL records. Other servers (standbys) only read the primary’s WAL records and apply them. In logical replication all servers operate normally, can modify data, and generate their own WAL records. Any server can publish its changes, while others can subscribe to them. A single server can act as both a publisher and a subscriber, enabling flexible data flows between servers. The publisher reads its own WAL records, but unlike physical replication, it first decodes them into a logical format that is platform-independent and not tied to specific PostgreSQL version before transmitting to subscribers. Therefore, binary compatibility is not required — only the replication protocol must be understood. It also supports selective replication, allowing only specific tables to be replicated. ## Page 4: Logical Replication {#source-page-004} ![Source slide page 4: Logical Replication](images/page-004.png){#source-figure-004 caption="Source slide page 4: Logical Replication"} ### Speaker notes On the publisher the wal\_sender process generates WAL records reflecting changes to published data. On the subscriber the logical replication worker receives the information from the publisher and applies it. The subscriber can receive changes either directly from the publisher or from the publisher’s standby. In the latter case, the standby’s wal\_sender process sends changes to the subscriber. This architecture helps reduce load on the publisher. ## Page 5: Publication and Subscription {#source-page-005} **Publication** - includes one or more database tables - can specify columns and row filters - processes INSERT, UPDATE, DELETE, TRUNCATE commands - sends row-level changes after transaction commit - uses a logical replication slot - Subscription - receives and applies changes - can do initial synchronization - no parsing, rewriting and planning, just direct execution - possible conflicts with local data ### Speaker notes Logical replication uses a publish and subscribe model. A publication is created on one server and can include several tables in a single database. Starting with version 15, PostgreSQL allows publishing partial table data by specifying column subsets and row filter conditions. Other servers can subscribe to this publication to receive and apply changes to the tables. Only table row modifications are replicated (not SQL commands). DDL commands are not transmitted, so target tables on the subscriber must be created manually. Initial synchronization of table contents can be performed when a subscription is created. After transaction commit, information about modified rows is extracted from WAL records on the publisher through logical decoding. The resulting messages are transmitted to subscribers via the replication protocol in a platform-and version-independent format. Changes are applied without executing SQL commands, avoiding parsing and planning overhead. On the other hand, a single SQL command can result in multiple one-row changes. ## Page 6: WAL Levels {#source-page-006} **wal\\\_level parameter** - minimal < replica < logical - crash recovery crash recovery crash recovery restore from backup, restore from backup, replication replication logical replication ### Speaker notes To enable the publisher to generate messages about changes at the row level, the WAL must include additional information, such as row identifiers involved in modifications and notifications about changes to table definitions and data types. This allows the subscriber to have up-to-date knowledge of the structure of replicated objects in any moment. This extended level of logging is called logical. Since the default level is replica, logical replication requires changing the wal\_level parameter on the publisher. TOCOL-LOGICAL-MESSAGES-FLOW ## Page 7: Logical Replication {#source-page-007} Assume the first server contains a table: ```sql => CREATE DATABASE replica_overview_logical_dba; ``` ```sql CREATE DATABASE ``` ```sql => \c replica_overview_logical_dba ``` ```text You are now connected to database "replica_overview_logical_dba" as user "student". ``` ```sql => CREATE TABLE test(id integer PRIMARY KEY, descr text); ``` ```sql CREATE TABLE ``` Let’s clone the cluster by creating a standalone backup (as covered in the Physical Replication Overview lesson), but omit the -R option from pg\_basebackup call since we need an independent server, not a standby. ```sh student$ rm -rf /home/student/tmp/backup ``` ```sh student$ pg_basebackup --pgdata=/home/student/tmp/backup --checkpoint=fast ``` If the second server is running, we stop it. ```sh student$ sudo pg_ctlcluster 16 replica stop ``` ```text Cluster is not running. ``` Move the backup to the data directory of the second server, changing the owner of the files: ```sh student$ sudo rm -rf /var/lib/postgresql/16/replica ``` ```sh student$ sudo mv /home/student/tmp/backup /var/lib/postgresql/16/replica ``` ```sh student$ sudo chown -R postgres: /var/lib/postgresql/16/replica ``` Start the second server: ```sh student$ sudo pg_ctlcluster 16 replica start ``` We got two independent servers, each of them has an empty test table. Let’s add a couple of lines to the table on the first server: ```sql => INSERT INTO test VALUES (1, 'One'), (2, 'Two'); ``` ```sql INSERT 0 2 ``` To establish logical replication between servers, additional information in the WAL of the publishing server is required: ```sql => ALTER SYSTEM SET wal_level = logical; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` Create a publication on the first server: ```sh student$ psql -d replica_overview_logical_dba ``` ```sql => CREATE PUBLICATION test_pub FOR TABLE test; ``` ```sql CREATE PUBLICATION ``` ```text => \dRp+ ``` ```text Publication test_pub Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------+------------+---------+---------+---------+-----------+---------- student | f | t | t | t | t | f Tables: "public.test" ``` On the second server, subscribe to the publication: ```sh student$ psql -p 5433 -d replica_overview_logical_dba ``` ```sql => CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432 user=student dbname=replica_overview_logical_dba' PUBLICATION test_pub; ``` ```sql NOTICE: created replication slot "test_sub" on publisher CREATE SUBSCRIPTION ``` ## Page 8: Page 8 {#source-page-008} ```text => \dRs ``` ```text List of subscriptions Name | Owner | Enabled | Publication ----------+---------+---------+------------- test_sub | student | t | {test_pub} (1 row) ``` Verify the replication: ```sql => INSERT INTO test VALUES (3, 'Three'); ``` ```sql INSERT 0 1 ``` ```sql => SELECT * FROM test; ``` ```text id | descr ----+------- 1 | One 2 | Two 3 | Three (3 rows) ``` The following view shows the state of the subscription: ```sql => SELECT * FROM pg_stat_subscription \gx ``` ```text -[ RECORD 1 ]---------+------------------------------ subid | 24578 subname | test_sub pid | 29284 leader_pid | relid | received_lsn | 0/3004298 last_msg_send_time | 2025-11-27 13:54:55.999151+03 last_msg_receipt_time | 2025-11-27 13:54:56.031801+03 latest_end_lsn | 0/3004298 latest_end_time | 2025-11-27 13:54:55.999151+03 ``` The logical replication apply worker process has been started (you can see its ID in pg\_stat\_subscription.pid): ```sh student$ ps -o pid,command --ppid 28969 ``` ```text PID COMMAND 28970 postgres: 16/replica: checkpointer 28971 postgres: 16/replica: background writer 28975 postgres: 16/replica: walwriter 28976 postgres: 16/replica: autovacuum launcher 28977 postgres: 16/replica: logical replication launcher 29250 postgres: 16/replica: student replica_overview_logical_dba [local] idle 29284 postgres: 16/replica: logical replication apply worker for subscription 24578 ``` ## Page 9: Conflicts {#source-page-009} **Identification modes for modifying and deleting rows** - primary key columns (default) - columns of a specific unique index with the NOT NULL constraint - all columns - no identification (default for the system catalog) - Conflicts: violation of integrity constraints - replication is suspended until the conflict is resolved manually ### Speaker notes Inserting new rows is straightforward. Changes and deletions are more complicated. These operations need to somehow identify the old version of the row. By default, primary key columns are used for this, but you can specify other ways (replica identity) when defining a table, i.e. use a unique index or all the table columns. Or you can disable replication for some tables altogether (system catalog tables have it disabled by default). Since the table on the publisher and the table on the subscriber can be changed independently of each other, conflicts in the form of integrity constraint violations are possible when inserting new row versions. Whenever this happens, the process of applying records is suspended until the conflict is resolved manually. ## Page 10: Conflicts {#source-page-010} Local modifications on the subscriber are not prohibited. Let’s insert a row into the table on the second server: ```sql => INSERT INTO test VALUES (4, 'Four (local)'); ``` ```sql INSERT 0 1 ``` If we add a row with the same primary key value on the publisher, the subscription will have a conflict when trying to apply the change. ```sql => INSERT INTO test VALUES (4, 'Four'); ``` ```sql INSERT 0 1 ``` ```sql => INSERT INTO test VALUES (5, 'Five'); ``` ```sql INSERT 0 1 ``` The subscription is unable to apply the change, replication stops. ```sql => SELECT * FROM pg_stat_subscription \gx ``` ```text -[ RECORD 1 ]---------+--------- subid | 24578 subname | test_sub pid | leader_pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | ``` ```sql => SELECT * FROM test; ``` ```text id | descr ----+-------------- 1 | One 2 | Two 3 | Three 4 | Four (local) (4 rows) ``` To resolve the conflict, remove the row on the second server and wait a moment... ```sql => DELETE FROM test WHERE id=4; ``` ```sql DELETE 1 ``` ```sql => SELECT * FROM test; ``` ```text id | descr ----+------- 1 | One 2 | Two 3 | Three 4 | Four 5 | Five (5 rows) ``` Replication continues. ### Dropping a Subscription If replication is no longer needed, the subscription should be deleted, otherwise the publisher will keep the open replication slot. ```sql => DROP SUBSCRIPTION test_sub; ``` ```sql NOTICE: dropped replication slot "test_sub" on publisher DROP SUBSCRIPTION ``` ## Page 11: Page 11 {#source-page-011} ## Page 12: Restrictions {#source-page-012} **Not replicated** - DDL commands - sequence values - large objects - changes to views, materialized views, and foreign tables - Not supported - automatic conflict resolution ### Speaker notes Logical replication has some fundamental limitations. DDL commands are not replicated — all schema changes must be copied manually. Only regular base tables and partitioned tables can be replicated. Other relation types such as views, materialized views, and foreign tables cannot be replicated. Sequence values are not replicated. This means that if the subscriber inserts rows into a replicated table with a surrogate primary key, conflicts may occur. These conflicts can be avoided by allocating different sequence ranges to each server or by using UUIDs instead of sequences. There is no built-in conflict resolution mechanism. These restrictions reduce the applicability of logical replication. ## Page 13: Features {#source-page-013} **Referential integrity** - for TRUNCATE operations, the publication must include all tables referenced - by foreign keys - Persistent connection is mandatory - inactive replication slots prevent WAL segment removal and hold back the - vacuum horizon - Potential issues - bulk data changes - changes made by long-running transactions ### Speaker notes To properly replicate TRUNCATE commands, the publication should include all tables that have foreign key references to the truncated table. The connection between the publisher and the subscriber must remain stable. If interrupted, the replication slot becomes inactive, forcing the server to retain WAL segments until reconnection. Inactive slots also hold back the vacuum horizon. Changes are replicated row-by-row, so SQL commands affecting many rows on the publisher create significant subscriber load. The default configuration handles long-running transactions poorly — they increase publisher load since changes are only sent after transaction commit. The subscription’s streaming parameter mitigates this by getting row change without delay, either buffering changes in temporary files or applying changes immediately when background process is available. ## Page 14: 1. Consolidation {#source-page-014} ![Source slide page 14: 1. Consolidation](images/page-014.png){#source-figure-014 caption="Source slide page 14: 1. Consolidation"} receipt and consolidation of data from regional servers ### Speaker notes Let’s discuss some logical replication use cases. Suppose there are several regional branches, each of which runs on its own PostgreSQL server. The goal is to consolidate some data on a central server. First, publications of the necessary data are created on regional servers. The central server subscribes to these publications. The received data can be processed using triggers on the central server (for example, unifying the data format). Inverted, the setup allows, for example, to transfer reference information from the central server to regional ones. The business logic may apply additional constraints on the system. In some use cases, scheduled batch data transfers may be preferable. The image shows two WAL receiving processes running on the central server, one for each subscription. ## Page 15: 2. Server Update {#source-page-015} ![Source slide page 15: 2. Server Update](images/page-015.png){#source-figure-015 caption="Source slide page 15: 2. Server Update"} updating the major version without interruption of service ### Speaker notes Case: update the major version on the server without interrupting the service. Two major versions are not binary compatible, so physical replication will not work. However, logical replication can solve the problem. As usual, external tools are required to switch users between servers. First, a new server is created with the desired PostgreSQL version. ## Page 16: 2. Server Update {#source-page-016} ![Source slide page 16: 2. Server Update](images/page-016.png){#source-figure-016 caption="Source slide page 16: 2. Server Update"} updating the major version without interruption of service ### Speaker notes Then, logical replication of all required databases is set up between the servers, and the data is synchronized. This is possible because logical replication does not require binary compatibility between servers. ## Page 17: 2. Server Update {#source-page-017} ![Source slide page 17: 2. Server Update](images/page-017.png){#source-figure-017 caption="Source slide page 17: 2. Server Update"} updating the major version without interruption of service ### Speaker notes Clients are then switched to the new server while the old one is terminated. In practice, the process of updating major server version using logical replication is much more complicated and difficult. It is discussed in more detail in the Server Update lesson of the DBA2 course. ## Page 18: 3. Primary-Primary {#source-page-018} ![Source slide page 18: 3. Primary-Primary](images/page-018.png){#source-figure-018 caption="Source slide page 18: 3. Primary-Primary"} a cluster where multiple servers can modify data ### Speaker notes Case: provide reliable data storage on multiple servers with the ability to write the data on any server (particularly useful for geo-distributed systems). This can be achieved through bidirectional logical replication, synchronizing changes for the same tables between servers in both directions. PostgreSQL 16 introduced bidirectional replication, where tables can be both published and subscribed to on different servers simultaneously. This requires that the applications working with the cluster are built with certain considerations in mind in order to avoid conflicts when modifying data in the same table. For example, to use globally unique identifiers or to ensure that different servers work with different ranges of keys. Keep in mind that the primary-primary setup with logical replication will not support global distributed transactions and thus cannot guarantee full data consistency between servers. In addition, PostgreSQL does not offer any tools for automatic failure processing, adding nodes to the cluster or removing nodes from it, etc. These tasks must be solved by external means. ## Page 19: Takeaways {#source-page-019} **Logical replication streams individual row changes** - multidirectional - requires protocol-level compatibility - Publish and subscribe model - All current restrictions must be considered ## Page 20: Practice {#source-page-020} 1. Set up logical replication of an arbitrary table to another table on the same server. 2. Set up bidirectional logical replication of the same table between two different servers. ### Speaker notes 1. If you try to perform standard operations, the CREATE SUBSCRIPTION command will hang. Read carefully the documentation: 2. Clone the second server from a backup, as shown in demonstration earlier. When creating subscriptions on both servers, specify the following parameters: copy\_data = false, origin = none. ## Page 21: 1. Replication on a Single Server {#source-page-021} Test table: ```sql => CREATE DATABASE replica_overview_logical_dba; ``` ```sql CREATE DATABASE ``` ```sql => \c replica_overview_logical_dba ``` ```text You are now connected to database "replica_overview_logical_dba" as user "student". ``` ```sql => CREATE TABLE test ( id int ); ``` ```sql CREATE TABLE ``` A copy of the database with the table: ```sql => \c student ``` ```text You are now connected to database "student" as user "student". ``` ```sql => CREATE DATABASE replica_overview_logical_dba2 TEMPLATE replica_overview_logical_dba; ``` ```sql CREATE DATABASE ``` Set the required WAL level: ```sql => ALTER SYSTEM SET wal_level = logical; ``` ```sql ALTER SYSTEM ``` ```sql => \q ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` Connect to the databases: ```sh student$ psql replica_overview_logical_dba ``` ```sh student$ psql replica_overview_logical_dba2 ``` Create a publication in the first database: ```sql => CREATE PUBLICATION test FOR TABLE test; ``` ```sql CREATE PUBLICATION ``` By default, the CREATE SUBSCRIPTION command creates a replication slot, which waits for all active transactions (at the time of slot creation) to complete before proceeding. One such transaction is the CREATE SUBSCRIPTION command itself, causing it to block indefinitely. This issue can be resolved by manually creating a replication slot and specifying its name when creating the subscription: ```sql => SELECT pg_create_logical_replication_slot('test_slot','pgoutput'); ``` ```text pg_create_logical_replication_slot ------------------------------------ (test_slot,0/21819E8) (1 row) ``` ```sql => CREATE SUBSCRIPTION test CONNECTION 'user=student dbname=replica_overview_logical_dba' PUBLICATION test WITH (slot_name = test_slot, create_slot = false); ``` ```sql CREATE SUBSCRIPTION ``` Verification: ```sql => INSERT INTO test SELECT * FROM generate_series(1,100); ``` ```sql INSERT 0 100 ``` ```sql => SELECT count(*) FROM test; ``` ## Page 22: Page 22 {#source-page-022} ```text count ------- 100 (1 row) ``` Replication is working. Delete the publication, subscription, and the second database. ```sql => DROP PUBLICATION test; ``` ```sql DROP PUBLICATION ``` ```sql => DROP SUBSCRIPTION test; ``` ```sql NOTICE: dropped replication slot "test_slot" on publisher DROP SUBSCRIPTION ``` ```sql => DROP DATABASE replica_overview_logical_dba2 (FORCE); ``` ```sql DROP DATABASE ``` ### 2. Bidirectional Replication We clone the server using a backup: ```sh student$ pg_basebackup --pgdata=/home/student/tmp/backup --checkpoint=fast ``` Make sure the second server is stopped and push the backup: ```sh student$ sudo pg_ctlcluster 16 replica stop ``` ```text Cluster is not running. ``` ```sh student$ sudo rm -rf /var/lib/postgresql/16/replica ``` ```sh student$ sudo mv /home/student/tmp/backup /var/lib/postgresql/16/replica ``` ```sh student$ sudo chown -R postgres: /var/lib/postgresql/16/replica ``` Start the second server: ```sh student$ sudo pg_ctlcluster 16 replica start ``` The database with the table and the log level setting were also cloned: ```sh student$ psql -p 5433 replica_overview_logical_dba ``` ```text => SHOW wal_level; ``` ```text wal_level ----------- logical (1 row) ``` ```sql => SELECT count(*) FROM test; ``` ```text count ------- 100 (1 row) ``` We publish the table on both servers: ```sql => CREATE PUBLICATION test FOR TABLE test; ``` ```sql CREATE PUBLICATION ``` ```sql => CREATE PUBLICATION test FOR TABLE test; ``` ```sql CREATE PUBLICATION ``` Subscribe: ```sql => CREATE SUBSCRIPTION test CONNECTION 'port=5433 user=student dbname=replica_overview_logical_dba' PUBLICATION test WITH (copy_data = false, origin = none); ``` ```sql NOTICE: created replication slot "test" on publisher CREATE SUBSCRIPTION ``` ## Page 23: Page 23 {#source-page-023} ```sql => CREATE SUBSCRIPTION test CONNECTION 'port=5432 user=student dbname=replica_overview_logical_dba' PUBLICATION test WITH (copy_data = false, origin = none); ``` ```sql NOTICE: created replication slot "test" on publisher CREATE SUBSCRIPTION ``` Modify table rows: ```sql => UPDATE test SET id = id + 100; ``` ```sql ERROR: cannot update table "test" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. ``` For changes and deletions to be replicated properly, row identification must be configured. By default, rows are identified by their primary key. ```sql => ALTER TABLE test ADD PRIMARY KEY (id); ``` ```sql ALTER TABLE ``` ```sql => ALTER TABLE test ADD PRIMARY KEY (id); ``` ```sql ALTER TABLE ``` Retry the operation: ```sql => UPDATE test SET id = id + 100; ``` ```sql UPDATE 100 ``` ```sql => UPDATE test SET id = id + 100; ``` ```sql UPDATE 100 ``` Check the Result: ```sql => SELECT min(id), max(id) FROM test; ``` ```text min | max -----+----- 201 | 300 (1 row) ``` ```sql => SELECT min(id), max(id) FROM test; ``` ```text min | max -----+----- 201 | 300 (1 row) ```