Postgres DB diff

PyPi

https://pypi.python.org/pypi/postgres-db-diff/

About

Command line tool to compare two PostgreSQL databases. It is based on parsing psql meta commands output. Such as \dt for tables and \dv for views.

https://www.postgresql.org/docs/current/static/app-psql.html

How to install

There are two options:

1. Use any python package installing tool. Recommended pip.
2. Just copy/paste postgresdbdiff.py into your dir and run it using python postgresdbdiff.py

Usage

usage: postgresdbdiff.py [-h] --db1 DB1 --db2 DB2 [--diff-folder DIFF_FOLDER]
optional arguments:
-h, --help show this help message and exit
--db1 DB1 First DB name
--db2 DB2 Second DB name
--diff-folder DIFF_FOLDER
Directory to output diffs

Example

Create two DBs. One using this SQL:

CREATETABLEtable_a (
id INTEGERPRIMARY KEY,
test_unique VARCHAR (100) UNIQUE,
test_not_null VARCHAR (100) NOT NULL,
test_checks INTEGERNOT NULL
);
CREATETABLEtable_b (
id INTEGERPRIMARY KEY,
table_a_id integerREFERENCES table_a (id)
);
CREATETABLEtable_c (
id INTEGERPRIMARY KEY
);
CREATEVIEWview_aASSELECT
id, test_unique, 42AS some_number
FROM table_a;

Other using this SQL:

CREATETABLEtable_a (
id INTEGERPRIMARY KEY,
test_unique VARCHAR (100),
test_not_null VARCHAR (100),
test_checks INTEGERNOT NULLCHECK (test_checks >0)
);
CREATETABLEtable_b (
id INTEGERPRIMARY KEY,
table_a_no integerREFERENCES table_a (id)
);
CREATEVIEWview_aASSELECT
id, test_unique
FROM table_a;

Then run this command

python postgresdbdiff.py --db1 diff_a --db2 diff_b --diff-folder diffs

Output should be like this

TABLES: additional in "diff_a"
table_c
TABLES: not matching
table_a
table_b
VIEWS: not matching
view_a

And there should be the folder named diffs with files looking like this

# diffs/table_a.diff--- TABLES.diff_a.table_a+++ TABLES.diff_b.table_a@@ -1,12 +1,13 @@
Table "public.table_a"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
id | integer | | not null |
test_checks | integer | | not null |
- test_not_null | character varying(100) | | not null |+ test_not_null | character varying(100) | | |
test_unique | character varying(100) | | |
Indexes:
"table_a_pkey" PRIMARY KEY, btree (id)
- "table_a_test_unique_key" UNIQUE CONSTRAINT, btree (test_unique)+Check constraints:+ "table_a_test_checks_check" CHECK (test_checks > 0)
Referenced by:
- TABLE "table_b" CONSTRAINT "table_b_table_a_id_fkey" FOREIGN KEY (table_a_id) REFERENCES table_a(id)+ TABLE "table_b" CONSTRAINT "table_b_table_a_no_fkey" FOREIGN KEY (table_a_no) REFERENCES table_a(id)# diffs/table_b.diff--- TABLES.diff_a.table_b+++ TABLES.diff_b.table_b@@ -1,9 +1,9 @@
Table "public.table_b"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
id | integer | | not null |
- table_a_id | integer | | |+ table_a_no | integer | | |
Indexes:
"table_b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
- "table_b_table_a_id_fkey" FOREIGN KEY (table_a_id) REFERENCES table_a(id)+ "table_b_table_a_no_fkey" FOREIGN KEY (table_a_no) REFERENCES table_a(id)# diffs/view_a.diff--- VIEWS.diff_a.view_a+++ VIEWS.diff_b.view_a@@ -1,6 +1,5 @@
View "public.view_a"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
id | integer | | |
- some_number | integer | | |
test_unique | character varying(100) | | |