Re: Updated backup APIs for non-exclusive backups

[David Steele]

On 12/12/18 12:58 AM, Laurenz Albe wrote:
> On Tue, 2018-12-11 at 23:43 -0500, David Steele wrote:
> [about managing backups from pre- and post-file-system-backup scrips]
>>> I have come up with some sample code here:
>>> https://github.com/cybertec-postgresql/safe-backup
>>>
>>> This just uses bash and psql.
>>> Does that look reasonably safe?
>>>
>>> It's probably too big to be introduced into the documentation, but maybe
>>> we could add it to the Wiki.
>>
>> My bash-fu is not all that great, but it seems to me you could do this
>> all in one script and forgo the table entirely.  Just do the file copy
>> at about line 60 in pgpre.sh.
>>
>> It does look workable to me, just wonder if it could be simpler.
> 
> Thanks for having a look.  Yes, it looks appallingly complicated.
> 
> Sure, if I knew there was a file to write, and I knew where to write
> it, I could do it in the "pre" script.  

Good point!

> But since I have no idea how the
> actual backup is performed and how the "backup_label" file is going to
> be saved, I thought it best to return the information to the caller and
> persist it somewhere, and only the "post" script can actually return the
> information.

Perhaps it could be done in a bash file that is included into the user's
script, rather than doing pre/post.

So, they would have start and stop backup methods they could call that
would manage the psql process in the background.  In that case it should
be possible to eliminate the table.

I do see the table as a weak spot.  It appears to work, but does add a
lot of complexity.  Limiting to one backup at a time can be a feature,
though.  In pgBackRest we use advisory locks to enforce this.

-- 
-David
da...@pgmasters.net

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

On Tue, 2018-12-11 at 23:43 -0500, David Steele wrote:
> > > > [about managing backups from pre- and post-file-system-backup scrips]
> > I have come up with some sample code here:
> > https://github.com/cybertec-postgresql/safe-backup
> > 
> > This just uses bash and psql.
> > Does that look reasonably safe?
> > 
> > It's probably too big to be introduced into the documentation, but maybe
> > we could add it to the Wiki.
> 
> My bash-fu is not all that great, but it seems to me you could do this
> all in one script and forgo the table entirely.  Just do the file copy
> at about line 60 in pgpre.sh.
> 
> It does look workable to me, just wonder if it could be simpler.

Thanks for having a look.  Yes, it looks appallingly complicated.

Sure, if I knew there was a file to write, and I knew where to write
it, I could do it in the "pre" script.  But since I have no idea how the
actual backup is performed and how the "backup_label" file is going to
be saved, I thought it best to return the information to the caller and
persist it somewhere, and only the "post" script can actually return the
information.

Yours,
Laurenz Albe

Re: Updated backup APIs for non-exclusive backups

[David Steele]

On 12/11/18 5:33 PM, Laurenz Albe wrote:
> On Mon, 2018-11-26 at 10:18 +0100, Magnus Hagander wrote:
>>> [about managing backups from pre- and post-file-system-backup scrips]
 I agree with your point that it's not an uncommon thing to need. If a good 
 solution
 for it can be proposed that requires the exclusive backup interface, then 
 I wouldn't
 be against un-deprecating that.  But that's going to require a lot more 
 than just a
 documentation change, IMHO. What could perhaps be handled with a 
 documentation change,
 however, is to document a good way for this type of setup to use the new 
 interfaces.
>>>
>>> Good point, and it puts the ball in my court :^)
>>
>> Enjoy :)
> 
> I have come up with some sample code here:
> https://github.com/cybertec-postgresql/safe-backup
> 
> This just uses bash and psql.
> Does that look reasonably safe?
> 
> It's probably too big to be introduced into the documentation, but maybe
> we could add it to the Wiki.

My bash-fu is not all that great, but it seems to me you could do this
all in one script and forgo the table entirely.  Just do the file copy
at about line 60 in pgpre.sh.

It does look workable to me, just wonder if it could be simpler.

-- 
-David
da...@pgmasters.net

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

On Mon, 2018-11-26 at 10:18 +0100, Magnus Hagander wrote:
> > [about managing backups from pre- and post-file-system-backup scrips]
> > > I agree with your point that it's not an uncommon thing to need. If a 
> > > good solution
> > > for it can be proposed that requires the exclusive backup interface, then 
> > > I wouldn't
> > > be against un-deprecating that.  But that's going to require a lot more 
> > > than just a
> > > documentation change, IMHO. What could perhaps be handled with a 
> > > documentation change,
> > > however, is to document a good way for this type of setup to use the new 
> > > interfaces.
> > 
> > Good point, and it puts the ball in my court :^)
> 
> Enjoy :)

