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;
$_$;
---------------------------------------
