Database replication is creating a replica/copy of the existing database, allowing data to be shared and synchronized across multiple systems. One powerful replication approach is Bidirectional Replication (BDR). Unlike simple one-way replication, BDR enables two servers to exchange data with each other. Both servers act as publishers and subscribers, continuously sending and receiving updates.
In businesses, PostgreSQL Bidirectional Database Replication using pglogical reduces operational costs and helps to build transformative applications. Database replication is required for multiple purposes. Sharing information with multiple users is one of those purposes.
With this feature, users can access data relevantly without interrupting another task in progress and it also helps in collecting data from multiple servers.
Advantages of database replication:
1. The data availability: Imagine if one of the databases fails then you already have another database with the same information so that your data is not lost.
2. Over different users, the same level of information can be shared.
There are multiple types of database replication but mainly we are going to focus on bidirectional database replication. Bidirectional Replication simply means that replication is done in two ways. The following example will explain the bidirectional replication:
Imagine you have two databases DB_Test_1 and DB_Test_2. Now we configure the DB_Test_2 database so that it is the replica of the database DB_Test_1. So from this whatever the queries run on the first database, at the same time the same query will run on the second database. Now further let's configure DB_Test_1 in such a way that when the query is running from the second database the same is reflected in the first database. Running the above simultaneously is known as bidirectional replication. In bidirectional replication, the changes made on the fir-st database are replicated on the second database and the changes made on the second database are replicated on the first database.
Now let’s see how bidirectional replication is done using the PostgreSQL database server.
The replication configuration explained would run on following postgres versions:
OS: ubuntu 18.04
PostgreSQL Version: 14, 15, 16 and 17
PostgreSQL supports two main types of replications:
Physical Replication: Copies the entire data directory block by block. This is ideal for standby servers where high availability is crucial.
Logical Replication: Replicates specific tables or databases at the row level, which is more flexible and allows for multi-master setups.
While physical replication allows you to replicate all the data from the database, in logical-based replication, you can also configure only a subset of the data. PostgreSQL started to add the row-based feature after version 10 but from 9.4 onwards, the feature for decoding was available. Much of the logical replication feature of the PostgresQL application is available from an open-source PostgreSQL logical extension called pglogical. There are also limitations to this extension such as foreign key constraints are not applied for the replication process i.e even if the foreign key is violated, it gets applied to the other database.
This Extension supports unidirectional and bidirectional replication. For our bidirectional replication, we are going to use this extension.
PostgreSQL bi-directional replication using pglogical
Quicksteps Required for the replication:
- Installation of PostgreSQL server and pglogical extension
- Replication Configuration
- First database configuration
- Second database configuration
- Configuration of replication of database one -> database two.
- Configuration of replication of database two -> database one.
- Configuration to do after adding new tables to database
Step 1: Installation of PostgreSQL server and pglogical extension in the system
Run the following commands in the Linux terminal:
# Add the PostgreSQL 17 repository:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
# Update the package list:
sudo apt update
# Install PostgreSQL 17
sudo apt install postgresql-17 postgresql-contrib-17 postgresql-17-pglogical -y
sudo systemctl start postgresql
sudo systemctl enable postgresql
# Check the version and ensure it's Postgresql 17:
psql --version
Step 2: Replication Configuration
a. Create Node A cluster
Edit Node A’s configuration file as:
Change/add:
listen_addresses = 'localhost'
wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'
Add:
local all all trust
host all all 127.0.0.1/32 trust
host replication all 127.0.0.1/32 trust
Stop main cluster:
Restart nodea cluster:
b. Create Node B cluster
Edit its conf file as:
listen_addresses = 'localhost'
wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'
sudo nano /etc/postgresql/17/nodeb/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
host replication all 127.0.0.1/32 trust
Restart nodeb cluster:
- sudo pg_ctlcluster 17 nodeb restart
Step 3: First database configuration
Run the following commands in the terminal
sudo -i –u postgres
psql -p 5432
CREATE ROLE repuser WITH LOGIN SUPERUSER PASSWORD 'rep';
CREATE DATABASE db_a OWNER repuser;
Login to db_a and run:
CREATE EXTENSION pglogical;
CREATE TABLE test_table (
id serial PRIMARY KEY,
data text
);
SELECT pglogical.create_node( node_name := 'node_a', dsn := 'host=localhost port=5432 dbname=db_a user=repuser password=rep' );
SELECT pglogical.create_replication_set( set_name := 'replica_set', replicate_insert := true, replicate_update := true, replicate_delete := true );
SELECT pglogical.replication_set_add_table( set_name := 'replica_set', relation := 'public.test_table', synchronize_data := true );
\q
Step 4: Second database configuration
Run the following commands in the terminal
CREATE ROLE repuser WITH LOGIN SUPERUSER PASSWORD 'rep';
CREATE DATABASE db_b OWNER repuser;
Login to db_b and run:
CREATE EXTENSION pglogical;
CREATE TABLE test_table (
id serial PRIMARY KEY,
data text
);
SELECT pglogical.create_node( node_name := 'node_b', dsn := 'host=localhost port=5433 dbname=db_b user=repuser password=rep' );
SELECT pglogical.create_replication_set( set_name := 'replica_set', replicate_insert := true, replicate_update := true, replicate_delete := true );
SELECT pglogical.replication_set_add_table( set_name := 'replica_set', relation := 'public.test_table', synchronize_data := true );
\q
Step 5: Configuration of replication of database one -> database two
Run the following commands in the terminal to connect to db_a database and create subscription:
- psql -p 5432 -U repuser -d db_a -h 127.0.0.1
SELECT pglogical.create_subscription( subscription_name := 'sub_from_b', provider_dsn := 'host=localhost port=5433 dbname=db_b user=repuser password=rep', replication_sets := ARRAY['replica_set'], synchronize_structure := false, synchronize_data := false, forward_origins := '{}' );
\q
Step 6: Configuration of replication of database two -> database one
Run the following commands in the terminal to connect to db_b database and create subscription:
- psql -p 5433 -U repuser -d db_b -h 127.0.0.1
SELECT pglogical.create_subscription( subscription_name := 'sub_from_a', provider_dsn := 'host=localhost port=5432 dbname=db_a user=repuser password=rep', replication_sets := ARRAY['replica_set'], synchronize_structure := false, synchronize_data := false, forward_origins := '{}' );
Test Subscription status as:
SELECT * FROM pglogical.show_subscription_status();
Step 7: Configuration to do after adding new tables to database
After creating new identical tables on both nodes, add them to the replication set using the steps below to synchronize replication for them as well.
- Create identical table in Node A and Node B, add it to replication set on both nodes as:
SELECT pglogical.replication_set_add_all_tables( set_name := 'replica_set', schema_names := ARRAY['public'] );
SELECT pglogical.replication_set_add_all_sequences( set_name := 'replica_set', schema_names := ARRAY['public'] );
If the replication status is replicating in both of the databases, then you’re ready for replication.
To verify the replication, you can just run an insert query in one of the tables in database one and check if the same is reflected in database two. If this works, then the replication is halfway successful. Now run an insert/update query in the table of database two and check if the same is reflected in database one. If it is then the bidirectional replication is successful.
Conclusion
For the above process, we’ve configured bidirectional replication using pglogical extension. This type of replication can come in handy in situations such as when the same level of information is required for multiple users.
Also, the process of PostgreSQL Bidirectional Database Replication using pglogical is a bit complex, so one must be sure whether the requirement requires replication or not. If the replication is required and if the requirements can be met only by a read replication then it is beneficial that one should use master->slave replication instead of bidirectional.
Hopefully, this article helped you to replicate PostgreSQL Bidirectional Database using pglogical. If you still have a query or need an assistance, we are a Digital Transformation enabling company and our developers are just an email away. Please feel free to get in touch with us.
