We have a fairly 'good' process at our shop that we follow that works
for us....
First we do a schema comparison between our prod and devl/test
environments using the EMS PostgreSQL database comparer tool...
We extract the DDL changes that it produces ("alter table add column,
etc") and place in a single script file.
We then add/subtract elements from the script as necessary based on our
knowledge of how our test environment differs from our prod one
We have a piece of code that enumerates each table and denotes what
permissions SQL to apply. We update this code to add all new tables
(from the EMS compare)....
The code will produce the appropriate DDL for us (GRANT blah on blah)
and we then add it to our 'script'
We have some tables that we completely drop from production and reload
from the devl/test environment. In those cases we simply add the
appropriate commands to the script (drop table blah, insert into table
values(blah,blah, etc)
For piecemeal data that needs to make it's way from test to prod, we
dump (extract) those records as insert statements and add those to the
script.
When it's all said and done, we place a begin work at the top and run it
against the production database.
We find errors/issues and adjust the script accordingly (rolling back
each time).....when done we run it one last time and do the commit;
postgresql@xxxxxxxxx wrote:
Hi,
I'm looking for advice on how to update both structural changes and data
changes between PostgreSQL databases.
Here is my situation:
I have both a development and production environment. In the development
environment I have a PostgreSQL database that has many tables. Some of the
tables contain records that must be removed when the database is in
production (for example records that were entered while testing the
database) and other tables have information that must be maintained when the
database is moved to production (for example records that contain the
contents of drop down menus in my web application).
Here are my questions:
1. How should I do the initial move of the database from development to
production where the structure of the database is moved and only the data in
specific tables is moved?
2. After further development is done in the development environment, how do
I make those changes in the production database? I will need to update the
following:
a. structural changes(ex. Adding or removing fields from tables,
adding new tables, removing old tables)
b. administrative changes (ex. setting permissions on databases
and/or tables)
c. data changes (ex. Adding new records to tables that have
"maintained data")
d. ensuring that test data does not end up in the production
database (ex. Adding fake users to the system, or updating counters with
inaccurate data)
e. ensuring that existing data is not removed from the production
database (ex. A user's account information is not deleted)
I would really appreciate any and all thoughts and ideas on how to go about
these procedures.
Thank you in advance,
Courtenay
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
