Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

[rsindlin]

Hi Joe,

I have run into what seems to be a similar issue with pg_dump --schema-only
in its trigger ordering.  Did you ever find a satisfactory solution to this? 
I posted my specific problem on  DBA.StackExchange

 
, and based on some research I did, it seems like it could be an issue
related to the Collate setting of the DB.  I was wondering if you had come
across anything supporting or refuting that.

Thanks,
-Randall



--
View this message in context: 
http://postgresql.nabble.com/Comparing-two-PostgreSQL-databases-order-of-pg-dump-output-tp4751332p5877720.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Comparing two PostgreSQL databases -- order of pg_dump output

[Peter Eisentraut]

On tis, 2011-08-30 at 19:11 -0400, Stephen Frost wrote:
 * Joe Abbate (j...@freedomcircle.com) wrote:
  In order to compare the schema of two presumably identical
  databases, I've been diffing the output of pg_dump -Osx.  
 
 I'm not sure exactly how it does it, but check_postgres.pl offers this.
 
 http://bucardo.org/wiki/Check_postgres

That tool is also not without bugs in this regard.

Also, the interface it works with necessarily doesn't offer a good way
to examine the differences in detail; it only shows you that there are
differences.



-- 
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Peter Eisentraut]

On tis, 2011-08-30 at 18:07 -0400, Tom Lane wrote:
 Joe Abbate j...@freedomcircle.com writes:
  In order to compare the schema of two presumably identical databases, 
  I've been diffing the output of pg_dump -Osx.  However, I've found that 
  the order of the output is not very reliable.
 
 Yeah, we've been around on that before.  pg_dump does actually sort the
 output items (modulo dependency requirements), but it sorts by the same
 tag values that are printed by pg_restore -l, and those aren't currently
 designed to be unique.  It's not too clear if we could get away with
 changing the definitions of the tag strings.

It's a bit strange that the tag for a trigger is name but the tag for
the trigger's comment is name ON table.  Not having the table name in
the trigger tag sounds wrong, because it makes the tag not very useful
for selecting the trigger from the TOC.



-- 
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Tom Lane]

Peter Eisentraut pete...@gmx.net writes:
 On tis, 2011-08-30 at 18:07 -0400, Tom Lane wrote:
 Yeah, we've been around on that before.  pg_dump does actually sort the
 output items (modulo dependency requirements), but it sorts by the same
 tag values that are printed by pg_restore -l, and those aren't currently
 designed to be unique.  It's not too clear if we could get away with
 changing the definitions of the tag strings.

 It's a bit strange that the tag for a trigger is name but the tag for
 the trigger's comment is name ON table.  Not having the table name in
 the trigger tag sounds wrong, because it makes the tag not very useful
 for selecting the trigger from the TOC.

I don't think changing that would be a problem.  What gets unpleasant is
trying to guarantee that pg_dump object tags are unconditionally unique.
That would, for example, mean that every argument type of every function
would have to be written out fully-schema-qualified.

Short of that sort of anal-retentiveness, there are going to be cases
where the dump order is a bit unpredictable.  IMO what we need is a
reasonable compromise between verbosity and uniqueness, such that in
normal cases (ie, where you *didn't* intentionally create near-identical
functions in different schemas) you get a unique ordering.  To get to
that, somebody's got to go through all the tag writing code and identify
where the trouble spots are.  So far we've heard triggers and operators
nominated ... what else?

regards, tom lane

-- 
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Joe Abbate]

On 08/31/2011 10:17 AM, Tom Lane wrote:

Short of that sort of anal-retentiveness, there are going to be cases
where the dump order is a bit unpredictable.  IMO what we need is a
reasonable compromise between verbosity and uniqueness, such that in
normal cases (ie, where you *didn't* intentionally create near-identical
functions in different schemas) you get a unique ordering.  To get to
that, somebody's got to go through all the tag writing code and identify
where the trouble spots are.  So far we've heard triggers and operators
nominated ... what else?


