SHELL Script to Compare Database Tables / Indexes / Constraints

From: Edwin UY <edwin(dot)uy(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: SHELL Script to Compare Database Tables / Indexes / Constraints
Date: 2025-08-21 07:03:09
Message-ID: CA+wokJ-s562t8R4bVP225EeX+2n1iFBxsb8=O=ZTW-qzCtfBWw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

Hoping to be 'lucky' here.
Anyone aware of any shell script/s that can list all tables / indexes /
constraints for 2 DBs and compare them for any differences? I am only
wanting to compare the structure, not the data.
Found some from browsing around, pgcmp, pgdiff, but the problem is I am not
allowed to install stuff on the server. The DB version, 11.7, is really old
too so not sure whether these ones I found will work.
There is also one that uses pgdump and pg_restore -l to do the comparison.

I am looking at something like below:
- connect to db01 and db02
- \dt - select from pg_tables for list of tables and output each table to a
file of their own
- \di - select from pg_indexes for list of indexes and output each index to
a file of their own
- doesn't there is a \ version for constraints but I think I can do select
from pg_constraints for list of constraints and output each constraint to a
file of their own
- compare/diff the file content of each file
- will start with just the tables for now

Regards,
Ed

Browse pgsql-admin by date

  From Date Subject
Next Message Achilleas Mantzios 2025-08-22 08:16:11 The same again with 16.9 : was Re: PostgreSQL 16.6 , query stuck with STAT Ssl, wait_event_type : IPC , wait_event : ParallelFinish
Previous Message SOzcn 2025-08-20 22:17:03 Re: PostgreSQL - HA Cluster / Etcd Issue