Logical replication

Eugenia Kuzina

Overview

Logical replication is a replication of data objects and their changes based on their replication identity (usually a primary key). We use the term "logical replication" as opposed to physical replication, which uses block addresses and byte-by-byte replication. Logical replication allows you to control both data replication and security.

The logical replication uses a publish/subscribe model with one or more subscribers that subscribe to one or more publications on the publishing node. Subscribers receive data from the publications and can republish the data for cascading replication or more complex configurations.

The logical replication of a table starts with taking a snapshot of the data in the publisher database and copying it to the subscriber (see Architecture). After that, the changes on the publisher are sent to the subscriber in real-time as they occur. Publications within the same subscription guarantee transactional integrity. The subscriber applies data changes in the same order as the publisher so that transactional consistency is guaranteed for publications within a single subscription. This method of data replication is also known as transactional replication.

The typical use cases for logical replication are:

  • sending incremental changes in a single database or a subset of databases to subscribers;

  • firing triggers for individual changes when a subscriber receives them;

  • combining multiple databases into one (for example, for analysis purposes);

  • replicating between different PostgreSQL major versions;

  • replicating between PostgreSQL instances on different platforms;

  • providing access to replicated data to different groups of users;

  • sharing a database subset across multiple databases.

The subscriber database behaves in the same way as any other ADP/PostgreSQL instance and can be a publisher if you create publications in it. When a subscriber acts as a read-only application, there are no conflicts with a single subscription. If any applications or other subscribers write to the same set of tables, conflicts can occur.

To set up a logical replication, perform the following steps:

Limitations of logical replication

The logical replication has the following restrictions:

  • The database schema and DDL commands are not replicated.

  • Sequences are not replicated. The data in serial or identity columns filled by sequences will be replicated as part of the table, but the sequence itself will show the start value on the subscriber.

  • Replication of the TRUNCATE command is supported, but truncating groups of tables connected by foreign keys should be done with caution. If some tables to be truncated on the subscriber have foreign-key links to tables that are not part of the same (or any) subscription, the application of the truncate action on the subscriber will fail.

  • Large objects are not replicated.

  • Only tables, including partitioned tables, can be replicated. Attempts to replicate other types of relations, such as views, materialized views, or foreign tables, will result in an error.

  • When replicating partitioned tables, the actual replication originates, by default, from the leaf partitions on the publisher, so these partitions must also exist on the subscriber as valid target tables.

For detailed information about these restrictions, refer to Restrictions.

Configuration settings

The logical replication requires several configuration options to be set. Use the ADPG configurations section on the Clusters → ADP cluster → Services → ADPG → Primary configuration tab in ADCM UI to specify these options. See Configuration parameters for details.

Specify the following parameters on the publisher side:

  • wal_level must be set to logical.

  • max_replication_slots must be set to at least the number of subscriptions expected to connect, plus some reserve for table synchronization.

  • max_wal_senders should be set to at least the max_replication_slots value plus the number of physical replicas that are connected at the same time.

 

Set the following parameters on the subscriber side:

  • max_replication_slots should be set to at least the number of subscriptions added to the subscriber, plus some reserve for table synchronization.

  • max_logical_replication_workers should be also set to at least the number of subscriptions added to the subscriber, plus some reserve for table synchronization.

  • max_worker_processes should include additional worker processes for the replication, at least max_logical_replication_workers plus 1. Note, some extensions and parallel queries also occupy slots from max_worker_processes.

You also need to check if the settings specified in the PG_HBA section on the Clusters → ADP cluster → Services → ADPG → Primary configuration tab in ADCM UI allow the replication. These settings depend on your network configuration and the user you want to utilize for the connection.

Publication

A publication can be defined on any physical replication primary. A node that contains a publication is called a publisher. A publication is a set of changes generated from a table or a group of tables. It can also be described as a replication set. Each publication exists in only one database.

