RE: Initial Schema Sync for Logical Replication

> From: vignesh C > Sent: Thursday, October 19, 2023 10:41 AM > Can you rebase the patch and post the complete set of required changes for > the concurrent DDL, I will have a look at them. Sure , I will try to send the complete rebased patch within a week. Regards Sachin

Re: Initial Schema Sync for Logical Replication

On Fri, 7 Jul 2023 at 12:41, Masahiko Sawada wrote: > > On Wed, Jul 5, 2023 at 11:14 AM Masahiko Sawada wrote: > > > > On Mon, Jun 19, 2023 at 5:29 PM Peter Smith wrote: > > > > > > Hi, > > > > > > Below are my review comments for the PoC patch 0001. > > > > > > In addition, the patch needed

Re: Initial Schema Sync for Logical Replication

On Thu, 31 Aug 2023 at 17:18, Kumar, Sachin wrote: > > Hi Everyone, based on internal discussion with Masahiko > I have implemented concurrent DDL support for initial schema sync. > > Concurrent Patch workflow > > 1. When TableSync worker creates a replicaton slot, It will > save the slot lsn

RE: Initial Schema Sync for Logical Replication

Hi Everyone, based on internal discussion with Masahiko I have implemented concurrent DDL support for initial schema sync. Concurrent Patch workflow 1. When TableSync worker creates a replicaton slot, It will save the slot lsn into pg_subscription_rel with SUBREL_SYNC_SCHEMA_DATA_SYNC state, and

Re: Initial Schema Sync for Logical Replication

On Mon, Jul 10, 2023 at 8:06 PM Kumar, Sachin wrote: > > > > > From: Amit Kapila > > On Wed, Jul 5, 2023 at 7:45 AM Masahiko Sawada > > wrote: > > > > > > On Mon, Jun 19, 2023 at 5:29 PM Peter Smith > > wrote: > > > > > > > > Hi, > > > > > > > > Below are my review comments for the PoC patch

RE: Initial Schema Sync for Logical Replication

> From: Amit Kapila > On Wed, Jul 5, 2023 at 7:45 AM Masahiko Sawada > wrote: > > > > On Mon, Jun 19, 2023 at 5:29 PM Peter Smith > wrote: > > > > > > Hi, > > > > > > Below are my review comments for the PoC patch 0001. > > > > > > In addition, the patch needed rebasing, and, after I rebased

Re: Initial Schema Sync for Logical Replication

On Wed, Jul 5, 2023 at 7:45 AM Masahiko Sawada wrote: > > On Mon, Jun 19, 2023 at 5:29 PM Peter Smith wrote: > > > > Hi, > > > > Below are my review comments for the PoC patch 0001. > > > > In addition, the patch needed rebasing, and, after I rebased it > > locally in my private environment

RE: Initial Schema Sync for Logical Replication

> From: Masahiko Sawada > So I've implemented a different approach; doing schema synchronization at a > CREATE SUBSCRIPTION time. The backend executing CREATE SUBSCRIPTION > uses pg_dump and restores the table schemas including both partitioned tables > and their partitions regardless of

Re: Initial Schema Sync for Logical Replication

On Wed, Jul 5, 2023 at 11:14 AM Masahiko Sawada wrote: > > On Mon, Jun 19, 2023 at 5:29 PM Peter Smith wrote: > > > > Hi, > > > > Below are my review comments for the PoC patch 0001. > > > > In addition, the patch needed rebasing, and, after I rebased it > > locally in my private environment

Re: Initial Schema Sync for Logical Replication

On Mon, Jun 19, 2023 at 5:29 PM Peter Smith wrote: > > Hi, > > Below are my review comments for the PoC patch 0001. > > In addition, the patch needed rebasing, and, after I rebased it > locally in my private environment there were still test failures: > a) The 'make check' tests fail but only in

Re: Initial Schema Sync for Logical Replication

