Thread: Comparing database schema's
How are people doing automated comparisons of schemas in different databases? E.g. we have a dev database, a beta database, and a production database. We need to insure that changes in schema are propagated properly during the development and deployment process. I've experimented some with 'pg_dump -s' to do this: Do a schema dump on the two databases and a diff. But due to myriad small differences (order of creation of tables, constraints, etc.) it is very difficult to get a usable diff without engaging in massive massage of the dump output, including dumping a table at a time. I found a reference to a schema comparison tool in the mailing list archives, but it appears to be far out of date and not currently supported. Regards, Frank
Frank Kurzawa wrote: > How are people doing automated comparisons of schemas in different databases? > > E.g. we have a dev database, a beta database, and a production database. We > need to insure that changes in schema are propagated properly during the > development and deployment process. > > I've experimented some with 'pg_dump -s' to do this: Do a schema dump on the two > databases and a diff. But due to myriad small differences (order of creation > of tables, constraints, etc.) it is very difficult to get a usable diff without > engaging in massive massage of the dump output, including dumping a table at a time. > > I found a reference to a schema comparison tool in the mailing list archives, but > it appears to be far out of date and not currently supported. Consider to use the EMS tool postgres comparer: http://www.ems-hitech.com/pgsqlutils/ Regards Gaetano Mendola
Frank Kurzawa <fkurzawa@topazsoftware.com> writes:
> How are people doing automated comparisons of schemas in different databases?
> I've experimented some with 'pg_dump -s' to do this: Do a schema dump
> on the two databases and a diff. But due to myriad small differences
> (order of creation of tables, constraints, etc.) it is very difficult
> to get a usable diff without engaging in massive massage of the dump
> output, including dumping a table at a time.
FWIW, some attention has been paid to this issue recently. I think CVS
tip pg_dump is more useful for this task than existing releases. Don't
suppose you'd want to try it out?
regards, tom lane
Tom Lane wrote: > Frank Kurzawa <fkurzawa@topazsoftware.com> writes: > >>How are people doing automated comparisons of schemas in different databases? > > >>I've experimented some with 'pg_dump -s' to do this: Do a schema dump >>on the two databases and a diff. But due to myriad small differences >>(order of creation of tables, constraints, etc.) it is very difficult >>to get a usable diff without engaging in massive massage of the dump >>output, including dumping a table at a time. > > > FWIW, some attention has been paid to this issue recently. I think CVS > tip pg_dump is more useful for this task than existing releases. Don't > suppose you'd want to try it out? Yes, indeed now pg_dump when possible dump the objects in alfabetic order so shall be easy to perform a diff between the two schemas. Regards Gaetano Mendola