Re: Synchronize the dump with a logical slot with --snapshot

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 = 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, <maheshpostgres9(at)gmail(dot)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/
> .. 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, <zzzzz(dot)graf(at)gmail(dot)com> wrote:
>
>>
>>
>> On Sat, Sep 28, 2024 at 1:04 AM Durgamahesh Manne <
>> maheshpostgres9(at)gmail(dot)com> 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 <
>>> maheshpostgres9(at)gmail(dot)com> 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, <
>>>> maheshpostgres9(at)gmail(dot)com> 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-SLOT-NAME
>> 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
>>
>>
>