Skip site navigation (1) Skip section navigation (2)

Re: Question - help needed

From: Oliver Charles <oliver(at)ocharles(dot)org(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Question - help needed
Date: 2010-11-21 17:41:23
Message-ID: icblk3$ge3$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-novice
On 09/11/10 17:56, J. Roeleveld wrote:
> On Tuesday 09 November 2010 18:18:23 Dragan Keselj wrote:
>> Hello,
>>
>> I have 2 identical databases. One is with data and second one is empty (for
>> designing).
>> Is it possible to make changes on empty database (for example to add,
>> remove column or change name...) and, with these changes, refresh design
>> of second one database (without data changes)?
>>
>> thnx for help
>
> Hi,
>
> Simple answer: yes and no...
>
> More usefull answer:
> If you make changes to the empty database using "ALTER TABLE" commands, you
> should be able to use those same commands on the actual database.
>
> But, if you delete a table/column/... that actually contains data, you WILL
> loose that data.
>
> If you redesign using DROP TABLE / CREATE TABLE commands, you will need to find
> a tool that can compare 2 states of the database and create the ALTER TABLE
> commands for you.
>
> Same goes for other database objects, like VIEWs, INDEXes,....., etc.

I'd like to add in my 2 cents - I don't trust a tool to do this job. 
What we prefer to do at work is write scripts that transform the 
database from one state, to another. Here's how it works:

We start out with a CreateTables.sql file. Someone then needs to change 
the schema, so they modify this file. They *also* write a script to go 
with it, like update-20101121-description-here.sql. If they added a 
column to the database, this script would contain an appropriate ALTER 
TABLE statement.

This isn't automated, but it does the job for us because we don't tend 
to modify the schema all that often anymore. You can slightly automate 
this process, Rake for Ruby on Rails handles the scripts for you in a 
database agnostic manner. I wrote Schema-Evolution to do a similar type 
of thing, but with plain SQL statements [1].

Hope this helps you!

--
[1]: http://search.cpan.org/~cycles/SchemaEvolution-0.03/script/evolve.pl


In response to

pgsql-novice by date

Next:From: richard terryDate: 2010-11-22 03:26:59
Subject: Help with date query
Previous:From: bakkiyaDate: 2010-11-20 14:22:00
Subject: Re: Could not open relation with OID (table partitioning issue?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group