Home / server / Questions / 327545
In Process
Devin Ceartas
Asked: 2011-11-04 11:53:23 +0800 CST

PostgreSQL update tool?

  • 772

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.

postgresql

1 Answers

  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.

    • 2