Synchronize 2 databases in postgresql bidirectionally

There are several synchronization options, the “challenge” appears when it must be bidirectional synchronization, that is, changes in the database_1 are reflected in database_2 and also when I change something in database_2 are reflected in database_1.

I’m sure there must be more ways, but this is the solution I managed to implement.

In summary, and before moving on to the step by step, what I did was: using a Postgres tool (pglogic) I made postgres_node1 (docker with pg) listen for changes in postgres_node2 (another docker with pg) and postgres_node2 listen for changes in postgres_node1 and will replicate them. That’s all.

Step by Step

0. Network and Config

We need both Dockers to be able to see each other over a network, only then can we synchronize them. For that “we can” create a network.

docker network create network_bds

1. The Databases

postgres_node1 and postgres_node2 are the same, they are “dockers” built with the latest Postgres image. They have a user and password based on their names: user1-password1 and user2-password2, but their ports are different (because they are in the same environment) 5432 for node1 and 5433 for node2.

In addition, below are the sql scripts to create the test database (publications), the user and the password. Finally we give the user permissions on the database (we will use it to connect later) and we make the user “SUPERUSER” to work with the pglogical statements.

postgres_node1

docker run --name postgres-node1 --network network_bds -e POSTGRES_PASSWORD=password1 -d -p 5432:5432 postgres:latest
docker exec -it postgres-node1 psql -U postgres

CREATE USER user1 WITH PASSWORD 'password1';
CREATE DATABASE posts;

\c posts;

CREATE TABLE publications (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  content TEXT
);

GRANT ALL PRIVILEGES ON DATABASE posts TO user1;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user1;
ALTER USER user1 WITH SUPERUSER;

postgres_node2

docker run --name postgres-node2 --network network_bds -e POSTGRES_PASSWORD=password2 -d -p 5433:5432 postgres:latest
docker exec -it postgres-node2 psql -U postgres

CREATE USER user2 WITH PASSWORD 'password2';
CREATE DATABASE posts;

\c posts;

CREATE TABLE publications (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  content TEXT
);

GRANT ALL PRIVILEGES ON DATABASE posts TO user2;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user2;
ALTER USER user2 WITH SUPERUSER;

The two databases created

2. PgLogical

Install postgresql-15-pglogical (15 depends on the version of pg) and vim (not mandatory, but it is comfortable for me to edit in vim)

docker exec -it postgres-node1 bash
apt update && apt upgrade && apt install -y python3-pip postgresql-15-pglogical vim

docker exec -it postgres-node2 bash
apt update && apt upgrade && apt install -y python3-pip postgresql-15-pglogical vim

In both containers do the same, it is a necessary configuration so that pglogical can be used

vim /var/lib/postgresql/data/postgresql.conf

shared_preload_libraries = 'pglogical'
wal_level = logical

echo "en_US UTF-8 " >> /etc/locale.gen && locale-gen

Restart pg (or docker)!

3. Primary and Secondary

Primary on postgres-node1 and Secondary on postgres-node2

docker exec -it postgres-node1 psql -U postgres

\c posts
CREATE EXTENSION pglogical;
CREATE EXTENSION IF NOT EXISTS pglogical;
SELECT * FROM pg_extension WHERE extname = 'pglogical';

# give permissions to user user1 to do everything
GRANT USAGE ON SCHEMA pglogical TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA pglogical TO user1;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pglogical TO user1;

SELECT pglogical.create_node(node_name := 'node1', dsn := 'host=postgres-node1 dbname=posts port=5432 user=user1 password=password1');
SELECT pglogical.replication_set_add_table('default', 'publications');

docker exec -it postgres-node2 psql -U postgres

\c posts
CREATE EXTENSION pglogical;
SELECT pglogical.create_node(node_name := 'node2', dsn := 'host=postgres-node2 dbname=posts user=user2 password=password2');
SELECT pglogical.create_subscription(subscription_name := 'node1_subscription', provider_dsn := 'host=postgres-node1 dbname=posts user=user1 password=password1');

Now we do the opposite, we leave primary to postgres-node2 and secondary to postgres-node1

docker exec -it postgres-node2 psql -U postgres

\c posts
SELECT pglogical.replication_set_add_table('default', 'publications');

docker exec -it postgres-node1 psql -U postgres

\c posts
SELECT pglogical.create_subscription(subscription_name := 'node2_subscription', provider_dsn := 'host=postgres-node2 dbname=posts user=user2 password=password2 port=5433');

And…. It’s works!