Re: Database schema diff

On 13 October 2015 at 11:48, Michal Novotny <michal(dot)novotny(at)trustport(dot)com>
wrote:

> Hi guys,
>
> I would like to ask you whether is there any tool to be able to compare
> database schemas ideally no matter what the column order is or to dump
> database table with ascending order of all database columns.
>
> For example, if I have table (called table) in schema A and in schema B
> (the time difference between is 1 week) and I would like to verify the
> column names/types matches but the order is different, i.e.:
>
> Schema A (2015-10-01) | Schema B (2015-10-07)
> |
> id int | id int
> name varchar(64) | name varchar(64)
> text text | description text
> description text | text text
>
> Is there any tool to compare and (even in case above) return that both
> tables match? Something like pgdiff or something?
>
> This should work for all schemas, tables, functions, triggers and all
> the schema components?
>
> Also, is there any tool to accept 2 PgSQL dump files (source for
> pg_restore) and compare the schemas of both in the way above?
>
> Thanks a lot!
> Michal

I built a tool I call "pgcmp", which is out on GitHub <
https://github.com/cbbrowne/pgcmp>

The one thing that you mention that it *doesn't* consider is the ordering
of columns.

It would not be difficult at all to add that comparison; as simple as adding
an extra capture of table columns and column #'s. I'd be happy to consider
adding that in.

Note that pgcmp expects the database to be captured as databases; it pulls
data
from information_schema and such. In order to run it against a pair of
dumps,
you'd need to load those dumps into databases, first.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"