public inbox for [email protected]help / color / mirror / Atom feed
Starting logical replication at arbitrary point that's available in WAL 3+ messages / 2 participants [nested] [flat]
* Starting logical replication at arbitrary point that's available in WAL @ 2024-12-27 11:39 Alexander Uvizhev <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Alexander Uvizhev @ 2024-12-27 11:39 UTC (permalink / raw) To: [email protected] Hi, I'm doing a logical replication using streaming replication protocol and I'm trying to start a stream from a certain arbitrary point that's available in WAL. However, both CREATE_REPLICATION_SLOT and pg_create_logical_replication_slot() create slot with nearly last LSN/XID. Is it possible to create a replication slot with arbitrary LSN/XID values? Also pg_create_logical_replication_slot() for some reason gives different result than CREATE_REPLICATION_SLOT: new slot's `catalog_xmin` is set to the smallest `catalog_xmin` among already existing slots. Looks like a bug. I'm using PostgreSQL 16.6. -- AU || ^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Starting logical replication at arbitrary point that's available in WAL @ 2024-12-27 12:20 Zhijie Hou (Fujitsu) <[email protected]> parent: Alexander Uvizhev <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Zhijie Hou (Fujitsu) @ 2024-12-27 12:20 UTC (permalink / raw) To: Alexander Uvizhev <[email protected]>; +Cc: [email protected] <[email protected]> On Friday, December 27, 2024 7:39 PM Alexander Uvizhev <[email protected]> wrote: Hi, > I'm doing a logical replication using streaming replication protocol and > I'm trying to start a stream from a certain arbitrary point that's > available in WAL. However, both CREATE_REPLICATION_SLOT and > pg_create_logical_replication_slot() create slot with nearly last LSN/XID. > > Is it possible to create a replication slot with arbitrary LSN/XID values? I think it's not supported to specify arbitrary LSN/XID values for a newly created slot in core. However, if you have an existing slot with an older LSN/XID, you can copy it using pg_copy_logical_replication_slot, and then advance the copied slot to your desired position with pg_replication_slot_advance. > > Also pg_create_logical_replication_slot() for some reason gives > different result than CREATE_REPLICATION_SLOT: new slot's `catalog_xmin` > is set to the smallest `catalog_xmin` among already existing slots. > Looks like a bug. Could you provide a script to reproduce this issue ? That would be helpful in diagnosing the reason. Best Regards, Hou zj ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Starting logical replication at arbitrary point that's available in WAL @ 2024-12-27 13:18 Alexander Uvizhev <[email protected]> parent: Zhijie Hou (Fujitsu) <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Alexander Uvizhev @ 2024-12-27 13:18 UTC (permalink / raw) To: Zhijie Hou (Fujitsu) <[email protected]>; +Cc: [email protected] <[email protected]> On 12/27/24 13:20, Zhijie Hou (Fujitsu) wrote: > On Friday, December 27, 2024 7:39 PM Alexander Uvizhev<[email protected]> wrote: > > Hi, > >> I'm doing a logical replication using streaming replication protocol and >> I'm trying to start a stream from a certain arbitrary point that's >> available in WAL. However, both CREATE_REPLICATION_SLOT and >> pg_create_logical_replication_slot() create slot with nearly last LSN/XID. >> >> Is it possible to create a replication slot with arbitrary LSN/XID values? > I think it's not supported to specify arbitrary LSN/XID values for a newly > created slot in core. However, if you have an existing slot with an older LSN/XID, you > can copy it using pg_copy_logical_replication_slot, and then advance the copied > slot to your desired position with pg_replication_slot_advance. Thanks for the idea, I've already discovered it and it requires me to implement some script to advance that dedicated slot, which I was hoping to avoid. >> Also pg_create_logical_replication_slot() for some reason gives >> different result than CREATE_REPLICATION_SLOT: new slot's `catalog_xmin` >> is set to the smallest `catalog_xmin` among already existing slots. >> Looks like a bug. > Could you provide a script to reproduce this issue ? > That would be helpful in diagnosing the reason. Unfortunately, I have no such script. But it looks like this: test=> select slot_name, catalog_xmin, restart_lsn, confirmed_flush_lsn from pg_replication_slots; slot_name | catalog_xmin | restart_lsn | confirmed_flush_lsn ---------------+--------------+-------------+--------------------- inactive_slot | 10073 | 0/1101DB88 | 0/1101DB88 active_slot | 42607 | 0/17A87410 | 0/17A87410 physical | | 0/17A875A0 | test=> select pg_create_logical_replication_slot('test_slot', 'decoderbufs'); slot_name | catalog_xmin | restart_lsn | confirmed_flush_lsn ---------------+--------------+-------------+--------------------- inactive_slot| 10073 | 0/1101DB88 | 0/1101DB88 active_slot | 42607 | 0/17A87410 | 0/17A87410 physical | | 0/17A875D8 | test_slot | 10073 | 0/17A875A0 | 0/17A875D8 While using CREATE_REPLICATION_SLOT would give me the same slot with `catalog_xmin` = 42607. Hope this helps. > Best Regards, > Hou zj -- AU ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-12-27 13:18 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-27 11:39 Starting logical replication at arbitrary point that's available in WAL Alexander Uvizhev <[email protected]> 2024-12-27 12:20 ` Zhijie Hou (Fujitsu) <[email protected]> 2024-12-27 13:18 ` Alexander Uvizhev <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox