Hi, community!

Unfortunately can't find answer in docs and google. Hope only for you)

Have running 2 postgres locally: 

:5433 - postgres 11 as master

:5434 - postgres 12 as slave

 

Creating basic setup for testing:

[local]:5433 postgres@postgres=# create table tbl(id serial, d text, primary key(id));

CREATE TABLE

Time: 7,147 ms

 

[local]:5434 postgres@postgres=# create table tbl(id serial, d text, primary key(id));

CREATE TABLE

Time: 11,557 ms

 

[local]:5433 postgres@postgres=# create publication pub for table tbl;

CREATE PUBLICATION

Time: 6,646 ms

 

[local]:5434 postgres@postgres=# create subscription sub connection 'host=localhost port=5433 dbname=postgres user=postgres' publication pub;

NOTICE:  created replication slot "sub" on publisher

CREATE SUBSCRIPTION

Time: 18,584 ms

 

[local]:5433 postgres@postgres=# insert into tbl(d) values ('test');

INSERT 0 1

Time: 3,401 ms

 

[local]:5434 postgres@postgres=# select * from tbl;

 id |  d   

----+------

  1 | test

(1 row)

 

works like a charm. Ok. Lets drop "accidentally" publication and insert new data:

 

[local]:5433 postgres@postgres=# drop publication pub ;

DROP PUBLICATION

Time: 3,793 ms

 

[local]:5433 postgres@postgres=# insert into tbl(d) values ('test2');

INSERT 0 1

Time: 9,002 ms

 

Log for master:

 

2021-02-08 22:13:14.970 +07 [14075] postgres@postgres LOG:  starting logical decoding for slot "sub"

2021-02-08 22:13:14.970 +07 [14075] postgres@postgres DETAIL:  Streaming transactions committing after 8/FD435A80, reading WAL from 8/FD436948.

2021-02-08 22:13:14.970 +07 [14075] postgres@postgres LOG:  logical decoding found consistent point at 8/FD436948

2021-02-08 22:13:14.970 +07 [14075] postgres@postgres DETAIL:  There are no running transactions.

2021-02-08 22:13:14.970 +07 [14075] postgres@postgres ERROR:  publication "pub" does not exist

2021-02-08 22:13:14.970 +07 [14075] postgres@postgres CONTEXT:  slot "sub", output plugin "pgoutput", in the change callback, associated LSN 8/FD4369E8

 

slave:

2021-02-08 22:13:45.071 +07 [14110] LOG:  logical replication apply worker for subscription "sub" has started

2021-02-08 22:13:45.078 +07 [14110] ERROR:  could not receive data from WAL stream: ERROR:  publication "pub" does not exist

    CONTEXT:  slot "sub", output plugin "pgoutput", in the change callback, associated LSN 8/FD4369E8

2021-02-08 22:13:45.079 +07 [18374] LOG:  background worker "logical replication worker" (PID 14110) exited with exit code 1

 

Looks reasonable - publication pub does not exist. Ok, trying to recreate publication:

[local]:5433 postgres@postgres=# create publication pub for table tbl;

CREATE PUBLICATION

Time: 6,646 ms

 

result: nothing changed, same errors appears again and again. I couldn't find how to restore replication without drop&create subscription again. 

 

Questions here:

1. what is going under the hood here - why walsender thinks that "publication "pub" does not exist" when it actually exists?

2. what is the right way to restore replication in my example?

 

Thanks!