I have come up with some sample code here:
https://github.com/cybertec-postgresql/safe-backup

This just uses bash and psql.
Does that look reasonably safe?

It's probably too big to be introduced into the documentation, but maybe
we could add it to the Wiki.

Yours,
Laurenz Albe

Re: Updated backup APIs for non-exclusive backups

[Stephen Frost]

Greetings,

* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> On Sun, 2018-11-25 at 16:04 -0500, Stephen Frost wrote:
> > > Yes, you can come up with a post-backup script that somehow communicates
> > > with your pre-backup script to get the information, but it sure is
> > > inconvenient.  Simplicity is good in backup solutions, because complicated
> > > things tend to break more easily.
> > 
> > Not sure what communication is necessary here..?   The data needed for the 
> > backup
> > label file comes from pg_stop_backup in a non-exclusive backup.
> 
> Right, and pg_stop_backup has to be run from the "pre-backup" script.

No, pg_stop_backup has has to be run by a process that's been connected
since the pre-backup script ran, but you could certainly have some
process that starts from pre-backup, detaches, and then waits for a
signal from the post-backup script to run pg_stop_backup and record the
results somewhere.

Yes, that's rather annoying to do from a bash script, but I'm well past
the point of thinking that bash scripts are a good way to run backups in
PG.  Your efforts in this area might be better put to use by writing a
good tool which works with the non-exclusive API but can be called from
the pre/post backup scripts your backup solution provides.
Unfortunately, it seems pretty unlikely that there's a good generic way
to write such a tool or we could include it in PG.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Updated backup APIs for non-exclusive backups

[Stephen Frost]

Greetings,

* David Steele (da...@pgmasters.net) wrote:
> On 11/26/18 12:31 AM, Laurenz Albe wrote:
> >If there is a crash during the backup procedure, the backup is bad.
> >Doesn't matter during which part of the backup procedure it happens.
> 
> Yes, but in this case with exclusive backups your cluster also will not
> start.  That's a lot different than a bad backup because you should have
> many backups and a way to mark when they are complete.  If your cluster
> requires manual intervention to start because a backup was in progress
> that's not a good thing.
> 
> I'm of the opinion that we should remove the exclusive option for PG12. The
> recovery changes recently committed will completely break any automated
> recovery written for PG <= 11 so we might as go all the way.

Yeah, after chatting with David about that this morning, I agree.
Breaking everything around how restores works means that anyone doing
backups today needs to re-evaluate their restore procedures, even if
it's not an automated script, so it makes sense to also remove the
deprecated exclusive backup mode at the same time.

> Requiring non-exclusive backup will be an easy change for tools that already
> understand non-exclusive backup, which has been available in the last three
> versions of Postgres.
> 
> The exclusive backup method tries to make a hard problem look simple but
> does not succeed and causes issues besides.  Performing backups properly
> *is* hard and we don't do anyone a service by making it look like something
> can be done with a trivial bash script.

+1

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Updated backup APIs for non-exclusive backups

[David Steele]

On 11/26/18 12:31 AM, Laurenz Albe wrote:

On Sun, 2018-11-25 at 16:04 -0500, Stephen Frost wrote:

There isn’t any need to write the backup label before you restore the database,
just as you write recovery.conf then.


Granted.
But it is pretty convenient, and writing it to the data directory right away
is a good thing on top, because it reduces the danger of inadvertedly
starting the backup without recovery.


Writing it into the data directory is *not* a good thing because as soon as you 
do
that you risk there being an issue if there’s a crash.  Writing into the backup
isn’t a bad idea but if you’re managing your backups then writing it somewhere 
else
(such as where you write your WAL) and associating it with the backup 
(presumably
it has a label) should make it easy to pull back when you restore.


If there is a crash during the backup procedure, the backup is bad.
Doesn't matter during which part of the backup procedure it happens.


Yes, but in this case with exclusive backups your cluster also will not 
start.  That's a lot different than a bad backup because you should have 
many backups and a way to mark when they are complete.  If your cluster 
requires manual intervention to start because a backup was in progress 
that's not a good thing.


I'm of the opinion that we should remove the exclusive option for PG12. 
The recovery changes recently committed will completely break any 
automated recovery written for PG <= 11 so we might as go all the way.


Requiring non-exclusive backup will be an easy change for tools that 
already understand non-exclusive backup, which has been available in the 
last three versions of Postgres.


The exclusive backup method tries to make a hard problem look simple but 
does not succeed and causes issues besides.  Performing backups properly 
*is* hard and we don't do anyone a service by making it look like 
something can be done with a trivial bash script.


Regards,

