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 1subQX-0085WN-4O for pgsql-general@arkaria.postgresql.org; Sat, 28 Sep 2024 17:40:18 +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 1subQW-00B9Bx-1O for pgsql-general@arkaria.postgresql.org; Sat, 28 Sep 2024 17:40:16 +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 1subQV-00B9Bo-D0 for pgsql-general@lists.postgresql.org; Sat, 28 Sep 2024 17:40:15 +0000 Received: from mail-io1-xd33.google.com ([2607:f8b0:4864:20::d33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1subQS-001Vjx-E9 for pgsql-general@lists.postgresql.org; Sat, 28 Sep 2024 17:40:14 +0000 Received: by mail-io1-xd33.google.com with SMTP id ca18e2360f4ac-8323b555a68so186565739f.2 for ; Sat, 28 Sep 2024 10:40:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727545211; x=1728150011; darn=lists.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=XF1BDiiXbIZG/x03jpN/Gt82qlWP0C089gNab2aLIkU=; b=aDaoiI1HLnL8gRmDYiUYYwN4+h2h6KX7YLLWse5XpxVawW7wIq0IiOzsCi1ICtC4vl /JwIbbIRu29WgyE/tA+9WzhBeGtezUtmXKqfLb5m3snXN3cP1xp9QbVHtfPKcKhW1H1a ccTXJGku80KzVtX/8/XxS27ONITyvYJu0GQseb7LIWrewmu+GE03XvtmwGVaAamwBuWg w11l4dnriotFAwjFOAgL3LeUFqXoENRjr3KeWkV/uS0ySBIdDroz4IlqJTnQqk4L4IiF ACC0oPnU2BNhwyxNKDIJIg6wzGE1tmt3CdrwrkY8kthyVQ5mH4LMkttkOjYR7+cSOe/M nm9A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727545211; x=1728150011; 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=XF1BDiiXbIZG/x03jpN/Gt82qlWP0C089gNab2aLIkU=; b=oQgaqupkNmgjGPcC3cHsk3+wXdT+0kmG+3FzIOYB6GRHP+PUprdblr1M+ZSbL1BYWG OVTIio7rhMFWzTD38hmoEABKDZRW5Ep2V9XV1mrvYzNLv0bYWUgC/UsSMqyj7wjnHDOF g9ZltCq7hAAojnJlVGii8oxidA5ZzFKCCmMywKOgny0nMM41x97FWnlin3ZRdGgNKHrQ i2qx7mda2VEXokTrErRwSDHH/SCUTRA81Xf16pDDqjLEdCJHO2lE8nGDEf8P+AUqoeNI x5+stibxNSuyd8guWe5QDpR2NZJwkaUKHj8796LaWI+bJIYLSTrVxWpTFREyDQWBc/W6 WDig== X-Gm-Message-State: AOJu0YxlUr4BTRIgxK0UaaF/tYP1VfI+/44vExI43l959+IFCp2C77pQ IIo07/m+FdUFoMVQN+A3hgDD2ljUNjz3ie1/Hx+YQHGWOzB36w46wYQj3gN1VZ3iPsGqEH4a7/L DUmCLtnO3CYyiDUlJZV/AIaLiOr0= X-Google-Smtp-Source: AGHT+IF6pnVeWrhUlcpke6p6crvfpk8iaUGAxxco/WdypGJgKMgK83EGjdh6L1RmJFtvEDCjJJyJEMg8VDR7OHlxvrM= X-Received: by 2002:a05:6602:6d10:b0:82c:f172:6f07 with SMTP id ca18e2360f4ac-8349325d5afmr588511739f.14.1727545211363; Sat, 28 Sep 2024 10:40:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Sat, 28 Sep 2024 13:39:59 -0400 Message-ID: Subject: Re: Synchronize the dump with a logical slot with --snapshot To: Durgamahesh Manne Cc: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org, Greg Sabino Mullane , Koichi Suzuki Content-Type: multipart/alternative; boundary="000000000000a82da00623317622" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a82da00623317622 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Sep 28, 2024 at 1:04=E2=80=AFAM Durgamahesh Manne 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 logica= l >> 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 >> >> example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutpu= t; >> 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 pgoutp= ut; >>> 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-SNAPSHOT-= SYNCHRONIZATION Then you can create the logical replication slot with using that slotname option https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREATESU= BSCRIPTION-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 initial sync. Thanks Justin --000000000000a82da00623317622 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=C2=A0

O= n 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 members ?

Durga Mahe= sh=C2=A0

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 i= s useful when needing to synchronize the dump with a logical replication sl= ot) as per the pgdg
=
How do we synch= ronize the dump with a logical replication slot with --snapshot?

I am using the postgresql 14 version which s= upports only pg_create_logical_replication_slot. How to generate a internal= snapshot with it ?
=
Below CREATE_RE= PLICAION_SLOT not supported by postgresql 14
=
example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL = pgoutput;
=C2=A0 slo= t_name | consistent_point | snapshot_name | output_plugin
-------------+----------------= --+---------------------+---------------
=C2=A0lsr_sync_01 | 0/C000110 | 00000003-000000= 02-1 | pgoutput

=
Regards,

Durga Mahesh



On Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, <maheshpostgres9@gmail.com> wrote:
Hi Team

<= code style=3D"box-sizing:border-box;font-size:1em;border-radius:0.25rem;mar= gin:0.6rem 0px">--snapshot=3Dsnapshotname

(Use the specified synchr= onized snapshot when making a dump of the database

This option is useful when needing to synchronize the dump with a l= ogical replication slot) as per the pgdg

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

I am using th= e postgresql 14 version which supports only pg_create_logical_replication_s= lot. How to generate a snapshot with it ?

Below CREATE_REPLICAION_SLOT not s= upported by postgresql 14

= example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_s= ync_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 | pgoutput

Regard= s,

Durga Mahesh



Hi=C2=A0Durgamahesh,

I am not sure what you are after with= matching pg_dump and replication slot together unless you are trying=C2=A0= to 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 publisher...=C2= =A0

You have to create a snapshot using the export function

<= a href=3D"https://www.postgresql.org/docs/current/sql-set-transaction.html"= >https://www.postgresql.org/docs/current/sql-set-transaction.html
https://www.postgresql.org/docs/17/functions-= admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION

Then you can creat= e the=C2=A0logical replication slot with=C2=A0using that slotname optionhttps://www.postgresql= .org/docs/17/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH= -SLOT-NAME=C2=A0 and no sync option.

Th= en you tell pg_dump to use that snapshot name snapshot with this option
=
--sna= pshot=3D<= code style=3D"box-sizing:border-box;border-radius:0.25rem;margin:0.6rem 0px= ">snapshotname

https://www.postgresql.org/docs/current/app-pgdump.html<= br>
Once pg_restore is done on the destination , you can create a subscr= iption 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 s= ure what the benefit=C2=A0would be just allowing logical replication to do = the initial sync.


Thanks
Justin
=C2=A0
--000000000000a82da00623317622--