How to extract schema difference in pgSQL DB

There is a interesting tool available in postgresql to get the differences of  various schema of seperate databas. The name of tool is "apgdiff", can be download from this web-link.

Its a java based tool. You need to provide it 2 argument which is schema file location for which you want to get the difference.

Pre-requiste

1. Java installed on your server where apgdiff needs to be run.

2. You need to have both schema file, one would be original where changes needs to be apply, second schema file which is already having latest change.


1st file would be the original schema where you want to make changes
2nd file would be the schema file location where latest changes exist.

Step by step example :

1. Create two database on test machine. 

[pgsql@## ~]$ createdb original;
[pgsql@## ~]$ createdb dev;

2. Create a test table at both above defined databases. 

[pgsql@## ~]$ psql -Upgsql original -c "create table public.test(id integer)"
CREATE TABLE
[pgsql@## ~]$ psql -Upgsql dev -c "create table public.test(id integer)"
CREATE TABLE

3. Modify the schema at dev databases. 
[pgsql@## ~]$ psql -Upgsql dev -c "ALTER TABLE public.test ADD COLUMN name text;"
ALTER TABLE

4. Dump the schema of both database for comparision. 
[pgsql@##]$ pg_dump -Upgsql -s original -f original_schema.dump
[pgsql@##]$ pg_dump -Upgsql -s dev -f dev_schema.dump

5. Run the apgdiff tool to get the DIFF sql in below format. 
[pgsql@##]$ cd apgdiff-2.3/

[pgsql@i2adb20 apgdiff-2.3]$ java -jar apgdiff-2.3.jar original_schema.dump dev_schema.dump

ALTER TABLE test
        ADD COLUMN name text;

[pgsql@##]$


Catch : This tool only extract the schema difference not the permission one.