Re: Survey on backing up unlogged tables: help us with PostgreSQL development!

On Wed, Nov 17, 2010 at 12:49 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

>
> As was already mentioned, application logs. Unlogged tables would be
>> perfect for that, provided they don't go *poof* every now and then for
>> no good reason. Nobody's going to be too heart broken if a handful of
>> log records go missing, or get garbled, after a server crash or power
>> outage. Delete 'em all after every restart though, and that's a problem.
>>
>
> That's a nice thought, but it's not how data corruption works in the event
> of a crash. If a table is corrupted, *we don't know* how it's corrupted,
> and it's not just "the last few records" which are corrupted. So for
> unlogged tables, there is never going to be any other option for crashes
> than to truncate them.
>
> Robert Haas did discuss the ability to synch unlogged tables on a planned
> shutdown, though. However, that's liable to wait until 9.2, given the
> multiple steps required to make it work.
>
> Note that you would have the option of periodically synching an unlogged
> table to pgdump or to a logged table, via script, if you cared about
> retaining the data. That would probably give you the behavior you want,
> above.
>
>
In an airplane, a pilot can kill the engine mid-flight if [s]he wants to.
They can deploy the flaps /slats at cruise speed / altitude, and if they're
so minded, they can land with a full tank of gas. Now, none of these things
are particularly wise, but that's why the pilots are given *slightly* more
learning than your average bus driver.

If you want to have a widely usable 'unlogged' table feature, I highly
recommend that 'truncate on server crash/restart' be an option that is
defaulted to true. That way, I can go in an push the buttons I want and
give corrupted data to whomever, whenever i like. (Land with a full tank of
Jet-A).

Whatever the decision is about backup, doesn't really matter IMO, but I
honestly think that the benefit of an unlogged table is there for both
session data (I run my session db's in fsync mode anyway and re-initdb them
on boot) AND for logging data where I can't take WAL anymore, but would like
to be able to have them in the same cluster as other stuff. If they just
disappear then this feature won't be useful [to me] and I'll have to either
wait for the patch or give up on it and do a flat-file / lucene project just
to deal with it (I really don't want to do that :-).

--Scott

>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>