Database schema diff

On 13 October 2015 at 11:48, Michal Novotny <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&gt;

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?"

On Tue, Oct 13, 2015 at 5:48 PM, Michal Novotny <
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]/messages/by-id/AANLkTikLHA2x6U=q-t0j0YS78txHFmdtyxJfsrsRcLqN@mail.gmail.com).

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]: /messages/by-id/AANLkTikLHA2x6U=q-t0j0YS78txHFmdtyxJfsrsRcLqN@mail.gmail.com
/messages/by-id/AANLkTikLHA2x6U=q-t0j0YS78txHFmdtyxJfsrsRcLqN@mail.gmail.com

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> 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 options and so on

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 options and so on

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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] /messages/by-id/AANLkTikLHA2x6U=q-t0j0YS78txHFmdtyxJfsrsRcLqN@mail.gmail.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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&gt;

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?"

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers