详谈pg逻辑复制系列之逻辑复制的搭建(二)

详谈pg逻辑复制系列之逻辑复制的搭建(二)

**发布端**
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)
```


上一篇:sql server 2008 的数据类型大全


下一篇:sql server 2008常考的题目