Re: Updates/Changes to a database

imageguy wrote:
> I am building an application with Postrges as the backend foundation.
> This is my first application and it has struck me that as we add
> features/functionality to the application and database with each new
> version, we will need some method of obtaining the current structure
> of the customers database and then modifying/updating the structure so
> that it matches the application revision standard.
>
> Are there pre-existing tools out there that does this sort of thing ??
>
> My present direction is to create a small SQLite db that has there
> expected structure, compare each table against the SQL
> "information_Schema.columns" and the create a series of SQL commands
> to be executed that would add columns and/or table as needed.
>
> -- any thoughts or comments ?

Not sure why you need SQLite when you, *ahem*, have and are modifying
PostgreSQL. All the info you seek is in the system tables. To get a
jump-start, try running psql with the -E option to see the backend
queries that generate the displays of tables and table layouts.

How you go about performing the updates will depend on many things:

Are other apps running against the DB - especially the tables your app uses?

Will the app be running on various versions of PG or will you control that?

Will you allow any version to any version updates or only updates to the
next version?

What about the ability to downgrade to prior versions?

Will the client-side be updated simultaneously with the database schema?

What permissions will be required to perform the update?

Updates in a sophisticated system will not be as simple as just matching
table structures. You need to consider alterations to constraints -
especially foreign-key constraints. Also the effect on views. It is
likely that any version-to-version updates will need to be done in a
specific and tested order. As a simple example, you would need to update
a table to add a column before updating a view that refers to that column.

One thing that might be useful is to create a simple function that just
returns a version number:

create or replace function my_app_version()
 returns text
 language sql
 as 'select ''1.01''::text;';


You can use this as needed. The client application can check the
database-side version and either modify its behavior appropriately (ie.
hide unavailable features) or refuse to start if there is an
un-reconcilable mismatch.

You could also create scripts to verify your database setup against the
returned version and report errors, and you can base your update
activity on the returned value. For example:

1. Test that existing tables/views/indexes/etc. match the returned
version number - exit if not

2. If yes, check for availability of handler to change existing version
to desired version - exit if one isn't available.

3. Perform backup.

4. Perform update including update of version-number function. As
appropriate to your situation, you could change the version-number
function at the start of your operation, say from '1.01' to '1.01->1.15'
and program the clients to display an appropriate message if they try to
connect during the upgrade. You will, of course, need to use
transactions, locks, etc. to prevent access during the upgrade.

5. Verify database against new value of my_app_version()

Cheers,
Steve