Re: Schema as versioning strategy

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 .

i think you're best off with a pgdump