Обсуждение: Updates/Changes to a database
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 ?
On Jul 12, 2007, at 10:07 AM, 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 ? I tend to keep a schema version field (typically in a one-row, one- column table) in the database. Then I have a set of SQL scripts that'll upgrade from version n to version n+1, and they can be applied manually or automatically in sequence to bring the schema version up to the version required by the application. That's pretty common amongst big database backed apps, and if you're nice you also provide downgrade scripts to back out revisions. Maintaining the SQL patch scripts by hand isn't too hard to do, but I've found these tools useful too: http://dbmstools.sourceforge.net/ Cheers, Steve
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
On Jul 12, 4:03 pm, scrawf...@pinpointresearch.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.
On 7/12/07, imageguy <imageguy1206@gmail.com> wrote: > Are there pre-existing tools out there that does this sort of thing ?? Rails and Django -- two popular web development frameworks -- support a simple mechanism for doing schema migrations. In Rails, in particular, each schema change is encapsulated as a class. Each such change is called a migration, and implements two methods for effecting and rolling back the migration, respectively. Since these are Ruby classes, they can do anything at all -- execute SQL, flush caches, restart daemons, etc. Transactions ensure that each migration is executed atomically. All the migrations are then collected in a directory, and numbered: $ ls -l db/migrate ... -rw-r--r-- 1 alex alex 1691 Jun 28 15:21 163_send_dns_message_to_domain_owners.rb -rw-r--r-- 1 alex alex 711 Jun 28 20:56 164_create_image_batches.rb -rw-r--r-- 1 alex alex 1087 Jun 28 17:12 165_delete_some_dns_messages.rb -rw-r--r-- 1 alex alex 970 Jul 2 14:39 166_add_reader_to_visitor_transistion.rb -rw-r--r-- 1 alex alex 1267 Jul 2 15:33 170_create_indexes3.rb In the database, a dedicated table is used to store the last applied migration number. Rails itself provides a command that sets the database to a specific migration number, allowing you to roll forward and backward in the schema evolution. I know somebody has released an independent schema migration tool based on numbered SQL scripts, but I don't remember the name. Might be of use to you. I recommend looking on SourceForge or FreshMeat. Alexander.