Replication
Logical Replication
16
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.
2
Topics
Logical vs Physical Replication
Publications and Subscriptions
Initial Synchronization
Row Identification and Filtration
Logical Decoding
Large Transactions
Conflicts
Replication Sources
3
Comparison
Physical
leader-replica: data flow in one direction only
stream WAL records or send WAL files
binary server compatibility is required
whole cluster replication only
Logical
publication-subscriptions: servers have no dedicated roles
row-level changes are replicated
protocol-level compatibility is required
can replicate individual tables
During physical replication, one server is assigned the leader role and others
become replicas. The leader transfers WAL records to replicas to be applied
to data files there. The replica does not “understand” the changes, just
applies them mechanically without any interpretation of what the changes
mean, so binary compatibility between servers is necessary (the same
platforms and major PostgreSQL versions). Since the WAL is shared across
the entire cluster, only the cluster as a whole can be replicated.
In logical replication, one server creates a publication that other servers can
subscribe to. Servers don't have fixed roles: the same server can both
publish changes and subscribe to publications from other servers (or even
its own).
By default, information about table row changes is transmitted to the
subscription in a text format that's platform-independent and doesn't require
binary compatibility.
4
max_wal_senders = 10 max_logical_replication_workers = 4
max_replication_slots = 10 max_worker_processes = 8
How It Works
publisher
server
select, insert
update, delete
subscriber
wal sender
WAL segments
select, insert
update, delete
WAL segments
logical
replication
apply worker
logical
replication
launcher
Logical replication uses the publish-subscribe pattern.
When the instance starts, the logical replication launcher background
process starts with it, periodically checking the pg_subscription table in the
system catalog for new subscriptions. As soon as a subscription appears, a
background logical replication apply worker process is launched to
handle it. It connects
to the publishing server, which, like any replication connection, starts the wal
sender process. This process reads the WAL, selects the necessary records
and converts them
to platform-independent protocol messages. A logical replication slot is
always used for that.
On the subscription side, the worker receives messages and applies
changes. Meanwhile, the subscriber server can receive normal read and
write queries.
Note that the publishing server can run multiple wal sender processes, one
for each subscription. Both max_wal_senders and max_replication_slots
must be no less than the number of subscriptions.
On the subscriber server, you need to configure
max_logical_replication_workers (for processes that receive subscription
changes) and max_worker_processes in general (more by one at least,
since there's also the logical replication launcher process, though this pool is
actually used for other purposes as well).
5
Publication
Database object
sends data changes row by row in the order they are committed in
only for regular and partitioned tables
wal_level = logical
Published operations
insert, update, delete, truncate
Privileges
creating a new publication – CREATE privilege for the database
add table – owner or superuser
add all tables for a schema or a database – superuser
A publication is created on the server to collect changes from one or more
tables (regular or partitioned) in
the same database. To create a publication, you need the CREATE privilege
on the database. To replicate from multiple databases, multiple publications
must be created.
To include a table in a publication, you must be either a superuser or the
owner of the table. To publish all tables belonging to a schema or a
database, you must be a superuser.
For logical replication to work, the publishing server's WAL must contain
additional information (wal_level = logical).
Publishing includes row changes that occur in tables as a result of DML
commands. The MERGE command executed on the publishing server is
applied as INSERT, UPDATE, or DELETE.
By default, changes are not transferred immediately, but only when the
transaction is committed.
6
Subscription
Database object
receives messages from one or more publications
binary subscription possible
applies changes row by row
Tables and columns are matched by full names
Privileges
create subscription – pg_create_subscription role or superuser
By defining subscriptions to publications in a database, you can receive and
apply changes.
Basically, a subscription is a connection
to the publishing server that serves for data replication.
Members of the pg_create_subscription role or superusers can create
subscriptions.
Changes are always applied row by row. Although each individual change
doesn't require query parsing and planning, bulk changes will still apply
more slowly because of this row-by-row approach.
Tables are identified by their full names (including the schema), and columns
are identified by their names as well. This allows the subscription to use a
different data schema (for example, having additional columns in the table).
When creating a subscription, you can specify the parameter binary to
transfer the data in binary format. The binary format can speed up
processing if binary receive and send functions are defined for the required
data types. However, when using binary format, it's important that the
machine representation of data is compatible across different platforms.
7
S2
S1
P2
P1
P2
P1
Publications and
Subscriptions
publisher
server
subscriber
wal sender
logical repl.
apply worker
S1
P2
P1
subscriber
wal sender
logical repl.
apply worker
wal sender
logical repl.
apply worker
pg_stat_replication
pg_replication_slots
pg_stat_subscription
pg_stat_subscription_stats
You can distribute objects to publications in any way on the publishing
server. You can include all tables in one publication, you can split them
between multiple publications – this does not affect performance.
However, each subscription creates a separate connection to the publishing
server, creating unnecessary load.
Several wal sender processes will run on the publishing server, each of
which will independently read the WAL and perform logical decoding (more
on that below) in its local memory.
Therefore, if you need to subscribe to several publications, it is better to do it
in one common subscription.
Remember that publications and subscriptions are database objects, so
each database will require a separate connection.
Logical replication monitoring on the publishing server is identical to physical
replication monitoring. Your most important tools here are the views
pg_stat_replication and pg_replication_slots.
Subscription status information is stored in pg_subscription.
Information about applying changes is found in the pg_stat_subscription
view.
Replication errors go to pg_stat_subscription_stats.
9
Limitations
Not replicated
sequences
materialized views
temporary and unlogged tables
foreign tables
large objects
DDL
TRUNCATE restriction
tables sharing foreign keys must belong to the same subscription
Only changes to the content of regular and partitioned tables caused by
DML commands are replicated.
The contents of the rest of the objects, broadly defined as “relations”, are not
replicated. This includes sequences, materialized views, temporary and
unlogged tables, foreign tables. Large objects are not replicated.
DDL commands are also not replicated, so you must first create the
necessary tables on the subscription side.
TRUNCATE replicates successfully only if all subscriber tables whose
foreign keys reference the tables being truncated are included in the same
subscription.
10
One synchronization process per table
but no more than max_sync_workers_per_subscription = 2
Initial Synchronization
publisher
server
select, insert
update, delete
subscriber
wal sender
WAL segments
select, insert
update, delete
WAL segments
T1 sync
worker
T2 sync
worker
logical repl.
apply worker
logical
replication
launcher
pg_subscription_rel
By default, if you do not specify a value for copy_data = false, initial table
content synchronization occurs when a subscription is created. This
happens seamlessly through the use of the snapshot export mechanism.
The initial synchronization should be completed as soon as possible so as
not to delay the vacuuming horizon longer than necessary. Therefore,
synchronization is usually done in parallel: the apply worker process looks at
the list of replicated tables and starts additional workers that do the actual
synchronization. One table is always synchronized by one process.
The maximum number of concurrent synchronization processes is limited by
max_sync_workers_per_subscription
(default is two).
The replication status for each table in the subscription is shown in the
pg_subscription_rel view.
If the subscription is flagged as binary, then not only replication will be
performed in binary format, but also initial synchronization.
12
Row Identification
There must be a suitable row for change and delete operations
Logical row ID
ALTER TABLE ... REPLICA IDENTITY ...
DEFAULT primary key columns (default)
USING INDEX unique index columns with NOT NULL constraint
FULL all columns
NOTHING no identification
(default for the system catalog)
New rows are inserted unambiguously on the subscription side. Changes
and deletions are more complicated. You need to determine which row on
the subscriber to apply the operation to. Therefore, a logical row ID is
defined for each table. By default, rows are identified by primary key
columns, but the identifier can be changed with ALTER TABLE ... REPLICA
IDENTITY.
Rows can be identified by another unique index,
in which all columns have a NOT NULL constraint.
Another option is identification by all columns. In PostgreSQL
16 and higher, you can use full B-tree index lookup, where the first field is a
column from the publication. PostgreSQL 17 and up can also use hash
indexes. If there is no index available, a full scan is performed, which is
extremely ineffective for large tables.
You can also completely disable replication support for certain tables (this is
how system catalog tables are set up by default).
When changing or deleting, the column values of the tuple to be deleted do
not get into the replica WAL – this is one of the reasons why the logical WAL
level is required on the publishing server. At logical, it stores the old values
of the columns included in the logical identifier. This is why with identification
by all columns, the WAL size can be significantly larger compared to the
replica level.
13
Row Filtering
Filter at publication
Filtering possible
insert for any published columns
delete for logical ID columns
update for logical ID columns
this may transform into insert or delete
truncate filter ignored
Plain expressions only
By default, the publishing server sends out all published table rows.
Since PostgreSQL 15, you can specify a filter expression. Rows that
evaluate as false or NULL will not be published.
Filtering does not affect the truncate operation. For insert operations, any
published columns can be included in the filter expression, and for update
and delete, only the columns included in the logical row ID.
For update, the filter expression is evaluated for both the old and the new
row version.
If the filter expression is false or NULL for both, the row is not replicated. If
the old row doesn't match the filter but the new one does, the new row is
inserted. If the opposite is true — the old one matches but the new one does
not — the row is deleted.
If both match, update goes through.
You can only filter using plain expressions: no user-defined functions,
operators, types and collations, no referencing system columns, no calling
built-in functions with volatile and stable variability.
15
Logical Decoding
logical operations
WAL records
reorder
buffer
logical
decodin
g
out
put
mo
dule
logical_decoding_work_mem = 64MB
replication protocol messages
logical replication slot
committed transactions
wal sender
During streaming physical replication, the wal sender process reads WAL
records and passes them to the replica unchanged.
During logical replication, records are converted. The logical decoding
module selects records that correspond to the published operations and
tables, taking into account the columns and filter conditions. Selected
records are converted to logical operations information,
which is sent to the reorder buffer and stored there in accordance with the
transaction IDs.
At the logical level, WAL additionally records the information required for
logical decoding, in particular:
●
for update – new values of all columns, not just changed ones,
●
for update and delete – the old values of the columns in the
logical ID,
●
for commit – database OID.
The reorder buffer is in the local memory of the wal sender process. If the
amount of data in it approaches the value of the
logical_decoding_work_mem parameter, the largest transaction is sent to
the replication slot (a file in PGDATA/pg_replslots/name).
When a transaction is committed, all its operations are sent to the output
module, which converts them into replication protocol messages.
17
Large Transactions
WAL segments
select, insert
update, delete
apply worker
apply worker
apply worker
apply parallel
worker
apply parallel
worker
streaming =
parallel
streaming = on
streaming = off
wal sender
wal sender
temp.
file
temp.
file
temp.
file
wal sender
max_parallel_apply_workers_per_subscription = 2
By default (subscription streaming = off), changes are accumulated in the
reorder buffer and sent to the subscriber after the transaction is committed.
If a transaction changes a lot of data, problems can occur:
●
wal sender's reorder buffer memory costs,
●
writing files when the reorder buffer exceeds
(logical_decoding_work_mem) memory,
●
replica lag.
If you set streaming = on, the publishing server will pass data changes to the
subscriber while the transaction is still running.
When the total amount of changes exceeds the value of the
logical_decoding_work_mem parameter, the accumulated changes of the
largest transaction in terms of volume are not dumped into to a file, but are
immediately sent to the output module.
The subscriber saves these changes
in its temporary files and applies them when committing the transaction.
Starting with PostgreSQL 16, if streaming = parallel and there are workers
available, changes are not saved at the subscriber, but are immediately
applied by the free workers. The number of such processes per subscription
is limited by the max_parallel_apply_workers_per_subscription parameter.
18
Conflicts
Lead to errors
constraint violations,
insufficient rights to target table or row level security
replication error,
subscription with disable_on_error goes down
you can manually correct subscription-side data
or skip the conflicting transaction
Ignored
row being updated or deleted does not exist at the subscriber
replication continues, but consistency may be compromised
Because the tables on the publishing server and at the subscriber can
change independently of each other, a subscription operation may conflict
with existing data.
Often a conflict is a situation where an operation cannot be performed,
usually due to a violation of an integrity constraint, or the lack of permissions
on the target table, or row level security restrictions.
In this case, the operation fails. The subscriber periodically repeats
attempts, but they will fail until the conflict is resolved. Conflicts can only be
resolved manually, automatic resolution has not yet been implemented.
There are two options: fix the data at the subscriber or skip the conflicting
transaction. In the second case, you need to disable replication, call the
pg_replication_origin_advance function or execute the ALTER
SUBSCRIPTION ... SKIP..., and enable replication again. You can also pre-
configure automatic disabling of replication by specifying the
disable_on_error subscription option.
If the row to be modified or deleted does not exist at the subscriber, the
operation will fail. In this case, no error occurs, but such ignored conflicts
indicate that the data consistency between the publishing server and the
subscriber is compromised.
20
origin subscription option
any — transmit all changes (default)
none — transmit local changes only
Replication origin
select, insert
update, delete
WAL segments
select, insert
update, delete
WAL segments
wal sender
logical repl.
worker
logical repl.
worker
wal sender
O1
O1 O2
origin
transaction
s
O1 O2
pg_replication_origin
pg_replication_origin_status
If you define both a publication and a subscription on the same server and
transfer all changes, the replicated changes will be replicated, and things will
snowball.
Starting with PostgreSQL 16, when the publishing server starts a transaction
that was initially received from another node, it sends a message about the
origin of this transaction first. This is also important for cascading replication.
By default, the subscription starts with the origin = any parameter and the
publication passes all transactions to it: both local and received from another
node.
However, in topologies where loops can occur, such as with bidirectional
replication, you should set origin = none.
Then the publication will transfer only local changes.
Information about the origins is stored in the pg_replication_origin view.
The current replay position for a specific replication origin is displayed in the
pg_replication_origin_status view.
22
Takeaways
Logical replication uses the publish-subscribe pattern.
Row-level changes are replicated
Rows are matched using logical identifiers
Data mismatch on different servers may cause conflicts
Origin information forwarding helps track the progress of
cascade replication and eliminate loops
23
Practice
1. Create two databases on a single server.
In the first database, create a table with a primary key and insert
a few rows into it. Set up logical replication for the table from
the first database to the second, verify that replication is working,
and then remove the subscription.
2. Configure logical replication of the table from alpha to beta.
Execute a transaction that modifies a large amount of data,
and before committing it, verify that the data has been
written to disk. Repeat the same steps, but this time in
streaming mode.
Did the data get to disk?
1. Use the pg_dump tool with the --schema-only option.
If you try the regular way, the subscription creation command will hang
because it needs to wait for all active transactions on the publishing server
to complete, including itself.
In this case, you must create a logical replication slot in advance, as
described in the documentation:
2. To prevent the publication from getting into the logical copy, use the --no-
publications option.
Check the value of the logical_decoding_work_mem configuration
parameter, which sets the maximum size of the reorder buffer. If this value is
exceeded, data will be pushed to disk.
24
Practice+
1. Create and publish a table with a bytea column on alpha.
Insert several million rows into the table. Compare the initial
subscription synchronization time in normal and binary modes
using the beta server.
2. Enable concurrent synchronization for the subscription. By
adding rows to the table, examine the subscription workers.
1. You can estimate the initial synchronization time by monitoring the
pg_subscription_rel table. You can set it up
on the client side using the \watch metacommand or on the server side
using a function written in, for example, PL/pgSQL.
2. Set an appropriate value for the streaming subscription option.
Use the pg_stat_subscription view to monitor subscription processes.
