在本篇博文中，我们将重点介绍复制和更具体的双向复制。要在 PostgreSQL 中实现双向复制，我们需要模块 pglogical。您可能想知道逻辑解码和 pglogical 之间的区别。
本质上，逻辑解码起源于PgLocigal。将 PgLocial 视为功能更强大的模块，而逻辑解码嵌入到 PostgreSQL 发行版中。
我们将创建自定义 PostgreSQL Docker 映像并安装 PgLogical。
# Use the official PostgreSQL image as base
FROM postgres:15
USER root
RUN apt-get update; apt-get install postgresql-15-pglogical -y
USER postgres
|
我们还需要一个 PostgreSQL 配置，以启用 PgLogical 复制和冲突解决。
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'
|
让我们来分析一下。我们添加了 pglogical 并启用了 track_commit_timestamp。通过启用此参数，PostgreSQL 会跟踪事务的提交时间。这对于冲突解决策略至关重要。
现在让我们看看冲突解决。我们选择了“first_update_wins”，因此如果两个事务在同一行上操作，则将考虑最先完成的事务。
在表上设置双向复制。由于我们使用Docker，因此我们将为 PostgreSQL 提供初始化脚本。
该脚本将：
- 启用 pglogical
- 创建表
- 添加目标节点
- 插入要进行测试的行
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
ALTER SYSTEM RESET shared_preload_libraries;
CREATE EXTENSION pglogical;
create schema test_schema;
create table test_schema.employee(
id SERIAL PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
email TEXT not null,
age INT NOT NULL,
salary real,
unique(email)
);
SELECT pglogical.create_node(
node_name := '$TARGET',
dsn := 'host=$TARGET port=5432 dbname=$POSTGRES_DB user=$POSTGRES_USER password=$POSTGRES_PASSWORD');
SELECT pglogical.replication_set_add_table('default', 'test_schema.employee', true);
insert into test_schema.employee (id,firstname,lastname,email,age,salary) values (1,'John','Doe 1','john1@doe.com',18,1234.23);
EOSQL
|
现在让我们使用 docker compose 创建实例。version: '3.1'
services:
postgres-a:
build: ./pglogicalimage
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
TARGET: postgres-b
volumes:
- ./config/postgresql.conf:/etc/postgresql/postgresql.conf
- ./init:/docker-entrypoint-initdb.d
command:
- "-c"
- "config_file=/etc/postgresql/postgresql.conf"
ports:
- 5431:5432
postgres-b:
build: ./pglogicalimage
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
TARGET: postgres-a
volumes:
- ./config/postgresql.conf:/etc/postgresql/postgresql.conf
- ./init:/docker-entrypoint-initdb.d
command:
- "-c"
- "config_file=/etc/postgresql/postgresql.conf"
ports:
- 5432:5432
|
我们可以通过执行以下命令来启动并运行实例docker compose
由于两个实例都已启动并正在运行，因此我们需要启用复制。因此，我们将节点相互订阅。
在第一个节点上执行
SELECT pglogical.create_subscription(
subscription_name := 'postgres_b',
provider_dsn := 'host=postgres-b port=5432 dbname=postgres user=postgres password=postgres',
synchronize_data := false,
forward_origins := '{}' );
|
在第二个节点执行SELECT pglogical.create_subscription(
subscription_name := 'postgres_a',
provider_dsn := 'host=postgres-a port=5432 dbname=postgres user=postgres password=postgres',
synchronize_data := false,
forward_origins := '{}' );
|
您可以使用任何适合您的 PostgreSQL 客户端。或者，您也可以使用 Docker 镜像中附带的 psql 客户端。
例如：
登录第一个节点
docker compose exec postgres-a psql --username postgres --dbname postgres
登录第二个节点
docker compose exec postgres-b psql --username postgres --dbname postgres
现在让我们看看冲突解决如何进行。
在第一个节点上，我们将运行以下代码片段
BEGIN;
UPDATE test_schema.employee SET lastname='first wins';
before committing start transaction on postgres-b
COMMIT;
|
不要立即按提交，而是花点时间在提交事务之前在第二个节点上启动以下事务。
BEGIN;
UPDATE test_schema.employee SET lastname='second looses';
make sure transaction on node postgres-a is committed first.
COMMIT;
|
该事务将在 postgres-a 中发生的事务之后提交。让我们检查一下 postgres-a-1 上的日志
postgres-a-1 | 2024-05-01 07:10:45.128 GMT [70] LOG: CONFLICT: remote UPDATE on relation test_schema.employee (local index employee_pkey). Resolution: keep_local.
postgres-a-1 | 2024-05-01 07:10:45.128 GMT [70] DETAIL: existing local tuple {id[int4]:1 firstname[text]:John lastname[text]:first wins email[text]:john1@doe.com age[int4]:18 salary[float4]:1234.23} xid=748,origin=0,timestamp=2024-05-01 07:10:42.269227+00; remote tuple {id[int4]:1 firstname[text]:John lastname[text]:second looses email[text]:john1@doe.com age[int4]:18 salary[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 remote relation test_schema.employee in commit before 0/16181C0, xid 747 committed at 2024-05-01 07:10:45.125791+00 (action #2) from node replorigin 1
|
- postgres-a 上发生的事务首先完成。
- Postgres-a 从节点 postgres-b 的事务接收了复制数据。
- 对提交时间戳进行了比较，因为 postgres-a 上事务的提交时间戳较早，
因此解决方案是保留本地更改。我们可以在 postgres-b 上看到反向
postgres-b-1 | 2024-05-01 07:10:45.127 GMT [81] LOG: CONFLICT: remote UPDATE on relation test_schema.employee (local index employee_pkey). Resolution: apply_remote.
postgres-b-1 | 2024-05-01 07:10:45.127 GMT [81] DETAIL: existing local tuple {id[int4]:1 firstname[text]:John lastname[text]:second looses email[text]:john1@doe.com age[int4]:18 salary[float4]:1234.23} xid=747,origin=0,timestamp=2024-05-01 07:10:45.125791+00; remote tuple {id[int4]:1 firstname[text]:John lastname[text]:first wins email[text]:john1@doe.com age[int4]:18 salary[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 remote relation test_schema.employee in commit before 0/1618488, xid 748 committed at 2024-05-01 07:10:42.269227+00 (action #2) from node replorigin 1
|
让我们在数据库中检查一下结果。
postgres=# SELECT*FROM test_schema.employee;
id | firstname | lastname | email | age | salary
----+-----------+------------+---------------+-----+---------
1 | John | first wins | john1@doe.com | 18 | 1234.23
|
正如预期的那样，第一笔事务被保留了下来。
总结一下：
- 我们同时开始了两项事务
- 我们改变了同一行
- 我们接受了最先完成的事务的变更
就是这样。希望您玩得开心，现在您有了另一个满足您需求的工具。