public inbox for [email protected]  

help / color / mirror / Atom feed

From: Justin <[email protected]>
To: Durgamahesh Manne <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Greg Sabino Mullane <[email protected]>
Cc: Koichi Suzuki <[email protected]>
Subject: Re: Synchronize the dump with a logical slot with --snapshot
Date: Sat, 28 Sep 2024 13:39:59 -0400
Message-ID: <CALL-XeNd=8v7BF1RyamWK7ur9rjbbC40A3N8GFjBC+ZX3azeVQ@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoKi3SQ0jKqQFDuOTfeE1XRmtUqtGjCB+DTacpwEuG5MQQ@mail.gmail.com>
References: <CAJCZkoJ_=jAf+HVh8_fGRw-C+HAD7M0k+HKD=4Bvc1KcbZVnfA@mail.gmail.com>
	<CAJCZkoLGxyNESVWD5uPwWcA=hye44LQ9=v4=TJDA0s-Rhd04jg@mail.gmail.com>
	<CAJCZkoKi3SQ0jKqQFDuOTfeE1XRmtUqtGjCB+DTacpwEuG5MQQ@mail.gmail.com>

On Sat, Sep 28, 2024 at 1:04 AM Durgamahesh Manne <[email protected]>
wrote:

> Hi Team
>
> Can anyone respond to my question from respected team members ?
>
> Durga Mahesh
>
> On Thu, Sep 26, 2024 at 2:23 AM Durgamahesh Manne <
> [email protected]> wrote:
>
>> Hi Team
>>
>> --snapshot=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 internal snapshot
>> with it ?
>>
>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14
>>
>> example osdb_lsr=# 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, <
>> [email protected]> wrote:
>>
>>> Hi Team
>>>
>>> --snapshot=*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=# 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-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-CREATESUBSCRIPTION-PARAMS-WITH-SL...
and no sync option.

Then you tell pg_dump to use that snapshot name snapshot with this option
--snapshot=snapshotname

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 = 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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Synchronize the dump with a logical slot with --snapshot
  In-Reply-To: <CALL-XeNd=8v7BF1RyamWK7ur9rjbbC40A3N8GFjBC+ZX3azeVQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox