Re: PostgreSQL Portable

Hi Craig,

On Fri, 12 Sep 2014 11:33:55 +0800, Craig Ringer
<craig(at)2ndquadrant(dot)com> wrote:

>On 09/11/2014 03:16 PM, George Neuner wrote:
>>
>> If the driver permits it and you [or your users] can be trusted to
>> perform a safe unmount via the OS *before* disconnecting the device,
>> then you can enable write caching for the device using the device
>> manager. [Note that the device must be connected for it to be visible
>> in the device manager.]
>
>It shouldn't be living dangerously, actually.
>
>While I haven't tested it myself, writeback caching on the external
>drive should be safe so long as it continues to honour explicit disk
>flush requests.
>
>That's why we have the WAL and do periodic checkpoints. If you yank the
>drive mid-write you'll lose uncommitted transactions and might have
>slower startup next time around, but it should otherwise not be overly
>problematic.

For the most part you're correct, but recall that WAL itself can be
made asynchronous [see fsync() and synchronous_commit() settings] and
the periodic OS sync also may be disabled - which doesn't affect WAL
handling but may(?) affect the background writer.

Even having synchronous WAL the most recent transactions can be lost
if the log device fails *during* a write. That's why, if we use
external devices at all, we tend to use closely coupled devices - disk
array, wired SAN, etc. - that aren't very likely to be physically
disconnected. And uninterruptible power all around 8-)

A portable device can be reasonably safe if treated properly, but it
never will be quite as safe as an internal device.

George