Command line tool to compare two PostgreSQL databases
Project description
Postgres DB diff
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:
Use any python package installing tool. Recommended pip.
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:
CREATE TABLE table_a (
id INTEGER PRIMARY KEY,
test_unique VARCHAR (100) UNIQUE,
test_not_null VARCHAR (100) NOT NULL,
test_checks INTEGER NOT NULL
);
CREATE TABLE table_b (
id INTEGER PRIMARY KEY,
table_a_id integer REFERENCES table_a (id)
);
CREATE TABLE table_c (
id INTEGER PRIMARY KEY
);
CREATE VIEW view_a AS SELECT
id, test_unique, 42 AS some_number
FROM table_a;
Other using this SQL:
CREATE TABLE table_a (
id INTEGER PRIMARY KEY,
test_unique VARCHAR (100),
test_not_null VARCHAR (100),
test_checks INTEGER NOT NULL CHECK (test_checks > 0)
);
CREATE TABLE table_b (
id INTEGER PRIMARY KEY,
table_a_no integer REFERENCES table_a (id)
);
CREATE VIEW view_a AS SELECT
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) | | |
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
postgres-db-diff-0.9.1.tar.gz
(5.8 kB
view hashes)
Built Distribution
Close
Hashes for postgres_db_diff-0.9.1-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 0ac4f248e2059e2d25add34a754e787ab06fb687b79c2b6c12b8fe44f3ba728f |
|
MD5 | d5c49768063ae5e4bd609bc05ff68c49 |
|
BLAKE2b-256 | 23ea91e8d7b2bee6fa8be8f2386c43c93c30c615d02d0b07db045f55673608f6 |