Re: [HACKERS] Database schema diff

[Pavel Golub]

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:pa...@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

Re: [HACKERS] Database schema diff

[Shulgin, Oleksandr]

On Tue, Oct 13, 2015 at 5:48 PM, Michal Novotny <
michal.novo...@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-t0j0ys78txhfmdtyxjfsrsrc...@mail.gmail.com

Re: [HACKERS] Database schema diff

[Torello Querci]

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.shul...@zalando.de> wrote:

> On Tue, Oct 13, 2015 at 5:48 PM, Michal Novotny <
> michal.novo...@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-t0j0ys78txhfmdtyxjfsrsrc...@mail.gmail.com
>
>

Re: [HACKERS] Database schema diff

[Michal Novotny]

Hi,

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

# java -jar apgdiff-2.4.jar  

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

Re: [HACKERS] Database schema diff

[Michal Novotny]

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  as /mnt/dumps/old and  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
> > wrote:
> 
> On Tue, Oct 13, 2015 at 5:48 PM, Michal Novotny
> >
> 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-t0j0ys78txhfmdtyxjfsrsrc...@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

Re: [HACKERS] Database schema diff

[Michal Novotny]

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

[HACKERS] Database schema diff

[Michal Novotny]

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


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

Re: [HACKERS] Database schema diff

[Christopher Browne]

On 13 October 2015 at 11:48, Michal Novotny 
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?"