Hi everyone,
A very common question among new users is how wal_level works and it
levels. I heard about some situations like that, a user create a new
publication in its master database and he/she simply does not change
wal_level to logical, sometimes, this person lost maintenance window, or a
chance to restart postgres service, usually a production database, and it
will discover that wal_level is not right just in subscription creation.
Attempting to iterate between new (and even experienced) users with logical
replication, I am sending a patch that when an PUBLICATION is created and
the wal_level is different from logical prints a WARNING in console/log:
-> WARNING: `PUBLICATION` created but wal_level `is` not set to logical,
you need to change it before creating any SUBSCRIPTION
Initiatives like this can make a good user experience with PostgreSQL and
its own logical replication.
Thanks
--
*Lucas Viecelli*
<http://www.leosoft.com.br/coopcred>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 4d48be0b92..991065d0a2 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -232,6 +232,12 @@ CreatePublication(CreatePublicationStmt *stmt)
InvokeObjectPostCreateHook(PublicationRelationId, puboid, 0);
+ if (wal_level != WAL_LEVEL_LOGICAL)
+ {
+ elog(WARNING, "PUBLICATION created but wal_level is not set to logical, you need to "
+ "change it before creating any SUBSCRIPTION");
+ }
+
return myself;
}
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 02070fd8af..dbc3ffa63d 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -43,6 +43,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
FROM SQL WITH FUNCTION prsd_lextype(internal),
TO SQL WITH FUNCTION int4recv(internal));
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index afbbdd543d..2b05babf7a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -5,7 +5,14 @@ CREATE ROLE regress_publication_user LOGIN SUPERUSER;
CREATE ROLE regress_publication_user2;
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_publication_user';
+SHOW wal_level;
+ wal_level
+-----------
+ replica
+(1 row)
+
CREATE PUBLICATION testpub_default;
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
COMMENT ON PUBLICATION testpub_default IS 'test publication';
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
obj_description
@@ -14,6 +21,7 @@ SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
(1 row)
CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
ALTER PUBLICATION testpub_default SET (publish = update);
-- error cases
CREATE PUBLICATION testpub_xxx WITH (foo);
@@ -44,6 +52,7 @@ CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
CREATE VIEW testpub_view AS SELECT 1;
CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
CREATE TABLE testpub_tbl2 (id serial primary key, data text);
-- fail - can't add to for all tables publication
@@ -87,7 +96,9 @@ DROP PUBLICATION testpub_foralltables;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
\dRp+ testpub3
Publication testpub3
Owner | All tables | Inserts | Updates | Deletes | Truncates
@@ -112,6 +123,7 @@ CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
ERROR: "testpub_view" is not a table
DETAIL: Only tables can be added to publications.
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
-- fail - already added
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl"
@@ -197,6 +209,7 @@ SET ROLE regress_publication_user;
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
SET ROLE regress_publication_user2;
CREATE PUBLICATION testpub2; -- ok
+WARNING: PUBLICATION created but wal_level is not set to logical, you need to change it before creating any SUBSCRIPTION
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
ERROR: must be owner of table testpub_tbl1
SET ROLE regress_publication_user;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 815410b3c5..13498348ea 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -5,7 +5,7 @@ CREATE ROLE regress_publication_user LOGIN SUPERUSER;
CREATE ROLE regress_publication_user2;
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_publication_user';
-
+SHOW wal_level;
CREATE PUBLICATION testpub_default;
COMMENT ON PUBLICATION testpub_default IS 'test publication';
