10 PostgreSQL的逻辑复制不工作

Question

我安装PostgreSQL 10使用脚本：

$ wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add - 

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list' 

$ sudo apt-get update 
$ sudo apt-get install postgresql postgresql-contrib 

在主服务器：xx.xxx.xxx.xx-：

而在这之后在postgresql.conf里：

set wal_level = logical 

在从服务器 - ：

postgresql.conf中

：

set wal_level = logical 

而且毕竟我用下面的查询主服务器上：在从服务器

create table t1 (id integer primary key, val text); 
create user replicant with replication; 
grant select on t1 to replicant; 
insert into t1 (id, val) values (10, 'ten'), (20, 'twenty'), (30, 'thirty'); 
create publication pub1 for table t1; 

和：

create table t1 (id integer primary key, val text, val2 text); 
create subscription sub1 connection 'dbname=dbsrc user=replicant' publication pub1; 

但是这是我面临的是表中的问题不同步和根据逻辑复制，当我在主服务器上插入新行时，从服务器没有获得该行。

我是新来的postgresql请帮帮我。

感谢您宝贵的时间。

现在，这里是我的PostgreSQL日志主服务器：

2017-10-17 11:06:16.644 UTC [10713] [email protected] LOG: starting logical decoding for slot "sub_1" 
2017-10-17 11:06:16.644 UTC [10713] [email protected] DETAIL: streaming transactions committing after 1/F45EB0C8, reading WAL from 1/F45EB0C8 
2017-10-17 11:06:16.645 UTC [10713] [email protected] LOG: logical decoding found consistent point at 1/F45EB0C8 
2017-10-17 11:06:16.645 UTC [10713] [email protected] DETAIL: There are no running transactions. 

这是我从服务器PostgreSQL的日志：

2017-10-17 19:14:45.622 CST [7820] WARNING: out of logical replication worker slots 
2017-10-17 19:14:45.622 CST [7820] HINT: You might need to increase max_logical_replication_workers. 
2017-10-17 19:14:45.670 CST [7821] WARNING: out of logical replication worker slots 
2017-10-17 19:14:45.670 CST [7821] HINT: You might need to increase max_logical_replication_workers. 
2017-10-17 19:14:45.680 CST [7822] WARNING: out of logical replication worker slots 
2017-10-17 19:14:45.680 CST [7822] HINT: You might need to increase max_logical_replication_workers. 
2017-10-17 19:14:50.865 CST [7820] WARNING: out of logical replication worker slots 
2017-10-17 19:14:50.865 CST [7820] HINT: You might need to increase max_logical_replication_workers. 
2017-10-17 19:14:50.917 CST [7821] WARNING: out of logical replication worker slots 
2017-10-17 19:14:50.917 CST [7821] HINT: You might need to increase max_logical_replication_workers. 
2017-10-17 19:14:50.928 CST [7822] WARNING: out of logical replication worker slots 
2017-10-17 19:14:50.928 CST [7822] HINT: You might need to increase max_logical_replication_workers. 
2017-10-17 19:14:55.871 CST [7820] WARNING: out of logical replication worker slots 
2017-10-17 19:14:55.871 CST [7820] HINT: You might need to increase max_logical_replication_workers. 

和增加max_logical_replication_workers后，我得到这个：

2017-10-17 19:44:45.898 CST [7987] LOG: logical replication table synchronization worker for subscription "sub2", table "t1" has started 
2017-10-17 19:44:45.982 CST [7988] LOG: logical replication table synchronization worker for subscription "myadav_test", table "test_replication" h$ 
2017-10-17 19:44:45.994 CST [7989] LOG: logical replication table synchronization worker for subscription "sub3", table "t1" has started 
2017-10-17 19:44:48.621 CST [7987] ERROR: could not start initial contents copy for table "staging.t1": ERROR: permission denied for schema staging 
2017-10-17 19:44:48.623 CST [7962] LOG: worker process: logical replication worker for subscription 20037 sync 20027 (PID 7987) exited with exit co$ 
2017-10-17 19:44:48.705 CST [7988] ERROR: could not start initial contents copy for table "staging.test_replication": ERROR: permission denied for$ 
2017-10-17 19:44:48.707 CST [7962] LOG: worker process: logical replication worker for subscription 20025 sync 20016 (PID 7988) exited with exit co$ 
2017-10-17 19:44:48.717 CST [7989] ERROR: duplicate key value violates unique constraint "t1_pkey" 
2017-10-17 19:44:48.717 CST [7989] DETAIL: Key (id)=(10) already exists. 
2017-10-17 19:44:48.717 CST [7989] CONTEXT: COPY t1, line 1 
2017-10-17 19:44:48.718 CST [7962] LOG: worker process: logical replication worker for subscription 20038 sync 20027 (PID 7989) exited with exit co$ 
2017-10-17 19:44:51.629 CST [8008] LOG: logical replication table synchronization worker for subscription "sub2", table "t1" has started 
2017-10-17 19:44:51.712 CST [8009] LOG: logical replication table synchronization worker for subscription "myadav_test", table "test_replication" h$ 
2017-10-17 19:44:51.722 CST [8010] LOG: logical replication table synchronization worker for subscription "sub3", table "t1" has started 

现在我终于意识到逻辑复制对于postgres数据库，但不适用于同一服务器上的其他数据库。我正在获取日志模式的权限问题。

Answer 1

使用拥有订阅的用户的权限应用行更改。默认情况下，这是创建订阅的用户。

因此，请确保订阅拥有足够权限的用户。格兰特需要表格的权利，或者如果您不能打扰，请使拥有所有权利的超级用户拥有的订阅。

参见：

• CREATE SUBSCRIPTION
• logical replication - security
• logical replication