Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Is there a tool (PHP, Perl, Ruby, shell, online SaaS) which can take a PostgreSQL database dump, a new schema, and migrate (w/o data loss)? Dealing with dropping and adding dependencies is a bit of a hassle.

share|improve this question

1 Answer 1

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:

  • CREATE TABLE, 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 (or 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.1.sql, updates-1.2.2.sql, . . ., updates-1.3.0.sql


A few tips on the upgrade scripts:

  1. Make sure everything is wrapped in a transaction (BEGIN . . . ROLLBACK . . . COMMIT).
    If something goes wrong you want to be able to abort the whole debacle without leaving your database in a screwy/inconsistent state.

  2. 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.

  3. Expect the upgrades to fail -- Include detailed errors (RAISE EXCEPTION/RAISE NOTICE) and make sure the programs executing the upgrade sql scripts stop if an error is encountered.

share|improve this 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." Truer words were never spoken! –  Matthew Wood Nov 14 '11 at 17:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.