pg15逻辑复制搭建.txt
5墨值下载
逻辑复制可跨版本迁移
1.主库操作
配置逻辑复制参数
[pgsql@orcl pgdata]$ cat postgresql.conf |grep wal_level
wal_level = logical # minimal, replica, or logical
参数修改后需要重启实例生效
[pgsql@orcl:/postgresql/pgdata]$pg_ctl restart
主库对需要配置的数据库执行逻辑发布， p 为发布设置的名称可以自定义
postgres=# CREATE PUBLICATION p FOR ALL TABLES WITH (publish = 'insert, update,
delete, truncate', publish_via_partition_root = false);
CREATE PUBLICATION
创建测试表：
CREATE TABLE cs02 (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INTEGER
);
INSERT INTO cs02 (name, age) VALUES ('John Doe', 30);
INSERT INTO cs02 (name, age) VALUES ('Jane Smith', 25);
主库目前信息
SELECT nspname || '.' || relname AS object_name,
CASE
WHEN relkind = 'r' THEN 'table'
WHEN relkind = 'v' THEN 'view'
WHEN relkind = 'm' THEN 'materialized view'
WHEN relkind = 'i' THEN 'index'
WHEN relkind = 'S' THEN 'sequence'
WHEN relkind = 's' THEN 'special'
ELSE 'unknown'
END AS object_type
FROM pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema';
object_name | object_type
--------------------+-------------
public.cs01_id_seq | sequence
public.cs01 | table
public.cs01_pkey | index
public.cs01_id | index
public.cs02_id_seq | sequence
public.cs02 | table
public.cs02_pkey | index
(7 rows)
2.从库操作
在从库服务器对主库进行 pg_dump 备份表结构：
[pgsql@orcl:/postgresql/backup]$pg_dump -h192.168.31.211 -Upostgres -d postgres
-s -f /postgresql/backup/pg41.sql
在从库主恢复主库表结构
##CREATE DATABASE new_database;
[pgsql@orcl:/postgresql/backup]$psql -h127.0.0.1 -Upostgres -d postgres -f
pg41.sql
配置逻辑复制参数
[pgsql@orcl pgdata]$ cat postgresql.conf |grep wal_level
wal_level = logical # minimal, replica, or logical
参数修改后需要重启实例生效
[pgsql@orcl:/postgresql/pgdata]$pg_ctl restart
从库目前信息
SELECT nspname || '.' || relname AS object_name,
CASE
WHEN relkind = 'r' THEN 'table'
WHEN relkind = 'v' THEN 'view'
WHEN relkind = 'm' THEN 'materialized view'
WHEN relkind = 'i' THEN 'index'
WHEN relkind = 'S' THEN 'sequence'
WHEN relkind = 's' THEN 'special'
ELSE 'unknown'
END AS object_type
FROM pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema';
object_name | object_type
--------------------+-------------
public.cs01 | table
public.cs01_id_seq | sequence
public.cs02 | table
public.cs02_id_seq | sequence
public.cs01_pkey | index
public.cs02_pkey | index
public.cs01_id | index
(7 rows)
备库订阅：
在从库实例上选择用来订阅主实例的数据库，创建逻辑订阅
--修改 postgres 用户密码： ALTER USER postgres WITH PASSWORD 'postgres';
CREATE SUBSCRIPTION s
CONNECTION 'host=192.168.31.211 port=5432 user=postgres dbname=postgres
connect_timeout=10 password=postgres'
PUBLICATION p
WITH
(connect=true,enabled=true,copy_data=true,create_slot=true,synchronous_commit='r
emote_apply');
## s 为订阅设置的名称'host=192.168.31.21 port=5432 user=postgres dbname=postgres
connect_timeout=10 password=postgres' 为主库的连接信息
ALTER SUBSCRIPTION s REFRESH PUBLICATION WITH (copy_data = true);
查询表同步情况，内容无误
##如果需要配置同步复制的话需要修改参数 postgresql.conf：
synchronous_commit=remote_apply ##表示流复制主库提交事务时 ，需等待备库完成相应部分
apply 才向客户端返回成功
synchronous_standby_names='s' ##这里的‘s’是上面配置的订阅名字
插入数据测试无误：
postgres=# insert into cs01 values(3,'cs',40);
3.设置同步 DDL 操作
PostgreSQL 的 逻辑订阅并不会同步 DDL 操作 ，所以对于数据库的建表等操作是不会进行主从同步的，我
们需要手动维护 主从实例数据库上的表结构使其保持一致。
当在主和从数据库都创建完成表之后需要在从库上执行以下刷新订阅的 SQL，每次主库新增或者删除了表，从
库都需要执行这个操作。
主库创建了 cs 表，备库也需要手动创建 cs 表然后执行下面命令：
[pgsql@orcl:/postgresql/pgdata]$pg_dump -h192.168.31.41 -Upostgres -d postgres
-s -t cs -f /postgresql/backup/pg41.sql
[pgsql@orcl:/postgresql/pgdata]$psql -h127.0.0.1 -d postgres -f
/postgresql/backup/pg41.sql
ALTER SUBSCRIPTION s REFRESH PUBLICATION WITH (copy_data = true);
命令的作用是刷新订阅的逻辑复制发布，并将所有数据从发布端复制到订阅端。
具体地说，该命令会执行以下操作：
1.刷新逻辑复制订阅。这将强制刷新订阅端已订阅的广告的元数据，包括表结构和列信息等。
2.同步所有数据。在刷新广告之后，该命令将使用 copy_data = true 参数将所有数据从发布端复制到订
阅端。这意味着，除了同步新更改之外，它还将同步所有以前未同步的更改。如果不指定 copy_data =
true 参数，则只会同步尚未同步的更改。
需要注意的是，该命令可能会带来一些性能影响，并且可能需要较长时间才能完成，具体取决于数据量和网络
延迟等因素。因此，在生产环境中，最好只在必要时使用该命令，并在进行操作之前对其进行充分测试。
4.删除发布设置和订阅设置的操作
SELECT * FROM pg_publication; ##主库查询的所有发布信息
DROP PUBLICATION p; ##删除名字为 p 的发布信息
SELECT * FROM pg_subscription; ##从库查询当前所有订阅信息
DROP SUBSCRIPTION s; ##删除名字为 s 的订阅信息
然后记得去主库的 postgresql.conf 找到 synchronous_standby_names 删除 s 节点的配置
#synchronous_standby_names='s'
如果只有一个从节点的，则直接添加 # 对 synchronous_standby_names 进行注释即可
当有多个从库订阅的时候 synchronous_standby_names 还可以采用以下配置模式
synchronous_standby_names='s1' 代表 s1 备机返回就可以提交。
synchronous_standby_names='FIRST 2 (s1,s2,s3)' 代表 s1，s2，s3 三个备机中前两个 s1 和
s2 返回主库就可以提交。
synchronous_standby_names='ANY 2 (s1,s2,s3)' 代表 s1，s2，s3 三个备机中任意两个备机返回
主库就可以提交。
synchronous_standby_names='ANY 2 (*)' 代表所有备机中任意两个备机返回主库就可以提交。
synchronous_standby_names='*' 代表匹配任意主机，也就是任意主机返回就可以提交。
这里有一点需要注意，这是 PostgreSQL 在同步复制时的一个已知问题，假设 一个主库，一个备库 s1，采
用同步模式，然后 synchronous_standby_names 配置为 synchronous_standby_names='s1',虽然
从配置上来看似乎数据必须要提交到 s1 并且 s1 成功响应之后，主库才会为客户端返回事务操作成功的响应，
但是实际情况下，当备库挂掉的情况下，主库在收到一个事务操作时，在等待 s1 备库的返回时因为 s1 库已
经挂掉了所以这个操作肯定会超时，当主备节点通信超时之后，主节点还是会像客户端返回事务成功提交的命
令，客户端的操作还是会成功，同时因为每个事务操作都要经历这个超时的流程，所以客户端的所有事务操作
都会相对很卡。
比如每个 insert 都会经过主库和备库的这个通信超时过程，所以每个 insert 动作都变成了大约 30 秒次
才能完成，就会导致应用程序很卡。这时候就相当于主库在以（很卡的）独立模式运行，这个情况在备库重新
上线之后就会恢复正常（如果备库短期之内无法恢复，可以调整主库的 synchronous_standby_names 设
置 移除对于 s1 备库的事务等待验证，变为单库运行模式重启实例之后也就不会卡了），但是要注意当主库脱
离备库独立运行时，如果这个时候主库发生灾难比如硬盘坏掉，则就会产生数据丢失。所以建议至少有 2 个备
库来提升保障级别
of 3
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
1
2
白鳝-DBAIOPS：国产化替换浪潮进行时，信创数据库该如何选型？.pdf
3
centos7下oracle11.2.0.4 rac安装详细图文（虚拟机模拟多路径）.docx
4
PostgreSQL 缓存命中率低？可以这么做.doc
5
李飞-AI 引领的企业级智能分析架构演进与行业实践.pdf
6
达梦数据2024年年度报告.pdf
7
吕昕-如何建设人人都能训大模型的技术氛围-V脱敏.pdf
8
菜鸟低代码创新实践：全栈转型与技术效能突破.pdf
9
刘杰-江苏广电：从Oracle+Hadoop到TiDB，数据中台、实时数仓运维0负担.pdf
10
曾国洋-端侧模型的知识密度：迈向 AGI 的关键技术实践.pdf
相关文档
评论