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 1t96qA-007obT-DG for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2024 18:02:42 +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 1t96q7-000fEx-MZ for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2024 18:02:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t96q7-000fEp-74 for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 18:02:39 +0000 Received: from mail-oo1-xc2b.google.com ([2607:f8b0:4864:20::c2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t96q5-000fcm-1f for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 18:02:38 +0000 Received: by mail-oo1-xc2b.google.com with SMTP id 006d021491bc7-5eba976beecso1032084eaf.0 for ; Thu, 07 Nov 2024 10:02:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731002556; x=1731607356; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=i4Q1kmC71+3QUoS7XvQiAQkPSxYX6sc0PtIK7u/4Iq8=; b=C51T0AuF4VjvZolu+VcBDwksaAgC8MJ+bJpn9G33i/v1Fx+N4/SRVZby/hlr8mTcZi FhwnEdA80nsHG9RRxlTC7z+0GwSTr2CQulepgdwraZ+o766XHUhZwxvAviJG4vcr4j5q AlJjBloEnecWxsM+Qeo0SbNuVbbWBnRkOplVQJ7cwrE2Npr6QrQ94EFUdEJQfX5ZIJ6Z VUfc8DLA979RYiGnKgsD0oMK+LCQqPMQxsjJxspYMjFrvSiqDNvGHJ37SzEK3D11gFFE jRUq3NqW5LbNQWPGjgbj94PFAiolUYZinlEYQQOOK5PJgtIe7/igyEwnihiLR2dSJAi9 0YGQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731002556; x=1731607356; h=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=i4Q1kmC71+3QUoS7XvQiAQkPSxYX6sc0PtIK7u/4Iq8=; b=WSqcak3dhUufuM1ZLYN+8SbpF97kf1AqzSuoWjCwdW2Uz4yiVmydrnQixzLspocRuh l2h+eNlpdyLFvX+w/HQuwIaVOCZkntp9d7DWmnTRS/gbDHVctrLs55YLquEHdEjqEVet Fl0G9n1qBpudPO8Q/UQqpXvmci5ohzkQ11ctRXdwd49f/43WR0PHjWWrXqCdSSc3cQ+8 69063pbaF4MHva34ENu2u8zPtm23cvZVK6Yt2tIWN3FYgr7K3kbw6nMjQsh59POu2H+v EKdMaD2DOPhMm8DhRaEXEPU8yoiwn76OBd+Sti7GgXhXcVhy+L90pekZs0HBlsq78HZb EasQ== X-Gm-Message-State: AOJu0YxQ1XfHZyzLQS5vrw2MxRyj9rPvFtqttjuEbZRJVbVd9Zipla6d DYcoHi8L96dd/mJmIUL3LFj7gSbMKsDm+eQnT5FBA0Z/0Cx/lSOj73Av9Fy+KdcUgPaG9lWwccr kd4SQmT/ETZlpagNNNNlIB6kTlztp4A== X-Google-Smtp-Source: AGHT+IEgCyw6brMr2zuukBD6ApuaCgQ9I4N659eHRGqE4XtAIvgdDIqMdCocOZEQh4bHGr6m6cu2ZhfoKzBI8cWd3wY= X-Received: by 2002:a4a:b2c5:0:b0:5eb:cff9:d208 with SMTP id 006d021491bc7-5ee56987becmr332642eaf.2.1731002556047; Thu, 07 Nov 2024 10:02:36 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 7 Nov 2024 13:02:25 -0500 Message-ID: Subject: Re: Running rsync backups in pg15 To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000756cdf0626567000" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000756cdf0626567000 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM Evan Rempel wrote= : > We use a similar approach, but instead of using rsync, we use our backup > software directly which is an incremental forever tool. Allows backup of = TB > DBs in short minutes. Switching to pgbackrest is actually a step backward= s > for us. > Last night's pgbackrest incremental backup of a 5.1TB database took a whopping 92 *seconds*. How's that a backwards step? Sure, the weekly full backup takes 84 minutes, but that's in so way shape or form painfully slow. > But as the OP states, if you have to keep the postgresql session open for > the pg_start_backup and the pg_stop_backup then we will have to do a > significant architectual change. > > Anyone know if there is a straight forward way to allows the > pg_start_backup and the pg_stop_backup to be run in different sessions? > > > -- > Evan > > ------------------------------ > *From:* Ron Johnson > *Sent:* November 7, 2024 9:34 AM > *To:* pgsql-admin@postgresql.org > *Subject:* Re: Running rsync backups in pg15 > > On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM Murthy Nunna wr= ote: > > Hi, > > > > In PG14 and earlier, there is no requirement to keep database connection > while rsync is in progress. However, there is a change in PG15+ that > requires rsync to be while we have the same database session open that > executes SELECT pg_backup_start('label'). This change requires a rewrite > of existing scripts we have. > > > > Currently (pg14): > > > > In bash script (run from cron) > > 1. psql Select pg_start_backup > 2. rsync > 3. psql Select pg_stop_backup > > > > In pg15 and later: > > > > In bash script (run from cron) > > > > psql > > Select pg_start_backup > > ! run-rsync-script > > Select pg_stop_backup > > > > It can be done, but it makes it ugly to check errors and so forth that > occur in the rsync script. > > > > Anybody found an elegant way of doing this? > > > Run pgbackrest instead of rsync, > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000756cdf0626567000 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM E= van Rempel <erempel@uvic.ca> w= rote:
We use a similar approach, but instead of using rsync, we use our backup so= ftware directly which is an incremental forever tool. Allows backup of TB D= Bs in short minutes. Switching to pgbackrest is actually a step backwards f= or us.

Last night's p= gbackrest incremental backup of a 5.1TB database took a whopping 92 seco= nds.=C2=A0 How's that a backwards step?

Su= re, the weekly full backup takes 84 minutes, but that's in so way shape= or form painfully slow.
=C2=A0
But as the OP states, if you have to keep the postgresql sess= ion open for the pg_start_backup and the pg_stop_backup=C2=A0then we will h= ave to do a significant architectual change.

Anyone know if there is a straight forward way to allows the pg_start_backu= p and the pg_stop_backup to be run in different sessions?


--
Evan

From:=C2=A0Ron Johnson <ronljohnsonjr@gmail.com>
Sent:=C2=A0November 7, 2024 9:34 AM
To:=C2=A0pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject:=C2=A0Re: Running rsync backups in pg15
=C2=A0
On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM Murthy= Nunna <mnunna@fnal.gov> wrote:

Hi,

=C2=A0

In PG14 and earli= er, there is no requirement to keep database connection while rsync is in p= rogress. However, there is a change in PG15+ that requires rsync to be whil= e we have the same database session open that executes SELECT pg_backup_start('label'). This ch= ange requires a rewrite of existing scripts we have.

=C2=A0

Currently (pg14):

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 In bash script (run from cron)

  1. psql Select pg_start_backup<= /li>
  2. rsync
  3. psql Select pg_stop_backup

=C2=A0

In pg15 and later:

=C2=A0

In bash script (run from cron= )

=C2=A0

psql

Select pg_start_backup

! run-rsync-script

Select pg_stop_backup

=C2=A0

It can be done, but it makes it ugly to check er= rors and so forth that occur in the rsync script.

=C2=A0

Anybody found an elegant way of doing this?


Run pgbackrest instead of rsync,

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


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