--
-David
da...@pgmasters.net

Re: Updated backup APIs for non-exclusive backups

[Magnus Hagander]

On Mon, Nov 26, 2018 at 6:44 AM Laurenz Albe 
wrote:

> On Sun, 2018-11-25 at 22:01 +0100, Magnus Hagander wrote:
> [about managing backups from pre- and post-file-system-backup scrips]
> > I agree with your point that it's not an uncommon thing to need. If a
> good solution
> > for it can be proposed that requires the exclusive backup interface,
> then I wouldn't
> > be against un-deprecating that.  But that's going to require a lot more
> than just a
> > documentation change, IMHO. What could perhaps be handled with a
> documentation change,
> > however, is to document a good way for this type of setup to use the new
> interfaces.
>
> Good point, and it puts the ball in my court :^)
>

Enjoy :)



> > > I'm arguing on behalf of users that run a few databases, want a simple
> backup
> > > solution and are ready to deal with the shortcomings.
> >
> > Those that want a simple backup solution have one -- pg_basebackup.
> >
> > The exclusive backup API is *not* simple. It is convenient, but it is
> not simple.
> >
> > Actually having a simple API that worked with the pre/post backup
> scripts, that
> > would be an improvement that we should definitely want!
>
> Right; unfortunately it is not simple to come up with one that doesn't
> exhibit
> the problems of the existing exclusive backup.
>

Right, it turns out to actually be a hard problem. The old API pretended it
wasn't, which wasn't really very helpful in the long run...


Perhaps it's theoretically impossible.  The goal is to disambiguate what a
> file
> system backup sees in backup mode and what the startup process sees after
> a crash
> in backup mode, and I can't see how that could be done.
>

Not if it's in the same physical location, no, I think that's really hard.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

On Sun, 2018-11-25 at 22:01 +0100, Magnus Hagander wrote:
[about managing backups from pre- and post-file-system-backup scrips]
> I agree with your point that it's not an uncommon thing to need. If a good 
> solution
> for it can be proposed that requires the exclusive backup interface, then I 
> wouldn't
> be against un-deprecating that.  But that's going to require a lot more than 
> just a
> documentation change, IMHO. What could perhaps be handled with a 
> documentation change,
> however, is to document a good way for this type of setup to use the new 
> interfaces.

Good point, and it puts the ball in my court :^)

> > I'm arguing on behalf of users that run a few databases, want a simple 
> > backup
> > solution and are ready to deal with the shortcomings.
> 
> Those that want a simple backup solution have one -- pg_basebackup.
> 
> The exclusive backup API is *not* simple. It is convenient, but it is not 
> simple.
> 
> Actually having a simple API that worked with the pre/post backup scripts, 
> that
> would be an improvement that we should definitely want!

Right; unfortunately it is not simple to come up with one that doesn't exhibit
the problems of the existing exclusive backup.

Perhaps it's theoretically impossible.  The goal is to disambiguate what a file
system backup sees in backup mode and what the startup process sees after a 
crash
in backup mode, and I can't see how that could be done.

Yours,
Laurenz Albe

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

On Sun, 2018-11-25 at 16:04 -0500, Stephen Frost wrote:
> > > There isn’t any need to write the backup label before you restore the 
> > > database,
> > > just as you write recovery.conf then.
> > 
> > Granted.
> > But it is pretty convenient, and writing it to the data directory right away
> > is a good thing on top, because it reduces the danger of inadvertedly
> > starting the backup without recovery.
> 
> Writing it into the data directory is *not* a good thing because as soon as 
> you do
> that you risk there being an issue if there’s a crash.  Writing into the 
> backup
> isn’t a bad idea but if you’re managing your backups then writing it 
> somewhere else
> (such as where you write your WAL) and associating it with the backup 
> (presumably
> it has a label) should make it easy to pull back when you restore. 

If there is a crash during the backup procedure, the backup is bad.
Doesn't matter during which part of the backup procedure it happens.

> > Yes, you can come up with a post-backup script that somehow communicates
> > with your pre-backup script to get the information, but it sure is
> > inconvenient.  Simplicity is good in backup solutions, because complicated
> > things tend to break more easily.
> 
> Not sure what communication is necessary here..?   The data needed for the 
> backup
> label file comes from pg_stop_backup in a non-exclusive backup.

Right, and pg_stop_backup has to be run from the "pre-backup" script.

Yours,
Laurenz Albe

Re: Updated backup APIs for non-exclusive backups

[Stephen Frost]

Greetings,

On Sun, Nov 25, 2018 at 15:45 Laurenz Albe  wrote:

