Re: pg_rawdump

On Wed, Oct 20, 2010 at 1:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> [ thinks for a bit... ]  Perhaps we could stick some sort of unique
> ID into tables, which could be correlated to the same unique ID
> appearing in a metadata fork.

It would be awfully nice if the table name was in the first few bytes
of the segments ;-)

> [ thinks some more... ]  Of course, at this point you have to start
> asking why the metadata fork should be thought to be any more reliable
> than the system catalogs.

It's not. But it's about data duplication. If you've lost some
files, and others are found in lost+found, etc. Or maybe you lost
only the "main" tablespace, and you have your big-honking-files from
another table-space around, etc.

The more copies of the data around, the more chance you have of being
able to get something usable from of it.

But we already have WAL archive as an external safe backup copy of *everything*.

Maybe the cost of those extra forks/duplicates is too much. Maybe it
would be cheaper to try and parse the WAL archive, and just
specifically rebuild the system catalogs for the couple of times
people actually need this type of data?

Or maybe a query/function that ultra paranoid types can run daily,
etc, which would show the system catalog information about table
storage format that the could save safely instead of munging GB of WAL
logs when they want it...

> You can bet that there'll be somebody out there who whines because their
> app does lots and lots of repeated ALTER TABLEs, and so the metadata fork
> grows forever.  I think we'd have to be a bit smarter than this.

Well, for bad habits, we have an equally bad solution ;-)
BEGIN;
ALTER TABLE bad_design RENAME TO bad_design_too_old;
CREATE TABLE bad_design LIKE bad_design_too_old INCLUDING DEFAULTS
CONSTRAINTS INDEXES;
INSERT INTO bad_design SELECT * FROM bad_design_too_old;
DROP TABLE bad_design_too_old;
COMMIT;

It's all about how much extra stuff do we want "around" to make
forensic/reconstruction type work easier when they can't go to the
documented, tried, tested, "normal restore from backup/WAL".

None? Or as much as possible? And what are the tradeoffs.

--
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.