0

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?

3
  • 2
    It is probably some simple user error, like not connecting to the correct database name.
    – jjanes
    Oct 21 '20 at 0:55
  • Why would the main db be showing a similar error though in that case? As it's not connecting to anything externally. Oct 21 '20 at 9:12
  • The main database instance is showing the error. I don't know what database that error shows up in, as you don't seem to be logging the database name in your log_line_prefix (unless you are, I don't know what test2 and pgtestpublic represent, but I don't think they are the dbname.)
    – jjanes
    Oct 21 '20 at 15:04
0

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.

CREATE SUBSCRIPTION chris
    CONNECTION 'host=localhost username=example password=example port=5540 dbname=finder connect_timeout=10'
    PUBLICATION chris;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.