How to rename in-use logical replication publication? - Mailing list pgsql-general

Hello!

Postgres v11.12. Getting "ERROR:  publication "new_rep_pub" does not
exist" after renaming an existing publication. And the only way to get
it working seems to start from scratch. What am I missing?

On PUB side:
CREATE TABLE rep_test (int_col int PRIMARY KEY, ts_col timestamp);
INSERT INTO rep_test (int_col, ts_col) SELECT generate_series(1,10),
current_timestamp;
CREATE PUBLICATION rep_pub FOR TABLE rep_test;

On SUB side:
CREATE TABLE rep_test (int_col int PRIMARY KEY, ts_col timestamp)
CREATE SUBSCRIPTION rep_sub CONNECTION 'host=localhost port=54311
dbname=postgres' PUBLICATION rep_pub;

ALTER SUBSCRIPTION rep_sub DISABLE ;
ALTER SUBSCRIPTION rep_sub SET PUBLICATION new_rep_pub WITH ( refresh = false );

On PUB side:
ALTER PUBLICATION rep_pub RENAME TO new_rep_pub;

On SUB side:
ALTER SUBSCRIPTION rep_sub ENABLE ;

The PUB complains:
2021-10-19 19:14:53.182 GMT [35] LOG:  statement: SELECT
pg_catalog.set_config('search_path', '', false);
2021-10-19 19:14:53.185 GMT [35] LOG:  starting logical decoding for
slot "rep_sub"
2021-10-19 19:14:53.185 GMT [35] DETAIL:  Streaming transactions
committing after 0/1678A10, reading WAL from 0/1678A10.
2021-10-19 19:14:53.186 GMT [35] LOG:  logical decoding found
consistent point at 0/1678A10
2021-10-19 19:14:53.186 GMT [35] DETAIL:  There are no running transactions.
2021-10-19 19:14:53.186 GMT [35] ERROR:  publication "new_rep_pub"
does not exist
2021-10-19 19:14:53.186 GMT [35] CONTEXT:  slot "rep_sub", output
plugin "pgoutput", in the change callback, associated LSN 0/1678A48

The SUB complains:
2021-10-19 19:16:10.340 GMT [33] LOG:  logical replication apply
worker for subscription "rep_sub" has started
2021-10-19 19:16:10.355 GMT [33] ERROR:  could not receive data from
WAL stream: ERROR:  publication "new_rep_pub" does not exist
    CONTEXT:  slot "rep_sub", output plugin "pgoutput", in the change
callback, associated LSN 0/1678A48
2021-10-19 19:16:10.361 GMT [1] LOG:  background worker "logical
replication worker" (PID 33) exited with exit code 1
2021-10-19 19:16:15.373 GMT [34] LOG:  logical replication apply
worker for subscription "rep_sub" has started
2021-10-19 19:16:15.378 GMT [34] ERROR:  could not receive data from
WAL stream: ERROR:  publication "new_rep_pub" does not exist
    CONTEXT:  slot "rep_sub", output plugin "pgoutput", in the change
callback, associated LSN 0/1678A48
2021-10-19 19:16:15.380 GMT [1] LOG:  background worker "logical
replication worker" (PID 34) exited with exit code 1

Yet it all looks like it should be OK:

postgres@postgres=# \dRp
                             List of publications
    Name     │  Owner   │ All tables │ Inserts │ Updates │ Deletes │ Truncates
─────────────┼──────────┼────────────┼─────────┼─────────┼─────────┼───────────
 new_rep_pub │ postgres │ f          │ t       │ t       │ t       │ t
(1 row)

postgres@postgres=# \dRs+
                                             List of subscriptions
  Name   │  Owner   │ Enabled │  Publication  │ Synchronous commit │
              Conninfo
─────────┼──────────┼─────────┼───────────────┼────────────────────┼───────────────────────────────────────────
 rep_sub │ postgres │ f       │ {new_rep_pub} │ off                │
host=localhost port=54311 dbname=postgres
(1 row)

Best regards,
--
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com