Hi, Below are my review comments for the PoC patch 0001. In addition, the patch needed rebasing, and, after I rebased it locally in my private environment there were still test failures: a) The 'make check' tests fail but only in a minor way due to changes colname b) the subscription TAP test

Re: Initial Schema Sync for Logical Replication

On Thu, Jun 15, 2023 at 4:14 PM Peter Smith wrote: > > On Thu, Jun 8, 2023 at 1:24 PM Masahiko Sawada wrote: > > > ... > > > We also need to research how to integrate the initial schema > > synchronization with tablesync workers. We have a PoC patch[2]. > > > > Regards, > > > > [1] > >

Re: Initial Schema Sync for Logical Replication

On Thu, Jun 8, 2023 at 1:24 PM Masahiko Sawada wrote: > ... > We also need to research how to integrate the initial schema > synchronization with tablesync workers. We have a PoC patch[2]. > > Regards, > > [1] > https://wiki.postgresql.org/wiki/Logical_replication_of_DDLs#Initial_Schema_Sync >

Re: Initial Schema Sync for Logical Replication

On Tue, May 23, 2023 at 2:31 PM Amit Kapila wrote: > > On Mon, May 22, 2023 at 6:37 AM Masahiko Sawada wrote: > > > > On Fri, Apr 28, 2023 at 4:16 PM Masahiko Sawada > > wrote: > > > Yes, in this approach, we need to dump/restore objects while > > > specifying with fine granularity. Ideally,

Re: Initial Schema Sync for Logical Replication

On Mon, May 22, 2023 at 6:37 AM Masahiko Sawada wrote: > > On Fri, Apr 28, 2023 at 4:16 PM Masahiko Sawada wrote: > > Yes, in this approach, we need to dump/restore objects while > > specifying with fine granularity. Ideally, the table sync worker dumps > > and restores the table schema, does

Re: Initial Schema Sync for Logical Replication

On Fri, Apr 28, 2023 at 4:16 PM Masahiko Sawada wrote: > Yes, in this approach, we need to dump/restore objects while > specifying with fine granularity. Ideally, the table sync worker dumps > and restores the table schema, does copy the initial data, and then > creates indexes, and triggers and

Re: Initial Schema Sync for Logical Replication

On Thu, Apr 27, 2023 at 12:02 PM Wei Wang (Fujitsu) wrote: > > On Fri, Apr 21, 2023 at 16:48 PM Masahiko Sawada > wrote: > > On Thu, Apr 20, 2023 at 8:16 PM Amit Kapila wrote: > > > > > > On Mon, Apr 17, 2023 at 9:12 AM Masahiko Sawada > > wrote: > > > > > > > > On Fri, Apr 7, 2023 at 6:37 PM

RE: Initial Schema Sync for Logical Replication

On Fri, Apr 21, 2023 at 16:48 PM Masahiko Sawada wrote: > On Thu, Apr 20, 2023 at 8:16 PM Amit Kapila wrote: > > > > On Mon, Apr 17, 2023 at 9:12 AM Masahiko Sawada > wrote: > > > > > > On Fri, Apr 7, 2023 at 6:37 PM Amit Kapila > > > wrote: > > > > > > > > On Thu, Apr 6, 2023 at 6:57 PM

Re: Initial Schema Sync for Logical Replication

On Thu, Apr 20, 2023 at 8:16 PM Amit Kapila wrote: > > On Mon, Apr 17, 2023 at 9:12 AM Masahiko Sawada wrote: > > > > On Fri, Apr 7, 2023 at 6:37 PM Amit Kapila wrote: > > > > > > On Thu, Apr 6, 2023 at 6:57 PM Masahiko Sawada > > > wrote: > > > > > > > > > > > > While writing a PoC patch, I

Re: Initial Schema Sync for Logical Replication

On Thu, Apr 20, 2023 at 9:41 PM Kumar, Sachin wrote: > > I am working on a prototype with above discussed idea, I think I will send it > for initial review by Monday. > Okay, but which idea are you referring to? pg_subscription_remote_rel + worker_pid idea Amit proposed? Regards, -- Masahiko

