Re: Schema as versioning strategy

[Richard Huxton <dev(at)archonet(dot)com>]

Owen Hartnett wrote:
> 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.)

Sounds perfectly reasonable. You could either do it as a series of:
  CREATE TABLE archive2007.foo AS SELECT * FROM public.foo;
or do a pg_dump of schema "public", tweak the file to change the schema 
names and restore it.

--
  Richard Huxton
  Archonet Ltd