Thread: pg_dump - schema diff compatibility
Looking at the mailing list archive, this is just one in a rather long line of questions regarding diffing db schema dumps, but I've been unable to find what I was looking for in any of the prior conversations. I know of apgdiff (seems to work very nicely) and of other specialized pg diff tools (as outdated or proprietary as they may be), but what I'm interested in is just a plain, basic schema dump with a database object order usable with diff. I can't find it now, but I'm fairly certain I've read somewhere (in the release changes of an 8.x pgsql version?) that pg_dump has been "upgraded" so that it orders database objects fist by their dependencies and then by name. I thought that would imply that dumping the database like so pg_dump -f out.sql -F p -s a_db would give me an sql script which I could compare versions of with plain old diff or svn diff or whatever existing diff tool I care to use. I guess my question is: is pg_dump supposed to dump the schema in a diff-compatible, predictable way but it's not working or is pg_dump only concerned with satisfying db object dependencies? I would very much like this functionality because it would make pgsql much better integrated into the work environment we have setup at the office (using e.g. svn diff would be very nice). Tools like apgdiff don't help as much: it great that it's command line (can be automated), it does it job well, but it sitll only tells me e.g. that a view is different, rather than showing me _how_ it is different or allowing me to compare object definitions using a generic diff - which is what I really want. Sorry for the confusing trail of thought and thanks for any comments, t.n.a.
Just an idea, but I would try to: - pg_dump both schemas in 'custom' format - extract TOC from each one - use the lists as a basis for compare (i guess that's what pgdiff does, maybe it just needs minor patch to extend its functionality ) 2007/10/12, Tomi N/A <hefest@gmail.com>: > Looking at the mailing list archive, this is just one in a rather long > line of questions regarding diffing db schema dumps, but I've been > unable to find what I was looking for in any of the prior > conversations. I know of apgdiff (seems to work very nicely) and of > other specialized pg diff tools (as outdated or proprietary as they > may be), but what I'm interested in is just a plain, basic schema dump > with a database object order usable with diff. > I can't find it now, but I'm fairly certain I've read somewhere (in > the release changes of an 8.x pgsql version?) that pg_dump has been > "upgraded" so that it orders database objects fist by their > dependencies and then by name. I thought that would imply that dumping > the database like so > pg_dump -f out.sql -F p -s a_db > would give me an sql script which I could compare versions of with > plain old diff or svn diff or whatever existing diff tool I care to > use. > > I guess my question is: is pg_dump supposed to dump the schema in a > diff-compatible, predictable way but it's not working or is pg_dump > only concerned with satisfying db object dependencies? > I would very much like this functionality because it would make pgsql > much better integrated into the work environment we have setup at the > office (using e.g. svn diff would be very nice). Tools like apgdiff > don't help as much: it great that it's command line (can be > automated), it does it job well, but it sitll only tells me e.g. that > a view is different, rather than showing me _how_ it is different or > allowing me to compare object definitions using a generic diff - which > is what I really want. > > Sorry for the confusing trail of thought and thanks for any comments, > t.n.a. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- Filip Rembiałkowski
SchemaCrawler for PostgreSQL will allow you to do the diffs. With SchemaCrawler for PostgreSQL, you can take human-readable snapshots of the schema and data, for later comparison. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff- able plain-text format (text, CSV, or XHTML). You can order data alphabetically, or by ordinal order. SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain-text formats. SchemaCrawler for PostgreSQL is free and open-source (under the GPL), and is available at SourceForge: http://schemacrawler.sourceforge.net/ Sualeh Fatehi.
2007/10/16, Sualeh Fatehi <sualeh.fatehi@gmail.com>: > SchemaCrawler for PostgreSQL will allow you to do the diffs. With > SchemaCrawler for PostgreSQL, you can take human-readable snapshots > of the schema and data, for later comparison. SchemaCrawler outputs > details of your schema (tables, views, procedures, and more) in a diff- > able plain-text format (text, CSV, or XHTML). You can order data > alphabetically, or by ordinal order. SchemaCrawler can also output > data (including CLOBs and BLOBs) in the same plain-text formats. > > SchemaCrawler for PostgreSQL is free and open-source (under the GPL), > and is available at SourceForge: http://schemacrawler.sourceforge.net/ Sounds promising. Thanks for the tip, I'll be sure to try it out. Cheers, t.n.a.