Обсуждение: recovery error while running any statement

Hello Experts,

It's postgres aurora version 16. While  running the ALTER command on any object we see an error "Only RowExclusiveLock or less can be acquired on database objects during recovery". If I run any DML it gives an error stating 'cannot execute UPDATE in a read-only transaction' , then I tried setting "set transaction read-write" and it erroring out with "cannot set transaction read-write mode during recovery".

Want to understand , what is the cause of this error and how to fix this?

On Thu, Jan 9, 2025 at 11:42 AM yudhi s <learnerdatabase99@gmail.com> wrote:

Hello Experts,

It's postgres aurora version 16. While  running the ALTER command on any object we see an error "Only RowExclusiveLock or less can be acquired on database objects during recovery". If I run any DML it gives an error stating 'cannot execute UPDATE in a read-only transaction' , then I tried setting "set transaction read-write" and it erroring out with "cannot set transaction read-write mode during recovery".

Want to understand , what is the cause of this error and how to fix this?

The cause of the error is that the instance is in recovery mode, just like the error message says.

Is your system a cluster?

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

On 1/9/25 08:42, yudhi s wrote:
> Hello Experts,
> It's postgres aurora version 16. While  running the ALTER command on any
> object we see an error "/Only RowExclusiveLock or less can be acquired
> on database objects during recovery/". If I run any DML it gives an
> error stating '/cannot execute UPDATE in a read-only transaction/' ,
> then I tried setting "/set transaction read-write/" and it erroring out
> with "/cannot set transaction read-write mode during recovery/".
>
> Want to understand , what is the cause of this error and how to fix this?

Per:

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html

Aurora Postgres is not the community edition, you should probably take
this up with AWS support.

On Thu, Jan 9, 2025 at 10:21 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 1/9/25 08:42, yudhi s wrote:
> Hello Experts,
> It's postgres aurora version 16. While  running the ALTER command on any
> object we see an error "/Only RowExclusiveLock or less can be acquired
> on database objects during recovery/". If I run any DML it gives an
> error stating '/cannot execute UPDATE in a read-only transaction/' ,
> then I tried setting "/set transaction read-write/" and it erroring out
> with "/cannot set transaction read-write mode during recovery/".
>
> Want to understand , what is the cause of this error and how to fix this?

Per:

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html

Aurora Postgres is not the community edition, you should probably take
this up with AWS support.

Sure will raise a ticket. I was trying to understand though , if it's possible in postgres to have these sudden "recovery errors" possible because of some long running DML/DDL killed unexpectedly using the "pg_terminate/pg_cancel" command? And if this error appears in community postgres and if it stays for a long time what we used to do?