> Stephen Frost wrote:
> > > > On restore, you're
> > > > going to need to create a recovery.conf (at least in released
> versions)
> > > > which provides a restore command (needed even in HEAD today) to get
> the
> > > > old WAL, so having to also create the backup_label file shouldn't be
> > > > that difficult.
> > >
> > > You write "recovery.conf" upon recovery, when you have the restored
> > > backup, so you have it on a file system.  No problem adding a file
> then.
> > >
> > > This is entirely different from adding a "backup_label" file to
> > > a backup that has been taken by a backup software in some arbitrary
> > > format in some arbitrary location (think snapshot).
> >
> > There isn’t any need to write the backup label before you restore the
> database,
> > just as you write recovery.conf then.
>
> Granted.
> But it is pretty convenient, and writing it to the data directory right
> away
> is a good thing on top, because it reduces the danger of inadvertedly
> starting the backup without recovery.


Writing it into the data directory is *not* a good thing because as soon as
you do that you risk there being an issue if there’s a crash.  Writing into
the backup isn’t a bad idea but if you’re managing your backups then
writing it somewhere else (such as where you write your WAL) and
associating it with the backup (presumably it has a label) should make it
easy to pull back when you restore.

> > > Lastly, if you really want, you can extract out the data from
> > > > pg_stop_backup in whatever your post-backup script is.
> > >
> > > Come on, now.
> > > You usually use backup techniques like that because you can't get
> > > your large database backed up in the available time window otherwise.
> >
> > I’m not following what you’re trying to get at here, why can’t you
> extract
> > the data for the backup label from pg_stop_backup..?  Certainly other
> tools
> > do, even ones that do extremely fast parallel backups..  the two are
> > completely independent.
> >
> > Did you think I meant pg_basebackup..?  I certaily didn’t.
>
> Oh yes, I misunderstood.  Sorry.
>
> Yes, you can come up with a post-backup script that somehow communicates
> with your pre-backup script to get the information, but it sure is
> inconvenient.  Simplicity is good in backup solutions, because complicated
> things tend to break more easily.


Not sure what communication is necessary here..?   The data needed for the
backup label file comes from pg_stop_backup in a non-exclusive backup.  You
*should* be grabbing the starting WAL from the start backup as well, to
confirm that you have all of the WAL for the backup, but you don’t actually
need to in order to write out the backup label.

> > I thought our goal is to provide convenient backup methods...
> >
> > Correctness would be first and having a broken system because of a crash
> during a backup isn’t correct.
>
> "Not starting up without manual intervention" is not actually broken...


Of course it is.  Having the behavior of the system be completely different
depending on if a backup happened to be running or not is just plain bad
and is broken.  It’s not a feature. Had this issue been realized when the
exclusive backup mode was developed I suspect it never would have been
implemented that way to begin with...

Thanks!

Stephen

Re: Updated backup APIs for non-exclusive backups

[Magnus Hagander]

On Sun, Nov 25, 2018 at 9:45 PM Laurenz Albe 
wrote:

> Stephen Frost wrote:
> > > > Lastly, if you really want, you can extract out the data from
> > > > pg_stop_backup in whatever your post-backup script is.
> > >
> > > Come on, now.
> > > You usually use backup techniques like that because you can't get
> > > your large database backed up in the available time window otherwise.
> >
> > I’m not following what you’re trying to get at here, why can’t you
> extract
> > the data for the backup label from pg_stop_backup..?  Certainly other
> tools
> > do, even ones that do extremely fast parallel backups..  the two are
> > completely independent.
> >
> > Did you think I meant pg_basebackup..?  I certaily didn’t.
>
> Oh yes, I misunderstood.  Sorry.
>
> Yes, you can come up with a post-backup script that somehow communicates
> with your pre-backup script to get the information, but it sure is
> inconvenient.  Simplicity is good in backup solutions, because complicated
> things tend to break more easily.
>

Yes, simplicity is good.

The problem with the previous interface is that it made things *look*
simple, but they were not. Thus, people got into all sorts of trouble
because they got things wrong.

The new interface is simple, and much harder to get wrong. What it isn't,
is that it's not as convenient as the old one. But correctness is a lot
more important than convenience.

That said, I agree with your point that it's not an uncommon thing to need.
If a good solution for it can be proposed that requires the exclusive
backup interface, then I wouldn't be against un-deprecating that.  But
that's going to require a lot more than just a documentation change, IMHO.
What could perhaps be handled with a documentation change, however, is to
document a good way for this type of setup to use the new interfaces.


> > I thought our goal is to provide convenient backup methods...
> >
> > Correctness would be first and having a broken system because of a crash
> during a backup isn’t correct.
>
> "Not starting up without manual intervention" is not actually broken...
>