Re: Initial Schema Sync for Logical Replication

I am working on a prototype with above discussed idea, I think I will send it for initial review by Monday. Regards Sachin

Re: Initial Schema Sync for Logical Replication

On Mon, Apr 17, 2023 at 9:12 AM Masahiko Sawada wrote: > > On Fri, Apr 7, 2023 at 6:37 PM Amit Kapila wrote: > > > > On Thu, Apr 6, 2023 at 6:57 PM Masahiko Sawada > > wrote: > > > > > > > > > While writing a PoC patch, I found some difficulties in this idea. > > > First, I tried to add

RE: Initial Schema Sync for Logical Replication

I am working on a prototype with above Idea , and will send it for review by Sunday/Monday Regards Sachin

RE: Initial Schema Sync for Logical Replication

> From: Masahiko Sawada > > > While writing a PoC patch, I found some difficulties in this idea. > > > First, I tried to add schemaname+relname to pg_subscription_rel but > > > I could not define the primary key of pg_subscription_rel. The > > > primary key on (srsubid, srrelid) doesn't work

Re: Initial Schema Sync for Logical Replication

On Fri, Apr 7, 2023 at 6:37 PM Amit Kapila wrote: > > On Thu, Apr 6, 2023 at 6:57 PM Masahiko Sawada wrote: > > > > On Thu, Mar 30, 2023 at 10:11 PM Masahiko Sawada > > wrote: > > > > > > On Thu, Mar 30, 2023 at 12:18 AM Masahiko Sawada > > > wrote: > > > > > > > > > > > > How can we

Re: Initial Schema Sync for Logical Replication

On Thu, Apr 6, 2023 at 6:57 PM Masahiko Sawada wrote: > > On Thu, Mar 30, 2023 at 10:11 PM Masahiko Sawada > wrote: > > > > On Thu, Mar 30, 2023 at 12:18 AM Masahiko Sawada > > wrote: > > > > > > > > > How can we postpone creating the pg_subscription_rel entries until the > > > tablesync

Re: Initial Schema Sync for Logical Replication

On Thu, Mar 30, 2023 at 10:11 PM Masahiko Sawada wrote: > > On Thu, Mar 30, 2023 at 12:18 AM Masahiko Sawada > wrote: > > > > On Wed, Mar 29, 2023 at 7:57 PM Kumar, Sachin wrote: > > > > > > > > > > From: Amit Kapila > > > > > > > > I think we won't be able to use same snapshot because the >

RE: Initial Schema Sync for Logical Replication

> From: Masahiko Sawada > > > > > > 3. The apply worker launches the tablesync workers for tables that > > > need to be synchronized. > > > > > > There might be DDLs executed on the publisher for tables before the > > > tablesync worker starts. But the apply worker needs to apply DDLs > > > for

Re: Initial Schema Sync for Logical Replication

On Mon, Apr 3, 2023 at 3:54 PM Kumar, Sachin wrote: > > > > > -Original Message- > > From: Masahiko Sawada > > > > I was thinking each TableSync process will call pg_dump --table, > > > > This way if we have N tableSync process, we can have N pg_dump -- > > table=table_name called in

RE: Initial Schema Sync for Logical Replication

> -Original Message- > From: Masahiko Sawada > > > I was thinking each TableSync process will call pg_dump --table, > > > This way if we have N tableSync process, we can have N pg_dump -- > table=table_name called in parallel. > > > In fact we can use --schema-only to get schema and

Re: Initial Schema Sync for Logical Replication

On Thu, Mar 30, 2023 at 12:18 AM Masahiko Sawada wrote: > > On Wed, Mar 29, 2023 at 7:57 PM Kumar, Sachin wrote: > > > > > > > > From: Amit Kapila > > > > > > > I think we won't be able to use same snapshot because the > > > > > > > transaction will be committed. > > > > > > > In

RE: Initial Schema Sync for Logical Replication

