Thread: BUG #18540: Does PG16 standby database support function pg_replication_origin_advance?

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

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

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