Correct. But that's not the worst case scenario here. Yes, some of the bad
ones are the ones amplified by said manual intervention, but user
interaction at the restore point is going to be even harder to get right.


I'm arguing on behalf of users that run a few databases, want a simple
> backup
> solution and are ready to deal with the shortcomings.
>

Those that want a simple backup solution have one -- pg_basebackup.

The exclusive backup API is *not* simple. It is convenient, but it is not
simple.

Actually having a simple API that worked with the pre/post backup scripts,
that would be an improvement that we should definitely want!

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

Stephen Frost wrote: 
> > > Seeing it often doesn't make it a good solution.  Running just
> > > pre-backup and post-backup scripts and copying the filesystem isn't
> > > enough to perform an online PostgreSQL backup- the WAL needs to be
> > > collected as well, and you need to make sure that you have all of the
> > > WAL before the backup can be considered complete.
> > 
> > Yes, that's why "pg_stop_backup" has the "wait_for_archive" parameter.
> > So this is not a problem.
> 
> That doesn’t actually make sure you have all of the WAL reliably saved
> across the backup, it just cares what archive command returns, which is
> sadly often a bad thing to depend on.  I certainly wouldn’t rely on only
> that for any system I cared about. 

If you write a bad archive_command, you have a problem.
But that is quite unrelated to the problem at hand, if I am not mistaken.

> > > On restore, you're
> > > going to need to create a recovery.conf (at least in released versions)
> > > which provides a restore command (needed even in HEAD today) to get the
> > > old WAL, so having to also create the backup_label file shouldn't be
> > > that difficult.
> > 
> > You write "recovery.conf" upon recovery, when you have the restored
> > backup, so you have it on a file system.  No problem adding a file then.
> > 
> > This is entirely different from adding a "backup_label" file to
> > a backup that has been taken by a backup software in some arbitrary
> > format in some arbitrary location (think snapshot).
> 
> There isn’t any need to write the backup label before you restore the 
> database,
> just as you write recovery.conf then.

Granted.
But it is pretty convenient, and writing it to the data directory right away
is a good thing on top, because it reduces the danger of inadvertedly
starting the backup without recovery.

> > > Lastly, if you really want, you can extract out the data from
> > > pg_stop_backup in whatever your post-backup script is.
> > 
> > Come on, now.
> > You usually use backup techniques like that because you can't get
> > your large database backed up in the available time window otherwise.
> 
> I’m not following what you’re trying to get at here, why can’t you extract
> the data for the backup label from pg_stop_backup..?  Certainly other tools
> do, even ones that do extremely fast parallel backups..  the two are
> completely independent.
> 
> Did you think I meant pg_basebackup..?  I certaily didn’t.

Oh yes, I misunderstood.  Sorry.

Yes, you can come up with a post-backup script that somehow communicates
with your pre-backup script to get the information, but it sure is
inconvenient.  Simplicity is good in backup solutions, because complicated
things tend to break more easily.

> > I thought our goal is to provide convenient backup methods...
> 
> Correctness would be first and having a broken system because of a crash 
> during a backup isn’t correct.

"Not starting up without manual intervention" is not actually broken...

> > But what's wrong with retaining the exclusive backup method and just
> > sticking a big "Warning: this may cause a restart to fail after a crash"
> > on it?  That sure wouldn't be unsafe.
> 
> I haven’t seen anyone pushing for it to be removed immediately, but users 
> should
> not use it and newcomers would be much better served by using the non 
> exclusive api.
> There is a reason it was deprecated and it’s because it simply isn’t a good 
> API.
> Coming along a couple years later and saying that it’s a good API while 
> ignoring
> the issues that it has doesn’t change that.

I don't think I'm ignoring the issues, I just think there is a valid use case 
for
the exclusive backup API, with all its caveats.

Of course I'm not arguing on behalf of organizations running lots of databases
for whom manual intervention after a crash is unacceptable.

I'm arguing on behalf of users that run a few databases, want a simple backup
solution and are ready to deal with the shortcomings.


But I will gladly accept defeat in this matter, I just needed to vent my 
opinion.

Yours,
Laurenz Albe

Re: Updated backup APIs for non-exclusive backups

[Stephen Frost]

Greetings,

On Sun, Nov 25, 2018 at 14:17 Laurenz Albe  wrote:

