public inbox for [email protected]
help / color / mirror / Atom feedFrom: Gerald Drouillard <[email protected]>
To: Evan Rempel <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Running rsync backups in pg15
Date: Thu, 7 Nov 2024 13:23:29 -0500
Message-ID: <CAC_Y+MDsAbEqkKBoh7YEa0oRrVEtoT+SKjbGMsHpy9SOPSepsA@mail.gmail.com> (raw)
In-Reply-To: <YT4P288MB0335F5D5E3C6EC2A827C4204B95C2@YT4P288MB0335.CANP288.PROD.OUTLOOK.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>
<YT4P288MB0335F5D5E3C6EC2A827C4204B95C2@YT4P288MB0335.CANP288.PROD.OUTLOOK.COM>
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 PM Evan Rempel <[email protected]> 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 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]> 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]>
> *Sent:* November 7, 2024 9:34 AM
> *To:* [email protected] <[email protected]>
> *Subject:* Re: Running rsync backups in pg15
>
> On Thu, Nov 7, 2024 at 11:35 AM Murthy Nunna <[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], [email protected]
Subject: Re: Running rsync backups in pg15
In-Reply-To: <CAC_Y+MDsAbEqkKBoh7YEa0oRrVEtoT+SKjbGMsHpy9SOPSepsA@mail.gmail.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