> From: Masahiko Sawada > > > > One related idea is that currently, we fetch the table list > > corresponding to publications in subscription and create the entries > > for those in pg_subscription_rel during Create Subscription, can we > > think of postponing that work till after the initial

Re: Initial Schema Sync for Logical Replication

On Wed, Mar 29, 2023 at 7:57 PM Kumar, Sachin wrote: > > > > > > From: Amit Kapila > > > > > > I think we won't be able to use same snapshot because the > > > > > > transaction will be committed. > > > > > > In CreateSubscription() we can use the transaction snapshot from > > > > > >

Re: Initial Schema Sync for Logical Replication

On Tue, Mar 28, 2023 at 8:30 PM Masahiko Sawada wrote: > > On Tue, Mar 28, 2023 at 6:47 PM Amit Kapila wrote: > > > > > > > > > > > > I think we can have same issues as you mentioned New table t1 is > > > > > > added > > > > > > to the publication , User does a refresh publication. > > > > > >

RE: Initial Schema Sync for Logical Replication

> > > > From: Amit Kapila > > > > > I think we won't be able to use same snapshot because the > > > > > transaction will be committed. > > > > > In CreateSubscription() we can use the transaction snapshot from > > > > > walrcv_create_slot() till walrcv_disconnect() is called.(I am > > > > > not

Re: Initial Schema Sync for Logical Replication

On Tue, Mar 28, 2023 at 6:47 PM Amit Kapila wrote: > > On Mon, Mar 27, 2023 at 8:17 AM Masahiko Sawada wrote: > > > > On Fri, Mar 24, 2023 at 11:51 PM Kumar, Sachin wrote: > > > > > > > From: Amit Kapila > > > > > I think we won't be able to use same snapshot because the transaction > > > > >

Re: Initial Schema Sync for Logical Replication

On Mon, Mar 27, 2023 at 8:17 AM Masahiko Sawada wrote: > > On Fri, Mar 24, 2023 at 11:51 PM Kumar, Sachin wrote: > > > > > From: Amit Kapila > > > > I think we won't be able to use same snapshot because the transaction > > > > will > > > > be committed. > > > > In CreateSubscription() we can

Re: Initial Schema Sync for Logical Replication

On Fri, Mar 24, 2023 at 11:51 PM Kumar, Sachin wrote: > > > From: Amit Kapila > > > I think we won't be able to use same snapshot because the transaction will > > > be committed. > > > In CreateSubscription() we can use the transaction snapshot from > > > walrcv_create_slot() till

RE: Initial Schema Sync for Logical Replication

On Friday, March 24, 2023 11:01 PM Euler Taveira wrote: Hi, > On Fri, Mar 24, 2023, at 8:57 AM, mailto:houzj.f...@fujitsu.com wrote: > > First, I think the current publisher doesn't know the version number of > > client(subscriber) so we need to check the feasibility of same. Also, having > >

RE: Initial Schema Sync for Logical Replication

> I am not if it's feasible to support the use case the replicate DDL to old > subscriber. > +1 > First, I think the current publisher doesn't know the version number of > client(subscriber) so we need to check the feasibility of same. Also, having > client's version number checks doesn't seem

Re: Initial Schema Sync for Logical Replication

On Fri, Mar 24, 2023, at 8:57 AM, houzj.f...@fujitsu.com wrote: > First, I think the current publisher doesn't know the version number of > client(subscriber) so we need to check the feasibility of same. Also, having > client's version number checks doesn't seem to be a good idea.

RE: Initial Schema Sync for Logical Replication

> From: Amit Kapila > > I think we won't be able to use same snapshot because the transaction will > > be committed. > > In CreateSubscription() we can use the transaction snapshot from > > walrcv_create_slot() till walrcv_disconnect() is called.(I am not sure > > about this part maybe

RE: Initial Schema Sync for Logical Replication

On Friday, March 24, 2023 12:02 AM Euler Taveira wrote: > > On Thu, Mar 23, 2023, at 8:44 AM, Amit Kapila wrote: > > On Thu, Mar 23, 2023 at 2:48 AM Euler Taveira > > wrote: > > > > > > On Tue, Mar 21, 2023, at 8:18 AM, Amit Kapila wrote: > > > > > > Now, how do we