Publications are different from schemas and do not affect the table access. You can include each table in multiple publications. Currently, publications can only contain tables. Objects must be explicitly added to them unless the publication is created for all tables with the FOR ALL TABLES or FOR TABLES IN SCHEMA specification.

Publications can limit the set of changes they contain. It is possible to choose any combination of the INSERT, UPDATE, DELETE, and TRUNCATE operations, similar to how triggers can fire on different event types. By default, all types of operations are replicated.

To replicate the UPDATE and DELETE operations, the published table must have a replica identity. It allows the system to find appropriate rows to update or delete them on the subscriber side. By default, this is the primary key. You can also specify another unique index for a replica identity. If the table does not have a suitable key, the entire row can be used as a key. To do this, set the replica identity to full. However, this approach is very inefficient and should be used if there is no other solution. If a replication identifier other than full is selected on the publisher side, an identifier consisting of the same or fewer columns must also be defined on the subscriber side. For more information about assigning a replica identity, see REPLICA IDENTITY. If a table without a replica identity is added to a publication, the replicated UPDATE or DELETE operation causes an error on the publisher. The INSERT operations can be processed without the specified replica identity.

Every publication can have multiple subscribers.

Create tables for test purposes:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    date DATE NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20));

ALTER TABLE orders REPLICA IDENTITY DEFAULT;

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author_id INT NOT NULL,
    in_stock INTEGER DEFAULT 0);

ALTER TABLE books REPLICA IDENTITY DEFAULT;

INSERT INTO books(title, author_id, in_stock) VALUES
    ('Mrs. Dalloway',1,25),
    ('To the Lighthouse',1,13),
    ('To Kill a Mockingbird',2,0),
    ('The Great Gatsby',3,3),
    ('The Lord of the Rings',4,45);

Use the CREATE PUBLICATION command to create a publication. The following command creates a publication that publishes all changes from the books table:

CREATE PUBLICATION publication1 FOR TABLE books;

The code below creates a publication that publishes all changes from all tables. This command will also be applied to tables that will be created later:

CREATE PUBLICATION all_tables FOR ALL TABLES;

It is also possible to define a publication that replicates changes for all tables in the specified list of schemas, including tables created in the future. To do this, use FOR TABLES IN SCHEMA:

CREATE PUBLICATION all_tables_in_schema FOR TABLES IN SCHEMA public;

The following publication publishes the DELETE operations performed on the books table:

CREATE PUBLICATION delete_publication FOR TABLE books WITH (publish = 'delete');

You can use the ALTER PUBLICATION command to change the definition of a publication.

The command below changes the delete_publication publication to publish deletes and updates:

ALTER PUBLICATION delete_publication SET (publish = 'update, delete');

The following code adds the orders table to publication1:

ALTER PUBLICATION publication1 ADD TABLE orders;

The code below removes the orders table from publication1:

ALTER PUBLICATION publication1 DROP TABLE orders;

Both the ADD TABLE and DROP TABLE operations are transactional. A table replication only starts or stops after the transaction is committed.

To remove a publication, call the DROP PUBLICATION command:

DROP PUBLICATION all_tables;

By default, all data from all published tables will be replicated to subscribers. Starting with ADP 16.3.1, you can limit the number of replicated rows specifying filters and the number of columns — using lists.

Row filters

A user can set row filters for security or performance reasons. If a published table has a row filter, a row is replicated only if its data satisfies the filter expression. It allows a table to be partially replicated. A row filter is defined per table. Row filters are applied before publishing the changes.

Use the WHERE clause, enclosed in parentheses, after the table name to specify a filter.

Examples:

CREATE PUBLICATION publication2 FOR TABLE orders WHERE (amount > 100 AND status = 'shipped');

CREATE PUBLICATION publication3 FOR TABLE orders WHERE (amount > 200 AND status = 'shipped'), books WHERE (book_id = 1);

The WHERE clause allows only simple expressions. It cannot contain user-defined functions, operators, types, collations, and others.

