Search Postgresql Archives

Re: Schema as versioning strategy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux