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 1sulJs-009fFk-Go for pgsql-in-general@arkaria.postgresql.org; Sun, 29 Sep 2024 04:14:05 +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 1sulJq-00HWhO-I2 for pgsql-in-general@arkaria.postgresql.org; Sun, 29 Sep 2024 04:14:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sulJq-00HWdM-4H for pgsql-in-general@lists.postgresql.org; Sun, 29 Sep 2024 04:14:02 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sulJl-001crS-D8 for pgsql-in-general@postgresql.org; Sun, 29 Sep 2024 04:14:01 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-a8d3cde1103so445846166b.2 for ; Sat, 28 Sep 2024 21:13:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727583237; x=1728188037; 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=S4NWdh9uDDtTdJSPWh1di0tYwws0a8+cYgfDhwPPYao=; b=Qw02d46IzF+v3VZ9SOQXnuRoP1Ddgx3pdu7SONplpj4unoBatjRt2hV4Y9ob9hCxsM GvU7YFj1u/VEvyWR7r16bJXtK1zK+jYlTcCtbNSJx6jqqxF11VZN/ogm0jM0lcVJK59S CLGCGyNgF9fqISPB8as7LFAWyzHHoC1/IV6XMk8nqbocHBhDndMLF6HkDaBD+Ev1Y6e5 8bHUxlq8YqlFxo5z7E5ISFN8rLubv7175D4DaczBf/WVKOj60ikG6IT7xucoxF3Arwih xMU7Eslvne6tjGgHZMsBeNxSagJCOGKFub0WF6p9sosa7OR8BmWrjKzouqSam6DzVgHZ +Ogw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727583237; x=1728188037; 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=S4NWdh9uDDtTdJSPWh1di0tYwws0a8+cYgfDhwPPYao=; b=L+Ihk4fyi9mhr8mqfx/X+03krH4cKYI1KMBDjCZCoQUfzUxBWD1BYmgA/YYJ4Ksfob oy7+7HSc7vysZRUxrHUQIF0Z3//1L9G9xolEQY5MLOHOcSWnXT/zoisAPHP6mfDsaoyQ bgwQiSg1nEeF2eYtsxyRXPeuAKG3yo1Q8x68/wNNm9Ub5d1x+mr87HaZsNNfHsuZEIsx gMWj/O+C3ob/EyOxHv7j7U8Wu7vL2fkOVhMsumzNNavVApndZX+7UgeIG6eQa1jvB191 z5A7a82HjOu3Ggr5vXIjVjQ9If/+eNLxqwoVxHMxGA2xmhb6msjOY+kQpkc/62p1OV2f NFbg== X-Gm-Message-State: AOJu0YxnpR7bbGIVCur3CQJ+JEdkjHTWs8Fk2bkfXeZm+WhUuHlrLH4l 2z/RCp/E8u7LJjHTaBXX5CrtCGVrZmPUTPTt0wUp+SQIlCOktwT2R1zNrROhK4f+ZuJulNSyI6y PC/rv3XvxEpxGfpZc8bCYeJeiynfyWF46 X-Google-Smtp-Source: AGHT+IEQ0rYBmJz2exSjd4brka4rMt4418QdFlbNjvyY9k6Yha5XpVZ/KRtc7OgNLmD5EW8j3ZzEdg2f/PlJ6w3I0zE= X-Received: by 2002:a17:907:e9e:b0:a86:fa3d:e984 with SMTP id a640c23a62f3a-a93c49097b4mr982152366b.20.1727583237075; Sat, 28 Sep 2024 21:13:57 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Sun, 29 Sep 2024 09:43:44 +0530 Message-ID: Subject: Re: Synchronize the dump with a logical slot with --snapshot To: Justin Cc: pgsql-in-general@postgresql.org Content-Type: multipart/alternative; boundary="0000000000002a7a2606233a51ec" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002a7a2606233a51ec Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, 29 Sept, 2024, 01:57 Justin, wrote: > > On Sat, Sep 28, 2024 at 3:45=E2=80=AFPM Durgamahesh Manne < > maheshpostgres9@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 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 st= eps >> >> Regards, >> Durga Mahesh >> >> 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/unlocking-initial-sync-for-logical-replicatio= n-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 >>> the 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 snaps= hot >>>>>> 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_plug= in >>>>>>> >>>>>>> -------------+------------------+---------------------+------------= --- >>>>>>> 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 initia= l >>>> 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-SNAP= SHOT-SYNCHRONIZATION >>>> >>>> Then you can create the logical replication slot with using that >>>> slotname option >>>> >>>> https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CRE= ATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME >>>> and no sync option. >>>> >>>> Then you tell pg_dump to use that snapshot name snapshot with this >>>> 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, not sure what >>>> the benefit would be just allowing logical replication to do the initi= al >>>> 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. P= G > 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. = *This > 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 a= re > 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... > Hi justin <> how do you covert read replica to LR replica with latest version when primary has multiples databases Let's say primary 14 version and target 16 version ? This approach in your scenario was absolutely correct My scenario >> Source 14 version db has 15 days interval partitioned tables Target 16 need to be 7 days days interval partitioned tables In this scenario if I create read replica then same copy of source would be there that does not work for me As per the reference link I gave you On postgres 14 I am working Let's see how does that work. Once done will share with you the details Regards, Durga Mahesh --0000000000002a7a2606233a51ec Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Sun, 29 Sept, 2024, 01:57 Justin, <zzzzz.graf@gmail.com> wrote:

On Sat, Sep 2= 8, 2024 at 3:45=E2=80=AFPM Durgamahesh Manne <maheshpostgres9@gma= il.com> wrote:
Hi Justin

