Re: Updates/Changes to a database

On Jul 12, 4:03 pm, scrawf(dot)(dot)(dot)(at)pinpointresearch(dot)com (Steve Crawford)
wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster- Hide quoted text -
>
> - Show quoted text -

Thanks very much for these thoughtful questions/hints/suggestions.
Much food for thought.

FWIW I was planning to use SQLite to store the new schema that the PG
database should be upgraded to. SQLite being simple, fast and
portable so it can easily be distributed with the next version upgrade
of the client programs.