Re: Running rsync backups in pg15

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 <erempel(at)uvic(dot)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
> 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 <ronljohnsonjr(at)gmail(dot)com>
>
> On Thu, Nov 7, 2024 at 12:47 PM Evan Rempel <erempel(at)uvic(dot)ca> 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 <ronljohnsonjr(at)gmail(dot)com>
> *Sent:* November 7, 2024 9:34 AM
> *To:* pgsql-admin(at)postgresql(dot)org <pgsql-admin(at)postgresql(dot)org>
> *Subject:* Re: Running rsync backups in pg15
>
> On Thu, Nov 7, 2024 at 11:35 AM Murthy Nunna <mnunna(at)fnal(dot)gov> 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!
>