I want to know where to find information about database maintenance as a
developer.
I'm distributing the executable along with the Definition of the database.
After some time, I made changes to the executable and to some tables too.
I have to apply the database changes to the production database and do the
following:
1) Backup the database data, pg_dump is of no use because if the table design
changed then the COPY table FROM stdin produced by pg_dump needs to be
modified to accomodate the space for the new columns.
2) Disable the data integrity checking
3) Drop the table (this drops the Foreign Key constraints)
4) Create the table with the new data layout or definition
5) Insert in the new table the old values (including the new columns or
dropping some columns)
6) Define the data integrity checking
7) Enable the data integrity checking (I'm not sure if this checks the
initial integrity or just start checking with the future inserts and
updates???)
All this is done manually and have to do very detailed analisis and planning
for each change with the customer.
The question is, has somebody faced this kind of problems and found some
automation possibly (even some hints to do scripts about this will be
welcome)
Thanks
Jesus Sandoval