Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sue2Y-008U8Q-1P for pgsql-in-general@arkaria.postgresql.org; Sat, 28 Sep 2024 20:27:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sue2W-00Cuou-1z for pgsql-in-general@arkaria.postgresql.org; Sat, 28 Sep 2024 20:27:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sue2V-00Cuok-EZ for pgsql-in-general@lists.postgresql.org; Sat, 28 Sep 2024 20:27:39 +0000 Received: from mail-io1-xd32.google.com ([2607:f8b0:4864:20::d32]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sue2O-001Wqx-10 for pgsql-in-general@postgresql.org; Sat, 28 Sep 2024 20:27:38 +0000 Received: by mail-io1-xd32.google.com with SMTP id ca18e2360f4ac-82a626d73efso113879339f.1 for ; Sat, 28 Sep 2024 13:27:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727555250; x=1728160050; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=GHKwFujrdKXu8z/lhe5dwVLXxickvTMtpVchYrwEAiQ=; b=gbdfZd6ZTxJBCuZ3TrvXq66tBpSYDJRNDJt56ik+cYds7V6r3tt5EeQwAfUAwGKw5l uuoYZVfscgaTDEjy5ZFJI87RbMJPirhYIrG1WLpReXo9ExumbLHEFr1VUouUx2p9GO9f LvnsDUXKFFoP/Iny7tf7JvoYAHBPDkYXZjgVN3vz8MEhRwE6FRWu/1Jh7HInVHFcKNuN xT4XjUtm5gcrwUc3VGUJQebzQ3LrQq+UbCo4FkoFlXDxOHKT9kD5FJ51L7BHXSOKXTzR SThgO8kqhd2OreDI5uibqXyGDDYT/r16+YM4srnH2S5uDePXFQCzsbaPQWRNx/WbbN0g nedg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727555250; x=1728160050; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=GHKwFujrdKXu8z/lhe5dwVLXxickvTMtpVchYrwEAiQ=; b=KcBeT5xT2aBp1nq46lIMqlf7BRDGlRVtWQ0XkVx1TBOSXAZ6WrVk9jMEXpUL2t+3QS Bb0/dUqTIkoy9swUyB4qfVkMFXQU0A/Vy7I7hP4a6qwii5+M8AFlJiyeBK86MSlu0bsW A/J8pfC+MAshCsFJ1ij6p28vYqZfosFmIm3bOtadEtso29JQmloSZkh4qrTTgT+B9HGJ BLj3fSLTzun4bysVIy7HE8BpB1pk5Ns4ryIsD9lN2k3r2qtN9jdKDUKVsZoyYykVEUN2 qod7tf0sAjuxlIUzR9lIHenp0v+OBKN212fIH0Jvc5lCE+0s6zXcvUI1IKPUgZZGYtap Zq+w== X-Gm-Message-State: AOJu0YykqfHr1SCeWcIYK8VRGOzqo9JtU2Ce0XoVXgGqHNyqL7hw+Dyl QccAyTjZt7f3i29VRsT93cW2VAe6Wwqm8un1celzqwmCEN1j904rHjOmRx52sVUGcuuReVvLrcJ XoSOpkhh+BqwqYhMMCuEPqly3HplJysRK X-Google-Smtp-Source: AGHT+IEoRZC/cYOiRRyK1IQW+sgtRK3NHpbg387XVuUs5c8YIHblDrAdzcFIO5hFXt6yyn2k17BWhPIENRpRnvkOGWA= X-Received: by 2002:a05:6602:3425:b0:82d:835:e66d with SMTP id ca18e2360f4ac-834931d27bcmr477063539f.9.1727555250460; Sat, 28 Sep 2024 13:27:30 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Sat, 28 Sep 2024 16:27:19 -0400 Message-ID: Subject: Re: Synchronize the dump with a logical slot with --snapshot To: Durgamahesh Manne Cc: pgsql-in-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000089fa9062333cd40" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000089fa9062333cd40 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Sep 28, 2024 at 3:45=E2=80=AFPM Durgamahesh Manne wrote: > Hi Justin > > We will have to take dump with --snapshot after creating slot then restor= e > it on target during which we need to create subscription in disable mode > with copy_data =3D false post which make origin advance to progress > replication finally can go with enable the subscription > > Generate snapshot with pg export snapshot function and allocate this to > slot with commands is very important and critical here to go for next ste= ps > > Regards, > Durga Mahesh > > On Sun, 29 Sept, 2024, 00:22 Durgamahesh Manne, > wrote: > >> Hi justin >> >> I raised question based on the reference link >> https://opensource-db.com/unlocking-initial-sync-for-logical-replication= -in-aws-rds-for-postgresql/ >> .. you can also go through it to see the steps >> This worked on postgres 10version but on postgres 14 I can go through th= e >> info you provided to implement the same >> >> Thanks for your valuable information >> >> Regards, >> Durga Mahesh >> >> >> On Sat, 28 Sept, 2024, 23:10 Justin, wrote: >> >>> >>> >>> On Sat, Sep 28, 2024 at 1:04=E2=80=AFAM Durgamahesh Manne < >>> maheshpostgres9@gmail.com> wrote: >>> >>>> Hi Team >>>> >>>> Can anyone respond to my question from respected team members ? >>>> >>>> Durga Mahesh >>>> >>>> On Thu, Sep 26, 2024 at 2:23=E2=80=AFAM Durgamahesh Manne < >>>> maheshpostgres9@gmail.com> wrote: >>>> >>>>> Hi Team >>>>> >>>>> --snapshot=3Dsnapshotname >>>>> (Use the specified synchronized snapshot when making a dump of the >>>>> database >>>>> >>>>> This option is useful when needing to synchronize the dump with a >>>>> logical replication slot) as per the pgdg >>>>> >>>>> How do we synchronize the dump with a logical replication slot with >>>>> --snapshot? >>>>> >>>>> I am using the postgresql 14 version which supports only >>>>> pg_create_logical_replication_slot. How to generate a internal snapsh= ot >>>>> with it ? >>>>> >>>>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14 >>>>> >>>>> example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL >>>>> pgoutput; >>>>> slot_name | consistent_point | snapshot_name | output_plugin >>>>> -------------+------------------+---------------------+--------------= - >>>>> lsr_sync_01 | 0/C000110 | 00000003-00000002-1 | pgoutput >>>>> >>>>> Regards, >>>>> >>>>> Durga Mahesh >>>>> >>>>> >>>>> >>>>> On Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, < >>>>> maheshpostgres9@gmail.com> wrote: >>>>> >>>>>> Hi Team >>>>>> >>>>>> --snapshot=3D*snapshotname* >>>>>> >>>>>> (Use the specified synchronized snapshot when making a dump of the >>>>>> database >>>>>> >>>>>> This option is useful when needing to synchronize the dump with a >>>>>> logical replication slot) as per the pgdg >>>>>> >>>>>> How do we synchronize the dump with a logical replication slot >>>>>> with --snapshot? >>>>>> >>>>>> I am using the postgresql 14 version which supports only >>>>>> pg_create_logical_replication_slot. How to generate a snapshot with = it ? >>>>>> >>>>>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14 >>>>>> >>>>>> example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL >>>>>> pgoutput; >>>>>> slot_name | consistent_point | snapshot_name | output_plugi= n >>>>>> -------------+------------------+---------------------+-------------= -- >>>>>> lsr_sync_01 | 0/C000110 | 00000003-00000002-1 | pgoutput >>>>>> >>>>>> Regards, >>>>>> >>>>>> Durga Mahesh >>>>>> >>>>> >>> >>> Hi Durgamahesh, >>> >>> I am not sure what you are after with matching pg_dump and replication >>> slot together unless you are trying to get a dump to handle the initial >>> data sync. There is not a benefit to doing that as the WAL is going to >>> build up on the publisher... >>> >>> You have to create a snapshot using the export function >>> >>> https://www.postgresql.org/docs/current/sql-set-transaction.html >>> >>> >>> https://www.postgresql.org/docs/17/functions-admin.html#FUNCTIONS-SNAPS= HOT-SYNCHRONIZATION >>> >>> Then you can create the logical replication slot with using that >>> slotname option >>> >>> https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREA= TESUBSCRIPTION-PARAMS-WITH-SLOT-NAME >>> and no sync option. >>> >>> Then you tell pg_dump to use that snapshot name snapshot with this opti= on >>> --snapshot=3Dsnapshotname >>> >>> https://www.postgresql.org/docs/current/app-pgdump.html >>> >>> Once pg_restore is done on the destination , you can create a >>> subscription using that slotname option probably and specify copy_data = =3D >>> false. >>> >>> Keep in mind the WAL will build up during this process, not sure what >>> the benefit would be just allowing logical replication to do the initia= l >>> sync. >>> >>> >>> Thanks >>> Justin >>> >> Hi Durgamahesh, The article does not state what is being gained following these instructions over just letting LR do the initial sync... And using pg_baseback to create the initial data set along with all these moving pieces. This article made this way harder than it has to be, create a read replica, using pg_basebackup, then convert it to an LR. PG 17 now includes a binary to do this for us. I have converted a Read Replica to LR replica several times in just 4 steps. I don't see why using pg_basebackup is a better solution than letting the initial sync handle the data copy. Using pg_dump to do initial data copy over LR initial sync what is being gained??? Don't forget the WAL will be building up on the publisher using pg_basebackup and pg_dump as the LR slot has to exist.. I have done 50 TB size databases with LR... I would not be following these instructions the assertions are not true *Snapshot: Copies all data at once. Simple but slow for large datasets. *T= his is not how LR initial sync works and can increase the number of sync workers to increase speed. On very large tables drop the indexes that are not the replica identity and recreate them after the table is in sync. I only do this when a specific table will take several days to copy over because it is several TB in size.. Base Backup and Continuous Archiving*: Makes a base copy and then tracks changes. More suitable for large datasets. ---*Don't know how this is true, Data size is data size and the time to copy does not really change. There is a tiny saving by not having to recreate the indexes, is this approach really worth it. Lots of manual moving pieces and making sure both databases are on the same LSN... --000000000000089fa9062333cd40 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sat, Sep 28, 2024 at 3:45=E2=80=AFPM D= urgamahesh Manne <maheshpos= tgres9@gmail.com> wrote:
Hi Justin

We will have to take dump with --snapshot after creating slot= then restore it on target during which we need to create=C2=A0 subscriptio= n in disable mode with copy_data =3D false post which make origin advance t= o progress replication finally can=C2=A0 go with enable the subscription=C2= =A0

Generate snapshot wi= th pg export snapshot function and allocate this to slot with commands is v= ery important and critical here to go for next steps=C2=A0

Regards,
Durga Ma= hesh

On Sun, 29 Sept, 2024, 00:22 Durgamahesh Manne, <maheshpostgres9@gmail.com= > wrote:
=
Hi justin

I = raised question based on the reference link https://opensource-db.com/unlock= ing-initial-sync-for-logical-replication-in-aws-rds-for-postgresql/=C2= =A0 =C2=A0 .. you can also go through it to see the steps=C2=A0
This worked on postgres 10version but on postgres 14 I can go t= hrough the info you provided to implement the same=C2=A0

Thanks for your valuable information<= /div>

Regards,
Durga Mahesh=C2=A0


On Sat, 28 Se= pt, 2024, 23:10 Justin, <zzzzz.graf@gmail.com> wrote:
=
=C2=A0
<= /dl>

On Sat, Sep 28= , 2024 at 1:04=E2=80=AFAM Durgamahesh Manne <maheshpos= tgres9@gmail.com> wrote:
Hi Te= am

Can anyone respond to my question from respected=C2=A0team= members ?

= Durga Mahesh=C2=A0

On Thu, Sep 26, 2024 at 2:23=E2=80=AFAM Dur= gamahesh Manne <maheshpostgres9@gmail.com> wrot= e:
Hi Team

--snapshot=3Dsnapshotname
(Use the specified synchronized snapshot when making a dump of th= e database

This option is useful wh= en needing to synchronize the dump with a logical replication slot) as per = the pgdg

=
How do we synchronize the = dump with a logical replication slot with --snapshot?

I am using the postgresql 14 version which supports onl= y pg_create_logical_replication_slot. How to generate a internal snapshot w= ith it ?

=
Below CREATE_REPLICAION_SL= OT not supported by postgresql 14

= example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;<= /font>
=C2=A0 slot_name | = consistent_point | snapshot_name | output_plugin
-------------+------------------+-------= --------------+---------------
=C2=A0lsr_sync_01 | 0/C000110 | 00000003-00000002-1 | pgo= utput

Regards,

Durga Mahesh

=

O= n Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, <mahesh= postgres9@gmail.com> wrote:
Hi Team=

--snapshot=3Dsnapshotname

(Us= e the specified synchronized snapshot when making a dump of the database

This option is useful when needing to synchro= nize the dump with a logical replication slot) as per the pgdg

How do= we synchronize the dump with a logical replication slot=C2=A0 with=C2=A0--= snapshot?

I am using the postgresql 14 version which supports only pg_create= _logical_replication_slot. How to generate a snapshot with it ?

<= /dd>

Below CREATE= _REPLICAION_SLOT not supported by postgresql 14

example osdb_lsr=3D# CREATE_= REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
=C2=A0 slot_name =C2=A0| = consistent_point | =C2=A0 =C2=A0snapshot_name =C2=A0 =C2=A0| output_plugin<= br>-------------+------------------+---------------------+---------------=C2=A0lsr_sync_01 | 0/C000110 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 00000003-00000= 002-1 | pgoutput

Regards,

<= font face=3D"monospace">Durga Mahesh



=
Hi=C2=A0Durgamahesh,

I am not sure wh= at you are after with matching pg_dump and replication slot together unless= you are trying=C2=A0to get a dump to handle the initial data sync. There i= s not=C2=A0a benefit=C2=A0to doing that as the WAL is going to build up on = the publisher...=C2=A0

You have to create a snapshot using the expor= t function

https:/= /www.postgresql.org/docs/current/sql-set-transaction.html

https= ://www.postgresql.org/docs/17/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCH= RONIZATION

Then you can create the=C2=A0logical replication slot= with=C2=A0using that slotname option
https://w= ww.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREATESUBSCRIPTIO= N-PARAMS-WITH-SLOT-NAME=C2=A0 and no sync option.

Then you tell pg_dump to use that snapshot name snapshot with th= is option
--snapshot=3Dsnapshotname

https://www.postgresql.org/docs/current/app-pgdump.html

= Once pg_restore is done on the destination , you can create a subscription = using that slotname option probably and specify copy_data =3D false.
Keep in mind the WAL will build up during this process,=C2=A0 not sure wha= t the benefit=C2=A0would be just allowing logical replication to do the ini= tial sync.


Thanks
Justin
=

Hi=C2=A0Durgamahesh,

The article does not state what = is being=C2=A0=C2=A0gained following these instructions over just letting L= R do the initial sync...=C2=A0

And using pg_baseback to create= the=C2=A0initial data set along with all these moving pieces.=C2=A0 This a= rticle=C2=A0made this way harder than it has to be,=C2=A0 create a read rep= lica, using pg_basebackup,=C2=A0 then convert it to an LR.=C2=A0 PG 17 now = includes a binary to do this for us.=C2=A0 =C2=A0I have converted a Read Re= plica to LR replica several times in just 4 steps.

I don't see w= hy using pg_basebackup is a better solution than letting the initial sync h= andle the data copy.=C2=A0

Using=C2=A0pg_dump to do initial data cop= y over LR initial sync what=C2=A0is being gained???

Don't forget= the WAL will be building up=C2=A0 on the publisher using pg_basebackup and= pg_dump as the LR slot has to exist..=C2=A0=C2=A0

I have done 50 TB= size databases with LR...

I would not be following these instructio= ns the assertions are not true

Snapshot: Copies all data at once. Sim= ple but slow for large datasets.=C2=A0 This is not how = LR initial sync works and can increase the number of sync workers to increa= se speed.=C2=A0 =C2=A0On very large tables drop the indexes that are not th= e=C2=A0 replica identity and recreate them after the table is in sync.=C2= =A0 I only do this=C2=A0when a specific table will take several days to cop= y over because it is several TB in size..
=C2=A0
Base Backup and Continuous Archiving: Makes a base copy and then tracks ch= anges. More suitable for large datasets.=C2=A0 ---Don't know how this is t= rue,=C2=A0 Data size is data size and the time to copy does not really chan= ge.=C2=A0 =C2=A0There is a tiny saving by not having to recreate the indexe= s,=C2=A0is this=C2=A0approach really worth it.=C2=A0 Lots of manual=C2=A0mo= ving pieces and making sure both databases are on the same LSN...
--000000000000089fa9062333cd40--