Re: Initial Schema Sync for Logical Replication

On Thu, Mar 23, 2023 at 9:24 PM Kumar, Sachin wrote: > > > From: Amit Kapila > > IIUC, this is possible only if tablesync process uses a snapshot different > > than the > > snapshot we have used to perform the initial schema sync, otherwise, this > > shouldn't be a problem. Let me try to

Re: Initial Schema Sync for Logical Replication

On Thu, Mar 23, 2023, at 8:44 AM, Amit Kapila wrote: > On Thu, Mar 23, 2023 at 2:48 AM Euler Taveira wrote: > > > > On Tue, Mar 21, 2023, at 8:18 AM, Amit Kapila wrote: > > > > Now, how do we avoid these problems even if we have our own version of > > functionality similar to pg_dump for selected

RE: Initial Schema Sync for Logical Replication

> From: Amit Kapila > IIUC, this is possible only if tablesync process uses a snapshot different > than the > snapshot we have used to perform the initial schema sync, otherwise, this > shouldn't be a problem. Let me try to explain my understanding with an example > (the LSNs used are just

Re: Initial Schema Sync for Logical Replication

On Thu, Mar 23, 2023 at 2:48 AM Euler Taveira wrote: > > On Tue, Mar 21, 2023, at 8:18 AM, Amit Kapila wrote: > > Now, how do we avoid these problems even if we have our own version of > functionality similar to pg_dump for selected objects? I guess we will > face similar problems. If so, we may

Re: Initial Schema Sync for Logical Replication

On Wed, Mar 15, 2023 at 11:12 PM Kumar, Sachin wrote: > > > Concurrent DDL :- > > User can execute a DDL command to table t1 at the same time when subscriber > is trying to sync > > it. pictorial representation https://imgur.com/a/ivrIEv8 [1] > > > > In tablesync process, it makes a connection

Re: Initial Schema Sync for Logical Replication

On Tue, Mar 21, 2023, at 8:18 AM, Amit Kapila wrote: > Now, how do we avoid these problems even if we have our own version of > functionality similar to pg_dump for selected objects? I guess we will > face similar problems. If so, we may need to deny schema sync in any > such case. There are 2

Re: Initial Schema Sync for Logical Replication

> > Yes. Do we have any concrete use case where the subscriber is an older > > version, in the first place? > > > > As per my understanding, it is mostly due to the reason that it can > work today. Today, during an off-list discussion with Jonathan on this > point, he pointed me to a similar

Re: Initial Schema Sync for Logical Replication

On Wed, Mar 22, 2023 at 2:16 PM Amit Kapila wrote: > > On Wed, Mar 22, 2023 at 8:29 AM Masahiko Sawada wrote: > > > > On Tue, Mar 21, 2023 at 8:18 PM Amit Kapila wrote: > > > > > > On Tue, Mar 21, 2023 at 7:32 AM Euler Taveira wrote: > > > > > > > You should > > > > exclude them removing these

RE: Initial Schema Sync for Logical Replication

> From: Amit Kapila > Sent: Wednesday, March 22, 2023 5:16 AM > To: Masahiko Sawada > Cc: Euler Taveira ; Kumar, Sachin > ; Alvaro Herrera ; pgsql- > hack...@lists.postgresql.org; Jonathan S. Katz > Subject: RE: [EXTERNAL]Initial Schema Sync for Logical Replication >

RE: Initial Schema Sync for Logical Replication

On Wednesday, March 22, 2023 1:16 PM Amit Kapila wrote: > > On Wed, Mar 22, 2023 at 8:29 AM Masahiko Sawada > wrote: > > > > On Tue, Mar 21, 2023 at 8:18 PM Amit Kapila > wrote: > > > > > > On Tue, Mar 21, 2023 at 7:32 AM Euler Taveira wrote: > > > > > > > You should > > > > exclude them

