Wednesday, March 2, 2011

Postgresql Database Schema Comparison

0 comments

I really like the "Another Postgresql Diff Tool (apgdiff)" in order to find out the differences between two databases or schemas in Postgresql. It is a java based application.
I downloaded it from http://apgdiff.com/download.php

Databases' Structure

I have two databases testdb and testdb1 as follows.

postgres=# \c testdb
You are now connected to database "testdb".
testdb=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | emp            | table    | postgres
 public | pg_buffercache | view     | postgres
 public | t              | table    | postgres
 public | t_s_seq        | sequence | postgres
(4 rows)
testdb=# \d emp
              Table "public.emp"
 Column  |          Type          | Modifiers
---------+------------------------+-----------
 empno   | integer                | not null
 empname | character varying(100) |
 city    | character varying(100) |
Indexes:
    "empno_pk" PRIMARY KEY, btree (empno)

\c testdb1
testdb1=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)
testdb1=# \d emp
      Table "public.emp"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 name   | name    | 

I used apgdiff to fund out the difference between the two databases.
At first, I had to take sql dumps using pg_dump.

pg_dump testdb > /tmp/testdb.sql
pg_dump testdb1 > /tmp/testdb1.sql

Make testdb as testdb1

java -jar apgdiff-2.3.jar  /tmp/testdb.sql /tmp/testdb1.sql > /tmp/make_testdb_as_testdb1.sql
less /tmp/make_testdb_as_testdb1.sql
-----------------------------------
DROP FUNCTION pg_buffercache_pages();
DROP FUNCTION reffunc(refcursor);
DROP VIEW pg_buffercache;
DROP TABLE t;
DROP SEQUENCE t_s_seq;
ALTER TABLE emp
        DROP COLUMN empno,
        DROP COLUMN empname,
        DROP COLUMN city,
        ADD COLUMN id integer,
        ADD COLUMN name name;
-----------------------------------


Make testdb1 as testdb

java -jar apgdiff-2.3.jar  /tmp/testdb1.sql /tmp/testdb.sql > /tmp/make_testdb1_as_testdb.sql
less /tmp/make_testdb1_as_testdb.sql
---------------------------------------
CREATE SEQUENCE t_s_seq
        START WITH 1
        INCREMENT BY 1
        NO MAXVALUE
        NO MINVALUE
        CACHE 1;
CREATE TABLE t (
        s integer DEFAULT nextval('t_s_seq'::regclass) NOT NULL,
        i integer
);
ALTER TABLE emp
        DROP COLUMN id,
        DROP COLUMN name,
        ADD COLUMN empno integer NOT NULL,
        ADD COLUMN empname character varying(100),
        ADD COLUMN city character varying(100);
CREATE OR REPLACE FUNCTION pg_buffercache_pages() RETURNS SETOF record
    LANGUAGE c
    AS '$libdir/pg_buffercache', 'pg_buffercache_pages';

CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor
    LANGUAGE plpgsql
    AS $_$
BEGIN
    OPEN $1 FOR
  
    SELECT  *
    FROM emp
;
    RETURN $1;
END;
$_$;
---------------------------------------

Leave a Reply

Labels