**发布端** 1. 查看发布端的相关参数并修改为合适值 ```bash alter system set wal_level =logical; alter system set max_replication_slots=8; alter system set max_wal_senders=10; --应该将此参数设置的比 max_replication_slots 参数要大 --修改参数后重启数据库,使参数生效 pg_ctl restart ``` 2. 发布节点创建发布 ```bash postgres=# create user a password'root123'; CREATE ROLE postgres=# create database a owner a; CREATE DATABASE postgres=# \c a a -- 创建发布测试表 a=> create table badtable (id serial primary key,name varchar(32),age int,hobby a(> text,date timestamp); CREATE TABLE a=> insert into badtable (name,age,hobby,date) values ('postgres', a(> (random()*100)::int,md5('random()::highgo'), a(> generate_series('2008-03-01 00:00'::timestamp,'2008-03-04 23:00', '10 hours')); INSERT 0 10 a=> select count(*) from badtable; count ------- 10 (1 row) --创建发布 [root@rhel1 data]# psql a a a=> create publication pub1 for table badtable ; --查询发布 a=> select * from pg_publication; oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate -------+---------+----------+--------------+-----------+-----------+-----------+------------- 16399 | pub1 | 16385 | f | t | t | t | t (1 row) ``` 3. 发布节点创建逻辑复制用户 ```bash a=> \c a postgres a=# create user logicalrep password'root123' replication; ``` 4.配置replication网络访问控制 ```bash $ vi $PGDATA/pg_hba.conf #IPv4 host all all 0.0.0.0/0 md5 #replication host replication all 0.0.0.0/0 md5 $ pg_ctl reload ``` **订阅端配置** 1. 查看相关参数并修改为合适值 ```bash alter system set max_replication_slots=8; alter system set max_logical_replication_workers=8; $ vi $PGDATA/pg_hba.conf # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 0.0.0.0/0 md5 # replication privilege. local replication all md5 host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5 host replication all 0.0.0.0/0 md5 ``` 2. 订阅节点创建订阅 ```bash --先将订阅端表创建 postgres=# create user b password'root23'; postgres=# create database b owner b; postgres=# \c b b b=> create table badtable (id serial primary key,name varchar(32),age int,hobby b(> text,date timestamp); --创建订阅 b=> \c b postgres b=# create subscription sub1 connection 'host=192.168.230.101 port=5866 dbname=a user=logicalrep password=root123' publication pub1; NOTICE: created replication slot "sub1" on publisher -----备端连到主端创建逻辑复制槽 作用也是保证没有被订阅端解析的wal日志被主端删除 CREATE SUBSCRIPTION --订阅节点查询订阅信息 select * from pg_subscription; ``` 3. 查询订阅端表 ```bash 2021-03-15 15:04:37.786 CST [20014] LOG: logical replication table synchronization worker for subscription "sub1", table "badtable" has started NOTICE: 2021-03-15 15:04:37.928 CST [20014] ERROR: could not start initial contents copy for table "public.badtable": ERROR: permission denied for table badtable 2021-03-15 15:04:37.937 CST [20001] LOG: background worker "logical replication worker" (PID 20014) exited with exit code 1 2021-03-15 15:04:42.961 CST [20015] LOG: logical replication table synchronization worker for subscription "sub1", table "badtable" has started --没有数据同步,主要是复制用户logicalrep对表所属的schema及表没有读写权限 --在发布端进行表授权 a=> grant usage on schema public to logicalrep; a=> grant select on badtable to logicalrep ; a=> alter default privileges in schema public grant select on tables to logicalrep ; --订阅端再次查询数据 b=> select * from badtable; id | name | age | hobby | date ----+--------+-----+----------------------------------+--------------------- 1 | postgres | 81 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-01 00:00:00 2 | postgres | 58 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-01 10:00:00 3 | postgres | 14 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-01 20:00:00 4 | postgres | 66 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-02 06:00:00 5 | postgres | 37 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-02 16:00:00 6 | postgres | 52 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-03 02:00:00 7 | postgres | 21 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-03 12:00:00 8 | postgres | 78 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-03 22:00:00 9 | postgres | 20 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-04 08:00:00 10 | postgres | 31 | 311e7462a6242cbc0578f9d6d91d5d33 | 2008-03-04 18:00:00 --查询状态 b=# select * from pg_subscription; oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications -------+---------+---------+----------+------------+----------------------------------------------------------- -----------------+-------------+---------------+----------------- 16400 | 16385 | sub1 | 9999 | t | host=192.168.230.101 port=5866 dbname=a user=logicalrep pa ssword=root123 | sub1 | off | {pub1} (1 row) ``` 来源:http://www./article/10024
|