Re: Initial Schema Sync for Logical Replication

On Wed, Mar 22, 2023 at 8:29 AM Masahiko Sawada wrote: > > On Tue, Mar 21, 2023 at 8:18 PM Amit Kapila wrote: > > > > On Tue, Mar 21, 2023 at 7:32 AM Euler Taveira wrote: > > > > > You should > > > exclude them removing these objects from the TOC before running > > > pg_restore or > > > adding

Re: Initial Schema Sync for Logical Replication

On Tue, Mar 21, 2023 at 8:18 PM Amit Kapila wrote: > > On Tue, Mar 21, 2023 at 7:32 AM Euler Taveira wrote: > > > > On Mon, Mar 20, 2023, at 10:10 PM, Kumar, Sachin wrote: > > > > > From: Alvaro Herrera > > > Subject: RE: [EXTERNAL]Initial Schema Sync

Re: Initial Schema Sync for Logical Replication

On Tue, Mar 21, 2023 at 7:32 AM Euler Taveira wrote: > > On Mon, Mar 20, 2023, at 10:10 PM, Kumar, Sachin wrote: > > > From: Alvaro Herrera > > Subject: RE: [EXTERNAL]Initial Schema Sync for Logical Replication > > On 2023-Mar-15, Kumar, Sachin wrote: > > > &

Re: Initial Schema Sync for Logical Replication

On Mon, Mar 20, 2023, at 10:10 PM, Kumar, Sachin wrote: > > From: Alvaro Herrera > > Subject: RE: [EXTERNAL]Initial Schema Sync for Logical Replication > > On 2023-Mar-15, Kumar, Sachin wrote: > > > > > 1. In CreateSubscription() when we create replicat

RE: Initial Schema Sync for Logical Replication

Hi Alvaro, > From: Alvaro Herrera > Subject: RE: [EXTERNAL]Initial Schema Sync for Logical Replication > On 2023-Mar-15, Kumar, Sachin wrote: > > > 1. In CreateSubscription() when we create replication > > slot(walrcv_create_slot()), should use CRS_EXPORT_SNAPSH

RE: Initial Schema Sync for Logical Replication

Hi Amit, > From: Amit Kapila > > > Hi, > > > > > > I have a couple of questions. > > > > > > Q1. > > > > > > What happens if the subscriber already has some tables present? For > > > example, I did not see the post saying anything like "Only if the > > > table does not already exist then it will

Re: Initial Schema Sync for Logical Replication

On Thu, Mar 16, 2023 at 10:27 PM Kumar, Sachin wrote: > > > Hi, > > > > I have a couple of questions. > > > > Q1. > > > > What happens if the subscriber already has some tables present? For > > example, I did not see the post saying anything like "Only if the table does > > not already exist then

Re: Initial Schema Sync for Logical Replication

On 2023-Mar-15, Kumar, Sachin wrote: > 1. In CreateSubscription() when we create replication > slot(walrcv_create_slot()), should > use CRS_EXPORT_SNAPSHOT, So that we can use this snapshot later in the > pg_dump. > > 2. Now we can call pg_dump with above snapshot from CreateSubscription.

RE: Initial Schema Sync for Logical Replication

Hi Peter, > Hi, > > I have a couple of questions. > > Q1. > > What happens if the subscriber already has some tables present? For > example, I did not see the post saying anything like "Only if the table does > not already exist then it will be created". > My assumption was the if subscriber

Re: Initial Schema Sync for Logical Replication

Hi, I have a couple of questions. Q1. What happens if the subscriber already has some tables present? For example, I did not see the post saying anything like "Only if the table does not already exist then it will be created". On the contrary, the post seemed to say SUBREL_STATE_CREATE 'c'

Initial Schema Sync for Logical Replication

Hi Everyone, I am working on the initial schema sync for Logical replication. Currently, user have to manually create a schema on subscriber side. Aim of this feature is to add an option in create subscription, so that schema sync can be automatic. I am sharing Design Doc below