Thread: Database schema diff

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 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.

Take a look a tool called apgdiff http://apgdiff.com/
Its development seems suspended, but it is still useful tool, except cases with new features etc.
Anyway, you could find bunch of forks at the github - I did support for instead of triggers, other people did another
optionsand so on 

Hi,

thanks a lot for your reply, unfortunately it's not working at all, I
run it as:

# java -jar apgdiff-2.4.jar <old-dump-from-pg_dump> <new-dump-from-pg_dump>

But it's stuck on the futex wait so unfortunately it didn't work at all.

Thanks for the reply anyway,
Michal


On 10/14/2015 01:53 PM, Иван Фролков wrote:
>> 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.
> 
> Take a look a tool called apgdiff http://apgdiff.com/
> Its development seems suspended, but it is still useful tool, except cases with new features etc.
> Anyway, you could find bunch of forks at the github - I did support for instead of triggers, other people did another
optionsand so on
 
> 
> 

I have to admit I was having the same idea few years ago however I never
got to implement it, nevertheless I should mount 2 trees for diff
comparison, isn't that correct?

I mean to mount <old-dump> as /mnt/dumps/old and <new-dump? as
/mnt/dumps/new and run diff tool from /mnt/dumps on old and new to get
the difference. This, however, requires mounting directly onto a file
system space (the main advantage why to use FUSE) which is what I would
like to avoid.

Nevertheless, if I overlook my unwillingness to mount it, and if we say,
it's fine for me, does it accept the dump file to be mounted or does it
work on the live system directly in the PgSQL database system?

Thanks,
Michal

On 10/14/2015 10:59 AM, Torello Querci wrote:
> Few years ago I developed a tool called fsgateway
> (https://github.com/mk8/fsgateway) that show metadata (table, index,
> sequences, view) as normal files using fuse.
> In this way to yout can get differences between running db instance
> using diff, meld or what do you prefear.
> 
> Unfortunally at the moment not all you need is supported, yet.
> 
> Best regards
> 
> P.S. I think that this is the wrong list for questione like this one.
> 
> On Wed, Oct 14, 2015 at 10:26 AM, Shulgin, Oleksandr
> <oleksandr.shulgin@zalando.de <mailto:oleksandr.shulgin@zalando.de>> wrote:
> 
>     On Tue, Oct 13, 2015 at 5:48 PM, Michal Novotny
>     <michal.novotny@trustport.com <mailto:michal.novotny@trustport.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
> 
> 

Hi Christopher,

thanks a lot for your suggestion however I need to run against dump
files so it's useless for me.

Thanks anyway,
Michal


On 10/13/2015 07:23 PM, Christopher Browne wrote:
> On 13 October 2015 at 11:48, Michal Novotny
> <michal.novotny@trustport.com <mailto:michal.novotny@trustport.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?"

Hello, Michal.

Take a look in MicroOLAP Database Designer for PostgreSQL.

You may use it in such way:
1. Reverse Engineering for existent database
2. Apply some changes
3. Modify database - you will get SQL script with all changes

http://microolap.com/products/database/postgresql-designer/

You wrote:

MN> Hi guys,

MN> I would like to ask you whether is there any tool to be able to compare
MN> database schemas ideally no matter what the column order is or to dump
MN> database table with ascending order of all database columns.

MN> For example, if I have table (called table) in schema A and in schema B
MN> (the time difference between is 1 week) and I would like to verify the
MN> column names/types matches but the order is different, i.e.:

MN> Schema A (2015-10-01) |  Schema B (2015-10-07)
MN>                       |
MN> id int                |  id int
MN> name varchar(64)      |  name varchar(64)
MN> text text             |  description text
MN> description text      |  text text

MN> Is there any tool to compare and (even in case above) return that both
MN> tables match? Something like pgdiff or something?

MN> This should work for all schemas, tables, functions, triggers and all
MN> the schema components?

MN> Also, is there any tool to accept 2 PgSQL dump files (source for
MN> pg_restore) and compare the schemas of both in the way above?

MN> Thanks a lot!
MN> Michal







-- 
With best wishes,Pavel                          mailto:pavel@gf.microolap.com