1.什么是逻辑复制:
逻辑复制是基于表的复制方式,可以有选择性的复制一些表,可以实现跨大版本的数据同步。
主库从库都可读写,适用于从库要求写的业务场景。
2.搭建逻辑复制:
2.1 修改逻辑主库的postgresql.conf
wal_level=logical
max_wal_senders=10
max_replication_slots=8
参数简要介绍:
wal_level:
max_wal_senders:walsender进程的最大数量
max_replication_slots:复制槽的最大数量
2.2 修改逻辑从库的postgresql.conf
wal_level=logical
max_replication_slots=8
max_logical_replication_workers = 8
2.3 创建流复制账号:
postgres=# create user logical_repl replication login connection limit 8 encrypted password 'logical_repl';
CREATE ROLE
2.4 在逻辑主库上创建库和表:
postgres=# create database sourcedb;
CREATE DATABASE
postgres=# \c sourcedb
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# create table logical_tbl(id int primary key,name varchar(100));
CREATE TABLE
2.5 在逻辑主库上创建发布:
sourcedb=# create publication pub1 for table logical_tbl;
CREATE PUBLICATION
sourcedb=# select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
pub1 | 10 | f | t | t | t
2.6 在逻辑从库上 建库和表
逻辑从库的表需要自己手动创建
postgres=# create database desdb;
CREATE DATABASE
postgres=# \c desdb;
You are now connected to database "desdb" as user "postgres".
desdb=# create table logical_tbl(id int primary key,name varchar(100));
CREATE TABLE
2.7 在逻辑从库上创建订阅:
desdb=# create subscription sub1 connection 'host=192.168.246.112 port=5432 user=logical_repl dbname=sourcedb password=logical_repl' publication pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
创建成功后在主库上可以看到:
sourcedb=# select slot_name,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name = 'sub1';
slot_name | slot_type | database | active | restart_lsn
-----------+-----------+----------+--------+-------------
sub1 | logical | sourcedb | t | 0/80AC5BF0
(1 row)
在逻辑从库上可以看到:
desdb=# select * from pg_subscription;
subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublicat
ions
---------+---------+----------+------------+--------------------------------------------------------------------------------------+-------------+---------------+------------
-----
25436 | sub1 | 10 | t | host=192.168.246.112 port=5432 user=logical_repl dbname=sourcedb password=login_repl | sub1 | off | {pub1}
(1 row)
2.8 给复制账号赋权:
在3.3中只创建了复制账号,并未对需要复制的表进行赋权,因此,此时逻辑从库日志中有如下错误:
ERROR,XX000,"could not start initial contents copy for table ""public.logical_tbl"": ERROR: permission denied for relation logical_tbl",,,,,,,,,""
现在对复制账号进行赋权:
sourcedb=# grant usage on schema public to logical_repl;
GRANT
sourcedb=# grant select on logical_tbl to logical_repl;
GRANT
赋权后逻辑从库的日志显示正常:
2020-07-10 16:00:25.959 CST [28204] LOG: logical replication table synchronization
worker for subscription "sub1", table "logical_tbl" has started
2020-07-10 16:00:25.967 CST [28204] LOG: logical replication table synchronization
worker for subscription "sub1", table "logical_tbl" has finished
2.9 测试数据同步
在逻辑主库中插入数据
/** 在sourcedb中插入两条数据 */
sourcedb=# insert into logical_tbl(id,name)values(1,'a'),(2,'b');
INSERT 0 2
在逻辑从库查看结果:
desdb=# select * from logical_tbl;
id | name
----+------
1 | a
2 | b
(2 rows)
由此可见数据同步完成。
2.10 添加复制所需的表
在逻辑主库和逻辑从库均添加一张表,并添加到发布列表中:
sourcedb=# create table logical_tbl2(id int primary key,name varchar(100));
CREATE TABLE
desdb=# create table logical_tbl2(id int primary key,name varchar(100));
CREATE TABLE
sourcedb=# insert into logical_tbl2 values(1,'beijing');
INSERT 0 1
sourcedb=# grant select on logical_tbl2 to logical_repl;
GRANT
sourcedb=# alter publication pub1 add table logical_tbl2;
ALTER PUBLICATION
可是逻辑从库中却并未同步表2的数据:
desdb=# select * from logical_tbl2;
id | name
----+------
(0 rows)
这是因为需要在从库刷新下订阅:
desdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
desdb=# select * from logical_tbl2;
id | name
----+---------
1 | beijing
(1 row)
2.11 从主库查询发布了哪些表
sourcedb=# select * from pg_publication_tables ;
pubname | schemaname | tablename
---------+------------+-------------
pub1 | public | logical_tbl
(1 row)
2.12 主库上表结构发生改变,从库还会继续同步数据吗
sourcedb=# alter table logical_tbl add column flag text;
ALTER TABLE
sourcedb=# insert into logical_tbl values(2,'qaz2',2);
INSERT 0 1
在从库查询,可以看到,并没有同步第2条数据
desdb=# SELECT * FROM logical_tbl ;
id | name | grade
----+------+-------
1 | qaz | 1
(1 row)
从库也添加flag字段后,从库的表才会继续同步数据;
desdb=# alter table logical_tbl add column flag text;
ALTER TABLE
desdb=# SELECT * FROM logical_tbl ;
id | name | grade | flag
----+------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
(2 rows)
2.13从库表中加条数据,主库再加这条数据,从库会同步主库中新添加的这条数据吗
desdb=# insert into logical_tbl values(3,'ss',3);
INSERT 0 1
desdb=# SELECT * FROM logical_tbl ;
id | name | grade | flag
----+------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
3 | ss | 3 |
(3 rows)
sourcedb=# insert into logical_tbl values(3,'wuliahao',3);
INSERT 0 1
sourcedb=# select * from logical_tbl;
id | name | grade | flag
----+----------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
3 | wuliahao | 3 |
(3 rows)
desdb=# SELECT * FROM logical_tbl ;
id | name | grade | flag
----+------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
3 | ss | 3 |
(3 rows)
这是因为表logical_tbl有主键限制,3这条记录在表中已存在,插入不进去了,后面主库再插入的数据也被阻塞,插入不进去了。
sourcedb=# insert into logical_tbl values(4,'nonghl',3,'wdfr');
INSERT 0 1
sourcedb=# select * from logical_tbl;
id | name | grade | flag
----+----------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
3 | wuliahao | 3 |
4 | nonghl | 3 | wdfr
(4 rows)
在从库中只看到3条记录:
desdb=# SELECT * FROM logical_tbl ;
id | name | grade | flag
----+------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
3 | ss | 3 |
(3 rows)
从日志中可以看到:提示主键重复
20:05:37.991 CST,,,5828,,5f143711.16c4,2,,2020-07-19 20:05:37 CST,3/723,159750,ERROR,23505,
"duplicate key value violates unique constraint ""logical_tbl_pkey""","Key (id)=(3) already exists.",,,,,,,,""
20:05:37.993 CST,,,3265,,5f14169d.cc1,400,,2020-07-19 17:47:09
CST,,0,LOG,00000,"worker process: logical replication worker for subscription 25451
(PID 5828) exited with exit code 1",,,,,,,,,""
把从库这条ID=3的数据删掉试试呢
可以看到从库和主库数据一样了
desdb=# delete from logical_tbl where id =3;
DELETE 1
desdb=# SELECT * FROM logical_tbl ;
id | name | grade | flag
----+----------+-------+------
1 | qaz | 1 |
2 | qaz2 | 2 |
3 | wuliahao | 3 |
4 | nonghl | 3 | wdfr
(4 rows)