Re: Support logical replication of DDLs - Mailing list pgsql-hackers

On Sat, Jul 23, 2022 at 2:49 AM Zheng Li <zhengli10@gmail.com> wrote:
>
> Hello,
>
> Here is a patch that supports replication of global object commands,
> these include ROLE statements, database statements and tablespace statements.
> The patch should be applied on top of the v13 DDL replication patch set that
> ZJ Hou sent in the previous email.
>
> 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 -
> LogLogicalDDLMessage, towards the end of standard_ProcessUtility.
>

I noticed that LogLogicalDDLMessage() uses MyDatabaseId and then
decoding can take some action (filtering) based on that. Is it Okay to
use that function for global objects, if so, you might want to add a
comment for the same?

> 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 is global objects can get inconsistent between the
> publisher and the subscriber if a command changes the global object
> in a database (on the source side) which doesn't configure logical replication.
> I think we can work on the following directions in order to avoid such
> inconsistency:
>
> 1. Introduce a publication option for global objects command replication
> and document that logical replication of global objects commands is preferred
> to be configured on all databases. Otherwise inconsistency can happen
> if a command changes the global object in a database which doesn't configure
> logical replication.
>
> 2. Introduce database cluster level logical replication to avoid such
> inconsistency,
> this is especially handy when there is a large number of databases to
> configure for logical
> replication.
>

In general, I agree with your comment below that we can work on this
after we have some more concrete plans/discussions. I think we can
probably consider this when we have more discussion around the
publication commands for the DDL objects. However, it would be good if
you can add some more details about the above two options.

As per my understanding, the overall work on this project includes the
following sub-tasks:
a. DDL Deparsing: This is required to support DDL replication of
non-global objects. The work for this is in progress, this is based on
prior work by Alvaro.
b. DDL Replication: This includes replication of DDL commands based on
event triggers and DDL deparsing. The work on this is also in
progress.
c. DDL Replication of global objects: It requires a different approach
due to the reasons quoted above in your email. Zheng has started
working on it.
d. Initial Sync: I think there is a brief discussion about this in the
thread but no concrete proposal yet. I think it is important to solve
this part of the puzzle as well to have an overall design ready for
this project. Do let me know if you, Sawada-San, or anybody else
intends to work on it? I think that will avoid overlap of work.

-- 
With Regards,
Amit Kapila.