Automating PostgreSQL Tasks
Subject:   Some other helpful hints
Date:   2004-12-12 17:37:48
From:   pspinler
Good stuff. We do very similar stuff in our shop. Here's a couple of additional, possibly useful hints which we do:

1) Make a script per entity.

Assume that you're maintaining an already existing application, and you have to make partial changes to the database. A script per entity will allow you to change one entity at a time.

2) Use a revision control system. Using CVS, or better, something like SVN or Darcs on the scripts will allow you a lot more flexibility.

If you have e.g. a seperate development, test, and production database for an application, where you start a change in development, then migrate that change to test, and finally to production, here's a revision control system really shines.

2.a) use tags. Tag specific script revisions with the environment, e.g. "cvs tag -r 1.6 TEST foo_table.sql"

Then, to reconstruct the test database, pull everything with a TEST tag. When the next change gets migrated in, move the TEST tag to that revision of the script, DEVELOPMENT tagged scripts are what you'd use to rebuild development, etc.

3) Don't be afraid to use the database provided load/unload programs to create your scripts and data files. For instance, "pg_dump -a -t <table>" will dump only data for table, and "pg_dump -s -t <table>" will dump only schema for table.

Why, you ask ? Well, then you can let developers or other users make changes to schema or data, and after you review them, you can dump them to a file, and start migrating them to test or production databases.

You could even set up a web or script based set of functions to dump out schema, check it into cvs/svn, and tag it; then another set of scripts / web pages to check out a set of specific tags, and migrate it to a new database or rebuild a development database.

Anyway, hope folks find this interesting. good luck.

-- Pat

1 to 1 of 1
  1. Some other helpful hints
    2005-01-12 13:29:44  tomlag [View]

1 to 1 of 1