recovery error while running any statement

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.

Regards
Yudhi

--
Adrian Klaver
adrian.klaver@aklaver.com

Yes , it's a cluster aurora global database. I am a bit new to this. Can
you please explain a bit more, what must be the reason and what should we
do to make it back online to serve the queries? It's a non-prod though.

On Thu, Jan 9, 2025 at 10:19 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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?

On 1/9/25 09:01, yudhi s wrote:

On Thu, Jan 9, 2025 at 10:21 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto: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 <https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html&gt;

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?

Since you have provided essentially zero information about your setup
there is no real way to answer the above.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Jan 9, 2025 at 12:01 PM yudhi s <learnerdatabase99@gmail.com> wrote:

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?

Are you *positive* that you're connected to the *primary* node? Because
this is exactly the kind of error you'll see when connected to the
*secondary* node.

Or maybe, for some reason, AWS failed you over to the secondary node,
making it now the primary node, while what you think is the primary node is
really the secondary node.

That's how "real" Postgresql acts, at least. Not how AWS RDS Postgresql
acts (it's all hidden behind a virtual IP that you always connect to), but
I don't know anything about Aurora.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!