pg_dump - schema diff compatibility

[Tomi N/A]

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/

[Laurent ROCHE]

Hi,

I have complained before on this list that the latest pg_dump version will not have SERIAL declaration in the generated script but the equivalent SEQUENCE code (because I wanted to read the script and compare-diff it). I was replied that the aim of pg_dump is to produce a script to be able to recreate an identical database and not to generate a script readable by humans.

I don't know whether this is still the case but it was then.

Still I second your suggestion of having a pg_dump being able to generate a nice clean script readable by developpers and tools to be able to work on it (diff, automatic modifications, ...).



Cheers,
***@u
The Computing Froggy

----- Message d'origine ----
De : Tomi N/A <***@gmail.com>
À : postgres general <pgsql-***@postgresql.org>
Envoyé le : Vendredi, 12 Octobre 2007, 15h46mn 29s
Objet : [GENERAL] 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.

---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/






______________________________________________________________________________
Stockage illimité de vos mails avec Yahoo! Mail. Changez aujourd'hui de mail !

[Filip Rembiałkowski]

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 )

Post by Tomi N/A
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/

[Sualeh Fatehi]

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.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Tomi N/A]

Post by Sualeh Fatehi
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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Laurent ROCHE]

Hi,

May be a solution will be to make a diff fo the XML file produced by another tool: DdlUtils (http://db.apache.org/ddlutils/) !
However, DdlUtils won't produce a file with all PG elements (no user types, views, triggers, ...).


Have fun,
***@u
The Computing Froggy

----- Message d'origine ----
De : Sualeh Fatehi <***@gmail.com>
À : pgsql-***@postgresql.org
Envoyé le : Mardi, 16 Octobre 2007, 13h53mn 00s
Objet : Re: [GENERAL] pg_dump - schema diff compatibility

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.


---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq






______________________________________________________________________________
Stockage illimité de vos mails avec Yahoo! Mail. Changez aujourd'hui de mail !