pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I change replication slot's restart_lsn from SQL?
5+ messages / 2 participants
[nested] [flat]
* How can I change replication slot's restart_lsn from SQL?
@ 2023-05-16 14:23 hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2023-05-16 14:23 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Hi,
I'm working on a workaround for a bug in Pg
(https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
I want to create replication slot, and advance is manually, keeping it
always a bit lagging behind real replication slot.
I can create slot, no problem:
select pg_create_logical_replication_slot('depesz', 'test_decoding');
and then I can, theoretically, advance it to whatever position with
select * from pg_replication_slot_advance('depesz', '...');
*BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
was.
How can I advance restart_lsn of a slot?
Generally my idea is to get lsn from real replication slot, subtract,
let's say 1GB from it, and advance my "fake slot" to this value, this
keeping always buffer of 1GB in case the bug with removed wal happened
again.
I will be doing this on Pg12, which can limit my options, but perhaps
there is some way to do it via plain(ish) SQL ?
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I change replication slot's restart_lsn from SQL?
@ 2023-05-16 17:35 hubert depesz lubaczewski <depesz@depesz.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2023-05-16 17:35 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, May 16, 2023 at 04:23:02PM +0200, hubert depesz lubaczewski wrote:
> Hi,
> I'm working on a workaround for a bug in Pg
> (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> I want to create replication slot, and advance is manually, keeping it
> always a bit lagging behind real replication slot.
>
> I can create slot, no problem:
> select pg_create_logical_replication_slot('depesz', 'test_decoding');
> and then I can, theoretically, advance it to whatever position with
> select * from pg_replication_slot_advance('depesz', '...');
>
> *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> was.
>
> How can I advance restart_lsn of a slot?
>
> Generally my idea is to get lsn from real replication slot, subtract,
> let's say 1GB from it, and advance my "fake slot" to this value, this
> keeping always buffer of 1GB in case the bug with removed wal happened
> again.
>
> I will be doing this on Pg12, which can limit my options, but perhaps
> there is some way to do it via plain(ish) SQL ?
Hi,
if doing it via SQL is not an option, is there any way to have
replication slot and modify it's restart_lsn, regardless of whether it
will b e physical, or logical, and what tool shoudl I use.
I tried with pg_recvlogical, and read
https://www.postgresql.org/docs/current/protocol-replication.html but
I don't see how I can make restart_lsn advance to anything.
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I change replication slot's restart_lsn from SQL?
@ 2023-05-17 06:16 Laurenz Albe <laurenz.albe@cybertec.at>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-05-17 06:16 UTC (permalink / raw)
To: depesz@depesz.com, pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > I'm working on a workaround for a bug in Pg
> > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> > I want to create replication slot, and advance is manually, keeping it
> > always a bit lagging behind real replication slot.
> >
> > I can create slot, no problem:
> > select pg_create_logical_replication_slot('depesz', 'test_decoding');
> > and then I can, theoretically, advance it to whatever position with
> > select * from pg_replication_slot_advance('depesz', '...');
> >
> > *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> > was.
> >
> > How can I advance restart_lsn of a slot?
> >
> > Generally my idea is to get lsn from real replication slot, subtract,
> > let's say 1GB from it, and advance my "fake slot" to this value, this
> > keeping always buffer of 1GB in case the bug with removed wal happened
> > again.
> >
> > I will be doing this on Pg12, which can limit my options, but perhaps
> > there is some way to do it via plain(ish) SQL ?
>
> if doing it via SQL is not an option, is there any way to have
> replication slot and modify it's restart_lsn, regardless of whether it
> will b e physical, or logical, and what tool shoudl I use.
>
> I tried with pg_recvlogical, and read
> https://www.postgresql.org/docs/current/protocol-replication.html but
> I don't see how I can make restart_lsn advance to anything.
You could shutdown the server and edit the file in "pg_replslot" with
a hex editor. Not very convenient, and you'd have to study the source
to understand the format of the file.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I change replication slot's restart_lsn from SQL?
@ 2023-05-17 06:21 hubert depesz lubaczewski <depesz@depesz.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2023-05-17 06:21 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Wed, May 17, 2023 at 08:16:41AM +0200, Laurenz Albe wrote:
> On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > > I'm working on a workaround for a bug in Pg
> > > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> > > I want to create replication slot, and advance is manually, keeping it
> > > always a bit lagging behind real replication slot.
> > >
> > > I can create slot, no problem:
> > > select pg_create_logical_replication_slot('depesz', 'test_decoding');
> > > and then I can, theoretically, advance it to whatever position with
> > > select * from pg_replication_slot_advance('depesz', '...');
> > >
> > > *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> > > was.
> > >
> > > How can I advance restart_lsn of a slot?
> > >
> > > Generally my idea is to get lsn from real replication slot, subtract,
> > > let's say 1GB from it, and advance my "fake slot" to this value, this
> > > keeping always buffer of 1GB in case the bug with removed wal happened
> > > again.
> > >
> > > I will be doing this on Pg12, which can limit my options, but perhaps
> > > there is some way to do it via plain(ish) SQL ?
> >
> > if doing it via SQL is not an option, is there any way to have
> > replication slot and modify it's restart_lsn, regardless of whether it
> > will b e physical, or logical, and what tool shoudl I use.
> >
> > I tried with pg_recvlogical, and read
> > https://www.postgresql.org/docs/current/protocol-replication.html but
> > I don't see how I can make restart_lsn advance to anything.
>
> You could shutdown the server and edit the file in "pg_replslot" with
> a hex editor. Not very convenient, and you'd have to study the source
> to understand the format of the file.
OK. Shutting down production server every 5 minutes to advance the slot
isn't really an option.
How do "normal" wal consumers update restart_lsn? Is there a reason why
it can't be done using pg_rec(eive|v)* ?
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I change replication slot's restart_lsn from SQL?
@ 2023-05-17 13:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-05-17 13:32 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Wed, 2023-05-17 at 08:21 +0200, hubert depesz lubaczewski wrote:
> On Wed, May 17, 2023 at 08:16:41AM +0200, Laurenz Albe wrote:
> > On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > > > I'm working on a workaround for a bug in Pg
> > > > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> > > > I want to create replication slot, and advance is manually, keeping it
> > > > always a bit lagging behind real replication slot.
> > > >
> > > > How can I advance restart_lsn of a slot?
> > >
> > > if doing it via SQL is not an option, is there any way to have
> > > replication slot and modify it's restart_lsn, regardless of whether it
> > > will b e physical, or logical, and what tool shoudl I use.
> > >
> > > I tried with pg_recvlogical, and read
> > > https://www.postgresql.org/docs/current/protocol-replication.html but
> > > I don't see how I can make restart_lsn advance to anything.
> >
> > You could shutdown the server and edit the file in "pg_replslot" with
> > a hex editor. Not very convenient, and you'd have to study the source
> > to understand the format of the file.
>
> OK. Shutting down production server every 5 minutes to advance the slot
> isn't really an option.
>
> How do "normal" wal consumers update restart_lsn? Is there a reason why
> it can't be done using pg_rec(eive|v)* ?
I thought that was a one-time operation. No idea if there are alternatives.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-05-17 13:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-16 14:23 How can I change replication slot's restart_lsn from SQL? hubert depesz lubaczewski <depesz@depesz.com>
2023-05-16 17:35 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-05-17 06:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-17 06:21 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-05-17 13:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox