詳談pg邏輯復制系列之邏輯復制的搭建（二）

麥可網(wǎng)絡 2021-03-28

展開全文

**發(fā)布端**
1. 查看發(fā)布端的相關參數(shù)并修改為合適值

```bash
alter system set wal_level =logical;
alter system set max_replication_slots=8;
alter system set max_wal_senders=10; --應該將此參數(shù)設置的比 max_replication_slots 參數(shù)要大
--修改參數(shù)后重啟數(shù)據(jù)庫,使參數(shù)生效
pg_ctl restart
```

2. 發(fā)布節(jié)點創(chuàng)建發(fā)布

```bash
postgres=# create user a password'root123';
CREATE ROLE
postgres=# create database a owner a;
CREATE DATABASE
postgres=# \c a a
-- 創(chuàng)建發(fā)布測試表
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)
--創(chuàng)建發(fā)布
[root@rhel1 data]# psql a a
a=> create publication pub1 for table badtable ;
--查詢發(fā)布
a=> select * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
16399 | pub1 | 16385 | f | t | t | t | t
(1 row)
```

3. 發(fā)布節(jié)點創(chuàng)建邏輯復制用戶

```bash
a=> \c a postgres
a=# create user logicalrep password'root123' replication;
```
4.配置replication網(wǎng)絡訪問控制

```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. 查看相關參數(shù)并修改為合適值

```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. 訂閱節(jié)點創(chuàng)建訂閱

```bash
--先將訂閱端表創(chuàng)建
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);

--創(chuàng)建訂閱
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 -----備端連到主端創(chuàng)建邏輯復制槽作用也是保證沒有被訂閱端解析的wal日志被主端刪除
CREATE SUBSCRIPTION
--訂閱節(jié)點查詢訂閱信息
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
--沒有數(shù)據(jù)同步,主要是復制用戶logicalrep對表所屬的schema及表沒有讀寫權限
--在發(fā)布端進行表授權
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 ;
--訂閱端再次查詢數(shù)據(jù)
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
--查詢狀態(tài)
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)
```