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 1t9LGj-009N1P-7p for pgsql-admin@arkaria.postgresql.org; Fri, 08 Nov 2024 09:27:04 +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 1t9LGg-007ebG-Ku for pgsql-admin@arkaria.postgresql.org; Fri, 08 Nov 2024 09:27: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 1t97AX-00148S-Bf for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 18:23:46 +0000 Received: from mail-lj1-f178.google.com ([209.85.208.178]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t97AT-000iV7-CF for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 18:23:45 +0000 Received: by mail-lj1-f178.google.com with SMTP id 38308e7fff4ca-2fb587d0436so14223841fa.2 for ; Thu, 07 Nov 2024 10:23:42 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731003821; x=1731608621; 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=rikr5SzacXEh8eSxNaOs+ml1bhBp/ogOgL7cjZ/FUd0=; b=H+/jfd1Bu2YVfIAXcwSkSLXNA/bR5aw48rpl0JvRj+7YdToVrs4YMEzISh6mtfLCAG iGQ/Qm9KFGta8TaBt7rDM45ziIw1E6+qsdVBk7C9+HIg6pIUWXpTsOFbeTmfQSDMIKYF Ir2BjjKcu4AKoGpzOHTrHq8RCOtQt4m3ii0aqdWi3dijkY8wlVP2TLYUeuA6H0PCkLXP 8Vxp6nYwDLWNZRvOOZkKr+ExIj+ZTE13VZ/RKZjAT4XNd/kSKIv3L3U2vcoz7WDWaeWh hTkPmR+XD3D+0j295vP4kxy4uJuZePErhDAbirq5qgTikhiNLMKPVwG4YBNHqLSEEvpu ky4g== X-Gm-Message-State: AOJu0YwwKLyBRnDL4aFTUqlk8lAh0QsSQRGZlw7gatx0wf1xOZyZ/mvH goMpS27RtfXFLW42XNR+LtQUhUnc55fAYUYmrcQw1ZKFR94XigGmFOFqkYBz0vH+hwAIJ/CKWNV MtsBOGJg29n+L8zKyqY6yClW5iVM= X-Google-Smtp-Source: AGHT+IF8rqInM/TA8mry7PVp/H9OVhPcYuUWshgLgiK69Fh4KdVM/m8wM6ZFxWVvCdvFGMZ69cxRj+N2UwwRG8O/l0c= X-Received: by 2002:a05:651c:1501:b0:2fa:d534:3ee7 with SMTP id 38308e7fff4ca-2ff1f70932fmr1965271fa.35.1731003820837; Thu, 07 Nov 2024 10:23:40 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Gerald Drouillard Date: Thu, 7 Nov 2024 13:23:29 -0500 Message-ID: Subject: Re: Running rsync backups in pg15 To: Evan Rempel Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000d89639062656bbc5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d89639062656bbc5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable You could take a snapshot if your data directory is on a zfs file system. Then send it to another server wherever you like. On Thu, Nov 7, 2024 at 1:13=E2=80=AFPM Evan Rempel wrote: > Using pgbackrest requires that I have additional local disk space for me > to stage the backup before I use my actual backup software to actually > perform an off-server backup. > > Incrementally forever backups means that there isn't a need to do a weekl= y > full backup. I never have to do the 84 minutes of backup. > > The same thing is true for performing restores. Pgbackrest requires a two > (actually 3 if you include wal replay) stage restore. Once from my actual > backup system into the staging area, and then a second to get the > pgbackrest files restored to the postgresql data space. There are even mo= re > steps for pgbackrest if I have to go back to the last full backup and rol= l > forward. > > What I am hearing from everyone is that there isn't a way within > postgresql to remove the requirement for keeping the session open. I will > have to write scripts to handle a persistent connection for the duration = of > the backup =F0=9F=99=81 > > > -- > Evan > > ------------------------------ > *From:* Ron Johnson > > On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM Evan Rempel wro= te: > > 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! > > > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000d89639062656bbc5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You could take a snapshot if your data directory is on a zfs file= system.=C2=A0 Then send it to another server wherever you like.

On Th= u, Nov 7, 2024 at 1:13=E2=80=AFPM Evan Rempel <erempel@uvic.ca> wrote:
Using pgbackrest requires that I have additional local disk space for me to= stage the backup before I use my actual backup software to actually perfor= m an off-server backup.

Incrementally forever backups means that there isn't a need to do a wee= kly full backup. I never have to do the 84 minutes of backup.

The same thing is true for performing restores. Pgbackrest requires a two (= actually 3 if you include wal replay) stage restore. Once from my actual ba= ckup system into the staging area, and then a second to get the pgbackrest = files restored to the postgresql data space. There are even more steps for pgbackrest if I have to go back = to the last full backup and roll forward.

What I am hearing from everyone is that there isn't a way within postgr= esql to remove the requirement for keeping the session open. I will have to= write scripts to handle a persistent connection for the duration of the ba= ckup =F0=9F=99=81


--
Evan

From:=C2=A0Ron Johnson <ronljohnsonjr@gmail.com>

On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM Evan R= empel <erempel@uvic.ca> wrote:
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 pgbackrest incremental backup= of a 5.1TB database took a whopping 92 seconds.=C2=A0 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.
=C2=A0
But as the OP states, if you have to keep the postgresql session open for t= he pg_start_backup and the pg_stop_backup=C2=A0then we will have to do a si= gnificant 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=A0p= gsql-admin@postgresql.org=C2=A0<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!
--000000000000d89639062656bbc5--