Re: Schema as versioning strategy

What about creating NOW empty schemas 'till 2038?
Your application will move automatically on the new empty schema on the new
year without any changes to the db structure.

On 4/26/07, Owen Hartnett <owen(at)clipboardinc(dot)com> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Angelo Rossi
Bluemetrix Ltd
Northpoint House
Northpoint Business Park
Mallow Road
Cork
Ireland

Ph: +353 021 4640107
Fax: +353 21 4309131
Web: www.bluemetrix.com

The content of this e-mail may be confidential or legally privileged. If you
are not the named addressee or the intended recipient please do not copy it
or forward it to anyone. If you have received this email in error please
destroy it and kindly notify the sender. Email cannot be guaranteed to be
secure or error-free, it is your responsibility to ensure that the message
(including attachments) is safe and authorised for use in your environment.
Bluemetrix Ltd, Registered in Ireland at Northpoint House, Northpoint
Business Park, Mallow Road, Cork

Co Reg No.: 335879