Postgresql 10 逻辑复制不工作

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] replicant@postgres LOG:  starting logical decoding for slot "sub_1"
2017-10-17 11:06:16.644 UTC [10713] replicant@postgres DETAIL:  streaming transactions committing after 1/F45EB0C8, reading WAL from 1/F45EB0C8
2017-10-17 11:06:16.645 UTC [10713] replicant@postgres LOG:  logical decoding found consistent point at 1/F45EB0C8
2017-10-17 11:06:16.645 UTC [10713] replicant@postgres 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

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

因此请确保订阅由具有足够权限的用户所有。授予对表所需的权限，或者如果您不想被打扰，请将订阅归 super 用户所有，该 super 用户对所有内容都拥有全部权限。

参见:

• CREATE SUBSCRIPTION
• logical replication - security
• logical replication