Replication
Overview of Logical Replication
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey 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.
2
Topics
Logical Replication
WAL Levels
Publications and Subscriptions
Conflict Detection and Resolution
Replica Usage
3
Logical Replication
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
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.
4
Logical Replication
publisher
select, insert
update, delete
subscriber
wal sender
WAL segments
select, insert
update, delete
WAL segments
logical replication
worker
or its
standby
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.
5
Publication and Subscription
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
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.
6
wal_level parameter
minimal < replica < logical
crash recovery crash recovery crash recovery
restore from backup, restore from backup,
replication replication
logical replication
WAL Levels
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.
8
Conflicts
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
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.
10
Restrictions
Not replicated
DDL commands
sequence values
large objects
changes to views, materialized views, and foreign tables
Not supported
automatic conflict resolution
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.
11
Features
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
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.
12
receipt and consolidation
of data from regional servers
1. Consolidation
central
server
select, insert
update, delete
regional
server
wal sender
regional
server
wal sender
select, insert
update, delete
WAL segments
select, insert
update, delete
WAL segments
logical repl.
worker
logical repl.
worker
WAL segments
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.
13
2. Server Update
updating the major version
without interruption of service
old server
select, insert
update, delete
new server
WAL segments
13.6
16.2
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.
14
2. Server Update
updating the major version
without interruption of service
old server
select, insert
update, delete
new server
wal sender
logical repl.
worker
WAL segments
13.6
16.2
initial
synchronization
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.
15
updating the major version
without interruption of service
2. Server Update
new server
16.2
select, insert
update, delete
WAL segments
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.
16
a cluster where
multiple servers can modify data
3. Primary-Primary
main server
main server
select, insert
update, delete
WAL segments
select, insert
update, delete
WAL segments
wal sender
logical repl.
worker
logical repl.
worker
wal sender
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.
17
Takeaways
Logical replication streams individual row changes
multidirectional
requires protocol-level compatibility
Publish and subscribe model
All current restrictions must be considered
18
Practice
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.
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.
