As you may perceive from my earlier blogs I’m actually into PostgreSQL.
Beforehand we ran Debezium in Embedded mode. Behind the scenes, Debezium consumes the adjustments that have been dedicated to the transaction log. This occurs by using the logical decoding characteristic of PostgreSQL.
On this weblog, we will concentrate on replication and extra particularly bidirectional replication. To realize bidirectional replication in PostgreSQL we’d like the module pglogical. You may marvel concerning the distinction between logical decoding and pglogical. Basically, logical decoding has its origins in PgLocigal. View PgLocial as a extra featureful module whereas logical decoding is embedded in a PostgreSQL distribution.
We’ll create a customized PostgreSQL Docker picture and set up PgLogical.
# Use the official PostgreSQL picture as base
FROM postgres:15
USER root
RUN apt-get replace; apt-get set up postgresql-15-pglogical -y
USER postgresAdditionally, we have to have a PostgreSQL configuration that can allow PgLogical replication and battle decision.
listen_addresses="*"
port = 5432
max_connections = 20
shared_buffers = 128MB
temp_buffers = 8MB
work_mem = 4MB
wal_level = logical
max_wal_senders = 3
track_commit_timestamp = on
shared_preload_libraries="pglogical"
pglogical.conflict_resolution = 'first_update_wins'Let’s break this down. We added pglogical and we enabled track_commit_timestamp. By enabling this parameter PostgreSQL tracks the commit time of transactions. This might be essential for the battle decision technique.
Now let’s see the battle decision. We chosen ‘first_update_wins’; due to this fact, in case of two transactions working on the identical row, the transaction that completed first would be the one to be thought-about.
Bidirectional replication is ready up upon a desk. Since we use Docker we will present an initialization script to PostgreSQL.
The script will:
- Allow pglogical
- Create the desk
- Add a goal node
- Insert the row we will run checks upon
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" Let’s create the situations now utilizing Docker Compose.
model: '3.1'
companies:
postgres-a:
construct: ./pglogicalimage
restart: at all times
setting:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
TARGET: postgres-b
volumes:
- ./config/postgresql.conf:/and so on/postgresql/postgresql.conf
- ./init:/docker-entrypoint-initdb.d
command:
- "-c"
- "config_file=/etc/postgresql/postgresql.conf"
ports:
- 5431:5432
postgres-b:
construct: ./pglogicalimage
restart: at all times
setting:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
TARGET: postgres-a
volumes:
- ./config/postgresql.conf:/and so on/postgresql/postgresql.conf
- ./init:/docker-entrypoint-initdb.d
command:
- "-c"
- "config_file=/etc/postgresql/postgresql.conf"
ports:
- 5432:5432We will get our situations up and working by issuing:
docker compose up
Docker Compose V2 is on the market with many good options, yow will discover extra about it within the e-book I authored: A Developer’s Important Information to Docker Compose.
Since each situations are up and working we have to allow the replication. Subsequently we will subscribe the nodes to one another.
Execute on the primary node:
SELECT pglogical.create_subscription(
subscription_name := 'postgres_b',
provider_dsn := 'host=postgres-b port=5432 dbname=postgres consumer=postgres password=postgres',
synchronize_data := false,
forward_origins := '{}' );Execute on the second node:
SELECT pglogical.create_subscription(
subscription_name := 'postgres_a',
provider_dsn := 'host=postgres-a port=5432 dbname=postgres consumer=postgres password=postgres',
synchronize_data := false,
forward_origins := '{}' );You should use any PostgreSQL shopper that fits you. Alternatively, you may simply use the psql shopper that comes packaged with the Docker Pictures.
For instance:
Login to the primary node:
docker compose exec postgres-a psql --username postgres --dbname postgres
Login to the second node:
docker compose exec postgres-b psql --username postgres --dbname postgres
Let’s see how battle decision will work now.
On the primary node, we will run the next snippet:
BEGIN;
UPDATE test_schema.worker SET lastname="first wins";
#earlier than committing begin transaction on postgres-b
COMMIT;Don’t press commit instantly, as a substitute take the time and earlier than you commit the transaction begin the next transaction on the second node.
BEGIN;
UPDATE test_schema.worker SET lastname="second looses";
#be sure transaction on node postgres-a is dedicated first.
COMMIT;This transaction might be dedicated after the transaction that takes place in postgres-a.
Let’s test the logs on postgres-a-1:
postgres-a-1 | 2024-05-01 07:10:45.128 GMT [70] LOG: CONFLICT: distant UPDATE on relation test_schema.worker (native index employee_pkey). Decision: keep_local.
postgres-a-1 | 2024-05-01 07:10:45.128 GMT [70] DETAIL: current native tuple {id[int4]:1 firstname[text]:John lastname[text]:first wins electronic mail[text]:john1@doe.com age[int4]:18 wage[float4]:1234.23} xid=748,origin=0,timestamp=2024-05-01 07:10:42.269227+00; distant tuple {id[int4]:1 firstname[text]:John lastname[text]:second looses electronic mail[text]:john1@doe.com age[int4]:18 wage[float4]:1234.23} in xact origin=1,timestamp=2024-05-01 07:10:45.125791+00,commit_lsn=0/16181C0
postgres-a-1 | 2024-05-01 07:10:45.128 GMT [70] CONTEXT: apply UPDATE from distant relation test_schema.worker in commit earlier than 0/16181C0, xid 747 dedicated at 2024-05-01 07:10:45.125791+00 (motion #2) from node replorigin 1
The transaction that befell on postgres-a completed first. Postgres-a acquired the replication knowledge from the transaction of node postgres-b. A comparability was issued on the commit timestamp as a result of the commit timestamp of the transaction on postgres-a was earlier the decision was to maintain the native adjustments.
We will see the reverse on postgres-b:
postgres-b-1 | 2024-05-01 07:10:45.127 GMT [81] LOG: CONFLICT: distant UPDATE on relation test_schema.worker (native index employee_pkey). Decision: apply_remote.
postgres-b-1 | 2024-05-01 07:10:45.127 GMT [81] DETAIL: current native tuple {id[int4]:1 firstname[text]:John lastname[text]:second looses electronic mail[text]:john1@doe.com age[int4]:18 wage[float4]:1234.23} xid=747,origin=0,timestamp=2024-05-01 07:10:45.125791+00; distant tuple {id[int4]:1 firstname[text]:John lastname[text]:first wins electronic mail[text]:john1@doe.com age[int4]:18 wage[float4]:1234.23} in xact origin=1,timestamp=2024-05-01 07:10:42.269227+00,commit_lsn=0/1618488
postgres-b-1 | 2024-05-01 07:10:45.127 GMT [81] CONTEXT: apply UPDATE from distant relation test_schema.worker in commit earlier than 0/1618488, xid 748 dedicated at 2024-05-01 07:10:42.269227+00 (motion #2) from node replorigin 1Let’s test the consequence within the database.
postgres=# SELECT*FROM test_schema.worker;
id | firstname | lastname | electronic mail | age | wage
----+-----------+------------+---------------+-----+---------
1 | John | first wins | john1@doe.com | 18 | 1234.23As anticipated the primary transaction is the one which stayed.
To wrap it up:
- We began two transactions in parallel
- We modified the identical row
- We accepted the adjustments of the transaction that completed first
That’s it. Hope you had some enjoyable and now you will have one other instrument in your wants. Within the subsequent weblog, we will study PostgreSQL’s driver capabilities and the way we will configure an automatic failover to a different occasion.