Thread

1. How to rename in-use logical replication publication?

   Kristjan Mustkivi <sonicmonkey@gmail.com> — 2021-10-19T19:31:42Z

   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
   
   
   
   

2. Re: How to rename in-use logical replication publication?

   Kristjan Mustkivi <sonicmonkey@gmail.com> — 2021-10-20T11:33:03Z

   On Tue, Oct 19, 2021 at 10:31 PM Kristjan Mustkivi
   <sonicmonkey@gmail.com> wrote:
   > 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?
   
   Ok, the recipe works perfectly in v13.4 which leads me to believe it
   is a bug in v11.12.
   
   Best regards,
   
   Kristjan Mustkivi