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!