We will have to take dump with --snapshot after creating slot then rest= ore it on target during which we need to create=C2=A0 subscription in disab= le mode with copy_data =3D false post which make origin advance to progress= replication finally can=C2=A0 go with enable the subscription=C2=A0
<= div dir=3D"auto">
Generate snapshot with pg expo= rt snapshot function and allocate this to slot with commands is very import= ant and critical here to go for next steps=C2=A0
Regards,
Durga Mahesh
=

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

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

Thanks for your = valuable information

Reg= ards,
Durga Mahesh=C2=A0

=

On Sat, 28 Sept, 2024, 23:10 Justin, <zzzzz.graf@gmai= l.com> wrote:
=C2=A0

On Sat, Sep 28, 2024 at 1:04=E2=80=AFAM Durgamahesh Manne= <maheshpostgres9@gmail.com> wrote:<= br>
Hi Team

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

Durga= Mahesh=C2=A0

On Thu, Sep 26, 2024 at 2:23=E2=80=AFAM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
=
Hi Team
=
--snapshot=3Dsn= apshotname
(Use the = specified synchronized snapshot when making a dump of the database

This option is useful when needing to synch= ronize 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 snapshot with it ?

Below CREATE_REPLICAION_SLOT not supported by= postgresql 14

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

Regards,

= Durga Mahesh



On Fri, 20 Sept, 2024, = 01:27 Durgamahesh Manne, <maheshpostgres9@g= mail.com> wrote:
Hi Team
--snapshot=3Dsnapshotname

(Use the speci= fied synchronized snapshot when making a dump of the database

This option is useful when needing to synchronize the du= mp with a logical replication slot) as per the pgdg

<= dd style=3D"box-sizing:border-box;margin-bottom:0.5rem;margin-left:0px;marg= in-top:1rem;color:rgb(0,0,0);font-size:14.4px">

How do we synchro= nize 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_re= plication_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;
=C2=A0 slot_name =C2=A0| consistent_= point | =C2=A0 =C2=A0snapshot_name =C2=A0 =C2=A0| output_plugin
--------= -----+------------------+---------------------+---------------
=C2=A0lsr= _sync_01 | 0/C000110 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 00000003-00000002-1 | pgo= utput

Regards,

Durga Mahesh



Hi=C2=A0Durgamahesh,

I am not sure what you ar= e 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 is not=C2= =A0a benefit=C2=A0to doing that as the WAL is going to build up on the publ= isher...=C2=A0

You have to create a snapshot using the export functi= on

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

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

Then you can create the=C2=A0logical re= plication slot with=C2=A0using that slotname option
https://www.postgresql.org/docs/17/sql-createsubscription.h= tml#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME=C2=A0 and no sync opti= on.

Then you tell pg_dump to use that snaps= hot name snapshot with this option
--snaps= hot=3Dsnapshotname

https://www.postgresql.org/doc= s/current/app-pgdump.html

Once pg_restore is done on the destina= tion , you can create a subscription using that slotname option probably an= d specify copy_data =3D false.

Keep in mind the WAL will build up du= ring this process,=C2=A0 not sure what the benefit=C2=A0would be just allow= ing logical replication to do the initial sync.


Thanks
Justin=
<= /div>

Hi=C2=A0Durgamahesh,
The article does not state what is being=C2=A0=C2=A0gained following t= hese instructions over just letting LR 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 article=C2=A0made this way harder than = it has to be,=C2=A0 create a read replica, 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 Replica to LR replica several times in= just 4 steps.

I don't see why using pg_basebackup is a better s= olution than letting the initial sync handle the data copy.=C2=A0

Us= ing=C2=A0pg_dump to do initial data copy over LR initial sync what=C2=A0is = being gained???

Don't forget the WAL will be building up=C2=A0 o= n 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 instructions the assertions are not true
Snapsh= ot: Copies all data at once. Simple but slow for large datasets.=C2= =A0 This is not how LR initial sync works and can incre= ase the number of sync workers to increase speed.=C2=A0 =C2=A0On very large= tables drop the indexes that are not the=C2=A0 replica identity and recrea= te them after the table is in sync.=C2=A0 I only do this=C2=A0when a specif= ic table will take several days to copy over because it is several TB in si= ze..
=C2=A0
Base Backup and Continuous Archiv= ing= : Makes a base copy and then tracks changes. More suitable for large datase= ts.=C2=A0 ---Don't know how this is true,=C2=A0 Data size is data size and= the time to copy does not really change.=C2=A0 =C2=A0There is a tiny savin= g by not having to recreate the indexes,=C2=A0is this=C2=A0approach really = worth it.=C2=A0 Lots of manual=C2=A0moving pieces and making sure both data= bases are on the same LSN...


Hi justin=C2=A0

<&l= t;create a read replica, using pg_basebackup, then convert it to an LR. P= G 17 now includes a binary to do this for us. I have converted a Read Rep= lica to LR replica several times in just 4 steps.>>

how do you covert read replica to LR repl= ica with latest version=C2=A0 when primary has multiples databases=C2=A0
Let's say primary 14 version and target 16 version= ?

This approach in your= scenario was absolutely correct=C2=A0

My scenario >>
Source 14 versio= n db has 15 days interval partitioned tables=C2=A0
T= arget 16 need to be 7 days days interval partitioned tables=C2=A0

In this scenario if I create read= replica then same copy of source would be there that does not work for me= =C2=A0

As per the refere= nce link I gave you=C2=A0
On postgres 14=C2=A0 I am = working=C2=A0

Let's = see how does that work. Once done will share with you the details=C2=A0

Regards,
Durga Mahesh


--0000000000002a7a2606233a51ec--