On 07/11/17 02:37, Stephen Frost wrote:
Mark,
* Mark Kirkwood (mark.kirkwood@xxxxxxxxxxxxxxx) wrote:
On 03/11/17 00:11, Stephen Frost wrote:
Sure, that'll work much of the time, but that's about like saying that
PG could run without fsync being enabled much of the time and everything
will be ok. Both are accurate, but hopefully you'll agree that PG
really should always be run with fsync enabled.
It is completely different - this is a 'straw man' argument, and
justs serves to confuse this discussion.
I don't see it as any different at all. The point I was trying to make
there is that there's a minimum requirement for backups, just as there
is for ACID compliance, and any solution needs to meet that minimum to
be considered.
Ok and apologies - I thought you were going all 'schoolboy debating' on
me :-) . I'll discuss how I'm seeing this:
In the case of a db server running with fsync off, one crash and it may
not be able to be restarted - ever, so pretty severe loss of service.
In the case of a backup server crashing immediately after a backup
(assuming archive logs and backup going to same host for simplicity),
then *if undected* it could mean that later you cannot restore this
backup - very bad...so in that case I agree with you. However detection
(i.e monitoring) is essential otherwise a meticulously fsync'd set of
WAL can be lost or corrupted by the various usual suspects too (bad
ram/hba/disk...) - with the same result. So assuming we have monitoring
doing its thing, after the backup server crashes then missing or damaged
WAL can be retrieved from our still running db server - or if they have
been recycled, then we need to do another backup. No loss of service.
The crux of your argument seems to be concerning the synchronization
between pg_basbackup finishing and being sure you have the required
archive logs. Now just so we are all clear, when pg_basebackup ends
it essentially calls do_pg_stop_backup (from xlog.c) which ensures
that all required WAL files are archived, or to be precise here
makes sure archive_command has been run successfully for each
required WAL file.
pg_basebackup talks the replication protocol, to be clear, and sends a
BASE_BACKUP message, of which one of the options is 'NOWAIT' to indicate
if the server should wait until all of the WAL has been archived.
Typically, pg_basebackup does send a 'NOWAIT' to tell the server to not
hold up the final message until all of the WAL has been archived,
because it's handling the verification of the WAL having been archived.
In the unusual case that WAL isn't included with the pg_basebackup it
looks like it would wait for the archive_command to complete, which is
better than I had thought (and hadn't noticed on my first glance through
the code), though that does depend on a functional and perfect
archive_command, and there's no shortage of reasons for why that might
not be the case at the time the backup is happening.
That's an awful lot of action-at-a-distance hope for me to be
comfortable with, however. A backup solution really does need to verify
that the WAL has been completely and reliably stored, as discussed in
the documentation, before claiming a backup is valid, and there's
basically no reason not to unless the tool you've chosen to use makes
that particularly difficult (even if not *technically* impossible, given
enough effort). If your solution is built on the assumption that WAL
archiving is always working and there's no check happening during backup
to verify that you've got all the WAL then I have serious doubts about
it being reliable. If you're independently monitoring that all WAL has
been archived, that's certainly helpful, but I don't consider that to be
a complete substitute for making sure that you've got all of the WAL for
a given backup.
Your entire argument seems about whether said WAL is fsync'ed to
disk, and how this is impossible to ensure in a shell script.
[...]
So it is clearly *possible*.
Yes, it's possible, but it's not something I'd recommend doing and none
of your arguments have made me any more likely to recommend trying to
ensure a proper backup has completed using shell scripts. What I fail
to understand is your insistence on it being a good idea. I've seen
lots and lots of attempts at it, even made some myself, and have come to
the generally agreed upon conclusion that it's both a bad idea to hack
together your own backup solution for PG and that, even if you do want
to try, using shell scripts to attempt to accomplish it is a bad idea.
There's much better solutions out there which are really what folks
should be using. I'm not against using pg_basebackup either, but if
you're using it, let it handle the archiving because it does verify that
all of the WAL has been archived properly.
Actually I was helping him get a *reliable* backup system, I think
you misunderstood how swift changes the picture compared to a single
server/single disk design.
Ok, so I think we have moved closer to seeing each other's point of view
- been an interesting discussion so far!
I do understand the goals of things like swift and s3 and the intent
behind them to provide a better store than local disks, and I'm not
against using them, to be clear, but they only address one of the
requirements that I outlined for a reliable backup solution. I mention
both requirements consistently to, hopefully, ensure that those coming
along later to read these threads remember that it's more than just
making sure that you verify all the WAL has been archived during a
backup- but that they've been archived and actually fsync'd or written
out to reliable storage.
Here I think you have still not grasped that (e.g) swift achieves *both*
of these - without you attempting to call fsync after your uploads. (for
instance in our swift cluster, you would have to have all three data
centers down to lose access to your uploaded WAL...and we run with the
various storage mounted with barrier=on so the files will be there when
the centers return) - note that swift PUT operation (which is what
upload is doing) does fsync at the end.
regards
Mark
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