If a publication publishes the UPDATE or DELETE operations, the WHERE clause of a row filter must contain only columns that are included in the replica identity. If a publication publishes only INSERT operations, the WHERE clause can use any column.

When UPDATE is processed, the row filter expression is evaluated for both the old and new row. If both evaluations are true, it replicates the UPDATE change. If both evaluations are false, it does not replicate the change. If only one of the old/new rows matches the row filter expression, UPDATE is transformed to INSERT or DELETE to avoid any data inconsistency. If the old row satisfies the row filter expression (it is sent to the subscriber) but the new row does not, the old row should be removed from the subscriber. So, UPDATE is transformed into DELETE. If the old row does not satisfy the row filter expression (it is not sent to the subscriber), but the new row does, the new row should be added to the subscriber. So, UPDATE is transformed into INSERT.

If the publication contains a partitioned table, the publication parameter publish_via_partition_root determines which row filter is used. If publish_via_partition_root is true, the root partitioned table’s row filter is used. If publish_via_partition_root is false (default), each partition’s row filter is used.

Column lists

Each publication can optionally specify which columns of a table are replicated to subscribers. Add comma-separated column names enclosed in parentheses after the table name to specify a list of columns. The table on the subscriber side must have all the columns that are published.

If no column list is specified, any columns added to the table later are automatically replicated. It means that having a column list that includes all columns is not the same as having no column list at all.

A column list can contain only simple column references. The order of columns in the list is not preserved.

For partitioned tables, the publication parameter publish_via_partition_root determines which column list is used. If publish_via_partition_root is true, the root partitioned table’s column list is used. If publish_via_partition_root is false (default), each partition’s column list is used.

Example:

CREATE PUBLICATION publication4 FOR TABLE books (book_id, title, author_id);

View database publications

The pg_publication catalog contains all publications created in a database:

SELECT * FROM pg_publication;
  oid  |       pubname        | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+----------------------+----------+--------------+-----------+-----------+-----------+-------------+------------
 37252 | publication1         |       10 | f            | t         | t         | t         | t           | f
 37255 | delete_publication   |       10 | f            | f         | t         | t         | f           | f
 37258 | publication2         |       10 | f            | t         | t         | t         | t           | f
 37260 | publication3         |       10 | f            | t         | t         | t         | t           | f
 37263 | publication4         |       10 | f            | t         | t         | t         | t           | f
 37265 | all_tables_in_schema |       10 | f            | t         | t         | t         | t           | f

The pubinsert, pubupdate, pubdelete, and pubtruncate fields show if the INSERT, UPDATE, DELETE, and TRUNCATE operations are replicated, respectively.

If the pubviaroot field is t(true) operations on a leaf partition are replicated using the identity and schema of its topmost partitioned ancestor mentioned in the publication instead of its own.

The pg_publication_tables view provides information about the mapping between publications and the tables they contain:

SELECT * FROM pg_publication_tables;
        pubname        | schemaname |    tablename   |            attnames                        |                rowfilter
----------------------+------------+-----------------+--------------------------------------------+--------------------------------------------------------------------
 publication1         | public     | books           | {book_id,title,author_id,in_stock}         |
 delete_publication   | public     | books           | {book_id,title,author_id,in_stock}         |
 publication2         | public     | orders          | {order_id,customer_id,date,amount,status}  | ((amount > (100)::numeric) AND ((status)::text = 'shipped'::text))
 publication3         | public     | orders          | {order_id,customer_id,date,amount,status}  | ((amount > (100)::numeric) AND ((status)::text = 'shipped'::text))
 publication3         | public     | books           | {book_id,title,author_id,in_stock}         | (author_id = 1)
 publication4         | public     | books           | {book_id,title,author_id}                  |
 all_tables_in_schema | public     | orders          | {order_id,customer_id,date,amount,status}  |
 all_tables_in_schema | public     | books           | {book_id,title,author_id,in_stock}         |

