Re: Schema as versioning strategy

Jonathan Vanasco wrote:
>
> On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote:
>
>> 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.
>
> the create table method won't copy the constraints + fkeys .

Shouldn't matter for an archive though, since you'd not want anyone to
have permissions. Still, pg_dump is my preference. Apart from anything
else, you can keep a copy of the dump around too.

--
   Richard Huxton
   Archonet Ltd