I'm trying to setup logical replication between two database instances.
I've created a publication on the main db, and a subscription on the replica. Specifically:
on main db:
create publication chris for all tables;
on replica db:
CREATE SUBSCRIPTION chris
CONNECTION 'host=localhost port=5540 dbname=finder connect_timeout=10'
PUBLICATION chris;
However for some reason the replica does not seem to be recognizing the publication (essentially ERROR: could not receive data from WAL stream: ERROR: publication "chris" does not exist) (logs from the replica):
Oct 20 18:18:31 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:31.013 GMT [289] LOG: background worker "logical replication worker" (PID 3035) exited with exit code 1
Oct 20 18:18:36 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:36.020 GMT [3036] LOG: logical replication apply worker for subscription "chris" has started
Oct 20 18:18:36 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:36.022 GMT [3036] ERROR: could not receive data from WAL stream: ERROR: publication "chris" does not exist
Oct 20 18:18:36 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: CONTEXT: slot "chris", output plugin "pgoutput", in the change callback, associated LSN 0/6BA6EC8
Oct 20 18:18:36 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:36.023 GMT [289] LOG: background worker "logical replication worker" (PID 3036) exited with exit code 1
Oct 20 18:18:41 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:41.030 GMT [3039] LOG: logical replication apply worker for subscription "chris" has started
Oct 20 18:18:41 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:41.033 GMT [3039] ERROR: could not receive data from WAL stream: ERROR: publication "chris" does not exist
Oct 20 18:18:41 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: CONTEXT: slot "chris", output plugin "pgoutput", in the change callback, associated LSN 0/6BA6EC8
Oct 20 18:18:41 test2 8y7qb8wz8ps875yrvpmg9c1zsffls4hy-unit-script-postgresql-start[289]: 2020-10-20 18:18:41.034 GMT [289] LOG: background worker "logical replication worker" (PID 3039) exited with exit code 1
The main db's logs strangely seems to also be showing the same error (ERROR: publication "chris" does not exist):
Oct 20 18:22:56 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:22:56.545 GMT [514] CONTEXT: slot "chris", output plugin "pgoutput", in the change callback, associated LSN >
Oct 20 18:23:01 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:01.555 GMT [515] LOG: starting logical decoding for slot "chris"
Oct 20 18:23:01 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:01.555 GMT [515] DETAIL: Streaming transactions committing after 0/6BA6E60, reading WAL from 0/6BA6E28.
Oct 20 18:23:01 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:01.555 GMT [515] LOG: logical decoding found consistent point at 0/6BA6E28
Oct 20 18:23:01 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:01.555 GMT [515] DETAIL: There are no running transactions.
Oct 20 18:23:01 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:01.556 GMT [515] ERROR: publication "chris" does not exist
Oct 20 18:23:01 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:01.556 GMT [515] CONTEXT: slot "chris", output plugin "pgoutput", in the change callback, associated LSN >
Oct 20 18:23:06 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:06.566 GMT [518] LOG: starting logical decoding for slot "chris"
Oct 20 18:23:06 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:06.566 GMT [518] DETAIL: Streaming transactions committing after 0/6BA6E60, reading WAL from 0/6BA6E28.
Oct 20 18:23:06 pgtestpublic 22rj0nr8pn1k0r3q8q6pxixp6hn2zi4i-unit-script-postgresql-start[260]: 2020-10-20 18:23:06.566 GMT [518] LOG: logical decoding found consistent point at 0/6BA6E28
However I do see the correct expected output from:
select * from pg_catalog.pg_publication;
select * from pg_catalog.pg_publication;
on the main database.
What could I investigate further? And what may likely be the cause of the error?
Best Answer
I completely forgot to specify a username and password. Perhaps it used some default user/password combination by default. Otherwise, maybe a misleading error message.