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 1sucYs-008Hco-FY for pgsql-in-general@arkaria.postgresql.org; Sat, 28 Sep 2024 18:52:59 +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 1sucYp-00BtTo-Qm for pgsql-in-general@arkaria.postgresql.org; Sat, 28 Sep 2024 18:52:55 +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 1sucYp-00BtTg-HQ for pgsql-in-general@lists.postgresql.org; Sat, 28 Sep 2024 18:52:55 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sucYl-001Yvv-4l for pgsql-in-general@postgresql.org; Sat, 28 Sep 2024 18:52:54 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a8d43657255so479386766b.0 for ; Sat, 28 Sep 2024 11:52:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727549572; x=1728154372; 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=CHcpZO9vN4I5K2kQXrZEoCkW05D1DkdkG1JvUSdjzOs=; b=aecm4jj1Cqiu8syn7R55hdiCbrP1Jmq4L0JLK+tys2hSAiqNRLsTOXrHxtYA6sBhB5 kMKFbFqRPVV5Yj/fhwBxaUnx0bvvDhe2DypbhLP8Yq3wZfxt1g4sf3fKxiwcur3yXt1R nMNkXahFn2PF4HzHf3ySCV51GJXgaK4tTLUGCTqCaSWuOK4CX5QxMB7nzWwFnTTHQj8q 8OkO2vqt3lmg+isPYO6NILGYRKGDUDrMlDgAyMx4TbWEOFmBSuseCPn83U7XNDgSxDPz 2pMlEryPaMbaFy+YB2WN8aIZv8joAVQ3rjhzrk3NYK3Yb3rBB2ls7vKg5qPqIU+l2BQX kD3A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727549572; x=1728154372; 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=CHcpZO9vN4I5K2kQXrZEoCkW05D1DkdkG1JvUSdjzOs=; b=PRozJkjNNMKjVWTtzgz050qHoNzXaZtnup9/5j3MszDExqxuAStZvAE9j2BwH+wSRS fo8fwHYaG9glo+8kwMSLOz3sjxIAZ7AAZga3I/KluDJqSClE5EPerf7QdGZq6L4CMpn6 +kydvdp6CKFbFPwtTkzHmnklslUAarjvd1fp5m9R6+bp+ZulfXaQq7EDcZlLG2HduaB4 y93Q5Xw8eDbk/dARQvO2/WTt+MwzAjoWxYq/z9DXoko0eN2ppBZ49+XQ4LWwBIUzmEiX fdd60GQ9gBqkaOvI3FWip/7Nc1uBq/MbApquFdubB9vDOipUaV2p0+Va+WsLmCJ7+wE5 5r1g== X-Forwarded-Encrypted: i=1; AJvYcCW6VtVDlxMZXP0JnOxD1VdNMciKZCdwAe1yok7vE4D5Qq6mt8XO95ViQBl+gkZSv5Sp9oL5KQHINXdSns9qbcpg@postgresql.org X-Gm-Message-State: AOJu0YwDVkFfs1iIjtcIa7L82/A8ixKBei07Gk9oY7MCf8v8u7A4vqP8 rczx0ev4TPEW46twymhHMJF7cNnXFP6uNzRkshLGx9S/f7ppQf4eBnLE2yRXaPay8E+NER2TWG8 YZV2OBFgrfYXCYey/E8wuQJQp7bQ= X-Google-Smtp-Source: AGHT+IHpLFk4u2euUtYKsjVwlYa4rpQ6gM/67G0vjusw0YnMVNw9Oy5qu3ER9MWq/drlAGdWZStoiPD5tudCkXZP6P4= X-Received: by 2002:a17:907:5083:b0:a93:d5d3:be4 with SMTP id a640c23a62f3a-a93d5d30c76mr318116866b.13.1727549571374; Sat, 28 Sep 2024 11:52:51 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Sun, 29 Sep 2024 00:22:39 +0530 Message-ID: Subject: Re: Synchronize the dump with a logical slot with --snapshot To: Justin Cc: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org, Greg Sabino Mullane , Koichi Suzuki Content-Type: multipart/alternative; boundary="00000000000088a37d0623327a05" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000088a37d0623327a05 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 >>> >>> >>> >>> 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 pgout= put; >>>> 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-SNAPSHO= T-SYNCHRONIZATION > > Then you can create the logical replication slot with using that slotname > option > > https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREATE= SUBSCRIPTION-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 subscriptio= n > 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 th= e > benefit would be just allowing logical replication to do the initial sync= . > > > Thanks > Justin > > --00000000000088a37d0623327a05 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi justin

I = raised question based on the reference link https://opensource-db.com/unlocking-initial-sync-for-logical-replicat= ion-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 10= version but on postgres 14 I can go through the info you provided to implem= ent the same=C2=A0

T= hanks for your valuable information

Regards,
Durga Mahesh=C2=A0


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 Durga= mahesh Manne <maheshpostgres9@gmail.com> wrote:
=
Hi Team

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 Durgamahesh Manne <maheshpostgres= 9@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 --snap= shot?

I am using the postgresql 14 = version which supports only pg_create_logical_replication_slot. How to gene= rate a internal snapshot with it ?

= Below CREATE_REPLICAION_SLOT not supported by postgresql 14

= example osdb_lsr=3D# CREATE_REPLICATION_SLOT lsr_s= ync_01 LOGICAL pgoutput;
=C2=A0 slot_name | consistent_point | snapshot_name | output_pl= ugin
-------------+-= -----------------+---------------------+---------------
=C2=A0lsr_sync_01 | 0/C000110 | = 00000003-00000002-1 | pgoutput

Rega= rds,

Durga Mahesh



=
On Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, <<= a href=3D"mailto:maheshpostgres9@gmail.com" target=3D"_blank" rel=3D"norefe= rrer">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 needin= g to synchronize the dump with a logical replication slot) as per the pgdg<= /font>

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 on= ly 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;
=C2=A0 slot_n= ame =C2=A0| consistent_point | =C2=A0 =C2=A0snapshot_name =C2=A0 =C2=A0| ou= tput_plugin
-------------+------------------+---------------------+-----= ----------
=C2=A0lsr_sync_01 | 0/C000110 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 00= 000003-00000002-1 | pgoutput

Regards,

Durga Mahesh

=


Hi=C2=A0Durgamahesh,

I am= not sure what you are after with matching pg_dump and replication slot tog= ether unless you are trying=C2=A0to get a dump to handle the initial data s= ync. 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 usi= ng the export function

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

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

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

Then you= tell pg_dump to use that snapshot name snapshot with this option
--snapshot=3Ds= napshotname

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

Once pg_restore is done on the des= tination , you can create a subscription using that slotname option probabl= y and specify copy_data =3D false.

Keep in mind the WAL will build u= p during this process,=C2=A0 not sure what the benefit=C2=A0would be just a= llowing logical replication to do the initial sync.


Thanks
Ju= stin
=C2=A0
--00000000000088a37d0623327a05--