> On Sun, 2018-11-25 at 13:50 -0500, Stephen Frost wrote:
> > I don't see any compelling argument for trying to do something half-way
> > any more today than I did two years ago when this was being discussed.
>
> That may well be so.  It may be better to make users unhappy than to
> make them very unhappy...
>
> But I find the following points unconvincing:
>
> > > I would say the typical use case for the exclusive backup method is
> > > the following (and I have seen it often):
> > >
> > > You have some kind of backup software that does file system backups
> > > and is able to run a "pre-backup" and "post-backup" script.
> > > The backup is triggered by the backup software.
> >
> > Seeing it often doesn't make it a good solution.  Running just
> > pre-backup and post-backup scripts and copying the filesystem isn't
> > enough to perform an online PostgreSQL backup- the WAL needs to be
> > collected as well, and you need to make sure that you have all of the
> > WAL before the backup can be considered complete.
>
> Yes, that's why "pg_stop_backup" has the "wait_for_archive" parameter.
> So this is not a problem.


That doesn’t actually make sure you have all of the WAL reliably saved
across the backup, it just cares what archive command returns, which is
sadly often a bad thing to depend on.  I certainly wouldn’t rely on only
that for any system I cared about.

> On restore, you're
> > going to need to create a recovery.conf (at least in released versions)
> > which provides a restore command (needed even in HEAD today) to get the
> > old WAL, so having to also create the backup_label file shouldn't be
> > that difficult.
>
> You write "recovery.conf" upon recovery, when you have the restored
> backup, so you have it on a file system.  No problem adding a file then.
>
> This is entirely different from adding a "backup_label" file to
> a backup that has been taken by a backup software in some arbitrary
> format in some arbitrary location (think snapshot).


There isn’t any need to write the backup label before you restore the
database, just as you write recovery.conf then.

> Lastly, if you really want, you can extract out the data from
> > pg_stop_backup in whatever your post-backup script is.
>
> Come on, now.
> You usually use backup techniques like that because you can't get
> your large database backed up in the available time window otherwise.


I’m not following what you’re trying to get at here, why can’t you extract
the data for the backup label from pg_stop_backup..?  Certainly other tools
do, even ones that do extremely fast parallel backups..  the two are
completely independent.

Did you think I meant pg_basebackup..?  I certaily didn’t.

> > Another thing that is problematic with non-exclusive backups is that
> > > you have to write the backup_label file into the backup after the
> > > backup has been taken.  With a technique like the above, you cannot
> > > easily do that.
> >
> > ... why not?  You need to create the recovery.conf anyway, and you need
> > to be archiving WAL somewhere, so it certainly seems like you could put
> > the backup_label there too.
>
> As I said above, you don't add "recovery.conf" to the backup right away,
> so these two cases don't compare.


There’s no requirement that you add the backup label contents immediately
either, you just need to keep track of it and restore it when you restore
the database and create the recovery.conf file.

> > I expect that that will make a lot of users unhappy.
> >
> > If it means that they implement a better backup strategy, then it's
> > probably a good thing, which is the goal of this.
>
> I thought our goal is to provide convenient backup methods...


Correctness would be first and having a broken system because of a crash
during a backup isn’t correct.

Ignoring "backup_label" on restart, as I suggested in my previous message,
> probably isn't such a hot idea.


Agreed.

But what's wrong with retaining the exclusive backup method and just
> sticking a big "Warning: this may cause a restart to fail after a crash"
> on it?  That sure wouldn't be unsafe.


I haven’t seen anyone pushing for it to be removed immediately, but users
should not use it and newcomers would be much better served by using the
non exclusive api.  There is a reason it was deprecated and it’s because it
simply isn’t a good API. Coming along a couple years later and saying that
it’s a good API while ignoring the issues that it has doesn’t change that.

Thanks!

Stephen

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

On Sun, 2018-11-25 at 13:50 -0500, Stephen Frost wrote:
> I don't see any compelling argument for trying to do something half-way
> any more today than I did two years ago when this was being discussed.

That may well be so.  It may be better to make users unhappy than to
make them very unhappy...

But I find the following points unconvincing:

> > I would say the typical use case for the exclusive backup method is
> > the following (and I have seen it often):
> > 
> > You have some kind of backup software that does file system backups
> > and is able to run a "pre-backup" and "post-backup" script.
> > The backup is triggered by the backup software.
> 
> Seeing it often doesn't make it a good solution.  Running just
> pre-backup and post-backup scripts and copying the filesystem isn't
> enough to perform an online PostgreSQL backup- the WAL needs to be
> collected as well, and you need to make sure that you have all of the
> WAL before the backup can be considered complete.

Yes, that's why "pg_stop_backup" has the "wait_for_archive" parameter.
So this is not a problem.

> On restore, you're
> going to need to create a recovery.conf (at least in released versions)
> which provides a restore command (needed even in HEAD today) to get the
> old WAL, so having to also create the backup_label file shouldn't be
> that difficult.

