Logical replication has long been a feature of many database management systems. It’s the simple process of copying and maintaining identical data across multiple database servers. The methods taken to achieve this process, however, are quite varied – and new methods are being introduced all the time.
One of the newest methods is PostgreSQL’s bi-directional logical replication – and that’s the focus of this article.
What is logical replication?
Logical replication is how database management systems replicate changes to data. It refers to transmitting – or replicating – the “logic” of changes to data (logical), rather than copying all of the underlying storage files.
The logic between logical replication refers to copying individual operations pertaining to a database (SQL queries), allowing the destination database to replay those SQL queries and welcome the data.
What is bi-directional logical replication in PostgreSQL?
Bi-directional logical replication, introduced in PostgreSQL version 16, is where two or more PostgreSQL-based database instances exchange data at the same time.
It’s also sometimes referred to as a multi-master database replication setup.
With bi-directional logical replication, if you make changes to one PostgreSQL instance, they’ll be replicated to other PostgreSQL instances as well. In such a replication setup, all database nodes are publishers and subscribers: both servers can read, write, and update data.
Get started with PostgreSQL – free book download
This replication method is made easier when we pass specific replication configuration options usingCREATE SUBSCRIPTION … WITH:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE SUBSCRIPTION database_subscriber CONNECTION ‘host=publisher dbname=your_database user=replication_user password=replication_password’ PUBLICATION database_publisher WITH ( [options] ); |
This tells your PostgreSQL database that:
- This is a subscriber database node named
database_subscriber. - We connect to a publisher database with the credentials supplied in
CONNECTION. - A publisher database is named
publisher_name. WITH, and options within, define the specific options relevant to the publisher database node.
In this case, [options] would include any applicable filtering options. For example, we could use the ORIGIN=NONE option as part of the options we define to tell our database that only new data should be replicated across our database nodes.
To finish setting everything up, we’d need to configure the publisher database node too. Something like this would work:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `data_table` ( `id` INT PRIMARY KEY, `username` TEXT ); CREATE PUBLICATION `database_publisher` FOR TABLE `data_table` WITH ( publish = ‘insert, update, delete’ ); |
What are the pros and cons of bi-directional logical replication in PostgreSQL?
Bi-directional logical replication in PostgreSQL, as with everything else, is not 100% perfect: there are pros and cons.
The advantages of bi-directional logical replication in PostgreSQL
The advantages (pros) are:
Changes to any database nodes are propagated to the others
This is the main benefit of bi-directional logical replication in PostgreSQL. It allows both database nodes to publish and subscribe to changes made within themselves, meaning that updates made on any database node can automatically propagate to the others.
Improved disaster recovery
As with many replication types, bi-directional logical replication improves disaster recovery because, if any database node fails, the remaining nodes will continue to operate as usual. Plus, if two or more nodes are still active, they can continue replicating any changes to data between themselves.
Strong data distribution
When bi-directional logical replication is in use, data within PostgreSQL can be replicated across multiple regions.
The disadvantages of bi-directional logical replication in PostgreSQL
What’s not-so-good about bi-directional logical replication in PostgreSQL? Here are the cons:
Bi-directional logical replication only replicates changes to data
Only the INSERT, UPDATE, and DELETE SQL statements are replicated. If any other operations are required, they must be performed manually.
DDL (data definition language) statements are not replicated
Similarly, if you alter tables, drop or create columns or indexes within your database node, the changes must be propagated manually. Bi-directional logical replication does not have you covered in this regard.
Issues with auto-generated IDs (and similar)
According to some sources, since PostgreSQL uses sequences to automatically increment values, if different database nodes insert a row bearing the same generated ID into the same table, you may face issues. Manual double-checking is advised.
Move fast. Govern at scale.
Should I utilize bi-directional logical replication in PostgreSQL?
In reality, the boring answer is – as is often the case – “it depends”. It depends because:
Bi-directional logical replication is only available within Postgres 16 and above
If you’re not running PostgreSQL version >= 16, upgrade or explore other available options.
Bi-directional logical replication works differently in specific versions of PostgreSQL
While PostgreSQL version 16 introduced bi-directional logical replication, version 17 then further enhanced it. This means that you need to decide which version is best for your use case – there’s no ‘one-size-fits-all’ answer.
Bi-directional logical replication only works if you run two or more database nodes
You mustn’t modify data beyond using simple CREATE, UPDATE, and DELETE queries
Bi-directional logical replication in PostgreSQL doesn’t act on anything beyond simple UPDATE, DELETE, and data creation (INSERT) operations. If your use case necessitates frequent altercations to data or its structure, you’re going to have a hard time, because you’d have to perform all of those operations yourself – regardless of if you replicate or not.
Your databases need to be configured properly
If multiple nodes need to accept basic operations, your application is configured to deal with schema altercations, and you can manage the added operational complexity, bi-directional logical replication might be for you.
How to set up bi-directional logical replication in PostgreSQL
Further to these points, bi-directional logical replication in PostgreSQL can be quite hard to set up, but here’s a guide to help you out.
You will need to have PostgreSQL >=16 at the ready. Then, follow these steps:
Ensure that pg_hba.conf is configured to allow replication, by checking that these lines exist on the file within all database nodes:# Allow replication connections from localhost
host replication all 127.0.0.1/32 trust
Next, ensure that both database nodes are running, and that each database has data within itself (tables, etc.)
Then, connect to each node, and create a publication:
|
1 2 3 4 5 6 |
-- On db1 postgres=# CREATE PUBLICATION publisher_db1 FOR TABLE your_table; -- On db2 postgres=# CREATE PUBLICATION publisher_db2 FOR TABLE your_table; |
And create subscriptions on each database node, and ensure they run origin filtering:
|
1 2 3 4 5 6 7 8 |
-- On db1 postgres=# CREATE SUBSCRIPTION subscriber_db2 CONNECTION 'host=127.0.0.1 port=5433 user=username dbname=dbname' PUBLICATION publisher_db2 WITH (origin = 'none'); -- On db2 postgres=# CREATE SUBSCRIPTION subscriber_db1 CONNECTION 'host=127.0.0.1 port=5432 user=username dbname=dbname' PUBLICATION publisher_db1 WITH (origin = 'none'); |
Summary: bi-directional logical replication in PostgreSQL
And that’s it – everything you need to know about bi-directional logical replication in PostgreSQL. What do you think? Have you used it yourself? I’d love to hear from you down in the comments below!
You may also be interested in…
Simple Talk’s full archive of PostgreSQL articles and guides
FAQs: Bi-directional logical replication in PostgreSQL
1. What is bi-directional logical replication in PostgreSQL?
It’s a replication setup, introduced in PostgreSQL 16, where two or more database instances act as both publisher and subscriber simultaneously — sometimes called multi-master replication. Changes made on any node are replicated to the others.
2. What version of PostgreSQL do I need to use bi-directional logical replication?
You need PostgreSQL 16 or later. Version 17 further improved on the feature, so it’s worth checking which version best suits your setup before implementing.
3. What operations does bi-directional logical replication support?
Only INSERT, UPDATE, and DELETE statements are replicated. DDL changes (like altering tables or dropping columns) are not replicated automatically and must be applied manually to each node.
4. What are the main benefits of bi-directional logical replication in PostgreSQL?
It propagates changes across all nodes automatically, improves disaster recovery since remaining nodes keep operating if one fails, and supports strong data distribution across regions.
5. Are there any risks or downsides to bi-directional logical replication in PostgreSQL?
Yes — DDL changes require manual syncing, and auto-generated IDs (via sequences) can collide across nodes if the same ID is inserted independently on different servers. Careful configuration and monitoring are needed to avoid conflicts.
6. Do I need multiple database servers to use it?
Yes. Bi-directional logical replication only makes sense with two or more database nodes — there’s nothing to replicate to on a single server.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments