Pyrseas is a Database Version Control for PostgreSQL with a very interesting approach and with an easy way to script a database (database to a YAML file) and to apply changes to a database (YAML file to a database). It’s develped in Python by Joe Abbate (thank you Joe for it!)
Currently I’m using
dbtoyaml to script the database in every release and I commit it together with application code, but in the future I plan to use it together with
yamltodb for continuous integration.
Joe describes it as follows:
Pyrseas provides a framework and utilities to upgrade and maintain a PostgreSQL database.
- Pyrseas is free (libre) software and is distributed under the BSD license.
You can download it from GitHub and the most current documentation is available here.
Installing required packages
I suppose that in Linux its installation is quite straight forward, but in Windows Server 2008 R2 you need 64bits packages and not all required packages have their official 64bits version. So, I had to search for some alternative to install Pyrseas pre-requisites.
I installed these packages in the following order:
After downloading it from GitHub, unzip the file and into the directory execute:
python setup.py install
E:\Temp\pyrseas-0.5.0>python setup.py install
Searching for psycopg2==2.4.5
Best match: psycopg2 2.4.5
Adding psycopg2 2.4.5 to easy-install.pth file
Searching for distribute==0.6.26
Best match: distribute 0.6.26
Adding distribute 0.6.26 to easy-install.pth file
Installing easy_install-script.py script to E:\Python27\Scripts
Installing easy_install.exe script to E:\Python27\Scripts
Installing easy_install-2.7-script.py script to E:\Python27\Scripts
Installing easy_install-2.7.exe script to E:\Python27\Scripts
Finished processing dependencies for Pyrseas==0.5.0
Once installed, you can execute it. For example:
python dbtoyaml.py –host=”127.0.0.1″ -U myuser mydatabase > E:\temp\mydatabase.yaml
If everything goes fine, you should have the file E:\temp\dbagn.yaml that describes your database. Now you can test easily yamltodb by deleting or adding a column in a table in YAML file and run it:
python yamltodb.py –host=”127.0.0.1″ –user=postgres –output=”deltascript.sql” mydatabase mydatabase.yaml
Then, you’ll apply the changes made in the YAML file to the database!
It’s interesting, isn’t it? I like to script out the database schema and nowadays with ORM products it’s even more important: you never now what’s happening behind the scenes…