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);
of 3
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论