Re: Schema as versioning strategy

Owen Hartnett wrote:
>
> Hi:
>
> I'm a new user of Postgresql (8.2.3), and I'm very happy with both the
> performance and operation of the system. My compliments to you the many
> authors who keep this database running and useful.
>
> My question is:
>
> I want to "freeze" a snapshot of the database every year (think of end
> of year tax records). However, I want this frozen version (and all the
> previous frozen versions) available to the database user as read-only.
> My thinking is to copy the entire public schema (which is where all the
> current data lives) into a new schema, named 2007 (2008, etc.)
>
> Is this a valid plan. I had thought of using a different database, but
> that would require multiple opens. I looked to see if there were an
> easy way to script doing an exact schema copy, but I haven't found
> anything like it in the docs.
>
> This is not heavy usage, nor is there a large amount of data (current
> pg_dump backups are around 30 Megabytes.
>
> Am I on the right track, or would you suggest a different strategy?

I get the impression that table partitioning with constraint exclusion
would fit your purpose nicely. Effectively the data is split into
separate tables with a check constraint on a specific year each, while
the total dataset is still available through the common inherited base
table.

If possible (haven't used this myself yet) the big benefit is that your
data ends up in the right table, even if you are a little late starting
with your next years data.

Regards,

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //