Re: Bad recovery: no pg_xlog/RECOVERYXLOG

On 02/11/17 11:18, Stephen Frost wrote:
> Mark,
>
> * Mark Kirkwood (mark(dot)kirkwood(at)catalyst(dot)net(dot)nz) wrote:
>> However there is the use case for people that just want a minimal
>> backup solution that works for their specific environment, and don't
>> want to bring along a lot of extra machinery that a full coverage
>> all-singing-and-dancing product includes - this *can* be
>> accomplished by a few shell scripts. Yes, it does mean that you
>> spend extra time testing and debugging [1]. Err - I think that is
>> all the original author (who is probably scared off now), was
>> wanting a bit of help with.
> This is exactly the issue that concerns me. I'm not suggesting that
> these scripts are, or need to be, the end-all, be-all of PG backup
> solutions.
>
> What I'm pointing out is that shell-script based solutions are *broken*,
> not that they are lacking in features. Many, many years ago I also used
> to think it was possible to perform a PG backup using just shell scripts
> and have it be successful and reliable, but since then I've seen too
> many cases where exactly that has lead to incomplete and invalid
> backups to be able to agree that they're reasonable to use. Not having
> a way to reliably sync the WAL files copied by archive command to disk,
> in particular, really is an issue, it's not some feature, it's a
> requirement of a functional PG backup system. The other requirement for
> a functional PG backup system is a check to verify that all of the WAL
> for a given backup has been archived safely to disk, otherwise the
> backup is incomplete and can't be used.
>
> Both of those basic requirements are, at best, extremely difficult to
> do in a shell script. Maybe it's possible to do, but I've certainly yet
> to see it and I'm not going to agree that such "simple" shell scripts
> should be posted to our mailing lists without someone pointing out that
> they're broken because, otherwise, people will take and use them and end
> up with backups that are broken (often right when they end up actually
> needing it).
>
> If you'd like to develop a shell script that addresses these basic
> requirements of file-based PG backups and ask for critique on it while
> making it clear that it's in development, I'd be happy to provide
> comments on it. I won't agree that any shell-based solution that
> doesn't have these basic requirements met is an acceptable option.
>
>

Ok, that is interesting. In my experience, provided the a) construction
you are using in archive_command correctly reports success/failure, and
b) you have some monitoring that checks for archive failure then that
requirement of you having the required logs will be fine. Finally that
c) your pg_basebackup concoction properly checks return codes then you
are fine.

All these are reasonably straightforward to implement via shell.

Also, if what you are suggesting were actually the case, almost
everyone's streaming replication (and/or log shipping) would be broken
all the time.

With respect to 'If I would like to develop etc etc..' - err, all I was
doing in this thread was helping the original poster make his stuff a
bit better - I'll continue to do that.

Best wishes

Mark