Subscription

A subscription is the downstream side of logical replication. The node on which the subscription is defined is called the subscriber. A subscription defines the connection to another database and set of publications to which the subscriber wants to subscribe.

A subscriber database works as any other PostgreSQL instance and can be used as a publisher for other databases if its own publications are defined.

A subscriber node can have multiple subscriptions. It is possible to define multiple subscriptions between a single publisher/subscriber pair. It is necessary to ensure that the publication objects do not overlap.

Each subscription receives changes via one replication slot. Additional replication slots may be required for the initial data synchronization. They are dropped after the data synchronization is finished. For more information, see Replication slot management.

A logical replication subscription can be a standby for synchronous replication.

Subscriptions can be archived when a dump file is created. The pg_dump utility adds subscriptions to an SQL dump file, if it is run on behalf of a superuser. Otherwise, subscriptions are skipped, because non-superusers cannot read all subscription information from the pg_subscription catalog.

The tables are matched between the publisher and the subscriber using the fully qualified table name. The replication to a table with a different name is not supported.

Table columns are also matched by name. The order of columns does not have to match. The data types of the columns do not need to be the same if a text representation of the data can be converted to the target type. For example, you can replicate from a column of type integer to a column of type bigint. The target table can also have additional columns that the published table does not contain. These columns are filled with default values specified in the target table definition.

Create a subscription

For test purposes, create the same tables on the subscriber that were created on the publishing node. The examples below use publications created in the Publication section:

CREATE PUBLICATION publication1 FOR TABLE books;

CREATE PUBLICATION publication2 FOR TABLE orders WHERE (amount > 100 AND status = 'shipped');

Use the CREATE SUBSCRIPTION command to add subscriptions:

CREATE SUBSCRIPTION subscription1
    CONNECTION 'host=10.92.40.128 port=5432 user=postgres dbname=postgres password=postgres'
    PUBLICATION publication1;

CREATE SUBSCRIPTION subscription2
    CONNECTION 'host=10.92.40.128 port=5432 user=postgres dbname=postgres password=postgres'
    PUBLICATION publication2;

Test subscription1. To do this, follow the steps below:

  1. Check the books table data on the subscriber:

    SELECT * FROM books;
     book_id |         title         | author_id | in_stock
---------+-----------------------+-----------+----------
       1 | Mrs. Dalloway         |         1 |       25
       2 | To the Lighthouse     |         1 |       13
       3 | To Kill a Mockingbird |         2 |        0
       4 | The Great Gatsby      |         3 |        3
       5 | The Lord of the Rings |         4 |       45

  2. On the publisher, execute the following commands:

    DELETE FROM books WHERE book_id=5;

UPDATE books SET in_stock = 15 WHERE book_id=1;

  3. Check the result on the subscriber:

    SELECT * FROM books;
     book_id |         title         | author_id | in_stock
---------+-----------------------+-----------+----------
       1 | Mrs. Dalloway         |         1 |       15
       2 | To the Lighthouse     |         1 |       13
       3 | To Kill a Mockingbird |         2 |        0
       4 | The Great Gatsby      |         3 |        3

The data on the subscriber was updated successfully using subscription1.

Test subscription2:

  1. Check the orders table on the subscriber:

    SELECT * FROM orders;
     order_id | customer_id | date | amount | status
----------+-------------+------+--------+--------
(0 rows)

  2. On the publishing server, add data to the orders table:

    INSERT INTO orders (customer_id, date, amount, status)
SELECT
(random() * 500)::integer,
CURRENT_DATE - (random() * 365)::integer,
(random() * 500)::decimal,
CASE WHEN random() < 0.4 THEN 'pending'
WHEN random() < 0.7 THEN 'shipped'
ELSE 'delivered' END
FROM generate_series(1, 30);

  3. Check the orders table on the subscriber:

    SELECT * FROM orders;
     order_id | customer_id |    date    | amount | status
