In a comment to one of my early posts about version control, Peter Eisentraut stated he was “somewhat discouraged because [seeing how fast PostgreSQL develops and adds new features,] I don’t see how a tool like apgdiff can keep up and stay useful unless a lot more resources are put into it.” At the time, the first release of Pyrseas (0.1.0) was well over a month away. Currently, Pyrseas 0.3.0 is nearing release . This seems like a good time to summarize where Pyrseas stands vis-à-vis the list of PostgreSQL data definition features:
| DDL Feature | 0.1 | 0.2 | 0.3 |
|---|---|---|---|
| AGGREGATE | X | ||
| CAST | |||
| CONSTRAINT TRIGGER | |||
| CONVERSION | |||
| DOMAIN | X | ||
| FOREIGN DATA WRAPPER | |||
| FUNCTION | X | X | |
| GROUP | |||
| INDEX | X | X | X |
| LANGUAGE | X | X | |
| OPERATOR | |||
| OPERATOR CLASS | |||
| OPERATOR FAMILY | |||
| ROLE | |||
| RULE | X | ||
| SCHEMA | X | X | X |
| SEQUENCE | X | X | X |
| SERVER | |||
| TABLE | X | X | X |
| - CHECK CONSTRAINT | X | X | X |
| - PRIMARY KEY | X | X | X |
| - FOREIGN KEY | X | X | X |
| - UNIQUE CONSTRAINT | X | X | X |
| - INHERIT | X | X | |
| TABLESPACE | |||
| TEXT SEARCH CONFIGURATION | |||
| TEXT SEARCH DICTIONARY | |||
| TEXT SEARCH PARSER | |||
| TEXT SEARCH TEMPLATE | |||
| TRIGGER | X | ||
| TYPE | X | ||
| USER | |||
| USER MAPPING | |||
| VIEW | X | X | |
| COMMENT | X | X |
Note that PosgreSQL 9.1 will add EXTENSION to the list above. Note also that although the links in the matrix go to the corresponding CREATE object documentation page, Pyrseas supports CREATE, CREATE OR REPLACE, DROP and ALTER, as applicable. A final note: an “X” in a particular release doesn’t imply that the feature is supported in all its syntactic glory.
Caveats aside, progress has been made. Pyrseas 0.1.0 supported most of the basic relational database objects and concepts present in the SQL Standard, and usually covered by generic version control tools. Pyrseas 0.3.0 will support the features showcased in the Pagila sample database, which I hope is somewhat representative of the average PostgreSQL database (in terms of DDL features, of course).
The goal for the 0.4.0 release will be to cover the remaining features excluding Text Search, SQL/MED and ROLE/GROUP, which will be tackled in 0.5.0 or later. Is there a feature you or your organization is interested in? Let me know in the comments.
How about something for GRANT and REVOKE? You’ll need to be able to separate these from DDL, but they’re handy for deployments.
I believe the Pyrseas utilities (dbtoyaml and yamltodb) are most useful in development, where two or more DBAs/programmers make changes to their databases in a more or less “distributed” manner which they share with others just like they do the application code via a DVCS (or even a centralized VCS for that matter). In that environment, database object ownership and permissions are (probably) not too relevant (I’m assuming each DBA/programmer has his/her own database with objects owner by him or her). That is why I’m leaving GROUP/ROLE/USER for last.
I see your point about deployment and I’ll consider GRANT and REVOKE when working on the latter group.
Pingback: Joe Abbate: PostgreSQL Version Control Feature... | PostgreSQL | Syngu
Pingback: Version Control for PostGIS | Taming Serpents and Pachyderms
PgPirdie is doing something similar but is also far away from a really usable tool. At the moment (for my open source project) I use PgComparer. At the moment this is the best tool I have found but is not open source
It is realy painful while developing and delpoing doing all these tasks, so a tool like this would be for sure of interest.
I guess you mean PgBirdie, which is available at Google Code. That tool, written in C#, only runs on Windows (as does EMS DB Comparer for PostgreSQL, which I assume is what you mean by PgComparer–although there is also a pgCompare) whereas Pyrseas, written in Python, runs just about everywhere. Also, Pyrseas 0.4 has just been released and includes support for even more PostgreSQL features (see http://pyrseas.wordpress.com/2011/09/19/version-control-for-postgis/) than those listed above.