Re: Database schema diff

On Tue, Oct 13, 2015 at 5:48 PM, 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?
>

I've used pg_dump --split for this purpose a number of times (it requires
patching pg_dump[1]).

The idea is to produce the two database's schema dumps split into
individual files per database object, then run diff -r against the schema
folders. This worked really well for my purposes.

This will however report difference in columns order, but I'm not really
sure why would you like to ignore that.

--
Alex

[1]
http://www.postgresql.org/message-id/AANLkTikLHA2x6U=q-t0j0YS78txHFmdtyxJfsrsRcLqN@mail.gmail.com