https://www.highgo.ca/2023/12/18/new-in-postgresql-16-bi-directional-logical-replication/ Highgo Software Inc. * Home * Products + HighGo PostgreSQL Server * Yum * Blog * News * About Us * Careers * Contact Us Highgo Software Inc. * Home * Products + HighGo PostgreSQL Server * Yum * Blog * News * About Us * Careers * Contact Us New in PostgreSQL 16: Bi-Directional Logical Replication Enterprise PostgreSQL Solutions December 18, 2023 | By Tristen Raab Leave a Comment New in PostgreSQL 16: Bi-Directional Logical Replication [New-in-PostgreSQL-16-Bi-Directional-Logical-Replication] Introduction In this blog, we'll be going over some more advanced topics new in Postgres 16. Having some experience with Linux, Postgres, and SQL is necessary as we'll not only be going over these new features but also how to implement them. This blog was written using PostgreSQL 16 (Development Version) running on Ubuntu 23.04. First I'll go over some background and a brief introduction to what Bi-Directional Replication is, and why it's important, then finish off with how we implement Bi-Directional Logical Replication. Background Before we can start learning about Bi-Directional Logical Replication we first have to understand Logical Replication. Basics of Logical Replication Logical replication has been supported since PostgreSQL 10 and has received numerous updates and improvements in the following years. Logical Replication is the process of copying (ie. replicating) data objects represented as their changes. This way we can copy only specific changes of objects like tables rather than whole databases, and stream these changes across different platforms and versions. This is all in contrast to Physical replication which uses exact block addresses and as a result, is limited to only copying entire databases and cannot stream across platforms or versions since the data must match in both. [logical-replication-1]Fig 1: Logical Replication Architecture Overview Logical replication also introduces two very important elements necessary for understanding its Bi-Directional counterpart. These are Publishers and Subscribers, you can think of them as a leader node (Publisher) and a follower node (Subscriber). The Publisher will gather up its recent changes and send them as an ordered list of commands to the Subscriber. Once received the Subscriber takes this series of commands and applies it to its data. If both databases started with the same data, then the Subscriber will be up-to-date with the Publisher. Bi-Directional Replication Now that we understand what Logical Replication is, what is Bi-Directional Replication doing differently? In short, Bi-Directional Logical Replication is when all nodes in the replication are both Publisher and Subscriber. Each database can now handle read and write requests, and all the changes will be streamed to one another. This is the Bi-Directional aspect, as rather than changes flowing in one direction as before, they flow in both directions. [unnamed]Fig. 2: Bi-Directional Replication Architecture Overview What Postgres 16 adds is a new parameter to the WITH statement that filters out replication from certain nodes. Bi-Directional Logical Replication uses this parameter WITH(ORIGIN = NONE), this filters out all replication from connections with origins that are not NONE. Essentially, this only allows newly added data to be replicated, you can probably see why this is the case. If one database inserts new data and replicates it to a second, this second database will replicate the data also inserting it thus triggering another replication to the original database. We quickly get an infinite loop of replication, which is why this option is necessary to keep everything finite. Benefits The main benefit of Bi-Direction Logical Replication is that it allows more availability for both read and write requests since we have two Primary nodes. This can be extremely beneficial for a wide range of applications where writing is especially needed. Drawbacks Bi-Directional Logical Replication requires a few preconditions to operate correctly, as such many of its drawbacks are from these specific conditions. For example, when setting up replication the tables in each database must follow the same schema. Same name and columns, otherwise the Subscriber will not be able to find the table. Until Logical Replication can support replication of the Data Definition Language (DDL) used to create the tables, the user must do this manually to ensure consistency. Setting Up Now that we understand the basics of Bi-Directional Logical Replication, we can look into how we implement it between two databases. The beginning will be quite similar to setting up regular Logical Replication, but with a very important difference when we are creating the Publishers and Subscribers. First, we will create the two primary databases which will follow each other: $ initdb -D database1 $ initdb -D database2 In each database's postgres.conf file set each's way_level to logical and give each one a unique port number: postgres.conf database1 port = 5432 wal_level = logical postgres.conf database2 port = 5433 wal_level = logical Start both databases: pg_ctl -D database1 -l database1.log start pg_ctl -D database2 -l database2.log start Create the Publishers for each Database: # CREATE PUBLICATION mypub1 FOR TABLE mytable; # CREATE PUBLICATION mypub2 FOR TABLE mytable; Create the Subscribers for each Database: # CREATE SUBSCRIPTION mysub1 CONNECTION 'host=127.0.0.1 port=5433 user=postgres dbname=postgres' PUBLICATION mypub2 WITH(ORIGIN = NONE); # CREATE SUBSCRIPTION mysub2 CONNECTION 'host=127.0.0.1 port=5432 user=postgres dbname=postgres' PUBLICATION mypub1 WITH(ORIGIN = NONE); Note the order we created the publishers and subscribers, it is very important to first create the Publishers and then the Subscribers. You can refer to Figure 2 if you want a more visual representation, the number in the corner of each component denotes the order of their creation. Now, when any data is inserted into either database, it should be replicated across both nodes. Conclusion In this blog, we went over the new Bi-Directional Logical Replication feature in PostgreSQL 16. To start, we went over a brief background on Logical Replication and the Publisher/Subscriber model used for synchronizing data. We then went over how Bi-Directional Logical Replication works and the new parameter that allows it to function without triggering infinite replication loops. Finally, we looked at how to set up Bi-Directional Replication with two primary PostgreSQL databases. With support for synchronization between primary nodes, increasing availability and data persistence should be a breeze for any of your database applications. References * C, Vigneshwaran. Bi-Directional Replication Using Origin Filtering in PostgreSQL, Fujitsu, 31 Aug. 2023, www.postgresql.fastware.com/blog/ bi-directional-replication-using-origin-filtering-in-postgresql. Tristen Raab Tristen Raab Tristen received his Bachelor of Applied Science in Computer Engineering from the University of British Columbia in May 2023. He joined HighGo Software Inc. as a Software Engineer fresh out of university and is very excited for his engineering journey to begin. His main interests include Machine Learning, Embedded Systems, and Database Management Systems. With experience in C/C++ and advanced relational databases, Tristen hopes to contribute significantly to the PostgreSQL community as he continues to learn and grow his expertise. Tag: bi-directional logical replication, bi-directional replication, logical replication, postgres, postgresql, replication 0 0 [Managin] Managing PostgreSQL Like a Pro: A Kubernetes-based pgAdmin Tutorial November 6, 2023 Search for: [ ] Recent Posts * New in PostgreSQL 16: Bi-Directional Logical Replication * Explore Table Access Method Capabilities: How Data Insertion is Handled * Experiencing WAL REDO in PostgreSQL * Explore Table Access Method Capabilities: Sequential Scan Analyzed * Managing PostgreSQL Like a Pro: A Kubernetes-based pgAdmin Tutorial Recent Comments Archives * December 2023 * November 2023 * October 2023 * September 2023 * August 2023 * July 2023 * June 2023 * May 2023 * April 2023 * March 2023 * February 2023 * January 2023 * December 2022 * November 2022 * October 2022 * September 2022 * August 2022 * July 2022 * June 2022 * May 2022 * April 2022 * March 2022 * February 2022 * January 2022 * December 2021 * November 2021 * October 2021 * September 2021 * August 2021 * July 2021 * June 2021 * May 2021 * April 2021 * March 2021 * February 2021 * January 2021 * December 2020 * November 2020 * October 2020 * September 2020 * August 2020 * July 2020 * June 2020 * May 2020 * April 2020 * March 2020 * February 2020 * January 2020 * November 2019 * October 2019 * September 2019 * August 2019 * July 2019 * June 2019 Categories * AES * HA * highavailability * JIT * KMS * News * Parallel * performance * pgpool * postgresql * Sharding * SSL * TDE * Uncategorized * Vacuum Tags attach compute encryption global unique index ha highavailability HTTP index load balancer load balancing logical replication meson neon networking nginx orphaned prepared transactions performance pgpool Pgpool-II pitr pluggable storage api point in time recovery postgres postgresconf.cn postgresql prepared transactions proxy recovery replication REST reverse proxy savepoint security server sharding smgr sql storage streaming replication table access method TCP TLS UDP unique wal2mongo Canada Office Room 510 - 9850 King George Blvd, Surrey, BC V3T 0P9 Email contact@highgo.ca Contact Number +1 (604) 781-6749 (c) 2023, HighGo Software Inc. Send to Email Address [ ] Your Name [ ] Your Email Address [ ] [Send Email] Close