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

On Tue, Nov 16, 2010 at 5:46 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Folks,
>
> Please help us resolve a discussion on -hackers.
>
> PostgreSQL 9.1 is likely to have, as a feature, the ability to create
> tables which are "unlogged", meaning that they are not added to the
> transaction log, and will be truncated (emptied) on database restart.
> Such tables are intended for highly volatile, but not very valuable,
> data, such as session statues, application logs, etc.
>
> The question is, how would you, as a DBA, expect pg_dump backups to
> treat unlogged tables? Backing them up by default has the potential to
> both cause performance drag on the unlogged table and make your backups
> take longer unless you remember to omit them. Not backing them up by
> default has the drawback that if you forget --include-unlogged switch,
> and shut the database down, any unlogged data is gone. How would you
> *expect* unlogged tables to behave?

For 'as regular table' argument:
We are *assuming* the data is not very valuable. I'd rather assume it
is valuable. pg_dump has a very specific purpose: to back up the
database in it's current state into a file that can replay that state.
Not backing up large objects by default is a huge gotcha imnsho. I
understand the reasoning, but disagree with it. Certainly a switch to
tune them out would be nice, but not the default.

Against argument:
hm, I guess the counter agrument is that since because they are not
WAL logged, they can't possibly be replayed to a standby, and it makes
sense to have pg_dump and archive log based backup behave in similar
fashion. It's weird that different backup strategies produce
different results.

I think the 'against argument' is stronger by about .5, so I'm voting
.5 for pg_dump not to dump them. In fact, if that side of it wins,
maybe pg_dump shouldn't even deal with them at all. I guess that
would by my position.

merlin