You write "recovery.conf" upon recovery, when you have the restored
backup, so you have it on a file system.  No problem adding a file then.

This is entirely different from adding a "backup_label" file to
a backup that has been taken by a backup software in some arbitrary
format in some arbitrary location (think snapshot).

So these two cases don't compare.

> Lastly, if you really want, you can extract out the data from
> pg_stop_backup in whatever your post-backup script is.

Come on, now.
You usually use backup techniques like that because you can't get
your large database backed up in the available time window otherwise.

> > Another thing that is problematic with non-exclusive backups is that
> > you have to write the backup_label file into the backup after the
> > backup has been taken.  With a technique like the above, you cannot
> > easily do that.
> 
> ... why not?  You need to create the recovery.conf anyway, and you need
> to be archiving WAL somewhere, so it certainly seems like you could put
> the backup_label there too.

As I said above, you don't add "recovery.conf" to the backup right away,
so these two cases don't compare.

> > I expect that that will make a lot of users unhappy.
> 
> If it means that they implement a better backup strategy, then it's
> probably a good thing, which is the goal of this.

I thought our goal is to provide convenient backup methods...


Ignoring "backup_label" on restart, as I suggested in my previous message,
probably isn't such a hot idea.

But what's wrong with retaining the exclusive backup method and just
sticking a big "Warning: this may cause a restart to fail after a crash"
on it?  That sure wouldn't be unsafe.

If somebody prefers a simpler backup method at the price of having to
manually restart the server after a crash, what's wrong with that?
Why not give them the choice?

I'd say that one could write a server start script that just removes
"backup_label", but I am sure someone will argue that then somebody
will restore a backup and then restart the server without first
recovering the database...

Yours,
Laurenz Albe

Re: Updated backup APIs for non-exclusive backups

[Stephen Frost]

Greetings,

* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> On Sun, 2016-04-24 at 11:49 -0400, Stephen Frost wrote:
> > * Magnus Hagander (mag...@hagander.net) wrote:
> > > On Sun, Apr 24, 2016 at 5:37 AM, Bruce Momjian  wrote:
> > > > On Fri, Apr 22, 2016 at 11:53:46AM -0400, Robert Haas wrote:
> > > > > 
> > > > > Frankly, I think that's right.  It is one thing to say that the new
> > > > > method is preferred - +1 for that.  But the old method is going to
> > > > > continue to be used by many people for a long time, and in some cases
> > > > > will be superior.  That's not something we can deprecate, unless I'm
> > > > > misunderstanding the situation.
> > > > 
> > > > I agree with Robert.  One the one hand we are saying pg_stop_backup()
> > > > doesn't work well in psql because you get those two file contents output
> > > > that you have to write, and on the other hand we are saying we are going
> > > > to deprecate the method that does work well in psql?  I must be missing
> > > > something too, as that makes no sense.
> > > 
> > > I don't agree. I don't see how "making a backup using psql" is more
> > > important than "making a backup without potentially dangerous 
> > > sideeffects".
> > > But if others don't agree, could one of you at least provide an example of
> > > how you'd like the docs to read in a way that doesn't deprecate the unsafe
> > > way but still informs the user properly?
> > 
> > I'm with Magnus on this, primairly because I've come to understand just
> > how dangerous the old backup method is.  That method *should* be
> > deprecated and discouraged.  A backup method which means your database
> > doesn't restart properly if the system crashes during the backup is
> > *bad*.
> > 
> > Fixing that means using something more complicated than the old method
> > and that's a bit of a pain in psql, but that doesn't mean we should tell
> > people that the old method is an acceptable approach.
> > 
> > Perhaps we can look at improving psql to make it easier to use it for
> > the new backup method but, honestly, all these hackish scripts to do
> > backups aren't doing a lot of things that a real backup solution needs
> > to do.  Improving psql for this is a bit like new paint on the titanic.
> 
> I guess that the danger mentioned above is that the database may not
> restart if it crashes while in exclusive backup mode, because the
> WAL containing the starting checkpoint has already been purged.

The database would think it's trying to do recovery from a backup, not
doing crash recovery like it should be doing, so, yes, that's pretty
ugly.

> I would say the typical use case for the exclusive backup method is
> the following (and I have seen it often):
> 
> You have some kind of backup software that does file system backups
> and is able to run a "pre-backup" and "post-backup" script.
> The backup is triggered by the backup software.

Seeing it often doesn't make it a good solution.  Running just
pre-backup and post-backup scripts and copying the filesystem isn't
enough to perform an online PostgreSQL backup- the WAL needs to be
collected as well, and you need to make sure that you have all of the
WAL before the backup can be considered complete.  On restore, you're
going to need to create a recovery.conf (at least in released versions)
which provides a restore command (needed even in HEAD today) to get the
old WAL, so having to also create the backup_label file shouldn't be
that difficult.

