From: | Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | How to rename in-use logical replication publication? |
Date: | 2021-10-19 19:31:42 |
Message-ID: | CAOQPKauHK-9tJS9mZd7xNxNaADF+wvVUTrKiD0GbjH_2cc71jw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)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(at)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(dot)mustkivi(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Vijaykumar Jain | 2021-10-19 19:36:10 | Re: Connection queuing by connection pooling libraries |
Previous Message | David G. Johnston | 2021-10-19 19:13:44 | Re: connecting multiple INSERT CTEs to same record? |