So far, for Pyrseas, I've tested aggregates, casts, constraint triggers, 
conversions, domains, functions, indexes, languages, operators, rules, 
schemas, sequences, tables (including check constraints, primary keys, 
foreign keys, unique constraints and inherited tables), triggers, types 
(base and composite), views and comments on the various objects.  I'll 
be testing operator classes and operator families in the coming weeks. 
So far, triggers and operators are the only ones that have caused an 
issue when using the technique suggested by Jaime (pg_dump -Fc followed 
by pg_restore -l).  Functions also caused problems in the plain text 
pg_dump, e.g., because funcx(geography) sorts after funcx(geometry) if 
the latter is created first.


Joe

--
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Jaime Casanova]

On Tue, Aug 30, 2011 at 2:07 PM, Joe Abbate j...@freedomcircle.com wrote:
 Hi,

 In order to compare the schema of two presumably identical databases, I've
 been diffing the output of pg_dump -Osx.  However, I've found that the order
 of the output is not very reliable.

what about using pg_dump -Fc -Osx and use pg_restore -l to list
objects. then you can sort and compare objects and then a script that
compare schema of objects extracting them with -P, -T or -t

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

-- 
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Joe Abbate]

Hola Jaime,

On 08/30/2011 03:24 PM, Jaime Casanova wrote:

what about using pg_dump -Fc -Osx and use pg_restore -l to list
objects. then you can sort and compare objects and then a script that
compare schema of objects extracting them with -P, -T or -t


That appears to be of limited use (i.e., it would only work for 
functions, triggers and tables).  pg_restore -L/--use_list is more 
comprehensive.	So the script would have to do something like the following:


$ pg_dump -Fc -Osx postgis  postgis.dump
$ pg_restore -l postgis.dump | sort -k4   postgis.list
$ pg_restore -L postgis.list postgis.dump  postgis.sorted

Rinse and repeat on the second database and then diff the .sorted files. 
 Tried it and although it doesn't completely do the trick it's much 
better than diffing the plain text pg_dump outputs (3000+ diff lines vs. 
less than 200 and about half of that are actual differences).


Thanks,

Joe

--
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Jaime Casanova]

On Tue, Aug 30, 2011 at 3:14 PM, Joe Abbate j...@freedomcircle.com wrote:
 Hola Jaime,

 On 08/30/2011 03:24 PM, Jaime Casanova wrote:

 what about using pg_dump -Fc -Osx and use pg_restore -l to list
 objects. then you can sort and compare objects and then a script that
 compare schema of objects extracting them with -P, -T or -t

 That appears to be of limited use (i.e., it would only work for functions,
 triggers and tables).  pg_restore -L/--use_list is more comprehensive.
 So the script would have to do something like the following:

 $ pg_dump -Fc -Osx postgis  postgis.dump
 $ pg_restore -l postgis.dump | sort -k4   postgis.list

why not sort -k4,5?

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

-- 
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Joe Abbate]

On 08/30/2011 05:33 PM, Jaime Casanova wrote:

On Tue, Aug 30, 2011 at 3:14 PM, Joe Abbatej...@freedomcircle.com  wrote:

Hola Jaime,

On 08/30/2011 03:24 PM, Jaime Casanova wrote:


what about using pg_dump -Fc -Osx and use pg_restore -l to list
objects. then you can sort and compare objects and then a script that
compare schema of objects extracting them with -P, -T or -t


That appears to be of limited use (i.e., it would only work for functions,
triggers and tables).  pg_restore -L/--use_list is more comprehensive.
So the script would have to do something like the following:

$ pg_dump -Fc -Osx postgis  postgis.dump
$ pg_restore -l postgis.dump | sort -k4  postgis.list


why not sort -k4,5?


sort -k4 sorts from the fourth field, the object type, to the end of 
line.  -k4,5 would sort on the type and schema name.  I want to sort on 
object name/attributes as well.  BTW, I figured out why it doesn't fully 
work.  For functions, the arguments are listed, e.g.,


82; 1255 700618 FUNCTION public _st_covers(geography, geography) jma
459; 1255 700259 FUNCTION public _st_covers(geometry, geometry) jma

Unfortunately, for operators, the operand types are not included:

