Re: Schema as versioning strategy

At 9:23 AM +0100 4/26/07, Richard Huxton wrote:
>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.


Thanks to everyone for all the replies.  You've been most helpful. 
It looks like pg_dump is the way to go, though I'll have to think 
about it because I'm ultimately looking for a mechanical process that 
will automatically tweak the schema names.  I don't want to have to 
visit clients every year to archive their data.  Since the pg_dump 
file might change, my program may have to be version dependent.

-Owen