Re: Running rsync backups in pg15

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!