Clever Geek Handbook

How to update the production database of turbo engine applications

I have a production postgres database (which contains a lot of data). now I need to modify the tg-app model to add a couple of new tables to the database.

How should I do it? I am using sqlAlchemy.

+1


source to share


4 answers


It always works and requires little thought - just patience.

  • Make a backup.

  • Create a backup yourself. Everyone skips step 1 thinking they have a backup, but they can never find it or work with it. Do not trust a backup that cannot be recovered from.

  • Create a new database schema.

  • Define a new structure from scratch in a new schema. Ideally, you run a DDL script that will build a new schema. Don't have a script to build the circuit? Create it and put it under version control.

    With SA, you can define your tables and build your schema for you. This is ideal since you have your Python versioned schema.

  • Move data.

    and. For tables that do not change structure, move data from the old schema to the new schema using simple INSERT / SELECT statements.

    b. For tables that have changed structure, develop INSERT / SELECT scripts to transfer data from old to new. This can often be one SQL statement for a new table. In some cases, it must be a Python loop with two open connections.

    from. Load data for new tables.

  • Stop using the old scheme. Start using the new scheme. Find each program using the old schema and correct the configuration.

    Don't have a list of apps? Make one. Seriously, this is important.

    Do applications have hardcoded DB configurations? Fix that too while you're at it. Either create a common config file or use some common environment variable or something to (a) ensure consistency and (b) centralize the notion of "production".



You can do this procedure every time you perform a major operation. It never touches the old database except to retrieve data.

+1


source


The easiest way is to just write some sql update scripts and use them to update the database. Obviously a pretty low level approach (sort of).



If you think you'll be doing this a lot and want to stick with Python, you can have a look at sqlalchemy-migrate . There was an article about this in a recent Python magazine.

+1


source


I would agree in general with John . Single-pass SELECT and INSERTing would not be practical for a large database, and setting up delta or multi-pass differential SELECT / INSERTs would probably be more complex and error prone.

Personally, I use SQLAlchemy as the ORM in TurboGears . To migrate the schema, I run:

tg-admin sql status

To see the difference between the live and development schemas, then manually create the DDL (and version control) scripts to make the necessary changes.

For those using SQLAlchemy standalone (i.e. not under TurboGears) the functionality is sql status

pretty straightforward and can be found here in the TG source: http://svn.turbogears.org/branches/1.1/turbogears/command/sacommand.py (there are versions for older Python / SA versions in the 1.0 branch too).

+1


source


If you are just adding tables and not modifying any of the tables that have existing data, you can simply add the new sqlAlchemy table definitions to model.py and run:

tg-admin sql create

This will not overwrite any existing tables.

For schema migration, you can take a look at http://code.google.com/p/sqlalchemy-migrate/ , although I haven't used it myself yet.

Always back up your production database before migrating.

0


source






More articles:


All Articles