| 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
| 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 |