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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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