Thread: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18540 Logged by: Does PG16 standby database support function pg_replication_origin_advance? Email address: jason.chentj@homecredit.cn PostgreSQL version: 16.1 Operating system: Red Hat Enterprise Linux Server release 7.9 Description: Hi Guys, am testing the PG 16 VERSION standby database replicate slot function. when I issue below command to move the offset for replicate slot on standby database got below error: db_plumc=# select pg_replication_origin_advance('standbyslot','5EE/5D1B13F8'::pg_lsn); ERROR: cannot manipulate replication origins during recovery Seems the function pg_replication_origin_advance is not supported running on standby instance. It there any workaround for it? Thanks Jason
Re: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
From
Michael Paquier
Date:
On Mon, Jul 15, 2024 at 08:28:26AM +0000, PG Bug reporting form wrote: > Seems the function pg_replication_origin_advance is not supported running on > standby instance. It there any workaround for it? Not that I know of. One would need to study how to lift this restriction in place since the introduction of replication origins in 5aa2350426c4. -- Michael
Attachment
回复: [External]Re: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
From
"Jason ChenTJ (CN)"
Date:
Hi Michael,
Thanks for your reply!
I just check the patch: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5aa235042
The related source code located on src/backend/replication/logical/origin.c and it's marked as new file .
![](/media/maillist_attaches/pgsql-bugs/2024/07/22/ZQ0PR01MB1080F9DCA995F5190441F0E292A82@ZQ0PR01MB1080.CHNPR01.prod.partner.outlook.cn/image.png)
The error is from this function: replorigin_check_prerequisites
![](/media/maillist_attaches/pgsql-bugs/2024/07/22/ZQ0PR01MB1080F9DCA995F5190441F0E292A82@ZQ0PR01MB1080.CHNPR01.prod.partner.outlook.cn/image-1.png)
And you perform the WAL log offset reset function, must do a validation by replorigin_check_prerequisites.
![](/media/maillist_attaches/pgsql-bugs/2024/07/22/ZQ0PR01MB1080F9DCA995F5190441F0E292A82@ZQ0PR01MB1080.CHNPR01.prod.partner.outlook.cn/image-2.png)
So am not sure it's a bug or not on standby database, or some dev can fix it on next version?
(like add some logicals to identify the database role first...)
========================================================================================
On Mon, Jul 15, 2024 at 08:28:26AM +0000, PG Bug reporting form wrote:
> Seems the function pg_replication_origin_advance is not supported running on
> standby instance. It there any workaround for it?
Not that I know of. One would need to study how to lift this
restriction in place since the introduction of replication origins in
5aa2350426c4.
--
Michael
> Seems the function pg_replication_origin_advance is not supported running on
> standby instance. It there any workaround for it?
Not that I know of. One would need to study how to lift this
restriction in place since the introduction of replication origins in
5aa2350426c4.
--
Michael
========================================================================================
Thanks
Jason
发件人: Michael Paquier <michael@paquier.xyz>
发送时间: 2024年7月16日 9:00
收件人: Jason ChenTJ (CN) <Jason.ChenTJ@homecredit.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
主题: [External]Re: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
发送时间: 2024年7月16日 9:00
收件人: Jason ChenTJ (CN) <Jason.ChenTJ@homecredit.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
主题: [External]Re: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
This message is from an EXTERNAL SENDER - be CAUTIOUS, particularly with links and attachments.
________________________________
________________________________
Attachment
Re: 回复: [External]Re: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?
From
Bertrand Drouvot
Date:
Hi, On Mon, Jul 22, 2024 at 02:51:35AM +0000, Jason ChenTJ (CN) wrote: > Hi Michael, > > Thanks for your reply! > > I just check the patch: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5aa235042 > > The related source code located on src/backend/replication/logical/origin.c and it's marked as new file . > > [cid:9554a9eb-7db8-400f-a815-21f3de8917e7] > > > The error is from this function: replorigin_check_prerequisites > > [cid:57394eb2-b636-4c7f-8c99-c0ecdc3de3a5] > > And you perform the WAL log offset reset function, must do a validation by replorigin_check_prerequisites. > > [cid:856de95d-cc1d-4f6e-91cc-27575f3fc243] > > So am not sure it's a bug or not on standby database, or some dev can fix it on next version? > (like add some logicals to identify the database role first...) As one can not create a subscription on a standby nor execute pg_replication_origin_create() on a standby then I think that the only way to get a replication origin on a standby is when it comes from the primary (i.e if a subscription has been created on the primary or one executed pg_replication_origin_create() on the primary). Then it seems to me that pg_replication_origin_advance() should be executed on the primary (if needed). Could you please describe your use case? Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com