Monday, 3 October 2011

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.


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)"
[pgsql@## ~]$ psql -Upgsql dev -c "create table public.test(id integer)"

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

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

        ADD COLUMN name text;


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

No comments:

Post a Comment