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 1sA7MZ-007fK5-Qo for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 12:16:06 +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 1sA7MZ-00BhYb-G0 for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 12:16:03 +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 1sA7MZ-00BhYT-3b for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 12:16:03 +0000 Received: from mail-lf1-x132.google.com ([2a00:1450:4864:20::132]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sA7MV-000NFL-Ii for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 12:16:02 +0000 Received: by mail-lf1-x132.google.com with SMTP id 2adb3069b0e04-51fcb7dc722so1813137e87.1 for ; Thu, 23 May 2024 05:15:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716466559; x=1717071359; 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=ziKxNxyExeuYIg1/0Nf+7CzCi0qylE5dajw0J1pckqg=; b=AhpiYMEARJaPp6YN/i+fOF4HW4ERlCQCqQ0tT2+ogv+pj/cWfmYfcZUqkDK1MXRnuQ IZo9xWhOlqvcraGOanNnp4kaAhVuQzS6vIK7vSOwUIGVQgtpAS04+wKqdMdWA8jYgzBG fE074OoNHh+p/ZhCJRhY5epYCHgiFjiXzji90tYSB8DH1RTQ5xT/j4V2QlxRiSsHUuMm uj7abSjgHZhwg3zE5N56nEes0YIte6aKxaG4blQ9454XvlIeQwWL/Uuh7G9Q7SAYndbC 9XWA7uf/NmddoCUZ3tZCY7gyCJoOCgrrWKkarbQTDpdqMHb3RGLoYsolUEFhV8yb/xww PhJg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716466559; x=1717071359; 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=ziKxNxyExeuYIg1/0Nf+7CzCi0qylE5dajw0J1pckqg=; b=PxiklzzsPIamM1m60XQPxbarjIxQ1OBuiAzjQ6kqnqzml8/Hz66aHpFsQ8HwdjZVJE mXesqYm30l33vxh8DJM5MEupD1gmTqstrFGLqhoT7tLFUBnr7urXsTR6QLkaaKRkiFOI pPNXKwoMEwn8oP2lX20Ep0402QbNOmOjq3eUn1NrFlIiQbrjP3RmE1dtBSQVazVe5peu ydFRURe0QMYtnGpUIIvOMSvhIGLdIrIOCmLA7rcZmImFVfP0xNs6ddFtUcQnOAkkszbw OJ+loLCUDtzKa6wHHsKIBSlShqQRaCTf6n/EPOQsXFE4Ed8PrdOiXuN2GVTqNEKoPbV3 WQww== X-Gm-Message-State: AOJu0Yy9WmzGE8WsqxfyPKDbYT6+aQYHR2V08UK2xjVcicRlraXsGyts ESQeUXICDWbmUI7jl14kI6EyiNsT5oEQ3FTgD9gn1aIoSP83utApKxo6TmHmaJ1EgvIIdNaaKbL Y9+JcRmzHHujGZQPgy3iLzrmwUXiw X-Google-Smtp-Source: AGHT+IEYWOEnC6cd+uSlqMIAM4ZGnVwoDd9lFOHjst2FbCz4Lwzy+T/osAEpMteiqkAHOofkw8yCpkei9IByUmOYDeo= X-Received: by 2002:a05:6512:32a3:b0:523:892c:44ff with SMTP id 2adb3069b0e04-527eed418e8mr564410e87.1.1716466558703; Thu, 23 May 2024 05:15:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Date: Thu, 23 May 2024 14:15:47 +0200 Message-ID: Subject: Re: Backup failure Postgres To: Jethish Jethish Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000801f1e06191e03b8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000801f1e06191e03b8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Look, you have to compromise somewhere. Let me explain the problem. PG uses MVCC. That means if you update or delete rows, rows are not actually modified or added back to free space. They are just marked for later removal. That actual removal is VACUUM's task. The reason for doing so is that a concurrent transaction might still need to see the modified or deleted row. Now vacuum comes along and wants to actually add things back to free space. On the master that works fine because the master knows all concurrent transactions and what they might still need. So, vacuum will simply skip those rows. However, that knowledge does not extend to the replica. The master does not know which transactions are running on the replica. So a vacuum operation on the master might remove something that's still needed on the replica. Now, that modification made by vacuum also needs to be replayed on the replica. The way that works is by adding all modifications including insert or vacuum or any other change in sequential order to a log (write-ahead-log or WAL). This log is then simply shipped to the replica and replayed. It's not difficult to understand that these changes must be replayed in the same sequential order. Otherwise you get chaos. Now imagine a vacuum operation at the replica which removes stuff that is still needed by a transaction running on the replica like your COPY. Now the replica has 2 choices: - abort the transaction and prefer replaying WAL - pause replaying WAL and wait for the long running transaction The 1st case is obviously bad for the transaction. The 2nd choice is bad for everybody else because WAL can be replayed only in the same order as it is generated. So, nothing that happened after that vacuum can be replayed which leads to stale data on the replica. One way to mitigate this is hot_standby_feedback. That way the replica tells the master from time to time which old rows it still needs to see. The drawback of this is that your tables on the master might accumulate garbage that would normally be removed by vacuum earlier. That can affect query performance. Then you have the option to pause WAL replay one or the other way. max_standby_streaming_delay, disconnecting from the master or explicitly pausing replay, all fall in that category. The last option I know of would be to use logical replication. That comes with other problems. DDL becomes a bit finicky. Initial setup can be tricky. The process applying the changes can become a bottleneck. If you are really time-critical and you just want the COPY job to be done and neither lag nor bloat are acceptable, then maybe you create another streaming replica, disconnect it from the master, run your COPY job and destroy the replica. If 3TB is the database size, then that does not look unsurmountable. Of course, you need the resources. In my environment I'd estimate 3-4 hours. If you want a simple solution, then try hot_standby_feedback. On Thu, May 23, 2024 at 12:46=E2=80=AFPM Jethish Jethish wrote: > Hi Torsten, > > I have tried by increasing the max_standby_streaming_delay but I'm facing > lag issues on the replica server. > > When i increase the max_standby_streaming_delay even if a query runs for = 2 > minutes I'm facing lag issues for 2 minutes. > > Please suggest here. > Data size is 3TB > > On Thu, May 23, 2024, 3:53=E2=80=AFPM Torsten F=C3=B6rtsch > wrote: > >> As the error message says, your query was aborted due to it conflicting >> with recovery. There are many ways to deal with that. You could enable >> hot_standby_feedback on the replica. You could disconnect the replica fr= om >> the master for the time the COPY takes (reset primary_conninfo). You cou= ld >> increase max_standby_streaming_delay. Perhaps you could also wrap the CO= PY >> operation in pg_wal_replay_pause() / pg_wal_replay_resume(). >> >> On Thu, May 23, 2024 at 11:59=E2=80=AFAM Jethish Jethish >> wrote: >> >>> I'm frequently facing the below error while performing backup. Someone >>> please tell how solve this issues. >>> >>> >>> Failed : pg_dump: error: Dumping the contents of table "botsession" >>> failed: PQgetResult() failed. pg_dump: error: Error message from server= : >>> ERROR: canceling statement due to conflict with recovery DETAIL: User q= uery >>> might have needed to see row versions that must be removed. pg_dump: er= ror: >>> The command was: COPY public.botsession (id, userid, data, iscompressed= ) TO >>> stdout; >>> >> --000000000000801f1e06191e03b8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Look, you have=C2=A0to compromise somewhere. Let me explai= n the problem. PG uses MVCC. That means if you update or delete rows, rows = are not actually modified or added back to free space. They are just marked= for later removal. That actual removal is VACUUM's task. The reason fo= r doing so is that a concurrent transaction might still need to see the mod= ified or deleted row. Now vacuum comes along and wants to actually add thin= gs back to free space. On the master that works fine because the master kno= ws all concurrent transactions and what they might still need. So, vacuum w= ill simply skip those rows.

However, that knowledge does= not extend to the replica. The master does not know which transactions are= running on the replica. So a vacuum operation on the master might remove s= omething that's still needed on the replica. Now, that modification mad= e by vacuum also needs to be replayed on the replica. The way that works is= by adding all modifications including insert or vacuum or any other change= in sequential order to a log (write-ahead-log or WAL). This log is then si= mply shipped to the replica and replayed.

It's= not difficult to understand that these changes must be replayed in the sam= e sequential order. Otherwise you get chaos. Now imagine a vacuum operation= at the replica which removes stuff that is still needed by a transaction r= unning on the replica like your COPY. Now the replica has 2 choices:
<= div>
- abort the transaction and prefer replaying WAL
- pause replaying WAL and wait for the long running transaction
The 1st case is obviously bad for the transaction. The 2nd= choice is bad for everybody else because WAL can be replayed only in the s= ame order as it is generated. So, nothing that happened after that vacuum c= an be replayed which leads to stale data on the replica.

One way to mitigate this is hot_standby_feedback. That way the repli= ca tells the master from time to time which old rows it still needs to see.= The drawback of this is that your tables on the master might accumulate ga= rbage that would normally be removed by vacuum earlier. That can affect que= ry performance.

Then you have the option to pause = WAL replay one or the other way. max_standby_streaming_delay, disconnecting= from the master or explicitly pausing replay, all fall in that category.

The last option I know of would be to use logical r= eplication. That comes with other problems. DDL becomes a bit finicky. Init= ial setup can be tricky. The process applying the changes can become a bott= leneck.

If you are really time-critical and you ju= st want the COPY job to be done and neither lag nor bloat are acceptable, t= hen maybe you create another streaming replica, disconnect it from the mast= er, run your COPY job and destroy the replica. If 3TB is the database size,= then that does not look unsurmountable. Of course, you need the=C2=A0resou= rces. In my environment I'd estimate 3-4 hours.

If you want a simple solution, then try hot_standby_feedback.
=
On Thu= , May 23, 2024 at 12:46=E2=80=AFPM Jethish Jethish <jethish777@gmail.com> wrote:
Hi Torsten,

I have tried by increasing the max_= standby_streaming_delay but I'm facing lag issues on the replica server= .

When i increase the ma= x_standby_streaming_delay even if a query runs for 2 minutes I'm facing= lag issues for 2 minutes.

Please suggest here.
Data size is 3TB
=
On Thu= , May 23, 2024, 3:53=E2=80=AFPM Torsten F=C3=B6rtsch <tfoertsch123@gmail.com> wr= ote:
As the error message says, your query was aborted due to it conflicti= ng with recovery. There are many ways to deal with that. You could enable h= ot_standby_feedback on the replica. You could disconnect the replica from t= he master for the time the COPY takes (reset primary_conninfo). You could i= ncrease max_standby_streaming_delay. Perhaps you could also wrap the COPY o= peration in pg_wal_replay_pause() / pg_wal_replay_resume().

On Thu, May 23, = 2024 at 11:59=E2=80=AFAM Jethish Jethish <jethish777@gmail.com>= wrote:
I'm frequently facing the below error while performing backup= . Someone please tell how solve this issues.


Failed : pg_dump: error: Dumping the contents of table "= botsession" failed: PQgetResult() failed. pg_dump: error: Error messag= e from server: ERROR: canceling statement due to conflict with recovery DET= AIL: User query might have needed to see row versions that must be removed.= pg_dump: error: The command was: COPY public.botsession (id, userid, data,= iscompressed) TO stdout;

--000000000000801f1e06191e03b8--