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@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx 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
