Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVvnx-00Cjow-Gv for pgsql-general@arkaria.postgresql.org; Thu, 09 Jan 2025 16:54:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tVvnx-004baE-0A for pgsql-general@arkaria.postgresql.org; Thu, 09 Jan 2025 16:54:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVvnw-004ba5-LM for pgsql-general@lists.postgresql.org; Thu, 09 Jan 2025 16:54:44 +0000 Received: from mail-oi1-x233.google.com ([2607:f8b0:4864:20::233]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVvnt-000lTK-0y for pgsql-general@lists.postgresql.org; Thu, 09 Jan 2025 16:54:43 +0000 Received: by mail-oi1-x233.google.com with SMTP id 5614622812f47-3ebb2d8dac4so563669b6e.0 for ; Thu, 09 Jan 2025 08:54:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736441680; x=1737046480; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=yAS7nN1aOHx9Q0Wj6o+Qef+bTUFVTgvpqiwyRMXadLI=; b=AEIjNVv575qOAGDunTehDBPCLUWux9+0gjnb+IHsAn9ZSlY5D8wi3nOGjVydfVdPSC 3x45+kJfM1ehog55r8aIARPcEuP1hPanJCrPNPgb4M7dqIf0B9b7/3X7jtOtBngk3inQ 0d0m6BdjEg8u4mJHfZpIRNyvM9eYkXtD80t5ZVC8bEDGQ8d7DPg8mWAXorxAkT8YS3Mn oe6jyATk2fbJGtOnbZ9Dgs+vhXp6AHDETHhp9q3eQwm3Dq+7g65sXPdCrhTGdZO8QCT/ nq72qRIh6jWPacQr5AEW/NZtKZGbp58rx55798/z+eM3X1DF0xiQVaDcF6JZxrdcE0fp Ahww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736441680; x=1737046480; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=yAS7nN1aOHx9Q0Wj6o+Qef+bTUFVTgvpqiwyRMXadLI=; b=ZYJzs6HfvlyHlYZ+woHKZXiFpFtDJVWN3Ng6K04qxnm8GOIDQ8uwkXOMjheZm0gNvM GVLA77TADQE8/mRej5z5kroiihXoeB56QKbEpHKyt+wwzlubsjQt1KWMD1d9gk0F3Gfi h30mtuPKgtEcLgsqhtvhPk6x0Ebm+Yl0fN0em92p+Sgnhd1hBOyYNjc//Y1zdJiqIYoU 8BhXxD9liis774VML4T4gYZP6Uw3FM/1j0n2nx7Q4YR6ma6BBBYjutlXIjEP3mVH+p+L blGvvHh466p3sHxO0DZKstfAj/nd1CI2gAMOeoHknKPOvgZEt80tNva2f0rLxzcWl5ZT VfOQ== X-Gm-Message-State: AOJu0Yy32U6afF8CjY+HOCm7MoK6v3MKouN9uNcSXjhh1D8Zeh0//cPw 2iZaNBeeaDOIAAcSRs7RqV805CkheRfuzis9zt/hJ5EfTBRNgcxC0rNqbLYpHkBWizLPISZpIVP NpiA+yqQILINKfZIP8UTjdKiXE20= X-Gm-Gg: ASbGncvMgEYSjQXrFETdUv/iuUq/thfaXseM3/P9vlC1bMG1AxnWvWNv1hHqJGktuR+ 8dmmkNqJ59lKIyq517T5SW6h+2QLODnsy27VIK318rNPeZGlYVMqgSJnYMXg0OdZFLzVCWDDM X-Google-Smtp-Source: AGHT+IEvHzz5+KJwljtuxVgOOdkGEcRr2k3GQ6kVK+wWw3c5NI7lv48nwRiV3VsnH+09sGAGUWJDSI9Y574t61x+C9U= X-Received: by 2002:a05:6870:e390:b0:29d:c832:7ef6 with SMTP id 586e51a60fabf-2aa0699026dmr3815156fac.39.1736441680021; Thu, 09 Jan 2025 08:54:40 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Thu, 9 Jan 2025 22:24:26 +0530 X-Gm-Features: AbW1kvZXFvVc4AqJiIWwEZJFRRsxSMN9WH4FMtFdHqIN7FNPqzlnnR39uKq2IKM Message-ID: Subject: Re: recovery error while running any statement To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000082d1de062b48d536" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000082d1de062b48d536 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Ron Johnson wrote: > On Thu, Jan 9, 2025 at 11:42=E2=80=AFAM 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= ? >> > > The cause of the error is that the instance is in recovery mode, just lik= e > the error message says. > > Is your system a cluster? > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --00000000000082d1de062b48d536 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes , it's a cluster aurora global da= tabase. I am a bit new to this. Can you please explain a bit more, what mus= t be the reason and what should we do to make it back online to serve the q= ueries? It's a non-prod though.

On Thu, Jan 9, 202= 5 at 10:19=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, Jan = 9, 2025 at 11:42=E2=80=AFAM yudhi s <learnerdatabase99@gmail.com> wrote:
Hello Experts,
It's postgres aurora version 1= 6. While=C2=A0 running the ALTER command on any object we see an error &quo= t;Only RowExclusiveLock or less can be acquired on database objects duri= ng recovery". If I run any DML it gives an error stating 'c= annot execute UPDATE in a read-only transaction' , then I tried set= ting "set transaction read-write" and it erroring out with= "cannot set transaction read-write mode during recovery".=

Want to understand=C2=A0, what is the cause of th= is error and how to fix this?

T= he cause of the error is that the instance is in recovery mode, just like t= he error message says.

Is your system a cluster?

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