create subscription - improved warning message
WARNING: tables were not subscribed, you will have to run ALTER
SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
~
When I first encountered the above CREATE SUBSCRIPTION warning message
I thought it was dubious-looking English...
On closer inspection I think the message has some other things that
could be improved:
a) it is quite long which IIUC is generally frowned upon
b) IMO most of the text it is more like a "hint" about what to do
~
PSA a patch which modifies this warning as follows:
BEFORE
test_sub=# create subscription sub1 connection 'host=localhost
port=test_pub' publication pub1 with (connect = false);
WARNING: tables were not subscribed, you will have to run ALTER
SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
CREATE SUBSCRIPTION
AFTER
test_sub=# create subscription sub1 connection 'host=localhost
port=test_pub' publication pub1 with (connect = false);
WARNING: tables were not subscribed
HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION
to subscribe the tables.
CREATE SUBSCRIPTION
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v1-0001-create-subscription-warning-message.patchapplication/octet-stream; name=v1-0001-create-subscription-warning-message.patchDownload
From 5f3f9f06952fbc35af93d880a297b733bcb64055 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Fri, 7 Oct 2022 13:01:37 +1100
Subject: [PATCH v1] create subscription warning message.
A small improvement to an existing CREATE SUBSCRIPTION warning message.
Before:
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
After:
WARNING: tables were not subscribed
HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
---
src/backend/commands/subscriptioncmds.c | 3 ++-
src/test/regress/expected/object_address.out | 3 ++-
src/test/regress/expected/subscription.out | 27 ++++++++++++++++++---------
3 files changed, 22 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index f3bfcca..f58ef11 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -760,8 +760,9 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
}
else
ereport(WARNING,
+ (errmsg("tables were not subscribed"),
/* translator: %s is an SQL ALTER statement */
- (errmsg("tables were not subscribed, you will have to run %s to subscribe the tables",
+ errhint("You will have to run %s to subscribe the tables.",
"ALTER SUBSCRIPTION ... REFRESH PUBLICATION")));
table_close(rel, RowExclusiveLock);
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 3549b63..802c633 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -48,7 +48,8 @@ CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE PUBLICATION addr_pub_schema FOR TABLES IN SCHEMA addr_nsp;
RESET client_min_messages;
CREATE SUBSCRIPTION regress_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
+WARNING: tables were not subscribed
+HINT: 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;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index ef0ebf9..97d5500 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -29,7 +29,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: publication name "foo" used more than once
-- ok
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
@@ -64,7 +65,8 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: subscription with slot_name = NONE must also set enabled = false
-- ok - with slot_name = NONE
CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
-- fail
ALTER SUBSCRIPTION regress_testsub3 ENABLE;
ERROR: cannot enable subscription that does not have a slot name
@@ -75,7 +77,8 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: unrecognized origin value: "foo"
-- now it works
CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = none);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
\dRs+ regress_testsub4
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -199,7 +202,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: binary requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, binary = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -222,7 +226,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: streaming requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -278,7 +283,8 @@ ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (ref
DROP SUBSCRIPTION regress_testsub;
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION mypub
WITH (connect = false, create_slot = false, copy_data = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
ALTER SUBSCRIPTION regress_testsub ENABLE;
-- fail - ALTER SUBSCRIPTION with refresh is not allowed in a transaction
-- block or function
@@ -304,7 +310,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: two_phase requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, two_phase = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -328,7 +335,8 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;
-- two_phase and streaming are compatible.
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true, two_phase = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -343,7 +351,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: disable_on_error requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, disable_on_error = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: tables were not subscribed
+HINT: You will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
--
1.8.3.1
Peter Smith <smithpb2250@gmail.com> writes:
WARNING: tables were not subscribed, you will have to run ALTER
SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
When I first encountered the above CREATE SUBSCRIPTION warning message
I thought it was dubious-looking English...
On closer inspection I think the message has some other things that
could be improved:
a) it is quite long which IIUC is generally frowned upon
b) IMO most of the text it is more like a "hint" about what to do
You're quite right about both of those points, but I think there's
even more to criticize: "tables were not subscribed" is a basically
useless message, and probably not even conceptually accurate.
Looking at the code, I think the situation being complained of is that
we have created the subscription's main catalog entries locally, but
since we were told not to connect to the publisher, we don't know what
tables the subscription is supposed to be reading. I'm not sure what
the consequences of that are: do we not read any data at all yet, or
what?
I think maybe a better message would be along the lines of
WARNING: subscription was created, but is not up-to-date
HINT: You should now run %s to initiate collection of data.
Thoughts?
regards, tom lane
On Sat, Oct 8, 2022 at 2:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Smith <smithpb2250@gmail.com> writes:
WARNING: tables were not subscribed, you will have to run ALTER
SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tablesWhen I first encountered the above CREATE SUBSCRIPTION warning message
I thought it was dubious-looking English...On closer inspection I think the message has some other things that
could be improved:
a) it is quite long which IIUC is generally frowned upon
b) IMO most of the text it is more like a "hint" about what to doYou're quite right about both of those points, but I think there's
even more to criticize: "tables were not subscribed" is a basically
useless message, and probably not even conceptually accurate.
Looking at the code, I think the situation being complained of is that
we have created the subscription's main catalog entries locally, but
since we were told not to connect to the publisher, we don't know what
tables the subscription is supposed to be reading. I'm not sure what
the consequences of that are: do we not read any data at all yet, or
what?I think maybe a better message would be along the lines of
WARNING: subscription was created, but is not up-to-date
HINT: You should now run %s to initiate collection of data.Thoughts?
Yes, IMO it's better to change the message more radically as you did.
But if it's OK to do that then:
- maybe it should mention the connection since the connect=false was
what caused this warning.
- maybe saying 'replication' instead of 'collection of data' would be
more consistent with the pgdocs for CREATE SUBSCRIPTION
e.g.
WARNING: subscription was created, but is not connected
HINT: You should run %s to initiate replication.
(I can update the patch when the final text is decided)
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Peter Smith <smithpb2250@gmail.com> writes:
On Sat, Oct 8, 2022 at 2:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think maybe a better message would be along the lines of
WARNING: subscription was created, but is not up-to-date
HINT: You should now run %s to initiate collection of data.
[ how about ]
WARNING: subscription was created, but is not connected
HINT: You should run %s to initiate replication.
OK by me; anybody else want to weigh in?
regards, tom lane
On Mon, Oct 10, 2022 at 4:40 AM Peter Smith <smithpb2250@gmail.com> wrote:
But if it's OK to do that then:
- maybe it should mention the connection since the connect=false was
what caused this warning.
- maybe saying 'replication' instead of 'collection of data' would be
more consistent with the pgdocs for CREATE SUBSCRIPTIONe.g.
WARNING: subscription was created, but is not connected
HINT: You should run %s to initiate replication.
Yeah, this message looks better than the current one. However, when I
tried to do what HINT says, it doesn't initiate replication. It gives
me the below error:
postgres=# Alter subscription sub1 refresh publication;
ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions
Then, I enabled the subscription and again tried as below:
postgres=# Alter subscription sub1 enable;
ALTER SUBSCRIPTION
postgres=# Alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
Even after the above replication is not initiated. I see "ERROR:
replication slot "sub1" does not exist" in subscriber logs. Then, I
manually created this slot (by using
pg_create_logical_replication_slot) on the publisher. After that,
replication started to work.
If I am not missing something, don't you think we need a somewhat more
elaborative HINT, or may be just give the WARNING?
--
With Regards,
Amit Kapila.
Amit Kapila <amit.kapila16@gmail.com> writes:
Yeah, this message looks better than the current one. However, when I
tried to do what HINT says, it doesn't initiate replication. It gives
me the below error:
postgres=# Alter subscription sub1 refresh publication;
ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions
Geez ... is there *anything* that's not broken about this message?
I'm beginning to question the entire premise here. That is,
rather than tweaking this message until it's within hailing
distance of sanity, why do we allow the no-connect case at all?
It's completely obvious that nobody uses this option, or we'd
already have heard complaints about the advice being a lie.
What's the real-world use case?
regards, tom lane
On Mon, Oct 10, 2022 at 10:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Amit Kapila <amit.kapila16@gmail.com> writes:
Yeah, this message looks better than the current one. However, when I
tried to do what HINT says, it doesn't initiate replication. It gives
me the below error:postgres=# Alter subscription sub1 refresh publication;
ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptionsGeez ... is there *anything* that's not broken about this message?
I'm beginning to question the entire premise here. That is,
rather than tweaking this message until it's within hailing
distance of sanity, why do we allow the no-connect case at all?
The docs say [1]https://www.postgresql.org/docs/devel/logical-replication-subscription.html: "When creating a subscription, the remote host is
not reachable or in an unclear state. In that case, the subscription
can be created using the connect = false option. The remote host will
then not be contacted at all. This is what pg_dump uses. The remote
replication slot will then have to be created manually before the
subscription can be activated."
I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.
[1]: https://www.postgresql.org/docs/devel/logical-replication-subscription.html
--
With Regards,
Amit Kapila.
On Mon, Oct 10, 2022 at 4:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Oct 10, 2022 at 10:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Amit Kapila <amit.kapila16@gmail.com> writes:
...
The docs say [1]: "When creating a subscription, the remote host is
not reachable or in an unclear state. In that case, the subscription
can be created using the connect = false option. The remote host will
then not be contacted at all. This is what pg_dump uses. The remote
replication slot will then have to be created manually before the
subscription can be activated."I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.
+1
PSA patch v2 worded like that.
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v2-0001-create-subscription-warning-message.patchapplication/octet-stream; name=v2-0001-create-subscription-warning-message.patchDownload
From 46aec55dc7825f898d669f35da15f0d37885149f Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Mon, 10 Oct 2022 20:21:10 +1100
Subject: [PATCH v2] create subscription warning message.
A small improvement to an existing CREATE SUBSCRIPTION warning message.
Before:
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
After:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
---
src/backend/commands/subscriptioncmds.c | 3 ++-
src/test/regress/expected/object_address.out | 3 ++-
src/test/regress/expected/subscription.out | 27 ++++++++++++++++++---------
3 files changed, 22 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index f3bfcca..0d4cbe8 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -760,8 +760,9 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
}
else
ereport(WARNING,
+ (errmsg("subscription was created, but is not connected"),
/* translator: %s is an SQL ALTER statement */
- (errmsg("tables were not subscribed, you will have to run %s to subscribe the tables",
+ errhint("You should create the slot manually, enable the subscription, and run %s to initiate replication.",
"ALTER SUBSCRIPTION ... REFRESH PUBLICATION")));
table_close(rel, RowExclusiveLock);
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 3549b63..01011e6 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -48,7 +48,8 @@ CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE PUBLICATION addr_pub_schema FOR TABLES IN SCHEMA addr_nsp;
RESET client_min_messages;
CREATE SUBSCRIPTION regress_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
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 876b504..e28ac3e 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -29,7 +29,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: publication name "foo" used more than once
-- ok
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
@@ -98,7 +99,8 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: subscription with slot_name = NONE must also set enabled = false
-- ok - with slot_name = NONE
CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
-- fail
ALTER SUBSCRIPTION regress_testsub3 ENABLE;
ERROR: cannot enable subscription that does not have a slot name
@@ -109,7 +111,8 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: unrecognized origin value: "foo"
-- now it works
CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = none);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
\dRs+ regress_testsub4
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -233,7 +236,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: binary requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, binary = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -256,7 +260,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: streaming requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -312,7 +317,8 @@ ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (ref
DROP SUBSCRIPTION regress_testsub;
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION mypub
WITH (connect = false, create_slot = false, copy_data = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
ALTER SUBSCRIPTION regress_testsub ENABLE;
-- fail - ALTER SUBSCRIPTION with refresh is not allowed in a transaction
-- block or function
@@ -338,7 +344,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: two_phase requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, two_phase = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -362,7 +369,8 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;
-- two_phase and streaming are compatible.
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true, two_phase = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -377,7 +385,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: disable_on_error requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, disable_on_error = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: You should create the slot manually, enable the subscription, and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to initiate replication.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
--
1.8.3.1
On Mon, Oct 10, 2022 at 12:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm beginning to question the entire premise here. That is,
rather than tweaking this message until it's within hailing
distance of sanity, why do we allow the no-connect case at all?
That sounds pretty nuts to me, because of the pg_dump use case if
nothing else. I don't think it's reasonable to say "oh, if you execute
this DDL on your system, it will instantaneously and automatically
begin to create outbound network connections, and there's no way to
turn that off." It ought to be possible to set up a configuration
first and then only later turn it on. And it definitely ought to be
possible, if things aren't working out, to turn it back off, too.
--
Robert Haas
EDB: http://www.enterprisedb.com
Peter Smith <smithpb2250@gmail.com> writes:
On Mon, Oct 10, 2022 at 4:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.
+1
It feels a little strange to me that we describe two steps rather vaguely
and then give exact SQL for the third step. How about, say,
HINT: To initiate replication, you must manually create the replication
slot, enable the subscription, and refresh the subscription.
Another idea is
HINT: To initiate replication, create the replication slot on the
publisher, then run ALTER SUBSCRIPTION ... ENABLE and ALTER
SUBSCRIPTION ... REFRESH PUBLICATION.
regards, tom lane
On Mon, Oct 10, 2022 at 8:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Smith <smithpb2250@gmail.com> writes:
On Mon, Oct 10, 2022 at 4:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.+1
It feels a little strange to me that we describe two steps rather vaguely
and then give exact SQL for the third step. How about, say,HINT: To initiate replication, you must manually create the replication
slot, enable the subscription, and refresh the subscription.
LGTM. BTW, do we want to slightly adjust the documentation for the
connect option on CREATE SUBSCRIPTION page [1]https://www.postgresql.org/docs/devel/sql-createsubscription.html? It says: "Since no
connection is made when this option is false, no tables are
subscribed, and so after you enable the subscription nothing will be
replicated. You will need to then run ALTER SUBSCRIPTION ... REFRESH
PUBLICATION for tables to be subscribed."
It doesn't say anything about manually creating the slot and probably
the wording can be made similar.
[1]: https://www.postgresql.org/docs/devel/sql-createsubscription.html
--
With Regards,
Amit Kapila.
On Tue, Oct 11, 2022 at 2:46 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Oct 10, 2022 at 8:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Smith <smithpb2250@gmail.com> writes:
On Mon, Oct 10, 2022 at 4:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.+1
It feels a little strange to me that we describe two steps rather vaguely
and then give exact SQL for the third step. How about, say,HINT: To initiate replication, you must manually create the replication
slot, enable the subscription, and refresh the subscription.LGTM.
PSA patch v3-0001 where the message/hint is worded as suggested above
BTW, do we want to slightly adjust the documentation for the
connect option on CREATE SUBSCRIPTION page [1]? It says: "Since no
connection is made when this option is false, no tables are
subscribed, and so after you enable the subscription nothing will be
replicated. You will need to then run ALTER SUBSCRIPTION ... REFRESH
PUBLICATION for tables to be subscribed."It doesn't say anything about manually creating the slot and probably
the wording can be made similar.
PSA patch v3-0002 which changes CREATE SUBSCRIPTION pgdocs to use the
same wording as in the HINT message.
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v3-0002-create-subscription-pgdocs.patchapplication/octet-stream; name=v3-0002-create-subscription-pgdocs.patchDownload
From e2d21ef6715aa0e133492d62b7f1b1c09ee38bf7 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Tue, 11 Oct 2022 17:22:07 +1100
Subject: [PATCH v3] create subscription pgdocs.
Modifies the subscription_parameter 'connect' notes to make the text
consistent with the warning message when connect=false.
---
doc/src/sgml/ref/create_subscription.sgml | 8 +++-----
1 file changed, 3 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index bd12e71..89d8fc9 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -120,11 +120,9 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Since no connection is made when this option is
- <literal>false</literal>, no tables are subscribed, and so
- after you enable the subscription nothing will be replicated.
- You will need to then run
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
- for tables to be subscribed.
+ <literal>false</literal>, no tables are subscribed. To initiate
+ replication, you must manually create the replication slot, enable
+ the subscription, and refresh the subscription.
</para>
</listitem>
</varlistentry>
--
1.8.3.1
v3-0001-create-subscription-warning-message.patchapplication/octet-stream; name=v3-0001-create-subscription-warning-message.patchDownload
From f327391e2a31b440d5aeaa0077983456d24fe62c Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Tue, 11 Oct 2022 17:10:29 +1100
Subject: [PATCH v3] create subscription warning message.
A small improvement to an existing CREATE SUBSCRIPTION warning message.
Before:
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
After:
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
---
src/backend/commands/subscriptioncmds.c | 5 ++---
src/test/regress/expected/object_address.out | 3 ++-
src/test/regress/expected/subscription.out | 27 ++++++++++++++++++---------
3 files changed, 22 insertions(+), 13 deletions(-)
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 97594cd..8fb89a9 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -760,9 +760,8 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
}
else
ereport(WARNING,
- /* translator: %s is an SQL ALTER statement */
- (errmsg("tables were not subscribed, you will have to run %s to subscribe the tables",
- "ALTER SUBSCRIPTION ... REFRESH PUBLICATION")));
+ (errmsg("subscription was created, but is not connected"),
+ errhint("To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.")));
table_close(rel, RowExclusiveLock);
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 3549b63..dadd58e 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -48,7 +48,8 @@ CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE PUBLICATION addr_pub_schema FOR TABLES IN SCHEMA addr_nsp;
RESET client_min_messages;
CREATE SUBSCRIPTION regress_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
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 876b504..c13d218 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -29,7 +29,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: publication name "foo" used more than once
-- ok
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
@@ -98,7 +99,8 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: subscription with slot_name = NONE must also set enabled = false
-- ok - with slot_name = NONE
CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
-- fail
ALTER SUBSCRIPTION regress_testsub3 ENABLE;
ERROR: cannot enable subscription that does not have a slot name
@@ -109,7 +111,8 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: unrecognized origin value: "foo"
-- now it works
CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = none);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+ regress_testsub4
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -233,7 +236,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: binary requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, binary = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -256,7 +260,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: streaming requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -312,7 +317,8 @@ ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (ref
DROP SUBSCRIPTION regress_testsub;
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION mypub
WITH (connect = false, create_slot = false, copy_data = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
ALTER SUBSCRIPTION regress_testsub ENABLE;
-- fail - ALTER SUBSCRIPTION with refresh is not allowed in a transaction
-- block or function
@@ -338,7 +344,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: two_phase requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, two_phase = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -362,7 +369,8 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;
-- two_phase and streaming are compatible.
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true, two_phase = true);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
@@ -377,7 +385,8 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
ERROR: disable_on_error requires a Boolean value
-- now it works
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, disable_on_error = false);
-WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Synchronous commit | Conninfo | Skip LSN
--
1.8.3.1
On 2022-Oct-10, Peter Smith wrote:
On Mon, Oct 10, 2022 at 4:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.
I guess this is reasonable, but how do I know what slot name do I have
to create? Maybe it'd be better to be explicit about that:
HINT: You should create slot \"%s\" manually, enable the subscription, and run %s to initiate replication.
though this still leaves a lot unexplained about that slot creation
(which options do they have to use).
If this sounds like too much for a HINT, perhaps we need a documentation
subsection that explains exactly what to do, and have this HINT
reference the documentation? I don't think we do that anywhere else,
though.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Industry suffers from the managerial dogma that for the sake of stability
and continuity, the company should be independent of the competence of
individual employees." (E. Dijkstra)
On 2022-Oct-11, Peter Smith wrote:
On Tue, Oct 11, 2022 at 2:46 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Oct 10, 2022 at 8:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
It feels a little strange to me that we describe two steps rather
vaguely and then give exact SQL for the third step. How about,
say,HINT: To initiate replication, you must manually create the
replication slot, enable the subscription, and refresh the
subscription.LGTM.
PSA patch v3-0001 where the message/hint is worded as suggested above
LGTM.
BTW, do we want to slightly adjust the documentation for the
connect option on CREATE SUBSCRIPTION page [1]? It says: "Since no
connection is made when this option is false, no tables are
subscribed, and so after you enable the subscription nothing will be
replicated. You will need to then run ALTER SUBSCRIPTION ... REFRESH
PUBLICATION for tables to be subscribed."It doesn't say anything about manually creating the slot and probably
the wording can be made similar.PSA patch v3-0002 which changes CREATE SUBSCRIPTION pgdocs to use the
same wording as in the HINT message.
I think we want the documentation to explain in much more detail what is
meant. Users are going to need some help in determining which commands
to run for each of the step mentioned in the hint, so I don't think we
want the docs to say the same thing as the hint. How does the user know
the name of the slot, what options to use, what are the commands to run
afterwards. So I think we should aim to *expand* that text, not reduce
it.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
On Tue, Oct 11, 2022 at 4:27 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2022-Oct-10, Peter Smith wrote:
On Mon, Oct 10, 2022 at 4:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I think the below gives accurate information:
WARNING: subscription was created, but is not connected
HINT: You should create the slot manually, enable the subscription,
and run %s to initiate replication.I guess this is reasonable, but how do I know what slot name do I have
to create? Maybe it'd be better to be explicit about that:HINT: You should create slot \"%s\" manually, enable the subscription, and run %s to initiate replication.
I am not so sure about including a slot name because users can create
a slot with a name of their choice and set it via Alter Subscription.
--
With Regards,
Amit Kapila.
On Wed, Oct 12, 2022 at 12:34 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2022-Oct-11, Peter Smith wrote:
BTW, do we want to slightly adjust the documentation for the
connect option on CREATE SUBSCRIPTION page [1]? It says: "Since no
connection is made when this option is false, no tables are
subscribed, and so after you enable the subscription nothing will be
replicated. You will need to then run ALTER SUBSCRIPTION ... REFRESH
PUBLICATION for tables to be subscribed."It doesn't say anything about manually creating the slot and probably
the wording can be made similar.PSA patch v3-0002 which changes CREATE SUBSCRIPTION pgdocs to use the
same wording as in the HINT message.I think we want the documentation to explain in much more detail what is
meant. Users are going to need some help in determining which commands
to run for each of the step mentioned in the hint, so I don't think we
want the docs to say the same thing as the hint. How does the user know
the name of the slot, what options to use, what are the commands to run
afterwards.
I think it is a good idea to expand the docs for this but note that
there are multiple places that use a similar description. For example,
see the description slot_name option: "When slot_name is set to NONE,
there will be no replication slot associated with the subscription.
This can be used if the replication slot will be created later
manually. Such subscriptions must also have both enabled and
create_slot set to false.". Then, we have a few places in the logical
replication docs [1]https://www.postgresql.org/docs/devel/logical-replication-subscription.html that talk about creating the slot manually but
didn't explain in detail the name or options to use. We might want to
write a slightly bigger doc patch so that we can write the description
in one place and give reference to the same at other places.
[1]: https://www.postgresql.org/docs/devel/logical-replication-subscription.html
--
With Regards,
Amit Kapila.
On 2022-Oct-12, Amit Kapila wrote:
I think it is a good idea to expand the docs for this but note that
there are multiple places that use a similar description. For example,
see the description slot_name option: "When slot_name is set to NONE,
there will be no replication slot associated with the subscription.
This can be used if the replication slot will be created later
manually. Such subscriptions must also have both enabled and
create_slot set to false.". Then, we have a few places in the logical
replication docs [1] that talk about creating the slot manually but
didn't explain in detail the name or options to use. We might want to
write a slightly bigger doc patch so that we can write the description
in one place and give reference to the same at other places.
+1
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Para tener más hay que desear menos"
On Wed, Oct 12, 2022 at 2:08 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2022-Oct-12, Amit Kapila wrote:
I think it is a good idea to expand the docs for this but note that
there are multiple places that use a similar description. For example,
see the description slot_name option: "When slot_name is set to NONE,
there will be no replication slot associated with the subscription.
This can be used if the replication slot will be created later
manually. Such subscriptions must also have both enabled and
create_slot set to false.". Then, we have a few places in the logical
replication docs [1] that talk about creating the slot manually but
didn't explain in detail the name or options to use. We might want to
write a slightly bigger doc patch so that we can write the description
in one place and give reference to the same at other places.+1
Okay, then I think we can commit the last error message patch of Peter
[1]: /messages/by-id/CAHut+PtgkebavGYsGnROkY1=ULhJ5+yn4_i3Y9E9+yDeksqpwQ@mail.gmail.com
separate patch. What do you think?
[1]: /messages/by-id/CAHut+PtgkebavGYsGnROkY1=ULhJ5+yn4_i3Y9E9+yDeksqpwQ@mail.gmail.com
--
With Regards,
Amit Kapila.
On 2022-Oct-12, Amit Kapila wrote:
Okay, then I think we can commit the last error message patch of Peter
[1] as we have an agreement on the same, and then work on this as a
separate patch. What do you think?
No objection.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
On 2022-Oct-12, Amit Kapila wrote:
Okay, then I think we can commit the last error message patch of Peter
[1] as we have an agreement on the same, and then work on this as a
separate patch. What do you think?
No objection.
Yeah, the v3-0001 patch is fine. I agree that the docs change needs
more work.
regards, tom lane
On Thu, Oct 13, 2022 at 2:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
On 2022-Oct-12, Amit Kapila wrote:
Okay, then I think we can commit the last error message patch of Peter
[1] as we have an agreement on the same, and then work on this as a
separate patch. What do you think?No objection.
Yeah, the v3-0001 patch is fine. I agree that the docs change needs
more work.
Thanks to everybody for the feedback/suggestions. I will work on
improving the documentation for this and post something in a day or
so.
------
Kind Regards,
Peter Smith.
Fujitsu Australia.
On Wed, Oct 12, 2022 at 8:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
On 2022-Oct-12, Amit Kapila wrote:
Okay, then I think we can commit the last error message patch of Peter
[1] as we have an agreement on the same, and then work on this as a
separate patch. What do you think?No objection.
Yeah, the v3-0001 patch is fine.
Pushed this one.
--
With Regards,
Amit Kapila.
On Thu, Oct 13, 2022 at 9:07 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Thu, Oct 13, 2022 at 2:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
On 2022-Oct-12, Amit Kapila wrote:
Okay, then I think we can commit the last error message patch of Peter
[1] as we have an agreement on the same, and then work on this as a
separate patch. What do you think?No objection.
Yeah, the v3-0001 patch is fine. I agree that the docs change needs
more work.Thanks to everybody for the feedback/suggestions. I will work on
improving the documentation for this and post something in a day or
so.
PSA a patch for adding examples of how to activate a subscription that
was originally created in a disconnected mode.
The new docs are added as part of the "Logical Replication -
Subscription" section 31.2.
The CREATE SUBSCRIPTION reference page was also updated to include
links to the new docs.
Feedback welcome.
------
Kind Regards,
Peter Smith.
Fujitsu Australia.
Attachments:
v4-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patchapplication/octet-stream; name=v4-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patchDownload
From 9d83b423ccf0478156ecb8d8199ec4eb5df030a4 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Fri, 14 Oct 2022 13:36:51 +1100
Subject: [PATCH v4] create subscriptipon - pgdocs for deferred slot creation.
New documentation describing how to activate a subscription which was originally
created in a disconnected mode.
The new docs/examples are added as part of "Logical Replication - Subscription"
section 31.2.
The CREATE SUBSCRIPTION reference page is also updated to include links to it.
---
doc/src/sgml/logical-replication.sgml | 161 ++++++++++++++++++++++++++++++
doc/src/sgml/ref/create_subscription.sgml | 34 ++++---
2 files changed, 182 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index e98538e..735adc0 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -319,6 +319,167 @@
</para>
</sect2>
+ <sect2 id="logical-replication-subscription-slot-deferred">
+ <title>Deferred Replication Slot Creation</title>
+
+ <para>
+ Sometimes, either by choice (e.g. <literal>create_slot = false</literal>),
+ or by necessity (e.g. <literal>connect = false</literal>), the remote
+ replication slot is not created automatically during
+ <literal>CREATE SUBSCRIPTION</literal>. In these cases the user will have
+ to create the slot manually before the subscription can be activated.
+ </para>
+ <para>
+ The following examples demonstrate the steps required to activate such
+ subscriptions.
+ </para>
+ <para>
+ First, create a publication for the examples to use.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
+CREATE PUBLICATION
+</programlisting></para>
+ <para>
+ EXAMPLE 1: Where the subscription says <literal>connect = false</literal>
+ </para>
+ <para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot. Because the name was not
+ specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
+ slot to create is same as the subscription name, e.g. "sub1".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19404D0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, complete the activation of the subscription. After
+ this the tables of <literal>pub1</literal> will start replicating.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ EXAMPLE 2: Where the subscription says <literal>connect = false</literal>,
+ but also specifies the <literal>slot_name</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false, slot_name='myslot');
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using the same name that was
+ specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19059A0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, the remaining subscription activation steps are the
+ same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ EXAMPLE 3: Where the subscription specifies <literal>slot_name = NONE</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription. When <literal>slot_name = NONE</literal> then
+ <literal>enabled = false</literal>, and
+ <literal>create_slot = false</literal> are also needed.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using any name, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ myslot | 0/1905930
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, associate the subscription with the slot name just
+ created.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ The remaining subscription activation steps are same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
<sect2 id="logical-replication-subscription-examples">
<title>Examples</title>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index bd12e71..43b32e8 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -120,11 +120,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Since no connection is made when this option is
- <literal>false</literal>, no tables are subscribed, and so
- after you enable the subscription nothing will be replicated.
- You will need to then run
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
- for tables to be subscribed.
+ <literal>false</literal>, no tables are subscribed. To initiate
+ replication, you must manually create the replication slot, enable
+ the subscription, and refresh the subscription. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples.
</para>
</listitem>
</varlistentry>
@@ -135,8 +135,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Specifies whether the command should create the replication slot on
the publisher. The default is <literal>true</literal>.
+ </para>
+ <para>
If set to <literal>false</literal>, you are responsible for
- creating the publisher's slot in some other way.
+ creating the publisher's slot in some other way. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples.
</para>
</listitem>
</varlistentry>
@@ -162,11 +166,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Setting <literal>slot_name</literal> to <literal>NONE</literal>
- means there will be no replication slot
- associated with the subscription. Use this when you will be
- creating the replication slot later manually. Such
- subscriptions must also have both <literal>enabled</literal> and
- <literal>create_slot</literal> set to <literal>false</literal>.
+ means there will be no replication slot associated with the
+ subscription. Such subscriptions must also have both
+ <literal>enabled</literal> and <literal>create_slot</literal> set to
+ <literal>false</literal>. Use this when you will be creating the
+ replication slot later manually. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples.
</para>
</listitem>
</varlistentry>
@@ -357,8 +363,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
replication slot separately (using the
function <function>pg_create_logical_replication_slot</function> with the
plugin name <literal>pgoutput</literal>) and create the subscription using
- the parameter <literal>create_slot = false</literal>. This is an
- implementation restriction that might be lifted in a future release.
+ the parameter <literal>create_slot = false</literal>. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples. This is an implementation restriction that might be lifted in a
+ future release.
</para>
<para>
--
1.8.3.1
On Fri, Oct 14, 2022 at 8:22 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Thu, Oct 13, 2022 at 9:07 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Thu, Oct 13, 2022 at 2:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
On 2022-Oct-12, Amit Kapila wrote:
Okay, then I think we can commit the last error message patch of Peter
[1] as we have an agreement on the same, and then work on this as a
separate patch. What do you think?No objection.
Yeah, the v3-0001 patch is fine. I agree that the docs change needs
more work.Thanks to everybody for the feedback/suggestions. I will work on
improving the documentation for this and post something in a day or
so.PSA a patch for adding examples of how to activate a subscription that
was originally created in a disconnected mode.The new docs are added as part of the "Logical Replication -
Subscription" section 31.2.The CREATE SUBSCRIPTION reference page was also updated to include
links to the new docs.
You have used 'pgoutput' as plugin name in the examples. Shall we
mention in some way that this is a default plugin used for built-in
logical replication and it is required to use only this one to enable
logical replication?
--
With Regards,
Amit Kapila.
On Sun, Oct 16, 2022 at 12:14 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Oct 14, 2022 at 8:22 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Thu, Oct 13, 2022 at 9:07 AM Peter Smith <smithpb2250@gmail.com> wrote:
...
PSA a patch for adding examples of how to activate a subscription that
was originally created in a disconnected mode.The new docs are added as part of the "Logical Replication -
Subscription" section 31.2.The CREATE SUBSCRIPTION reference page was also updated to include
links to the new docs.You have used 'pgoutput' as plugin name in the examples. Shall we
mention in some way that this is a default plugin used for built-in
logical replication and it is required to use only this one to enable
logical replication?
Updated as sugggested.
PSA v5.
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v5-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patchapplication/octet-stream; name=v5-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patchDownload
From ab7e739d8f7b6ac4072dcf02cf688ce2753972cc Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Mon, 17 Oct 2022 12:43:03 +1100
Subject: [PATCH v5] create subscriptipon - pgdocs for deferred slot creation.
New documentation describing how to activate a subscription which was originally
created in a disconnected mode.
The new docs/examples are added as part of "Logical Replication - Subscription"
section 31.2.
The CREATE SUBSCRIPTION reference page is also updated to include links to it.
---
doc/src/sgml/logical-replication.sgml | 163 ++++++++++++++++++++++++++++++
doc/src/sgml/ref/create_subscription.sgml | 34 ++++---
2 files changed, 184 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index e98538e..a2b952c 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -319,6 +319,169 @@
</para>
</sect2>
+ <sect2 id="logical-replication-subscription-slot-deferred">
+ <title>Deferred Replication Slot Creation</title>
+
+ <para>
+ Sometimes, either by choice (e.g. <literal>create_slot = false</literal>),
+ or by necessity (e.g. <literal>connect = false</literal>), the remote
+ replication slot is not created automatically during
+ <literal>CREATE SUBSCRIPTION</literal>. In these cases the user will have
+ to create the slot manually before the subscription can be activated.
+ </para>
+ <para>
+ The following examples demonstrate the steps required to activate such
+ subscriptions. The examples specify the standard logical decoding plugin
+ (<literal>pgoutput</literal>), which is what the built-in logical
+ replication uses.
+ </para>
+ <para>
+ First, create a publication for the examples to use.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
+CREATE PUBLICATION
+</programlisting></para>
+ <para>
+ Example 1: Where the subscription says <literal>connect = false</literal>
+ </para>
+ <para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot. Because the name was not
+ specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
+ slot to create is same as the subscription name, e.g. "sub1".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19404D0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, complete the activation of the subscription. After
+ this the tables of <literal>pub1</literal> will start replicating.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Example 2: Where the subscription says <literal>connect = false</literal>,
+ but also specifies the <literal>slot_name</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false, slot_name='myslot');
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using the same name that was
+ specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19059A0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, the remaining subscription activation steps are the
+ same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Example 3: Where the subscription specifies <literal>slot_name = NONE</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription. When <literal>slot_name = NONE</literal> then
+ <literal>enabled = false</literal>, and
+ <literal>create_slot = false</literal> are also needed.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using any name, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ myslot | 0/1905930
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, associate the subscription with the slot name just
+ created.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ The remaining subscription activation steps are same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
<sect2 id="logical-replication-subscription-examples">
<title>Examples</title>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index bd12e71..43b32e8 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -120,11 +120,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Since no connection is made when this option is
- <literal>false</literal>, no tables are subscribed, and so
- after you enable the subscription nothing will be replicated.
- You will need to then run
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
- for tables to be subscribed.
+ <literal>false</literal>, no tables are subscribed. To initiate
+ replication, you must manually create the replication slot, enable
+ the subscription, and refresh the subscription. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples.
</para>
</listitem>
</varlistentry>
@@ -135,8 +135,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Specifies whether the command should create the replication slot on
the publisher. The default is <literal>true</literal>.
+ </para>
+ <para>
If set to <literal>false</literal>, you are responsible for
- creating the publisher's slot in some other way.
+ creating the publisher's slot in some other way. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples.
</para>
</listitem>
</varlistentry>
@@ -162,11 +166,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Setting <literal>slot_name</literal> to <literal>NONE</literal>
- means there will be no replication slot
- associated with the subscription. Use this when you will be
- creating the replication slot later manually. Such
- subscriptions must also have both <literal>enabled</literal> and
- <literal>create_slot</literal> set to <literal>false</literal>.
+ means there will be no replication slot associated with the
+ subscription. Such subscriptions must also have both
+ <literal>enabled</literal> and <literal>create_slot</literal> set to
+ <literal>false</literal>. Use this when you will be creating the
+ replication slot later manually. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples.
</para>
</listitem>
</varlistentry>
@@ -357,8 +363,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
replication slot separately (using the
function <function>pg_create_logical_replication_slot</function> with the
plugin name <literal>pgoutput</literal>) and create the subscription using
- the parameter <literal>create_slot = false</literal>. This is an
- implementation restriction that might be lifted in a future release.
+ the parameter <literal>create_slot = false</literal>. See
+ <xref linkend="logical-replication-subscription-slot-deferred"/> for
+ examples. This is an implementation restriction that might be lifted in a
+ future release.
</para>
<para>
--
1.8.3.1
On Mon, Oct 17, 2022 9:47 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Sun, Oct 16, 2022 at 12:14 AM Amit Kapila <amit.kapila16@gmail.com>
wrote:On Fri, Oct 14, 2022 at 8:22 AM Peter Smith <smithpb2250@gmail.com>
wrote:
On Thu, Oct 13, 2022 at 9:07 AM Peter Smith <smithpb2250@gmail.com>
wrote:
...
PSA a patch for adding examples of how to activate a subscription that
was originally created in a disconnected mode.The new docs are added as part of the "Logical Replication -
Subscription" section 31.2.The CREATE SUBSCRIPTION reference page was also updated to include
links to the new docs.You have used 'pgoutput' as plugin name in the examples. Shall we
mention in some way that this is a default plugin used for built-in
logical replication and it is required to use only this one to enable
logical replication?Updated as sugggested.
PSA v5.
Thanks for your patch. Here are some comments.
In Example 2, the returned slot_name should be "myslot".
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19059A0
+(1 row)Besides, I am thinking is it possible to slightly simplify the example. For
example, merge example 1 and 2, keep the steps of example 2 and in the step of
creating slot, mention what should we do if slot_name is not specified when
creating subscription.
Regards,
Shi yu
On Mon, Oct 17, 2022 at 7:17 AM Peter Smith <smithpb2250@gmail.com> wrote:
Updated as sugggested.
+ <para>
+ Sometimes, either by choice (e.g. <literal>create_slot = false</literal>),
+ or by necessity (e.g. <literal>connect = false</literal>), the remote
+ replication slot is not created automatically during
+ <literal>CREATE SUBSCRIPTION</literal>. In these cases the user will have
+ to create the slot manually before the subscription can be activated.
+ </para>This part looks a bit odd when in the previous section we have
explained the same thing in different words. I think it may be better
if we start with something like: "As mentioned in the previous
section, there are cases where we need to create the slot manually
before the subscription can be activated.". I think you can even
combine the next para in the patch with this one.
Also, it looks odd that the patch uses examples to demonstrate how to
manually create a slot, and then we have a separate section whose
title is Examples. I am not sure what is the best way to arrange docs
here but maybe we can consider renaming the Examples section to
something more specific.
--
With Regards,
Amit Kapila.
Thanks for the feedback.
On Mon, Oct 17, 2022 at 10:14 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Oct 17, 2022 at 7:17 AM Peter Smith <smithpb2250@gmail.com> wrote:
Updated as sugggested.
+ <para> + Sometimes, either by choice (e.g. <literal>create_slot = false</literal>), + or by necessity (e.g. <literal>connect = false</literal>), the remote + replication slot is not created automatically during + <literal>CREATE SUBSCRIPTION</literal>. In these cases the user will have + to create the slot manually before the subscription can be activated. + </para>This part looks a bit odd when in the previous section we have
explained the same thing in different words. I think it may be better
if we start with something like: "As mentioned in the previous
section, there are cases where we need to create the slot manually
before the subscription can be activated.". I think you can even
combine the next para in the patch with this one.
Modified the text and combined the paragraphs as suggested.
Also, it looks odd that the patch uses examples to demonstrate how to
manually create a slot, and then we have a separate section whose
title is Examples. I am not sure what is the best way to arrange docs
here but maybe we can consider renaming the Examples section to
something more specific.
Renamed the examples sections to make their purpose clearer.
~~~
PSA patch v6 with the above changes + one correction from Shi-san [1]/messages/by-id/OSZPR01MB631051BA9AAA728CAA8CBD88FD299@OSZPR01MB6310.jpnprd01.prod.outlook.com
------
[1]: /messages/by-id/OSZPR01MB631051BA9AAA728CAA8CBD88FD299@OSZPR01MB6310.jpnprd01.prod.outlook.com
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v6-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patchapplication/octet-stream; name=v6-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patchDownload
From 5ad653a9e4cc1485b16b1b2886350f69d0e772cc Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Tue, 18 Oct 2022 20:27:59 +1100
Subject: [PATCH v6] create subscriptipon - pgdocs for deferred slot creation.
New documentation describing how to activate a subscription which was originally
created in a disconnected mode.
The new docs/examples are added as part of "Logical Replication - Subscription"
section 31.2.
The CREATE SUBSCRIPTION reference page is also updated to include links to it.
---
doc/src/sgml/logical-replication.sgml | 161 +++++++++++++++++++++++++++++-
doc/src/sgml/ref/create_subscription.sgml | 34 ++++---
2 files changed, 181 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index e98538e..a5567cc 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -320,7 +320,7 @@
</sect2>
<sect2 id="logical-replication-subscription-examples">
- <title>Examples</title>
+ <title>Examples - Normal Usage</title>
<para>
Create some test tables on the publisher.
@@ -512,6 +512,165 @@ test_sub=# SELECT * FROM t3;
</programlisting></para>
</sect2>
+ <sect2 id="logical-replication-subscription-examples-deferred-slot">
+ <title>Examples - Deferred Replication Slot Creation</title>
+
+ <para>
+ There are some cases (e.g.
+ <xref linkend="logical-replication-subscription-slot"/>) where, if the
+ remote replication slot was not created automatically, the user must create
+ it manually before the subscription can be activated. The steps to create
+ the slot and activate the subscription are shown in the following examples.
+ These examples specify the standard logical decoding plugin
+ (<literal>pgoutput</literal>), which is what the built-in logical
+ replication uses.
+ </para>
+ <para>
+ First, create a publication for the examples to use.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
+CREATE PUBLICATION
+</programlisting></para>
+ <para>
+ Example 1: Where the subscription says <literal>connect = false</literal>
+ </para>
+ <para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot. Because the name was not
+ specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
+ slot to create is same as the subscription name, e.g. "sub1".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19404D0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, complete the activation of the subscription. After
+ this the tables of <literal>pub1</literal> will start replicating.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Example 2: Where the subscription says <literal>connect = false</literal>,
+ but also specifies the <literal>slot_name</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false, slot_name='myslot');
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using the same name that was
+ specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ myslot | 0/19059A0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, the remaining subscription activation steps are the
+ same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Example 3: Where the subscription specifies <literal>slot_name = NONE</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription. When <literal>slot_name = NONE</literal> then
+ <literal>enabled = false</literal>, and
+ <literal>create_slot = false</literal> are also needed.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using any name, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ myslot | 0/1905930
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, associate the subscription with the slot name just
+ created.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ The remaining subscription activation steps are same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect2>
+
</sect1>
<sect1 id="logical-replication-row-filter">
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index bd12e71..f9a1776 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -120,11 +120,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Since no connection is made when this option is
- <literal>false</literal>, no tables are subscribed, and so
- after you enable the subscription nothing will be replicated.
- You will need to then run
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
- for tables to be subscribed.
+ <literal>false</literal>, no tables are subscribed. To initiate
+ replication, you must manually create the replication slot, enable
+ the subscription, and refresh the subscription. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples.
</para>
</listitem>
</varlistentry>
@@ -135,8 +135,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Specifies whether the command should create the replication slot on
the publisher. The default is <literal>true</literal>.
+ </para>
+ <para>
If set to <literal>false</literal>, you are responsible for
- creating the publisher's slot in some other way.
+ creating the publisher's slot in some other way. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples.
</para>
</listitem>
</varlistentry>
@@ -162,11 +166,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Setting <literal>slot_name</literal> to <literal>NONE</literal>
- means there will be no replication slot
- associated with the subscription. Use this when you will be
- creating the replication slot later manually. Such
- subscriptions must also have both <literal>enabled</literal> and
- <literal>create_slot</literal> set to <literal>false</literal>.
+ means there will be no replication slot associated with the
+ subscription. Such subscriptions must also have both
+ <literal>enabled</literal> and <literal>create_slot</literal> set to
+ <literal>false</literal>. Use this when you will be creating the
+ replication slot later manually. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples.
</para>
</listitem>
</varlistentry>
@@ -357,8 +363,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
replication slot separately (using the
function <function>pg_create_logical_replication_slot</function> with the
plugin name <literal>pgoutput</literal>) and create the subscription using
- the parameter <literal>create_slot = false</literal>. This is an
- implementation restriction that might be lifted in a future release.
+ the parameter <literal>create_slot = false</literal>. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples. This is an implementation restriction that might be lifted in a
+ future release.
</para>
<para>
--
1.8.3.1
On Mon, Oct 17, 2022 at 7:11 PM shiy.fnst@fujitsu.com
<shiy.fnst@fujitsu.com> wrote:
...
Thanks for your patch. Here are some comments.
In Example 2, the returned slot_name should be "myslot".
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); + slot_name | lsn +-----------+----------- + sub1 | 0/19059A0 +(1 row)
Oops. Sorry for my cut/paste error. Fixed in patch v6.
Besides, I am thinking is it possible to slightly simplify the example. For
example, merge example 1 and 2, keep the steps of example 2 and in the step of
creating slot, mention what should we do if slot_name is not specified when
creating subscription.
Sure, it might be a bit shorter to combine the examples, but I thought
it’s just simpler not to do it that way because the combined example
will then need additional bullets/notes to say – “if there is no
slot_name do this…” and “if there is a slot_name do that…”. It’s
really only the activation part which is identical for both.
-----
Kind Regards,
Peter Smith.
Fujitsu Australia
On Tue, Oct 18, 2022 5:44 PM Peter Smith <smithpb2250@gmail.com> wrote:
On Mon, Oct 17, 2022 at 7:11 PM shiy.fnst@fujitsu.com
<shiy.fnst@fujitsu.com> wrote:...
Thanks for your patch. Here are some comments.
In Example 2, the returned slot_name should be "myslot".
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot',
'pgoutput');
+ slot_name | lsn +-----------+----------- + sub1 | 0/19059A0 +(1 row)Oops. Sorry for my cut/paste error. Fixed in patch v6.
Besides, I am thinking is it possible to slightly simplify the example. For
example, merge example 1 and 2, keep the steps of example 2 and in thestep of
creating slot, mention what should we do if slot_name is not specified when
creating subscription.Sure, it might be a bit shorter to combine the examples, but I thought
it’s just simpler not to do it that way because the combined example
will then need additional bullets/notes to say – “if there is no
slot_name do this…” and “if there is a slot_name do that…”. It’s
really only the activation part which is identical for both.
Thanks for updating the patch.
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTIONIn example 3, there is actually no such warning message when creating
subscription because "connect=false" is not specified.
I have tested the examples in the patch and didn't see any problem other than
the one above.
Regards,
Shi yu
On Wed, Oct 19, 2022 at 2:44 PM shiy.fnst@fujitsu.com
<shiy.fnst@fujitsu.com> wrote:
...
+test_sub=# CREATE SUBSCRIPTION sub1 +test_sub-# CONNECTION 'host=localhost dbname=test_pub' +test_sub-# PUBLICATION pub1 +test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false); +WARNING: subscription was created, but is not connected +HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. +CREATE SUBSCRIPTIONIn example 3, there is actually no such warning message when creating
subscription because "connect=false" is not specified.
Oh, thanks for finding and reporting that. Sorry for my cut/paste
errors. Fixed in v7. PSA,
I have tested the examples in the patch and didn't see any problem other than
the one above.
Thanks for your testing.
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v7-0001-create-subscription-pgdocs-for-deferred-slot-crea.patchapplication/octet-stream; name=v7-0001-create-subscription-pgdocs-for-deferred-slot-crea.patchDownload
From 39e2f825191717415d60e24f701c4ed124fffedb Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Wed, 19 Oct 2022 15:18:55 +1100
Subject: [PATCH v7] create subscription - pgdocs for deferred slot creation.
New documentation describing how to activate a subscription which was originally
created in a disconnected mode.
The new docs/examples are added as part of "Logical Replication - Subscription"
section 31.2.
The CREATE SUBSCRIPTION reference page is also updated to include links to it.
---
doc/src/sgml/logical-replication.sgml | 159 +++++++++++++++++++++++++++++-
doc/src/sgml/ref/create_subscription.sgml | 34 ++++---
2 files changed, 179 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index e98538e..bf84d5e 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -320,7 +320,7 @@
</sect2>
<sect2 id="logical-replication-subscription-examples">
- <title>Examples</title>
+ <title>Examples - Normal Usage</title>
<para>
Create some test tables on the publisher.
@@ -512,6 +512,163 @@ test_sub=# SELECT * FROM t3;
</programlisting></para>
</sect2>
+ <sect2 id="logical-replication-subscription-examples-deferred-slot">
+ <title>Examples - Deferred Replication Slot Creation</title>
+
+ <para>
+ There are some cases (e.g.
+ <xref linkend="logical-replication-subscription-slot"/>) where, if the
+ remote replication slot was not created automatically, the user must create
+ it manually before the subscription can be activated. The steps to create
+ the slot and activate the subscription are shown in the following examples.
+ These examples specify the standard logical decoding plugin
+ (<literal>pgoutput</literal>), which is what the built-in logical
+ replication uses.
+ </para>
+ <para>
+ First, create a publication for the examples to use.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
+CREATE PUBLICATION
+</programlisting></para>
+ <para>
+ Example 1: Where the subscription says <literal>connect = false</literal>
+ </para>
+ <para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false);
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot. Because the name was not
+ specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
+ slot to create is same as the subscription name, e.g. "sub1".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ sub1 | 0/19404D0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, complete the activation of the subscription. After
+ this the tables of <literal>pub1</literal> will start replicating.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Example 2: Where the subscription says <literal>connect = false</literal>,
+ but also specifies the <literal>slot_name</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false, slot_name='myslot');
+WARNING: subscription was created, but is not connected
+HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using the same name that was
+ specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ myslot | 0/19059A0
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, the remaining subscription activation steps are the
+ same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Example 3: Where the subscription specifies <literal>slot_name = NONE</literal>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Create the subscription. When <literal>slot_name = NONE</literal> then
+ <literal>enabled = false</literal>, and
+ <literal>create_slot = false</literal> are also needed.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
+CREATE SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the publisher, manually create a slot using any name, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name | lsn
+-----------+-----------
+ myslot | 0/1905930
+(1 row)
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ On the subscriber, associate the subscription with the slot name just
+ created.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>
+ The remaining subscription activation steps are same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect2>
+
</sect1>
<sect1 id="logical-replication-row-filter">
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index bd12e71..f9a1776 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -120,11 +120,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Since no connection is made when this option is
- <literal>false</literal>, no tables are subscribed, and so
- after you enable the subscription nothing will be replicated.
- You will need to then run
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
- for tables to be subscribed.
+ <literal>false</literal>, no tables are subscribed. To initiate
+ replication, you must manually create the replication slot, enable
+ the subscription, and refresh the subscription. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples.
</para>
</listitem>
</varlistentry>
@@ -135,8 +135,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Specifies whether the command should create the replication slot on
the publisher. The default is <literal>true</literal>.
+ </para>
+ <para>
If set to <literal>false</literal>, you are responsible for
- creating the publisher's slot in some other way.
+ creating the publisher's slot in some other way. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples.
</para>
</listitem>
</varlistentry>
@@ -162,11 +166,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
Setting <literal>slot_name</literal> to <literal>NONE</literal>
- means there will be no replication slot
- associated with the subscription. Use this when you will be
- creating the replication slot later manually. Such
- subscriptions must also have both <literal>enabled</literal> and
- <literal>create_slot</literal> set to <literal>false</literal>.
+ means there will be no replication slot associated with the
+ subscription. Such subscriptions must also have both
+ <literal>enabled</literal> and <literal>create_slot</literal> set to
+ <literal>false</literal>. Use this when you will be creating the
+ replication slot later manually. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples.
</para>
</listitem>
</varlistentry>
@@ -357,8 +363,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
replication slot separately (using the
function <function>pg_create_logical_replication_slot</function> with the
plugin name <literal>pgoutput</literal>) and create the subscription using
- the parameter <literal>create_slot = false</literal>. This is an
- implementation restriction that might be lifted in a future release.
+ the parameter <literal>create_slot = false</literal>. See
+ <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+ for examples. This is an implementation restriction that might be lifted in a
+ future release.
</para>
<para>
--
1.8.3.1
On Wed, Oct 19, 2022 at 10:10 AM Peter Smith <smithpb2250@gmail.com> wrote:
Thanks for your testing.
LGTM so pushed with a minor change in one of the titles in the Examples section.
--
With Regards,
Amit Kapila.
On Wed, Nov 2, 2022 at 9:02 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Wed, Oct 19, 2022 at 10:10 AM Peter Smith <smithpb2250@gmail.com> wrote:
Thanks for your testing.
LGTM so pushed with a minor change in one of the titles in the Examples section.
Thanks for pushing.
------
Kind Regards,
Peter Smith.
Fujitsu Australia