843; 2617 699799 OPERATOR public  jma
1861; 2617 700565 OPERATOR public  jma

so the pg_restore -L still keeps the original dump order (geometry 
before geography).


Joe

--
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Tom Lane]

Joe Abbate j...@freedomcircle.com writes:
 In order to compare the schema of two presumably identical databases, 
 I've been diffing the output of pg_dump -Osx.  However, I've found that 
 the order of the output is not very reliable.

Yeah, we've been around on that before.  pg_dump does actually sort the
output items (modulo dependency requirements), but it sorts by the same
tag values that are printed by pg_restore -l, and those aren't currently
designed to be unique.  It's not too clear if we could get away with
changing the definitions of the tag strings.

regards, tom lane

-- 
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Joe Abbate]

On 08/30/2011 06:07 PM, Tom Lane wrote:

Yeah, we've been around on that before.  pg_dump does actually sort the
output items (modulo dependency requirements), but it sorts by the same
tag values that are printed by pg_restore -l, and those aren't currently
designed to be unique.  It's not too clear if we could get away with
changing the definitions of the tag strings.


The approach suggested by Jaime works fairly well.  The only change I 
would make is to add OPERATOR args to the pg_restore -l output, e.g.,


1843; 2617 699799 OPERATOR public (geometry, geometry) jma
1861; 2617 700565 OPERATOR public (geography, geography) jma

Joe

--
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Stephen Frost]

* Joe Abbate (j...@freedomcircle.com) wrote:
 In order to compare the schema of two presumably identical
 databases, I've been diffing the output of pg_dump -Osx.  

I'm not sure exactly how it does it, but check_postgres.pl offers this.

http://bucardo.org/wiki/Check_postgres

It also offers a whole slew of other useful things to monitor.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

[Joe Abbate]

Hi Stephen,

On 08/30/2011 07:11 PM, Stephen Frost wrote:

* Joe Abbate (j...@freedomcircle.com) wrote:

In order to compare the schema of two presumably identical
databases, I've been diffing the output of pg_dump -Osx.


I'm not sure exactly how it does it, but check_postgres.pl offers this.

http://bucardo.org/wiki/Check_postgres

It also offers a whole slew of other useful things to monitor.


Note that what I'm looking for is something to compare just about 
EVERYTHING DDL under the PostgreSQL sun: tables, types, functions, 
operators, etc. The description of same_schema appears to imply only a 
subset of objects are compared (in fact, looking at the code, I can 
confirm that limitation).


BTW, I tried installing check_postgres, but not being much into Perl and 
not knowing what dependencies it has, make test failed 38/42 tests.


Joe

--
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] Comparing two PostgreSQL databases -- order of pg_dump output

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


 Note that what I'm looking for is something to compare just about 
 EVERYTHING DDL under the PostgreSQL sun: tables, types, functions, 
 operators, etc. The description of same_schema appears to imply only a 
 subset of objects are compared (in fact, looking at the code, I can 
 confirm that limitation).

You should try the latest version in git (which will soon be released 
as 2.18.0). The same_schema check has been overhauled, and now can also 
store a copy of a databases state to allow checking the same database 
over time to see what has changed. It doesn't check *everything* yet, 
but the only things missing are some of the more obscure items such 
as custom conversions. It should be pretty easy to add in anything 
that is not already covered, even for someone not versed in Perl.

 BTW, I tried installing check_postgres, but not being much into Perl and 
 not knowing what dependencies it has, make test failed 38/42 tests.

That's not much to worry about. It's a pretty straightforward script, 
in that it is very easy to determine if it is working for you or not, 
even if some of the tests fail. :)

 I'm not exactly sure how it does it

check_postgres queries the system catalogs, normalizes some things based 
on the version, and creates a Perl object representation of the database. 
It then compares that to the same thing from a different database/server, 
or to a frozen version of an earlier scan.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201108302203
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk5dl28ACgkQvJuQZxSWSsidhwCeMGEx8eVeaPlyRALuh8VuQ+rN
ynYAoLDGLOFNVbj3+NnRvZpLfgmh6Mgu
=w1eI
-END PGP SIGNATURE-


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