Lastly, if you really want, you can extract out the data from
pg_stop_backup in whatever your post-backup script is.

> Another thing that is problematic with non-exclusive backups is that
> you have to write the backup_label file into the backup after the
> backup has been taken.  With a technique like the above, you cannot
> easily do that.

... why not?  You need to create the recovery.conf anyway, and you need
to be archiving WAL somewhere, so it certainly seems like you could put
the backup_label there too.

> So in essence, all backup methods that work like the above won't be
> possible any more once the exclusive backup is gone.

That's why it's been deprecated and not removed...  but hopefully we
will be able to remove it in the future as it really isn't a good API.

> I expect that that will make a lot of users unhappy.

If it means that they implement a better backup strategy, then it's
probably a good thing, which is the goal of this.

> Would it be an option to change the semantics so that "backup_label"
> is ignored if there is no "recovery.conf"?  Of course that opens
> another way to corrupt your database (by starting it from a backup
> without first creating "recovery.conf"), but we could add another
> big warning.

My recollection is that other approaches were considered to deal with
this but none of them were all that good, though that was two years ago
at this point.  Certainly the issue with the above where a restored
backup without a recovery.conf could unwittingly be treated as 

Re: Updated backup APIs for non-exclusive backups

[Laurenz Albe]

I'm a bit late to the party, but I only recently noticed that the exclusive
backup API is deprecated.

On Sun, 2016-04-24 at 11:49 -0400, Stephen Frost wrote:
> * Magnus Hagander (mag...@hagander.net) wrote:
> > On Sun, Apr 24, 2016 at 5:37 AM, Bruce Momjian  wrote:
> > > On Fri, Apr 22, 2016 at 11:53:46AM -0400, Robert Haas wrote:
> > > > 
> > > > Frankly, I think that's right.  It is one thing to say that the new
> > > > method is preferred - +1 for that.  But the old method is going to
> > > > continue to be used by many people for a long time, and in some cases
> > > > will be superior.  That's not something we can deprecate, unless I'm
> > > > misunderstanding the situation.
> > > 
> > > I agree with Robert.  One the one hand we are saying pg_stop_backup()
> > > doesn't work well in psql because you get those two file contents output
> > > that you have to write, and on the other hand we are saying we are going
> > > to deprecate the method that does work well in psql?  I must be missing
> > > something too, as that makes no sense.
> > 
> > I don't agree. I don't see how "making a backup using psql" is more
> > important than "making a backup without potentially dangerous sideeffects".
> > But if others don't agree, could one of you at least provide an example of
> > how you'd like the docs to read in a way that doesn't deprecate the unsafe
> > way but still informs the user properly?
> 
> I'm with Magnus on this, primairly because I've come to understand just
> how dangerous the old backup method is.  That method *should* be
> deprecated and discouraged.  A backup method which means your database
> doesn't restart properly if the system crashes during the backup is
> *bad*.
> 
> Fixing that means using something more complicated than the old method
> and that's a bit of a pain in psql, but that doesn't mean we should tell
> people that the old method is an acceptable approach.
> 
> Perhaps we can look at improving psql to make it easier to use it for
> the new backup method but, honestly, all these hackish scripts to do
> backups aren't doing a lot of things that a real backup solution needs
> to do.  Improving psql for this is a bit like new paint on the titanic.

I guess that the danger mentioned above is that the database may not
restart if it crashes while in exclusive backup mode, because the
WAL containing the starting checkpoint has already been purged.
True, that is a problem.

I would say the typical use case for the exclusive backup method is
the following (and I have seen it often):

You have some kind of backup software that does file system backups
and is able to run a "pre-backup" and "post-backup" script.
The backup is triggered by the backup software.

Another thing that is problematic with non-exclusive backups is that
you have to write the backup_label file into the backup after the
backup has been taken.  With a technique like the above, you cannot
easily do that.

So in essence, all backup methods that work like the above won't be
possible any more once the exclusive backup is gone.


I expect that that will make a lot of users unhappy.

Would it be an option to change the semantics so that "backup_label"
is ignored if there is no "recovery.conf"?  Of course that opens
another way to corrupt your database (by starting it from a backup
without first creating "recovery.conf"), but we could add another
big warning.

I'd say that the people who ignore such a warning are the same
people that happily remove "backup_label" when they see the following
message upon starting a cluster from a backup without recovery:

  If you are not restoring from a backup, try removing the file "backup_label".

Yours,
Laurenz Albe