I'm trying to create a logical replication with two local postgresql servers (node1: port 5434, node2: port 5435).
I could successfully create publication and subscription on node1 and node2 for a table in public schema.
Node1:
CREATE PUBLICATION my_pub FOR TABLE t1;
GRANT SELECT ON t1 TO repuser;
Node2:
CREATE SUBSCRIPTION my_sub CONNECTION 'host=localhost port=5434 dbname=pub user=repuser password=password' PUBLICATION my_pub;
Node2 public.t1 replicates all data in node1 public.t1.
However, my problem is when I create publication and subscription with same code but in different schema, node2 fail to replicate.
Below is output of some pg_catalog query :
Node1:
pub=# select * from pg_catalog.pg_publication_tables;
pubname | schemaname | tablename
----------+------------+-----------
my_pub | public | t1
cdl_test | cdl | t1
pub_test | test | t1
Node2:
sub=# \dRs
List of subscriptions
Name | Owner | Enabled | Publication
--------------+----------+---------+-------------
cdl_sub_test | postgres | t | {cdl_test}
my_sub | postgres | t | {my_pub}
sub_test | postgres | t | {pub_test}
sub=# select * from pg_catalog.pg_replication_origin;
roident | roname
---------+----------
2 | pg_18460
1 | pg_18461
3 | pg_18466
sub=# select * from pg_catalog.pg_subscription_rel ;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+------------
18461 | 16386 | r | 0/3811C810
18466 | 18463 | d |
18460 | 18456 | d |
As it is shown in select * from pg_catalog.pg_subscription_rel, two subscription for test and cdl schema are in d(data is being copied) state.
Any recommendation on how to go about this problem or diagnose why the problem occurs?
As jjanes has suggested, a snippet of the log file is shown below:
2022-01-17 16:05:25.165 PST [622] WARNING: out of logical replication worker slots
2022-01-17 16:05:25.165 PST [622] HINT: You might need to increase max_logical_replication_workers.
2022-01-17 16:05:25.168 PST [970] LOG: logical replication table synchronization worker for subscription "cdl_sub_test", table "t1" has started
2022-01-17 16:05:25.245 PST [970] ERROR: could not start initial contents copy for table "cdl.t1": ERROR: permission denied for schema cdl
2022-01-17 16:05:25.247 PST [471] LOG: background worker "logical replication worker" (PID 970) exited with exit code 12022-01-17 16:05:25.797 PST [488] postgres@sub LOG: statement: /*pga4dash*/
It seems like the subscriber doesn't have permission to read cdl schema in publisher even after I gave permission for SELECT ON cdl.t1 TO repuser;.
You have to give the user repuser permission to read the table that should be replicated. That also requires USAGE permission on the schema that contains the table.