Re: Comparing two PostgreSQL databases -- order of pg_dump output

On 08/30/2011 05:33 PM, Jaime Casanova wrote:
> On Tue, Aug 30, 2011 at 3:14 PM, Joe Abbate<jma@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