Support logical replication of global object commands
Hello,
Logical replication of DDL commands support is being worked on in [1]/messages/by-id/CAAD30U+pVmfKwUKy8cbZOnUXyguJ-uBNejwD75Kyo=OjdQGJ9g@mail.gmail.com.
However, global object commands are quite different from other
non-global object DDL commands and need to be handled differently. For
example, global object commands include ROLE statements, DATABASE
statements, TABLESPACE statements and a subset of GRANT/REVOKE
statements if the object being modified is a global object. These
commands are different from other DDL commands in that:
1. Global object commands can be executed in any database.
2. Global objects are not schema qualified.
3. Global object commands are not captured by event triggers.
I’ve put together a prototype to support logical replication of global
object commands in the attached patch. This patch builds on the DDL
replication patch from ZJ in [2]/messages/by-id/OS0PR01MB5716009FDCCC0B50BCB14A99949D9@OS0PR01MB5716.jpnprd01.prod.outlook.com and must be applied on top of it.
Here is a list of global object commands that the patch replicate, you
can find more details in function LogGlobalObjectCommand:
/* ROLE statements */
CreateRoleStmt
AlterRoleStmt
AlterRoleSetStmt
DropRoleStmt
ReassignOwnedStmt
GrantRoleStmt
/* Database statements */
CreatedbStmt
AlterDatabaseStmt
AlterDatabaseRefreshCollStmt
AlterDatabaseSetStmt
DropdbStmt
/* TableSpace statements */
CreateTableSpaceStmt
DropTableSpaceStmt
AlterTableSpaceOptionsStmt
/* GrantStmt and RevokeStmt if objtype is a global object determined
by EventTriggerSupportsObjectType() */
GrantStmt
RevokeStmt
The idea with this patch is to support global objects commands
replication by WAL logging the command using the same function for DDL
logging - LogLogicalDDLMessage towards the end of
standard_ProcessUtility. Because global objects are not schema
qualified, we can skip the deparser invocation and directly log the
original command string for replay on the subscriber.
A key problem to address is that global objects can become
inconsistent between the publisher and the subscriber if a command
modifying the global object gets executed in a database (on the source
side) that doesn't replicate the global object commands. I think we
can work on the following two aspects in order to avoid such
inconsistency:
1. Introduce a publication option for global object commands
replication and document that logical replication of global object
commands is preferred to be enabled on all databases. Otherwise
inconsistency can happen if a command modifies the global object in a
database that doesn't replicate global object commands.
For example, we could introduce the following publication option
publish_global_object_command :
CREATE PUBLICATION mypub
FOR ALL TABLES
WITH (publish = 'insert, delete, update', publish_global_object_command = true);
We may consider other fine tuned global command options such as
“publish_role_statements”, “publish_database_statements”,
“publish_tablespace_statements” and "publish_grant_statements", i.e.
you pick which global commands you want replicated. For example, you
can do this if you need a permission or tablespace to be set up
differently on the target cluster. In addition, we may need to adjust
the syntax once the DDL replication syntax finalizes.
2. Introduce the following database cluster level logical replication
commands to avoid such inconsistency, this is especially handy when
there is a large number of databases to configure for logical
replication.
CREATE PUBLICATION GROUP mypub_
FOR ALL DATABASES
WITH (publish = 'insert, delete, update', publish_global_object_command = true);
CREATE SUBSCRIPTION GROUP mysub_
CONNECTION 'dbnames = \“path to file\” host=hostname user=username port=5432'
PUBLICATION GROUP mypub_;
Under the hood, the CREATE PUBLICATION GROUP command generates one
CREATE PUBLICATION mypub_n sub-command for each database in the
cluster where n is a monotonically increasing integer from 1. The
command outputs the (dbname, publication name) pairs which can be
saved in a file and then used on the subscription side.
Similarly, the CREATE SUBSCRIPTION GROUP command will generate one
CREATE SUBSCRIPTION mysub_n sub-command for each database in the
dbnames file. The dbnames file contains the (dbname, publication name)
pairs which come from the output of the CREATE PUBLICATION GROUP
command. Notice the connection string doesn’t have the dbname field,
During execution the connection string will be appended the dbname
retrieved from the dbnames file. By default the target DB name is the
same as the source DB name, optionally user can specify the source_db
to target_db mapping in the dbnames file.
In addition, we might want to create dependencies for the
publications/subscriptions created by the above commands in order to
guarantee the group consistency. Also we need to enforce that there is
only one group of publications/subscriptions for database cluster
level replication.
Logical replication of all commands across an entire cluster (instead
of on a per-database basis) is a separate topic. We can start another
thread after implementing a prototype.
Please let me know your thoughts.
[1]: /messages/by-id/CAAD30U+pVmfKwUKy8cbZOnUXyguJ-uBNejwD75Kyo=OjdQGJ9g@mail.gmail.com
[2]: /messages/by-id/OS0PR01MB5716009FDCCC0B50BCB14A99949D9@OS0PR01MB5716.jpnprd01.prod.outlook.com
With Regards,
Zheng Li
Amazon RDS/Aurora for PostgreSQL
Attachments:
0005-Support-replication-of-global-object-commands-these-.patchapplication/octet-stream; name=0005-Support-replication-of-global-object-commands-these-.patchDownload
From 9d5a11876b96685fa89bf416a8537f8056529ea1 Mon Sep 17 00:00:00 2001
From: "Zheng (Zane) Li" <zhelli@amazon.com>
Date: Fri, 22 Jul 2022 18:34:32 +0000
Subject: [PATCH 5/5] Support replication of global object commands, these
include ROLE statements, database statements, tablespace statements and a
subset of grantstmt/revokestmt if the object being changed is a global
object.
Global objects commands are different from other DDL commands in
that:
1. Global objects commands are allowed to be executed in any databases
2. Global objects are not schema qualified
2. Global objects commands are not captured by event triggers
This patch supports global objects commands replication by WAL
logging the command using the same function for DDL logging, i.e.
LogLogicalDDLMessage towards the end of standard_ProcessUtility.
Because global objects are not schema qualified, we skip the deparser
invocation and directly log the original command string for replay
on the subscriber.
A key problem to address is global objects can get inconsistent between
the publisher and the subscriber if a command changes the global object
in a database which doesn't replicate global objects command. I think
we can work on the following in order to avoid such inconsistency:
1. Introduce a publication option for global command replication
and document that logical replication of global command is preferred
to be configured in all databases, otherwise inconsistency can happen
if a command changes the global object in a database which doesn't
replicate global commands.
2. Introduce database cluster level logical replication, this is handy
when there is a large number of databases to configure for logical
replication.
---
src/backend/replication/logical/worker.c | 6 +-
src/backend/replication/pgoutput/pgoutput.c | 3 +
src/backend/tcop/utility.c | 59 +++++++++++++++++
src/include/replication/ddlmessage.h | 3 +-
.../subscription/t/032_ddl_replication.pl | 64 ++++++++++++++-----
5 files changed, 116 insertions(+), 19 deletions(-)
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index 443a3c2e0a..2bcc8df9a1 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -2587,7 +2587,11 @@ apply_handle_ddl(StringInfo s)
/* Make sure we are in a transaction command */
begin_replication_step();
- ddl_command = ddl_deparse_json_to_string(message);
+ if (strcmp(prefix, "deparse") == 0)
+ ddl_command = ddl_deparse_json_to_string(message);
+ else if (strcmp(prefix, "cmd") == 0)
+ ddl_command = message;
+
debug_query_string = ddl_command;
/* DestNone for logical replication */
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 7bf63dc4d4..a19b77c7ee 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1868,6 +1868,9 @@ pgoutput_ddlmessage(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
case DCT_ObjectDrop:
/* do nothing */
break;
+ case DCT_GlobalObjectCmd:
+ /* do nothing */
+ break;
default:
elog(ERROR, "unsupported type %d", cmdtype);
break;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 490b73b66e..3e9394e5fb 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -64,6 +64,7 @@
#include "postmaster/bgwriter.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteRemove.h"
+#include "replication/ddlmessage.h"
#include "storage/fd.h"
#include "tcop/pquery.h"
#include "tcop/utility.h"
@@ -532,6 +533,63 @@ ProcessUtility(PlannedStmt *pstmt,
dest, qc);
}
+/*
+ * Log a global object DDL command for logical replication
+ */
+static void
+LogGlobalObjectCommand(Node *parsetree, const char * queryString)
+{
+ switch (nodeTag(parsetree))
+ {
+ /* ROLE statements */
+ case T_CreateRoleStmt:
+ case T_AlterRoleStmt:
+ case T_AlterRoleSetStmt:
+ case T_DropRoleStmt:
+ case T_ReassignOwnedStmt:
+ case T_GrantRoleStmt:
+
+ /* Database statements */
+ case T_CreatedbStmt:
+ case T_AlterDatabaseStmt:
+ case T_AlterDatabaseRefreshCollStmt:
+ case T_AlterDatabaseSetStmt:
+ case T_DropdbStmt:
+
+ /* TableSpace statements */
+ case T_CreateTableSpaceStmt:
+ case T_DropTableSpaceStmt:
+ case T_AlterTableSpaceOptionsStmt:
+ if (XLogLogicalInfoActive())
+ {
+ LogLogicalDDLMessage("cmd",
+ InvalidOid,
+ DCT_GlobalObjectCmd,
+ queryString,
+ strlen(queryString) + 1);
+ }
+ break;
+ /* GrantStmt and RevokeStmt if the object is global object */
+ case T_GrantStmt:
+ {
+ GrantStmt *stmt = (GrantStmt *) parsetree;
+
+ if (!EventTriggerSupportsObjectType(stmt->objtype) &&
+ XLogLogicalInfoActive())
+ {
+ LogLogicalDDLMessage("cmd",
+ InvalidOid,
+ DCT_GlobalObjectCmd,
+ queryString,
+ strlen(queryString) + 1);
+ }
+ break;
+ }
+ default:
+ break;
+ }
+}
+
/*
* standard_ProcessUtility itself deals only with utility commands for
* which we do not provide event trigger support. Commands that do have
@@ -1077,6 +1135,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
}
+ LogGlobalObjectCommand(parsetree, queryString);
free_parsestate(pstate);
/*
diff --git a/src/include/replication/ddlmessage.h b/src/include/replication/ddlmessage.h
index a8dca863b5..f95c58eb78 100644
--- a/src/include/replication/ddlmessage.h
+++ b/src/include/replication/ddlmessage.h
@@ -26,7 +26,8 @@ typedef enum DeparsedCommandType
DCT_TableDropEnd,
DCT_TableAlter,
DCT_ObjectCreate,
- DCT_ObjectDrop
+ DCT_ObjectDrop,
+ DCT_GlobalObjectCmd
} DeparsedCommandType;
/*
diff --git a/src/test/subscription/t/032_ddl_replication.pl b/src/test/subscription/t/032_ddl_replication.pl
index 94de8edd08..d38b8d53e5 100644
--- a/src/test/subscription/t/032_ddl_replication.pl
+++ b/src/test/subscription/t/032_ddl_replication.pl
@@ -378,23 +378,23 @@ $node_publisher->safe_psql('postgres', "DROP TABLE tmp;");
# Test CREATE TABLE TABLESPACE (creating a tablespace is not replicated)
# Prepare the directories for the publisher and subscriber first.
-my ($basedir, $tablespace_dir);
-
-$basedir = $node_publisher->basedir;
-$tablespace_dir = "$basedir/tblspc_pub";
-mkdir($tablespace_dir);
-$node_publisher->safe_psql('postgres', "CREATE TABLESPACE mytblspc LOCATION '$tablespace_dir';");
-$basedir = $node_subscriber->basedir;
-$tablespace_dir = "$basedir/tblspc_sub";
-mkdir ($tablespace_dir);
-$node_subscriber->safe_psql('postgres', "CREATE TABLESPACE mytblspc LOCATION '$tablespace_dir';");
-
-$node_publisher->safe_psql('postgres', "CREATE TABLE tmp (id int) TABLESPACE mytblspc;");
-$node_publisher->safe_psql('postgres', "INSERT INTO tmp VALUES (1);");
-$node_publisher->wait_for_catchup('mysub');
-$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp;");
-is($result, qq(1), 'CREATE TABLE TABLESPACE replicated');
-$node_publisher->safe_psql('postgres', "DROP TABLE tmp;");
+#my ($basedir, $tablespace_dir);
+
+#$basedir = $node_publisher->basedir;
+#$tablespace_dir = "$basedir/tblspc_pub";
+#mkdir($tablespace_dir);
+#$node_publisher->safe_psql('postgres', "CREATE TABLESPACE mytblspc LOCATION '$tablespace_dir';");
+#$basedir = $node_subscriber->basedir;
+#$tablespace_dir = "$basedir/tblspc_sub";
+#mkdir ($tablespace_dir);
+#$node_subscriber->safe_psql('postgres', "CREATE TABLESPACE mytblspc LOCATION '$tablespace_dir';");
+
+#$node_publisher->safe_psql('postgres', "CREATE TABLE tmp (id int) TABLESPACE mytblspc;");
+#$node_publisher->safe_psql('postgres', "INSERT INTO tmp VALUES (1);");
+#$node_publisher->wait_for_catchup('mysub');
+#$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp;");
+#is($result, qq(1), 'CREATE TABLE TABLESPACE replicated');
+#$node_publisher->safe_psql('postgres', "DROP TABLE tmp;");
# Test CREATE TABLE OF (creating a type is not replicated)
$node_publisher->safe_psql('postgres', "CREATE TYPE mytype AS (id int, name text, age int);");
@@ -406,6 +406,36 @@ $result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp;");
is($result, qq(1), 'CREATE TABLE OF replicated');
$node_publisher->safe_psql('postgres', "DROP TABLE tmp");
+# Test CREATE ROLE is replicated
+$node_publisher->safe_psql('postgres', "CREATE ROLE test_user REPLICATION LOGIN;");
+$node_publisher->wait_for_catchup('mysub');
+$result = $node_subscriber->safe_psql('postgres', "SELECT COUNT(*) from pg_shadow where usename = 'test_user' and userepl = 't'");
+is($result, qq(1), 'CREATE ROLE replicated');
+
+# Test ALTER ROLE is replicated
+$node_publisher->safe_psql('postgres', "ALTER ROLE test_user NOREPLICATION LOGIN;");
+$node_publisher->wait_for_catchup('mysub');
+$result = $node_subscriber->safe_psql('postgres', "SELECT COUNT(*) from pg_shadow where usename = 'test_user' and userepl = 'f'");
+is($result, qq(1), 'ALTER ROLE replicated');
+
+# Test CREATE DATABASE is replicated
+$node_publisher->safe_psql('postgres', "CREATE DATABASE db1;");
+$node_publisher->wait_for_catchup('mysub');
+$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM pg_database WHERE datname = 'db1'");
+is($result, qq(1), 'CREATE DATABASE replicated');
+
+# Test ALTER DATABASE is replicated
+$node_publisher->safe_psql('postgres', "ALTER DATABASE db1 CONNECTION LIMIT 10;");
+$node_publisher->wait_for_catchup('mysub');
+$result = $node_subscriber->safe_psql('postgres', "SELECT datconnlimit FROM pg_database WHERE datname = 'db1'");
+is($result, qq(10), 'ALTER DATABASE is replicated');
+
+# Test DROP DATABASE is replicated
+$node_publisher->safe_psql('postgres', "DROP DATABASE db1;");
+$node_publisher->wait_for_catchup('mysub');
+$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM pg_database WHERE datname = 'db1'");
+is($result, qq(0), 'DROP DATABASE is replicated');
+
pass "DDL replication tests passed:";
$node_subscriber->stop;
--
2.37.1
On Tue, Aug 9, 2022 at 1:31 AM Zheng Li <zhengli10@gmail.com> wrote:
Hello,
Logical replication of DDL commands support is being worked on in [1].
However, global object commands are quite different from other
non-global object DDL commands and need to be handled differently. For
example, global object commands include ROLE statements, DATABASE
statements, TABLESPACE statements and a subset of GRANT/REVOKE
statements if the object being modified is a global object. These
commands are different from other DDL commands in that:1. Global object commands can be executed in any database.
2. Global objects are not schema qualified.
3. Global object commands are not captured by event triggers.I’ve put together a prototype to support logical replication of global
object commands in the attached patch. This patch builds on the DDL
replication patch from ZJ in [2] and must be applied on top of it.
Here is a list of global object commands that the patch replicate, you
can find more details in function LogGlobalObjectCommand:/* ROLE statements */
CreateRoleStmt
AlterRoleStmt
AlterRoleSetStmt
DropRoleStmt
ReassignOwnedStmt
GrantRoleStmt/* Database statements */
CreatedbStmt
AlterDatabaseStmt
AlterDatabaseRefreshCollStmt
AlterDatabaseSetStmt
DropdbStmt/* TableSpace statements */
CreateTableSpaceStmt
DropTableSpaceStmt
AlterTableSpaceOptionsStmt/* GrantStmt and RevokeStmt if objtype is a global object determined
by EventTriggerSupportsObjectType() */
GrantStmt
RevokeStmtThe idea with this patch is to support global objects commands
replication by WAL logging the command using the same function for DDL
logging - LogLogicalDDLMessage towards the end of
standard_ProcessUtility. Because global objects are not schema
qualified, we can skip the deparser invocation and directly log the
original command string for replay on the subscriber.A key problem to address is that global objects can become
inconsistent between the publisher and the subscriber if a command
modifying the global object gets executed in a database (on the source
side) that doesn't replicate the global object commands. I think we
can work on the following two aspects in order to avoid such
inconsistency:1. Introduce a publication option for global object commands
replication and document that logical replication of global object
commands is preferred to be enabled on all databases. Otherwise
inconsistency can happen if a command modifies the global object in a
database that doesn't replicate global object commands.For example, we could introduce the following publication option
publish_global_object_command :
CREATE PUBLICATION mypub
FOR ALL TABLES
WITH (publish = 'insert, delete, update', publish_global_object_command = true);
Tying global objects with FOR ALL TABLES seems odd to me. One possible
idea could be to introduce publications FOR ALL OBJECTS. However, I am
not completely sure whether tying global objects with
database-specific publications is what users would expect but OTOH I
don't have any better ideas here.
--
With Regards,
Amit Kapila.
On Fri, Aug 12, 2022 at 5:41 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Tue, Aug 9, 2022 at 1:31 AM Zheng Li <zhengli10@gmail.com> wrote:
Hello,
Logical replication of DDL commands support is being worked on in [1].
However, global object commands are quite different from other
non-global object DDL commands and need to be handled differently. For
example, global object commands include ROLE statements, DATABASE
statements, TABLESPACE statements and a subset of GRANT/REVOKE
statements if the object being modified is a global object. These
commands are different from other DDL commands in that:1. Global object commands can be executed in any database.
2. Global objects are not schema qualified.
3. Global object commands are not captured by event triggers.I’ve put together a prototype to support logical replication of global
object commands in the attached patch. This patch builds on the DDL
replication patch from ZJ in [2] and must be applied on top of it.
Here is a list of global object commands that the patch replicate, you
can find more details in function LogGlobalObjectCommand:/* ROLE statements */
CreateRoleStmt
AlterRoleStmt
AlterRoleSetStmt
DropRoleStmt
ReassignOwnedStmt
GrantRoleStmt/* Database statements */
CreatedbStmt
AlterDatabaseStmt
AlterDatabaseRefreshCollStmt
AlterDatabaseSetStmt
DropdbStmt/* TableSpace statements */
CreateTableSpaceStmt
DropTableSpaceStmt
AlterTableSpaceOptionsStmt/* GrantStmt and RevokeStmt if objtype is a global object determined
by EventTriggerSupportsObjectType() */
GrantStmt
RevokeStmtThe idea with this patch is to support global objects commands
replication by WAL logging the command using the same function for DDL
logging - LogLogicalDDLMessage towards the end of
standard_ProcessUtility. Because global objects are not schema
qualified, we can skip the deparser invocation and directly log the
original command string for replay on the subscriber.A key problem to address is that global objects can become
inconsistent between the publisher and the subscriber if a command
modifying the global object gets executed in a database (on the source
side) that doesn't replicate the global object commands. I think we
can work on the following two aspects in order to avoid such
inconsistency:1. Introduce a publication option for global object commands
replication and document that logical replication of global object
commands is preferred to be enabled on all databases. Otherwise
inconsistency can happen if a command modifies the global object in a
database that doesn't replicate global object commands.For example, we could introduce the following publication option
publish_global_object_command :
CREATE PUBLICATION mypub
FOR ALL TABLES
WITH (publish = 'insert, delete, update', publish_global_object_command = true);Tying global objects with FOR ALL TABLES seems odd to me. One possible
idea could be to introduce publications FOR ALL OBJECTS. However, I am
not completely sure whether tying global objects with
database-specific publications is what users would expect but OTOH I
don't have any better ideas here.
Can we think of relying to send WAL of such DDLs just based on whether
there is a corresponding publication (ex. publication of ALL OBJECTS)?
I mean avoid database-specific filtering in decoding for such DDL
commands but not sure how much better it is than the current proposal?
The other idea that occurred to me is to have separate event triggers
for global objects that we can store in the shared catalog but again
it is not clear how to specify the corresponding function as functions
are database specific.
Thoughts?
--
With Regards,
Amit Kapila.
Can we think of relying to send WAL of such DDLs just based on whether
there is a corresponding publication (ex. publication of ALL OBJECTS)?
I mean avoid database-specific filtering in decoding for such DDL
commands but not sure how much better it is than the current proposal?
I think a publication of ALL OBJECTS sounds intuitive. Does it mean we'll
publish all DDL commands, all commit and abort operations in every
database if there is such publication of ALL OBJECTS?
Best,
Zheng
On Tue, Aug 16, 2022 at 11:35 AM Zheng Li <zhengli10@gmail.com> wrote:
Can we think of relying to send WAL of such DDLs just based on whether
there is a corresponding publication (ex. publication of ALL OBJECTS)?
I mean avoid database-specific filtering in decoding for such DDL
commands but not sure how much better it is than the current proposal?I think a publication of ALL OBJECTS sounds intuitive. Does it mean we'll
publish all DDL commands, all commit and abort operations in every
database if there is such publication of ALL OBJECTS?
Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.
--
With Regards,
Amit Kapila.
I think a publication of ALL OBJECTS sounds intuitive. Does it mean we'll
publish all DDL commands, all commit and abort operations in every
database if there is such publication of ALL OBJECTS?Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.
I think ultimately we need a clean and efficient way to publish (and
subscribe to) any changes in all databases, preferably in one logical
replication slot.
--
Regards,
Zheng
On Tue, Aug 30, 2022 at 8:09 AM Zheng Li <zhengli10@gmail.com> wrote:
I think a publication of ALL OBJECTS sounds intuitive. Does it mean we'll
publish all DDL commands, all commit and abort operations in every
database if there is such publication of ALL OBJECTS?Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.I think ultimately we need a clean and efficient way to publish (and
subscribe to) any changes in all databases, preferably in one logical
replication slot.
Agreed. I was thinking currently for logical replication both
walsender and slot are database-specific. So we need a way to
distinguish the WAL for global objects and then avoid filtering based
on the slot's database during decoding. I also thought about whether
we want to have a WALSender that is not connected to a database for
the replication of global objects but I couldn't come up with a reason
for doing so. Do you have any thoughts on this matter?
--
With Regards,
Amit Kapila.
On Thu, Feb 16, 2023 at 12:02 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Tue, Aug 30, 2022 at 8:09 AM Zheng Li <zhengli10@gmail.com> wrote:
I think a publication of ALL OBJECTS sounds intuitive. Does it mean we'll
publish all DDL commands, all commit and abort operations in every
database if there is such publication of ALL OBJECTS?Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.I think ultimately we need a clean and efficient way to publish (and
subscribe to) any changes in all databases, preferably in one logical
replication slot.Agreed. I was thinking currently for logical replication both
walsender and slot are database-specific. So we need a way to
distinguish the WAL for global objects and then avoid filtering based
on the slot's database during decoding. I also thought about whether
we want to have a WALSender that is not connected to a database for
the replication of global objects but I couldn't come up with a reason
for doing so. Do you have any thoughts on this matter?
Another thing about the patch proposed here is that it LOGs the DDL
for global objects without any consideration of whether that is
required for logical replication. This is quite unlike what we are
planning to do for other DDLs where it will be logged only when the
publication has defined an event trigger for it.
--
With Regards,
Amit Kapila.
Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.I think ultimately we need a clean and efficient way to publish (and
subscribe to) any changes in all databases, preferably in one logical
replication slot.Agreed. I was thinking currently for logical replication both
walsender and slot are database-specific. So we need a way to
distinguish the WAL for global objects and then avoid filtering based
on the slot's database during decoding.
But which WALSender should handle the WAL for global objects if we
don't filter by database? Is there any specific problem you see for
decoding global objects commands in a database specific WALSender?
I also thought about whether
we want to have a WALSender that is not connected to a database for
the replication of global objects but I couldn't come up with a reason
for doing so. Do you have any thoughts on this matter?
Regards,
Zane
On Fri, Feb 17, 2023 at 10:58 AM Zheng Li <zhengli10@gmail.com> wrote:
Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.I think ultimately we need a clean and efficient way to publish (and
subscribe to) any changes in all databases, preferably in one logical
replication slot.Agreed. I was thinking currently for logical replication both
walsender and slot are database-specific. So we need a way to
distinguish the WAL for global objects and then avoid filtering based
on the slot's database during decoding.But which WALSender should handle the WAL for global objects if we
don't filter by database? Is there any specific problem you see for
decoding global objects commands in a database specific WALSender?
I haven't verified but I was concerned about the below check:
logicalddl_decode
{
...
+
+ if (message->dbId != ctx->slot->data.database ||--
With Regards,
Amit Kapila.
On Fri, Feb 17, 2023 at 4:48 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Feb 17, 2023 at 10:58 AM Zheng Li <zhengli10@gmail.com> wrote:
Actually, I intend something for global objects. But the main thing
that is worrying me about this is that we don't have a clean way to
untie global object replication from database-specific object
replication.I think ultimately we need a clean and efficient way to publish (and
subscribe to) any changes in all databases, preferably in one logical
replication slot.Agreed. I was thinking currently for logical replication both
walsender and slot are database-specific. So we need a way to
distinguish the WAL for global objects and then avoid filtering based
on the slot's database during decoding.But which WALSender should handle the WAL for global objects if we
don't filter by database? Is there any specific problem you see for
decoding global objects commands in a database specific WALSender?I haven't verified but I was concerned about the below check: logicalddl_decode { ... + + if (message->dbId != ctx->slot->data.database ||
OK, let's suppose we don't filter by database for global commands when
decoding ddl records, roughly what the following code does:
logicalddl_decode
{
...
if (message->dbId != ctx->slot->data.database ||
+ message->cmdtype != DCT_GlobalObjectCmd
But this is not enough, we also need the subsequent commit record of
the txn to be decoded in order to replicate the global command. So I
think we also need to make DecodeCommit bypass the filter by database
if global object replication is turned on and we have decoded a global
command in the txn.
Regards,
Zane
Hi,
On Tue, Aug 9, 2022 at 5:01 AM Zheng Li <zhengli10@gmail.com> wrote:
Hello,
Logical replication of DDL commands support is being worked on in [1].
However, global object commands are quite different from other
non-global object DDL commands and need to be handled differently. For
example, global object commands include ROLE statements, DATABASE
statements, TABLESPACE statements and a subset of GRANT/REVOKE
statements if the object being modified is a global object. These
commands are different from other DDL commands in that:1. Global object commands can be executed in any database.
2. Global objects are not schema qualified.
3. Global object commands are not captured by event triggers.I’ve put together a prototype to support logical replication of global
object commands in the attached patch. This patch builds on the DDL
replication patch from ZJ in [2] and must be applied on top of it.
Here is a list of global object commands that the patch replicate, you
can find more details in function LogGlobalObjectCommand:/* ROLE statements */
CreateRoleStmt
AlterRoleStmt
AlterRoleSetStmt
DropRoleStmt
ReassignOwnedStmt
GrantRoleStmt/* Database statements */
CreatedbStmt
AlterDatabaseStmt
AlterDatabaseRefreshCollStmt
AlterDatabaseSetStmt
DropdbStmt/* TableSpace statements */
CreateTableSpaceStmt
DropTableSpaceStmt
AlterTableSpaceOptionsStmt/* GrantStmt and RevokeStmt if objtype is a global object determined
by EventTriggerSupportsObjectType() */
GrantStmt
RevokeStmtThe idea with this patch is to support global objects commands
replication by WAL logging the command using the same function for DDL
logging - LogLogicalDDLMessage towards the end of
standard_ProcessUtility. Because global objects are not schema
qualified, we can skip the deparser invocation and directly log the
original command string for replay on the subscriber.A key problem to address is that global objects can become
inconsistent between the publisher and the subscriber if a command
modifying the global object gets executed in a database (on the source
side) that doesn't replicate the global object commands. I think we
can work on the following two aspects in order to avoid such
inconsistency:1. Introduce a publication option for global object commands
replication and document that logical replication of global object
commands is preferred to be enabled on all databases. Otherwise
inconsistency can happen if a command modifies the global object in a
database that doesn't replicate global object commands.For example, we could introduce the following publication option
publish_global_object_command :
CREATE PUBLICATION mypub
FOR ALL TABLES
WITH (publish = 'insert, delete, update', publish_global_object_command = true);We may consider other fine tuned global command options such as
“publish_role_statements”, “publish_database_statements”,
“publish_tablespace_statements” and "publish_grant_statements", i.e.
you pick which global commands you want replicated. For example, you
can do this if you need a permission or tablespace to be set up
differently on the target cluster. In addition, we may need to adjust
the syntax once the DDL replication syntax finalizes.2. Introduce the following database cluster level logical replication
commands to avoid such inconsistency, this is especially handy when
there is a large number of databases to configure for logical
replication.CREATE PUBLICATION GROUP mypub_
FOR ALL DATABASES
WITH (publish = 'insert, delete, update', publish_global_object_command = true);CREATE SUBSCRIPTION GROUP mysub_
CONNECTION 'dbnames = \“path to file\” host=hostname user=username port=5432'
PUBLICATION GROUP mypub_;Under the hood, the CREATE PUBLICATION GROUP command generates one
CREATE PUBLICATION mypub_n sub-command for each database in the
cluster where n is a monotonically increasing integer from 1. The
command outputs the (dbname, publication name) pairs which can be
saved in a file and then used on the subscription side.Similarly, the CREATE SUBSCRIPTION GROUP command will generate one
CREATE SUBSCRIPTION mysub_n sub-command for each database in the
dbnames file. The dbnames file contains the (dbname, publication name)
pairs which come from the output of the CREATE PUBLICATION GROUP
command. Notice the connection string doesn’t have the dbname field,
During execution the connection string will be appended the dbname
retrieved from the dbnames file. By default the target DB name is the
same as the source DB name, optionally user can specify the source_db
to target_db mapping in the dbnames file.In addition, we might want to create dependencies for the
publications/subscriptions created by the above commands in order to
guarantee the group consistency. Also we need to enforce that there is
only one group of publications/subscriptions for database cluster
level replication.Logical replication of all commands across an entire cluster (instead
of on a per-database basis) is a separate topic. We can start another
thread after implementing a prototype.Please let me know your thoughts.
Thank you for working on this item.
I think that there are some (possibly) tricky challenges that haven't
been discussed yet to support replicating global objects.
First, as for publications having global objects (roles, databases,
and tablespaces), but storing them in database specific tables like
pg_publication doesn't make sense, because it should be at some shared
place where all databases can have access to it. Maybe we need to have
a shared catalog like pg_shpublication or pg_publication_role to store
publications related to global objects or the relationship between
such publications and global objects. Second, we might need to change
the logical decoding infrastructure so that it's aware of shared
catalog changes. Currently we need to scan only db-specific catalogs.
Finally, since we process CREATE DATABASE in a different way than
other DDLs (by cloning another database such as template1), simply
replicating the CREATE DATABASE statement would not produce the same
results as the publisher. Also, since event triggers are not fired on
DDLs for global objects, always WAL-logging such DDL statements like
the proposed patch does is not a good idea.
Given that there seems to be some tricky problems and there is a
discussion for cutting the scope to make the initial patch small[1]/messages/by-id/CAA4eK1K3VXfTWXbLADcH81J==7ussvNdqLFHN68sEokDPueu7w@mail.gmail.com, I
think it's better to do this work after the first version.
Regards,
[1]: /messages/by-id/CAA4eK1K3VXfTWXbLADcH81J==7ussvNdqLFHN68sEokDPueu7w@mail.gmail.com
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
I think that there are some (possibly) tricky challenges that haven't
been discussed yet to support replicating global objects.First, as for publications having global objects (roles, databases,
and tablespaces), but storing them in database specific tables like
pg_publication doesn't make sense, because it should be at some shared
place where all databases can have access to it. Maybe we need to have
a shared catalog like pg_shpublication or pg_publication_role to store
publications related to global objects or the relationship between
such publications and global objects. Second, we might need to change
the logical decoding infrastructure so that it's aware of shared
catalog changes.
Thanks for the feedback. This is insightful.
Currently we need to scan only db-specific catalogs.
Finally, since we process CREATE DATABASE in a different way than
other DDLs (by cloning another database such as template1), simply
replicating the CREATE DATABASE statement would not produce the same
results as the publisher. Also, since event triggers are not fired on
DDLs for global objects, always WAL-logging such DDL statements like
the proposed patch does is not a good idea.
Given that there seems to be some tricky problems and there is a
discussion for cutting the scope to make the initial patch small[1], I
think it's better to do this work after the first version.
Agreed.
Regards,
Zane