Re: Support logical replication of global object commands

Hi,

On Tue, Aug 9, 2022 at 5:01 AM Zheng Li <zhengli10(at)gmail(dot)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
> 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.

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], I
think it's better to do this work after the first version.

Regards,

[1] https://www.postgresql.org/message-id/CAA4eK1K3VXfTWXbLADcH81J%3D%3D7ussvNdqLFHN68sEokDPueu7w%40mail.gmail.com

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com