Short answer: No.
Longer answer: Magically determining the path from Schema A to Schema B is beyond the capabilities of a computer program, even one as complex as a database system. A human needs to get involved.
Your database upgrade procedure should include creating an update script (e.g.
updates-1.3.0.sql) that does the appropriate and necessary things, including:
ALTER TABLE and
DROP TABLE commands
CREATE INDEX and
DROP INDEX commands
- Any SQL Magic (inserting/deleting/updating data)
- Creating appropriate triggers/constraints
- Doing a
VACUUM FULL /
VACUUM ANALYZE as appropriate)
You then compare your current schema version (you can store this in the DB somewhere) to the list of upgrade scripts you have, and execute the upgrade scripts in order to transition to the new schema.
For example, if you are running schema revision 1.2.0 and want to transition to 1.3.0 you would apply
updates-1.2.2.sql, . . .,
A few tips on the upgrade scripts:
Make sure everything is wrapped in a transaction (
BEGIN . . .
ROLLBACK . . .
If something goes wrong you want to be able to abort the whole debacle without leaving your database in a screwy/inconsistent state.
Write your update scripts as you write your new schema.
In fact, my usual method of writing a SQL file for creating the new schema in an empty DB is to develop the upgrade script, then run it on an empty database and dump the resulting schema -- This ensures that the "bare metal" script produces results identical to what you'd get out of the upgrade process.
Expect the upgrades to fail -- Include detailed errors (
RAISE NOTICE) and make sure the programs executing the upgrade sql scripts stop if an error is encountered.