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 1t98Vy-007ypO-Qh for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2024 19:49:58 +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 1t98Vv-001qEJ-W9 for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2024 19:49:56 +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 <01000193082d1077-34d9461d-49e4-44ef-b83a-0201df4fc0ba-000000@amazonses.com>) id 1t98Vv-001qEB-Gw for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 19:49:56 +0000 Received: from a8-84.smtp-out.amazonses.com ([54.240.8.84]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from <01000193082d1077-34d9461d-49e4-44ef-b83a-0201df4fc0ba-000000@amazonses.com>) id 1t98Vs-000j5Y-1i for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 19:49:55 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/simple; s=ug7nbtf4gccmlpwj322ax3p6ow6yfsug; d=amazonses.com; t=1731008991; h=Content-Type:Content-Transfer-Encoding:From:Mime-Version:Subject:Date:Message-Id:References:In-Reply-To:To:Feedback-ID; bh=hm6KEcQrBYmBC0/LXAhJQ7BklJx1/GftLZ739sXXpmc=; b=mfzZg4D5g+cynvJ0M0wfjehPd+WIMUNg/EwdJWyxSgzywvHapx9FKGy7jTKkYtuV 3Kz04Zahy3lfpTYHBhpSJRe1/cWwAMCu3HvcmkgcP5054WTXEf1BiMT9bqFmhkPmk2y E93/lv+NKsuVazABKDseHgXTc684y1Re+Dok2yG0= Content-Type: multipart/alternative; boundary=Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E Content-Transfer-Encoding: 7bit From: Doug Reynolds Mime-Version: 1.0 (1.0) Subject: Re: Running rsync backups in pg15 Date: Thu, 7 Nov 2024 19:49:51 +0000 Message-ID: <01000193082d1077-34d9461d-49e4-44ef-b83a-0201df4fc0ba-000000@email.amazonses.com> References: In-Reply-To: To: Pgsql-admin X-Virus-Scanned: clamav-milter 0.103.12 at postfix X-Virus-Status: Clean X-Mailer: iPhone Mail (22A3370) Feedback-ID: ::1.us-east-1.L+CISTGIImABHDx8bBKKEgULZ36xzLwmtjped/xJNPU=:AmazonSES X-SES-Outgoing: 2024.11.07-54.240.8.84 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable I would be curious to see how long it would= take to restore a 5TB database from each WAL ever created on the system.

We used to do a similar process with Oracle on a 30TB datab= ase years ago, but it literally took 26-28 hours to do a full backup.

<= div>

Doug

On Nov 7, 2024, at 1:05=E2=80=AF= PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

<= /div>

=EF=BB=BF
On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM Evan Rempel <erempel@uvic.ca> wrote:

We use a similar approach, but instead of using rsync, we use our backup sof= tware directly which is an incremental forever tool. Allows backup of TB DBs= in short minutes. Switching to pgbackrest is actually a step backwards for u= s.

Last night's pgbackrest= incremental backup of a 5.1TB database took a whopping 92 seconds.&n= bsp; How's that a backwards step?

Sure, the weekly f= ull backup takes 84 minutes, but that's in so way shape or form painfully sl= ow.

<= div class=3D"msg-1779621256372920782">

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 t= o 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:&= nbsp;Ron Johnson <ronljohnsonjr@gmail.com>
Sent: November 7, 2024 9:34 AM
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Running rsync backups in pg15

On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM Murthy N= unna <mnunna@fnal.gov&g= t; wrote:

Hi,

In PG14 and earlie= r, there is no requirement to keep database connection while rsync is in pro= gress. However, there is a change in PG15+ that requires rsync to be while w= e have the same database session open that executes SELECT pg_backup_start('label'). This change requ= ires a rewrite of existing scripts we have.

Currently (pg14):

&= nbsp; In bash script (run from cron)
=

psql Select pg_start_backup
rsync
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 err= ors and so forth that occur in the rsync script.

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!

= --Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E--