----------+-------------+------------+--------+---------
       15 |         498 | 2025-10-02 | 336.85 | shipped
       17 |          55 | 2025-09-02 | 318.28 | shipped
       18 |         234 | 2025-12-14 | 402.03 | shipped
       26 |         180 | 2025-05-20 | 107.43 | shipped
       27 |           1 | 2025-11-20 | 363.33 | shipped
       33 |         158 | 2025-11-21 | 100.19 | shipped
       36 |         304 | 2026-01-24 | 126.47 | shipped
       37 |         351 | 2025-07-17 | 106.98 | shipped
       39 |         305 | 2025-06-19 | 337.23 | shipped

Data was replicated to the orders table on the subscriber according to the specified row filters (amount > 100 AND status = 'shipped').

Change a subscription

Utilize the ALTER SUBSCRIPTION command to change a subscription. The following code changes the subscribed publication to all_tables_in_schema:

ALTER SUBSCRIPTION subscription1 SET PUBLICATION all_tables_in_schema;

The command below disables (stops) the subscription:

ALTER SUBSCRIPTION subscription1 DISABLE;

Delete a subscription

To remove a subscription, use DROP SUBSCRIPTION:

DROP SUBSCRIPTION subscription1;

When you drop and recreate a subscription, the synchronization information is lost. You need to resynchronize data after the recreation.

Conflicts

The logical replication behaves similarly to DML operations, the data is updated even if it was changed locally on the subscriber node. If incoming data violates any constraints, the replication stops. This is considered as a conflict.

A conflict produces an error and stops the replication. You have to resolve it manually. You can find details about the conflict in the subscriber server log. The default path is /pg_data1/adpg16/log.

Example:

2023-01-26 17:29:06.242 UTC [14525] LOG:  background worker "logical replication worker" (PID 1389) exited with exit code 1
2023-01-26 17:29:06.274 UTC [1390] ERROR:  duplicate key value violates unique constraint "test1_pkey"
2023-01-26 17:29:06.274 UTC [1390] DETAIL:  Key (employee_id)=(1) already exists.
2023-01-26 17:29:06.274 UTC [1390] CONTEXT:  COPY test1, line 1

To resolve the conflict, change the data on the subscriber to match the incoming change, or skip the problematic transaction. To omit the transaction, call the pg_replication_origin_advance function and pass a node name corresponding to the subscription name and a position as parameters. You can find the current position of origins in the pg_replication_origin_status system view.

When replicating an UPDATE or DELETE operation, missing data does not produce a conflict, and such an operation is skipped.

Security

If users can modify the schema of tables on the subscriber side, they also can execute arbitrary code as a superuser. Use roles to limit ownership and the TRIGGER privilege on these tables. Moreover, if untrusted users can create tables, use publications that list tables explicitly. Create the FOR ALL TABLES subscription only when you trust every user permitted to create a table on the publisher or subscriber.

The role used for the replication connection must have the REPLICATION attribute or be a superuser. If a role does not have the SUPERUSER or BYPASSRLS attribute, the publisher row security policies can execute. If a role does not trust all table owners, include options=-crow_security=off in the connection string. If a table owner adds a row security policy, this setting causes replication to halt rather than execute the policy. Use the PG_HBA section on the Clusters → ADP cluster → Services → ADPG → Primary configuration tab in ADCM UI to configure role access. A role for replication must have the LOGIN attribute.

To copy the initial table data, the role used for the replication connection must have the SELECT privilege on a published table or be a superuser.

To create a publication, the user must have the CREATE privilege in the database.

To add tables to a publication, the user must have ownership rights on the table. To create a publication that publishes all tables, the user must be a superuser.

To create a subscription, the user must be a superuser.

The process of applying subscription changes is performed in the local database with superuser rights.

Found a mistake? Seleсt text and press Ctrl+Enter to report it