public inbox for [email protected]
help / color / mirror / Atom feedFrom: Evan Rempel <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: Running rsync backups in pg15
Date: Thu, 7 Nov 2024 18:13:07 +0000
Message-ID: <YT4P288MB0335F5D5E3C6EC2A827C4204B95C2@YT4P288MB0335.CANP288.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CANzqJaCr-id_4YotukPeH=rHCELS58Z63FoEbkNFBSqzjGMueQ@mail.gmail.com>
References: <DM8PR09MB667714AB7E04AB70B4B5A7B1B85C2@DM8PR09MB6677.namprd09.prod.outlook.com>
<CANzqJaDqz07cB160gZo6dZ2U_i2PxgJw4hqdehpRjRwLDne2KQ@mail.gmail.com>
<YT4P288MB03353281BD846F7D5B9FCE99B95C2@YT4P288MB0335.CANP288.PROD.OUTLOOK.COM>
<CANzqJaCr-id_4YotukPeH=rHCELS58Z63FoEbkNFBSqzjGMueQ@mail.gmail.com>
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 weekly 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 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 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 🙁
--
Evan
________________________________
From: Ron Johnson <[email protected]>
On Thu, Nov 7, 2024 at 12:47 PM Evan Rempel <[email protected]<mailto:[email protected]>> 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 backwards 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 <[email protected]<mailto:[email protected]>>
Sent: November 7, 2024 9:34 AM
To: [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>>
Subject: Re: Running rsync backups in pg15
On Thu, Nov 7, 2024 at 11:35 AM Murthy Nunna <[email protected]<mailto:[email protected]>> wrote:
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 <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!
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Running rsync backups in pg15
In-Reply-To: <YT4P288MB0335F5D5E3C6EC2A827C4204B95C2@YT4P288MB0335.CANP288.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox