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 1sudNv-008P5P-Rg for pgsql-in-general@arkaria.postgresql.org; Sat, 28 Sep 2024 19:45:44 +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 1sudNu-00CReu-3f for pgsql-in-general@arkaria.postgresql.org; Sat, 28 Sep 2024 19:45:42 +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 1sudNt-00CRem-JT for pgsql-in-general@lists.postgresql.org; Sat, 28 Sep 2024 19:45:41 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sudNq-001Wb9-BG for pgsql-in-general@postgresql.org; Sat, 28 Sep 2024 19:45:40 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-5c8952f7f95so21472a12.0 for ; Sat, 28 Sep 2024 12:45:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727552737; x=1728157537; 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=X1jENzRlLjsZPBNuzHSWY+brSGYLmOXMe6T38VraIQM=; b=gnJ7K8pa5yIKNJERhtcbKySHp3zh33Ux8l262GWyHXf7+m9501h39tXKKWJ5/NiU3A Xin8Yk6UkvBuGyAAUySPKTltjy9TBzkbJiAM/zZwYZa4VAeaVyAmMiJRosL3HVIdqSdW HGClFhGRTu1xweQWVrDxKwk9mPWEv+7hw9B42bS3tb1L+RCORfQD1DcVzqb6JobECuMW 7JvOtmkAwgDlwyD3uvhLG/3NOYgph/oUAoOHJZTnG1Jidzw8WbhXjCSMtHaZh4qY3GaY n9Mgi3xU80PtPdF1DLxiVj2qvG7U4V60TvuQq6w04VmHH82cGzl05jIuqMUuftVCJy8e EXYQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727552737; x=1728157537; 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=X1jENzRlLjsZPBNuzHSWY+brSGYLmOXMe6T38VraIQM=; b=pVo6TI+kehyNOhajqEUk2lFLKLWUzfdO5LIu3svlyZNAyZAJfM0U9IRkN60SXeaSdD Tok2RdkKqBFPPUOBZmKCii9RitHDcRwUS65P7dxHAsqv2Ic2eW/yqJ3V9a/pUpslgNzA jNpmLQaVylIPlo/KTUial8uMMIamby6gelFkn8iMKndzZaF9v3K9x+r3tWYOdYHsL5PW aBFwQdc7EVRI//BgV4pDnCtOrzYd1j6DpiemK2xgP+AixQvXcblOZ3voBtqYRCT9YmmJ kc2OStqXrpMqhRpyXlyAROIrRHXFFTpjtYJJZTj0H5vMk/6wBwGlswqmSmRwaWi1SwV1 JY+Q== X-Gm-Message-State: AOJu0YwYMZyDjH+JzRDoexGnbzZrNO8sHl3aIZCfAea4hejBA3t8x7BJ v8kva71ngTKS4cAxSDH9z5waStUCJT8j5xGXZ2BP0h37iolEQUkLMkb2dNZgjUoafRp8Fkwf9BD tPgsLNObmDkxTsFlNPK5eFTDo5qs= X-Google-Smtp-Source: AGHT+IHwUpONJHgIe96xV2SxEjQGi6Pkqpsdhu4OAF2K6i6BroTxo1xVCNKox9aMiQIZPutPyrgGvmtQzM2koRYLK/A= X-Received: by 2002:a17:906:7946:b0:a8a:7897:c043 with SMTP id a640c23a62f3a-a93c4ae12c7mr820699966b.43.1727552736282; Sat, 28 Sep 2024 12:45:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Sun, 29 Sep 2024 01:15:24 +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="0000000000002d54b6062333378f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002d54b6062333378f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 steps 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 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 snapsho= t >>>> with it ? >>>> >>>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14 >>>> >>>> example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgout= put; >>>> 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 i= t ? >>>>> >>>>> 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 >>>>> >>>> >> >> 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-SNAPSH= OT-SYNCHRONIZATION >> >> Then you can create the logical replication slot with using that slotnam= e >> option >> >> https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREAT= ESUBSCRIPTION-PARAMS-WITH-SLOT-NAME >> and no sync option. >> >> Then you tell pg_dump to use that snapshot name snapshot with this optio= n >> --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 initial >> sync. >> >> >> Thanks >> Justin >> >> > --0000000000002d54b6062333378f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 subscription in disable mod= e with copy_data =3D false post which make origin advance to progress repli= cation finally can=C2=A0 go with enable the subscription=C2=A0

Generate snapshot with pg export sna= pshot function and allocate this to slot with commands is very important an= d 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://= opensource-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 = steps=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 yo= ur valuable information

= Regards,
Durga Mahesh=C2=A0
<= br>

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

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=C2=A0team membe= rs ?

Durga Mahesh=C2=A0
=
On Thu, Sep 26, 2024 at 2:23=E2=80=AFAM Durgamahe= sh Manne <maheshpostgres9@gmail.com> wrote:
=
Hi Team

= --snapshot=3Dsnapshotname
(Use the specified synchronized snapshot when making a dump of the da= tabase

This option is useful when n= eeding 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 snapshot with = it ?

Below CREATE_REPLICAION_SLOT n= ot supported by postgresql 14

examp= le osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
=C2=A0 slot_name | consi= stent_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, <maheshpostgre= s9@gmail.com> wrote:
Hi Team=

--snapshot=3Dsnapshotname

(Use the s= pecified synchronized snapshot when making a dump of the database

This option is useful when needing to synchronize th= e dump with a logical replication slot) as per the pgdg

How do we syn= chronize the dump with a logical replication slot=C2=A0 with=C2=A0--snapsho= t?

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

Below CREATE_REPLI= CAION_SLOT not supported by postgresql 14

example osdb_lsr=3D# CREATE_REPLIC= ATION_SLOT lsr_sync_01 LOGICAL pgoutput;
=C2=A0 slot_name =C2=A0| consis= tent_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= | pgoutput

=

Regards,

Durga Mahesh



Hi=C2=A0Durgamahesh,

I am not sure what yo= u 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 is not= =C2=A0a benefit=C2=A0to doing that as the WAL is going to build up on the p= ublisher...=C2=A0

You have to create a snapshot using the export fun= ction

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

https://ww= w.postgresql.org/docs/17/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZ= ATION

Then you can create the=C2=A0logical replication slot with= =C2=A0using that slotname option
https://www.po= stgresql.org/docs/17/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PAR= AMS-WITH-SLOT-NAME=C2=A0 and no sync option.
Then you tell pg_dump to use that snapshot name snapshot with this op= tion
--snapshot=3Dsnapshotname

h= ttps://www.postgresql.org/docs/current/app-pgdump.html

Once pg_r= estore is done on the destination , you can create a subscription using tha= t slotname option probably and specify copy_data =3D false.

Keep in = mind the WAL will build up during this process,=C2=A0 not sure what the ben= efit=C2=A0would be just allowing logical replication to do the initial sync= .


Thanks
Justin
=C2=A0
--0000000000002d54b6062333378f--