49.8. Synchronous Replication Support for Logical Decoding
49.8.1. Overview
Logical decoding can be used to build
synchronous
replication
solutions with the same user interface as synchronous
replication for
streaming
replication
. To do this, the streaming replication interface
(see
Section 49.3
) must be used to stream out
data. Clients have to send
Standby status update (F)
(see
Section 55.4
) messages, just like streaming
replication clients do.
Note
A synchronous replica receiving changes via logical decoding will work in
the scope of a single database. Since, in contrast to
that,
synchronous_standby_names
currently is
server wide, this means this technique will not work properly if more
than one database is actively used.
49.8.2. Caveats
In synchronous replication setup, a deadlock can happen, if the transaction has locked [user] catalog tables exclusively. See Section 49.6.2 for information on user catalog tables. This is because logical decoding of transactions can lock catalog tables to access them. To avoid this users must refrain from taking an exclusive lock on [user] catalog tables. This can happen in the following ways:
-
Issuing an explicit
LOCK
onpg_class
in a transaction. -
Perform
CLUSTER
onpg_class
in a transaction. -
PREPARE TRANSACTION
afterLOCK
command onpg_class
and allow logical decoding of two-phase transactions. -
PREPARE TRANSACTION
afterCLUSTER
command onpg_trigger
and allow logical decoding of two-phase transactions. This will lead to deadlock only when published table have a trigger. -
Executing
TRUNCATE
on [user] catalog table in a transaction.
Note that these commands that can cause deadlock apply to not only explicitly indicated system catalog tables above but also to any other [user] catalog table.