Subject: Re: Bad recovery: no pg_xlog/RECOVERYXLOG
To: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-admin@postgresql.org
References: <1070071508952614@web46j.yandex.ru>
 <ae7f2ee9-97ff-267e-b6dc-9399250a9314@cahoots.pl>
 <20171030154706.GE4628@tamriel.snowman.net>
 <7966ee9e-2241-eced-4639-e32c157b915a@catalyst.net.nz>
 <20171031114756.GP4628@tamriel.snowman.net>
 <2e3453d3-da6f-88af-05ec-7530e7f60547@catalyst.net.nz>
 <20171101122847.GS4628@tamriel.snowman.net>
 <4efa2769-78c4-7bf0-5c33-0cf2d942ce74@catalyst.net.nz>
 <20171101221807.GW4628@tamriel.snowman.net>
 <94a943ac-8b63-5cfc-9d48-22b7193b9766@catalyst.net.nz>
 <20171102111141.GZ4628@tamriel.snowman.net>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <45c0bedc-9ce8-b99b-80df-94a1180fbc88@catalyst.net.nz>
Date: Sat, 4 Nov 2017 11:00:03 +1300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: <20171102111141.GZ4628@tamriel.snowman.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Stephen,

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.

>
>> 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.
> No, again, this isn't an argument about if it'll work most of the time
> or not, it's about if it's correct.  PG without fsync will work most of
> the time too, but that doesn't mean it's actually correct.

No, it is pointing out that if your argument were correct, then there 
should be the above side effects - there are not, which is significant.

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.

Your entire argument seems about whether said WAL is fsync'ed to disk, 
and how this is impossible to ensure in a shell script. Actually it is 
possible quite simply: e.g suppose you archive command is:

rsync ... targetserver:/disk

There are several ways to get that to sync:

rsync .. targetserver:/disk && ssh target server sync

Alternatively amend  vm.dirty_bytes on targetserver to be < 16M, or 
mount the /disk with sync option!

So it is clearly *possible*.

However, I think you are obsessing over the minutiae of fsync to single 
server/disk when there are much more important (read likely to happen) 
problems to consider. For me, the critical consideration is, not 'are 
the WAL files there *right now*'..but 'will they be there tomorrow when 
I need them for a restore'? Next is 'will they be the same/undamaged 
when I read them tomorrow'?

This is why I'm *not* obsessing about fsyncing...make where you store 
these WAL files *reliable*...either via proxying/ip splitting so you 
send stuff to more that one server (if we are still thinking server + 
disk = backup solution). Alternatively use a distributed object store 
(Swift, S3 etc) that handle that for you, and in addition they checksum 
and heal any individual node data corruption for you as well.
>> 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.
> Ignoring the basic requirements which I outlined isn't helping him get
> to a reliable backup system.

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.

regards

Mark


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin