Database replication is creating a replica/copy of the existing database. Bi-directional Replication (BDR) is when two servers exchange data with each other. Both of these servers publish and then store data from each other. It critically improves an application’s reliability, performance, and data integrity.
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:
- The data availability: Imagine if one of the databases fails then you already have another database with the information so that your data is not lost.
- 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 first database are replicated on the second database and the changes made on the second database are replicated on the first database.
Now I am going to show you how bidirectional replication is done using the PostgreSQL database server.
The following are my system configurations:
OS: ubuntu 18.04
PostgreSQL Version: 14
PostgreSQL supports database replication in block-based (physical) and also row-based (logical). The physical type is mainly used to create read-only replication. The row-based replication can also be used in the write type i.e. you can configure the node to execute queries and at the same time application can directly write to the tables of the database.
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.
- Query to run after adding tables in the databases
Step 1: Installation of PostgreSQL server and pglogical extension in the system
Run the following commands in the Linux terminal:
curl https://dl.2ndquadrant.com/default/release/get/deb | sudo bash
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt install postgresql-14 postgresql-contrib-14
sudo apt-get install postgresql-14-pglogical
Step 2: Replication Configuration
1. Configure PostgreSQL user
a. Set a password for the PostgreSQL user
b. Switch to PostgreSQL user.
2. Configure server
a. User creation for replication.
createuser -s --replication ubuntu
b. pg_hba.conf modification
Run the following in terminal
cd /etc/postgresql/14/main
nano pg_hba.conf
Update the file as per following
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all peer
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host replication ubuntu 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
c . Modify the postgresql.conf file.
cd /etc/postgresql/14/main
nano postgresql.conf
Make the changes as per following:
listen_addresses = 'localhost'
wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical’
d.Restart postgres server to implement your changes:
Service postgresql restart
e.Database Creation:
createdb -p <postgres port> -O <user for replication> <database name>
Step 3: First database configuration
Run the following commands in the terminal
sudo su postgres
psql -p <postgres port>
\c dbname
CREATE EXTENSION pglogical_origin;
CREATE EXTENSION pglogical;
SELECT pglogical.create_node(node_name := 'provider',dsn := 'host=<hostname> port=<port> dbname=<database name> user=<user>);
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);
Step 4: Second database configuration
Run the following commands in the terminal
sudo su postgres
psql -p <postgres port>
\c dbname
CREATE EXTENSION pglogical_origin;
CREATE EXTENSION pglogical;
SELECT pglogical.create_node(node_name := 'provider',dsn := 'host=<hostname> port=<port> dbname=<database name> user=<user>);
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);
Step 5: Configuration of replication of database one -> database two
1. Run the following commands in the terminal
sudo su postgres
psql -p <postgres port>
/c dbname (it should be database one)
SELECT pglogical.create_subscription(subscription_name := 'subscription',provider_dsn := 'host=<database two hostname> port=<database two port name> dbname=<database two dbname> user=<username>',synchronize_structure := false, synchronize_data=false, forward_origins := '');
2. Run the following to check the replication status:
select subscription_name, status FROM pglogical.show_subscription_status();
Step 6: Configuration of replication of database two -> database one
3. Run the following commands in the terminal
sudo su postgres
psql -p <postgres port>
/c dbname (it should be database two)
SELECT pglogical.create_subscription(subscription_name := 'subscription',provider_dsn := 'host=<database one hostname> port=<database one port name> dbname=<database one dbname> user=<username>',synchronize_structure := false, synchronize_data := false , forward_origins := '');
4.Run the following to check the replication status:
select subscription_name, status FROM pglogical.show_subscription_status();
If the status is replicating then you’re ready to go. If it’s down, then wait a few minutes and try again. If the status is updated to replicating, then you’re ready to go.
Step 7:Query to run after adding tables in the database
Run the following queries in both the database
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
SELECT pglogical.replication_set_add_all_sequences('default', 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.
