public inbox for [email protected]
help / color / mirror / Atom feedRunning rsync backups in pg15
24+ messages / 9 participants
[nested] [flat]
* Running rsync backups in pg15
@ 2024-11-07 16:35 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 10:23 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
2024-11-11 19:38 ` RE: Running rsync backups in pg15 [email protected]
0 siblings, 3 replies; 24+ messages in thread
From: Murthy Nunna @ 2024-11-07 16:35 UTC (permalink / raw)
To: pgsql-admin
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?
Thank you in advance for your ideas.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-07 17:34 ` Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2 siblings, 1 reply; 24+ messages in thread
From: Ron Johnson @ 2024-11-07 17:34 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-07 17:47 ` Evan Rempel <[email protected]>
2024-11-07 17:54 ` Re: Running rsync backups in pg15 Scott Ribe <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Evan Rempel @ 2024-11-07 17:47 UTC (permalink / raw)
To: pgsql-admin
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.
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]<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!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
@ 2024-11-07 17:54 ` Scott Ribe <[email protected]>
1 sibling, 0 replies; 24+ messages in thread
From: Scott Ribe @ 2024-11-07 17:54 UTC (permalink / raw)
To: Evan Rempel <[email protected]>; +Cc: pgsql-admin
> On Nov 7, 2024, at 10:47 AM, Evan Rempel <[email protected]> wrote:
>
> 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?
Would it be feasible for you to write a small tool which executes pg_start_backup, runs in the background, and on command executes pg_stop_backup? Could maybe be done with a shell script & pipes...
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
@ 2024-11-07 18:02 ` Ron Johnson <[email protected]>
2024-11-07 18:13 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
1 sibling, 2 replies; 24+ messages in thread
From: Ron Johnson @ 2024-11-07 18:02 UTC (permalink / raw)
To: Pgsql-admin <[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!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-07 18:13 ` Evan Rempel <[email protected]>
2024-11-07 18:23 ` Re: Running rsync backups in pg15 Gerald Drouillard <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Evan Rempel @ 2024-11-07 18:13 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 18:13 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
@ 2024-11-07 18:23 ` Gerald Drouillard <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Gerald Drouillard @ 2024-11-07 18:23 UTC (permalink / raw)
To: Evan Rempel <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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!
>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-07 19:49 ` Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Doug Reynolds @ 2024-11-07 19:49 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
--Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">I would be curious to see how long it would=
take to restore a 5TB database from each WAL ever created on the system.<di=
v><br></div><div>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><=
div><br id=3D"lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Doug</div><d=
iv dir=3D"ltr"><br><blockquote type=3D"cite">On Nov 7, 2024, at 1:05=E2=80=AF=
PM, Ron Johnson <[email protected]> wrote:<br><br></blockquote><=
/div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr"><d=
iv dir=3D"ltr">On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM Evan Rempel <<a hr=
ef=3D"mailto:[email protected]">[email protected]</a>> wrote:<br></div><div c=
lass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div c=
lass=3D"msg-1779621256372920782">
<div dir=3D"ltr">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
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.</div></div></div></blockquote><div><br></div><div>Last night's pgbackrest=
incremental backup of a 5.1TB database took a whopping 92 <b>seconds</b>.&n=
bsp; How's that a backwards step?</div><div><br></div><div>Sure, the weekly f=
ull backup takes 84 minutes, but that's in so way shape or form painfully sl=
ow.</div><div> </div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
div class=3D"msg-1779621256372920782"><div dir=3D"ltr">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><span style=3D"font=
-size:12pt">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.</span><br></div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
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?</div>
<div id=3D"m_-1779621256372920782Signature">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(153,153,153)">
--</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(153,153,153)">
Evan</div>
</div>
<div id=3D"m_-1779621256372920782appendonsend"></div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<hr style=3D"display:inline-block;width:98%">
<div id=3D"m_-1779621256372920782divRplyFwdMsg" dir=3D"ltr"><span style=3D"f=
ont-family:Calibri,sans-serif;font-size:11pt;color:rgb(0,0,0)"><b>From:</b>&=
nbsp;Ron Johnson <<a href=3D"mailto:[email protected]" target=3D"_b=
lank">[email protected]</a>><br>
<b>Sent:</b> November 7, 2024 9:34 AM<br>
<b>To:</b> <a href=3D"mailto:[email protected]" target=3D"_bla=
nk">[email protected]</a> <<a href=3D"mailto:pgsql-admin@postgre=
sql.org" target=3D"_blank">[email protected]</a>><br>
<b>Subject:</b> Re: Running rsync backups in pg15</span>
<div> </div>
</div>
<div style=3D"direction:ltr">On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM Murthy N=
unna <<a href=3D"mailto:[email protected]" id=3D"m_-1779621256372920782OWA9=
253b4f7-8ed8-0b40-fa4d-91d75f5f0882" target=3D"_blank">[email protected]</a>&g=
t; wrote:</div>
<blockquote style=3D"margin:0px 0px 0px 0.8ex;padding-left:1ex;border-left:1=
px solid rgb(204,204,204)">
<p style=3D"direction:ltr"><span style=3D"font-size:12pt">Hi,</span></p>
<p style=3D"direction:ltr"><span style=3D"font-size:12pt"> </span></p>
<p style=3D"direction:ltr"><span style=3D"font-size:12pt">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 </span>SELECT pg_backup_start('label'). This change requ=
ires a rewrite of existing scripts we have.</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr">Currently (pg14):</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr"> &=
nbsp; In bash script (run from cron)</p>=
<ol start=3D"1" style=3D"direction:ltr;margin-top:0in">
<li style=3D"direction:ltr;margin-left:0.25in">psql Select pg_start_backup</=
li><li style=3D"direction:ltr;margin-left:0.25in">rsync</li><li style=3D"dir=
ection:ltr;margin-left:0.25in">psql Select pg_stop_backup</li></ol>
<p style=3D"direction:ltr;margin-left:0.75in"> </p>
<p style=3D"direction:ltr">In pg15 and later:</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr;margin-left:0.75in">In bash script (run from cron)=
</p>
<p style=3D"direction:ltr;margin-left:0.75in"> </p>
<p style=3D"direction:ltr;margin-left:0.75in">psql</p>
<p style=3D"direction:ltr;margin-left:1in">Select pg_start_backup</p>
<p style=3D"direction:ltr;margin-left:1in">! run-rsync-script</p>
<p style=3D"direction:ltr;margin-left:1in">Select pg_stop_backup</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr">It can be done, but it makes it ugly to check err=
ors and so forth that occur in the rsync script.</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr">Anybody found an elegant way of doing this?</p>
</blockquote>
<div style=3D"direction:ltr"><br>
</div>
<div style=3D"direction:ltr">Run pgbackrest instead of rsync,</div>
<div style=3D"direction:ltr"><br>
</div>
<div style=3D"direction:ltr">--</div>
<div style=3D"direction:ltr">Death to <Redacted>, and butter sauce.</d=
iv>
<div style=3D"direction:ltr">Don't boil me, I'm still alive.</div>
<div style=3D"direction:ltr"><Redacted> lobster!</div>
</div>
</div></blockquote></div><div><br clear=3D"all"></div><div><br></div><span c=
lass=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmai=
l_signature"><div dir=3D"ltr">Death to <Redacted>, and butter sauce.<d=
iv>Don't boil me, I'm still alive.<br><div><div><Redacted> lobster!</d=
iv></div></div></div></div></div>
</div></blockquote></div></body></html>=
--Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E--
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
@ 2024-11-07 20:58 ` Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Murthy Nunna @ 2024-11-07 20:58 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
rsync backups are not just incremental backups. They are incremental merged backups. You always get full backup at the end of the backup.
My database is 18TB. The very first backup took 14 hours. We just keep overwriting this full backup with daily rsyncs. The daily rsyncs take about 15 to 30 minutes depending up on the activity since last backup.
I feel the new way (pg_backup_start) is a step backward. I did not really see any issue with the old way. When I crashed my cluster (pg14), it is nicely removing the backup label if it exists at the restart. I think that is good enough.
From: Doug Reynolds <[email protected]>
Sent: Thursday, November 7, 2024 1:50 PM
To: Pgsql-admin <[email protected]>
Subject: Re: Running rsync backups in pg15
[EXTERNAL] – This message is from an external sender
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 database years ago, but it literally took 26-28 hours to do a full backup.
Doug
On Nov 7, 2024, at 1:05 PM, Ron Johnson <[email protected]<mailto:[email protected]>> wrote:
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!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-08 14:08 ` Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Ron Johnson @ 2024-11-08 14:08 UTC (permalink / raw)
To: pgsql-admin
On Thu, Nov 7, 2024 at 3:59 PM Murthy Nunna <[email protected]> wrote:
> rsync backups are not just incremental backups. They are incremental
> merged backups. You always get full backup at the end of the backup.
>
>
>
> My database is 18TB. The very first backup took 14 hours. We just keep
> overwriting this full backup with daily rsyncs. The daily rsyncs take about
> 15 to 30 minutes depending up on the activity since last backup.
>
That looks a whole lot like the results you get from async Streaming
Replication.
Which is, of course, a DR solution, NOT a backup solution.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-08 15:07 ` Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Murthy Nunna @ 2024-11-08 15:07 UTC (permalink / raw)
To: pgsql-admin
Yes. We already have streaming replication. But we still rely on rsync backups for PITR. We keep two copies of rsync backups. The oldest full rsync is 7 days. We save all WALs for 8 days. We do not want to change this scheme. There are some bells and whistles during rsync to monitor the backup. With the introduction of new requirement ( need to run pg_backup_start/stop in same connection) we end up rewriting our backup scripts.
As noted earlier, I did a crash test in pg14 (which uses old functions pg_start_backup/stop in separate connections) but I did not encounter any issue like lingering backup_label file that prevents cluster restart etc. What I noticed is pg14 is nicely renaming the backup_label file before restarting the cluster.
Please (pretty please) somebody tell me (via a URL that explains is good enough) what problem is solved by introducing the new restriction of having to run pg_backup_start/stop in same connection. I am pretty sure I am missing the point… May be I should run the crash test in a different way to reproduce the issue.
From: Ron Johnson <[email protected]>
Sent: Friday, November 8, 2024 8:09 AM
To: pgsql-admin <[email protected]>
Subject: Re: Running rsync backups in pg15
That looks a whole lot like the results you get from async Streaming Replication.
Which is, of course, a DR solution, NOT a backup solution.
--
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-08 15:32 ` Ron Johnson <[email protected]>
2024-11-08 15:40 ` Re: Running rsync backups in pg15 Scott Ribe <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Ron Johnson @ 2024-11-08 15:32 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
According to
https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
Section 26.3.3.2, SELECT pg_backup_start('label') is how you start
Exclusive Low Level backups.
https://www.postgresql.org/docs/release/15.0/ says that this has been
removed.
On Fri, Nov 8, 2024 at 10:07 AM Murthy Nunna <[email protected]> wrote:
> Yes. We already have streaming replication. But we still rely on rsync
> backups for PITR. We keep two copies of rsync backups. The oldest full
> rsync is 7 days. We save all WALs for 8 days. We do not want to change this
> scheme. There are some bells and whistles during rsync to monitor the
> backup. With the introduction of new requirement ( need to run
> pg_backup_start/stop in same connection) we end up rewriting our backup
> scripts.
>
>
>
> As noted earlier, I did a crash test in pg14 (which uses old functions
> pg_start_backup/stop in separate connections) but I did not encounter any
> issue like lingering backup_label file that prevents cluster restart etc.
> What I noticed is pg14 is nicely renaming the backup_label file before
> restarting the cluster.
>
>
>
> Please (pretty please) somebody tell me (via a URL that explains is good
> enough) what problem is solved by introducing the new restriction of having
> to run pg_backup_start/stop in same connection. I am pretty sure I am
> missing the point… May be I should run the crash test in a different way to
> reproduce the issue.
>
>
>
>
>
> *From:* Ron Johnson <[email protected]>
> *Sent:* Friday, November 8, 2024 8:09 AM
> *To:* pgsql-admin <[email protected]>
> *Subject:* Re: Running rsync backups in pg15
>
>
>
> That looks a whole lot like the results you get from async Streaming
> Replication.
>
>
>
> Which is, of course, a DR solution, NOT a backup solution.
>
>
>
> --
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-08 15:40 ` Scott Ribe <[email protected]>
2024-11-08 15:43 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Scott Ribe @ 2024-11-08 15:40 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Nov 8, 2024, at 8:32 AM, Ron Johnson <[email protected]> wrote:
>
> According to https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP Section 26.3.3.2, SELECT pg_backup_start('label') is how you start Exclusive Low Level backups.
That link is to PG 14 docs, exclusive backups were dropped in 15.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:40 ` Re: Running rsync backups in pg15 Scott Ribe <[email protected]>
@ 2024-11-08 15:43 ` Ron Johnson <[email protected]>
2024-11-08 16:29 ` Re: Running rsync backups in pg15 Scott Ribe <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Ron Johnson @ 2024-11-08 15:43 UTC (permalink / raw)
To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Right. I also linked to the 15 Release Notes.
On Fri, Nov 8, 2024 at 10:40 AM Scott Ribe <[email protected]>
wrote:
> > On Nov 8, 2024, at 8:32 AM, Ron Johnson <[email protected]> wrote:
> >
> > According to
> https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
> Section 26.3.3.2, SELECT pg_backup_start('label') is how you start
> Exclusive Low Level backups.
>
> That link is to PG 14 docs, exclusive backups were dropped in 15.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:40 ` Re: Running rsync backups in pg15 Scott Ribe <[email protected]>
2024-11-08 15:43 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-08 16:29 ` Scott Ribe <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Scott Ribe @ 2024-11-08 16:29 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Nov 8, 2024, at 8:43 AM, Ron Johnson <[email protected]> wrote:
>
> Right. I also linked to the 15 Release Notes.
>
> On Fri, Nov 8, 2024 at 10:40 AM Scott Ribe <[email protected]> wrote:
> > On Nov 8, 2024, at 8:32 AM, Ron Johnson <[email protected]> wrote:
> >
> > According to https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP Section 26.3.3.2, SELECT pg_backup_start('label') is how you start Exclusive Low Level backups.
>
> That link is to PG 14 docs, exclusive backups were dropped in 15.
I seem to have read your post as the inverse of what you meant.
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
@ 2024-11-08 16:53 ` Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Re: Running rsync backups in pg15 Fujii Masao <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Murthy Nunna @ 2024-11-08 16:53 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Yes, I know. Exclusive backups are removed.
From release notes of 15:
* Remove long-deprecated exclusive backup mode<https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-BASE-BACKUP; (David Steele, Nathan Bossart)
If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.
Sorry my question was not clear. As per above release notes - If the database server stops abruptly while in this mode, the server could fail to start. However, I crash tested in pg14 (stopped abruptly during exclusive backup) but the cluster still started fine. Pg14 renamed backup_label to backup_label.old and restarted the cluster successfully.
Repeating my question: What problem, if any, is resolved by removing exclusive backups in pg15 and above? There must be certain conditions other than just abrupt server crash which prompted the removal of exclusive backups. Just trying to find what it is.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-09 04:54 ` Fujii Masao <[email protected]>
2024-11-09 17:24 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2024-11-09 04:54 UTC (permalink / raw)
To: Murthy Nunna <[email protected]>; Pgsql-admin <[email protected]>
On 2024/11/09 1:53, Murthy Nunna wrote:
> Yes, I know. Exclusive backups are removed.
>
> From release notes of 15:
>
> * Remove long-deprecated *exclusive backup mode* <https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-BASE-BACKUP> (David Steele, Nathan Bossart)
>
> If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.
>
> Sorry my question was not clear. As per above release notes - If the database server stops abruptly while in this mode, the server could fail to start. However, I crash tested in pg14 (stopped abruptly during exclusive backup) but the cluster still started fine. Pg14 renamed backup_label to backup_label.old and restarted the cluster successfully.
If the server crashes during backup mode and the WAL files indicated by backup_label
as the recovery starting point are removed (due to checkpoints during backup mode),
the server won't start. You can reproduce this issue with the following steps, for example.
--------------------
initdb -D data
pg_ctl -D data start
psql -c "select pg_start_backup('test', true)"
psql -c "select pg_switch_wal(); checkpoint"
psql -c "select pg_switch_wal(); checkpoint"
kill -9 $(head -1 data/postmaster.pid)
pg_ctl -D data start
--------------------
I understand some people still prefer exclusive backups for some reasons, which is
why I developed the pg_exclusive_backup extension that provides functions for
exclusive backups on PostgreSQL 15 or later. However, since this is an unofficial extension,
it's generally better to update your backup method or script to use non-exclusive backups.
https://github.com/MasaoFujii/pg_exclusive_backup
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Re: Running rsync backups in pg15 Fujii Masao <[email protected]>
@ 2024-11-09 17:24 ` Murthy Nunna <[email protected]>
2024-11-10 05:34 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Murthy Nunna @ 2024-11-09 17:24 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Thanks, Fujii for your response.
I ran the test you outlined. Killed postmaster. Start failed. But simply removing the backup_label file made the server to restart successfully.
There is no data corruption. Is there a scenario where data can be corrupted because of exclusive backup?
I saw your pg_extension where you put the old routines back with some additional checks... But I am hesitant to use the old functionality if there is really some issue that I am not realizing.
Thanks again!
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Re: Running rsync backups in pg15 Fujii Masao <[email protected]>
2024-11-09 17:24 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-10 05:34 ` Laurenz Albe <[email protected]>
2024-11-10 15:05 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Laurenz Albe @ 2024-11-10 05:34 UTC (permalink / raw)
To: Murthy Nunna <[email protected]>; Pgsql-admin <[email protected]>
On Sat, 2024-11-09 at 17:24 +0000, Murthy Nunna wrote:
> I ran the test you outlined. Killed postmaster. Start failed. But simply removing the backup_label
> file made the server to restart successfully.
> There is no data corruption. Is there a scenario where data can be corrupted because of exclusive backup?
You would normally end up with data corruption or an error if you removed "backup_label"
from a properly taken backup. Manually removing "backup_label" is dangerous. It is safe
to do if the server crashed while in exclusive backup mode, but it is detrimental anywhere
else.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Re: Running rsync backups in pg15 Fujii Masao <[email protected]>
2024-11-09 17:24 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-10 05:34 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
@ 2024-11-10 15:05 ` Murthy Nunna <[email protected]>
2024-11-11 13:00 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Murthy Nunna @ 2024-11-10 15:05 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Thanks, Laurenz. I appreciate your explanation.
So, in the test case that Fujii sent, the server crashed during backup so it was safe to remove "backup_label".
However, in case of a completed exclusive backup, if you remove "backup_label" and then try to bring up server using the same backed up data as PGDATA, then you will have corrupted server. I get it. But I see this case as a user who is deliberately corrupting the server. There are tons of scenarios to corrupt a database from outside postgres. Postgres cannot be responsible for these type of user introduced corruptions.
In my humble opinion with respect to all, if this is the only reason (removing "backup_label" thus introducing corruption) for removing exclusive backups, I think it is an oversight by the Postgres Development Group approving this change.
-----Original Message-----
From: Laurenz Albe <[email protected]>
Sent: Saturday, November 9, 2024 11:34 PM
To: Murthy Nunna <[email protected]>; Pgsql-admin <[email protected]>
Subject: Re: Running rsync backups in pg15
You would normally end up with data corruption or an error if you removed "backup_label"
from a properly taken backup. Manually removing "backup_label" is dangerous. It is safe to do if the server crashed while in exclusive backup mode, but it is detrimental anywhere else.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Re: Running rsync backups in pg15 Fujii Masao <[email protected]>
2024-11-09 17:24 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-10 05:34 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
2024-11-10 15:05 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-11 13:00 ` Laurenz Albe <[email protected]>
2024-11-11 17:52 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Laurenz Albe @ 2024-11-11 13:00 UTC (permalink / raw)
To: Murthy Nunna <[email protected]>; Pgsql-admin <[email protected]>
On Sun, 2024-11-10 at 15:05 +0000, Murthy Nunna wrote:
> So, in the test case that Fujii sent, the server crashed during backup so it was safe to remove "backup_label".
>
> However, in case of a completed exclusive backup, if you remove "backup_label" and then try to bring up server
> using the same backed up data as PGDATA, then you will have corrupted server. I get it.
>
> In my humble opinion with respect to all, if this is the only reason (removing "backup_label" thus introducing
> corruption) for removing exclusive backups, I think it is an oversight by the Postgres Development Group
> approving this change.
The other reason is that in this day of automation it is unappealing that there is a case where a
crashed server cannot start without manual intervention. And automating the removal of "backup_label"
is not a good idea...
But I see your point, and I argued similarly when the exclusive backup mode was removed.
However, the majority disagreed.
See the discussion in
https://www.postgresql.org/message-id/flat/ac7339ca-3718-3c93-929f-99e725d1172c%40pgmasters.net
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 17:47 ` Re: Running rsync backups in pg15 Evan Rempel <[email protected]>
2024-11-07 18:02 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-07 19:49 ` Re: Running rsync backups in pg15 Doug Reynolds <[email protected]>
2024-11-07 20:58 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 15:07 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Re: Running rsync backups in pg15 Ron Johnson <[email protected]>
2024-11-08 16:53 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Re: Running rsync backups in pg15 Fujii Masao <[email protected]>
2024-11-09 17:24 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-10 05:34 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
2024-11-10 15:05 ` RE: Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-11 13:00 ` Re: Running rsync backups in pg15 Laurenz Albe <[email protected]>
@ 2024-11-11 17:52 ` Murthy Nunna <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Murthy Nunna @ 2024-11-11 17:52 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
Thanks, Laurenz. Makes me feel better this has been discussed. That is ok, majority rules.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-08 10:23 ` Laurenz Albe <[email protected]>
2 siblings, 0 replies; 24+ messages in thread
From: Laurenz Albe @ 2024-11-08 10:23 UTC (permalink / raw)
To: Murthy Nunna <[email protected]>; pgsql-admin
On Thu, 2024-11-07 at 16:35 +0000, Murthy Nunna wrote:
> 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.
>
> 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?
You could try
https://github.com/cybertec-postgresql/safe-backup
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: Running rsync backups in pg15
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
@ 2024-11-11 19:38 ` [email protected]
2 siblings, 0 replies; 24+ messages in thread
From: [email protected] @ 2024-11-11 19:38 UTC (permalink / raw)
To: 'Murthy Nunna' <[email protected]>; pgsql-admin
Hi,
First of all, have you ever thought what happens if your database crashed
while you are updating your single backup?! It is really a dangerous
scenario there!
But any way, I suppose you are using psql to do your pg_start_backup(),
pg_stop_backup().
What about this one (I didn’t change function names):
Select pg_start_backup();
\! your_rsync_script…
Select pg_stop_bacup();
This could be more sophisticated if you want…
Regards
Michel SALAIS
De : Murthy Nunna <[email protected]>
Envoyé : jeudi 7 novembre 2024 17:35
À : [email protected]
Objet : Running rsync backups in pg15
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?
Thank you in advance for your ideas.
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2024-11-11 19:38 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-07 16:35 Running rsync backups in pg15 Murthy Nunna <[email protected]>
2024-11-07 17:34 ` Ron Johnson <[email protected]>
2024-11-07 17:47 ` Evan Rempel <[email protected]>
2024-11-07 17:54 ` Scott Ribe <[email protected]>
2024-11-07 18:02 ` Ron Johnson <[email protected]>
2024-11-07 18:13 ` Evan Rempel <[email protected]>
2024-11-07 18:23 ` Gerald Drouillard <[email protected]>
2024-11-07 19:49 ` Doug Reynolds <[email protected]>
2024-11-07 20:58 ` Murthy Nunna <[email protected]>
2024-11-08 14:08 ` Ron Johnson <[email protected]>
2024-11-08 15:07 ` Murthy Nunna <[email protected]>
2024-11-08 15:32 ` Ron Johnson <[email protected]>
2024-11-08 15:40 ` Scott Ribe <[email protected]>
2024-11-08 15:43 ` Ron Johnson <[email protected]>
2024-11-08 16:29 ` Scott Ribe <[email protected]>
2024-11-08 16:53 ` Murthy Nunna <[email protected]>
2024-11-09 04:54 ` Fujii Masao <[email protected]>
2024-11-09 17:24 ` Murthy Nunna <[email protected]>
2024-11-10 05:34 ` Laurenz Albe <[email protected]>
2024-11-10 15:05 ` Murthy Nunna <[email protected]>
2024-11-11 13:00 ` Laurenz Albe <[email protected]>
2024-11-11 17:52 ` Murthy Nunna <[email protected]>
2024-11-08 10:23 ` Laurenz Albe <[email protected]>
2024-11-11 19:38 ` [email protected]
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox