MIME-Version: 1.0
From: Edwin UY <edwin.uy@gmail.com>
Date: Thu, 21 Aug 2025 19:03:09 +1200
Message-ID: 
 <CA+wokJ-s562t8R4bVP225EeX+2n1iFBxsb8=O=ZTW-qzCtfBWw@mail.gmail.com>
Subject: SHELL Script to Compare Database Tables / Indexes / Constraints
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c207f5063cdab051"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ-s562t8R4bVP225EeX%2B2n1iFBxsb8%3DO%3DZTW-qzCtfBWw%40mail.gmail.com>
Precedence: bulk

--000000000000c207f5063cdab051
Content-Type: text/plain; charset="UTF-8"

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

--000000000000c207f5063cdab051
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>Hoping to be &#39;lucky&=
#39; here.</div><div>Anyone aware=C2=A0of any shell script/s that can list =
all tables / indexes / constraints for 2 DBs and compare them for any diffe=
rences? I am only wanting to compare the structure, not the data.</div><div=
>Found some from browsing around, pgcmp, pgdiff, but the problem is I am no=
t allowed to install stuff on the server. The DB version, 11.7, is really o=
ld too so not sure whether these ones I found will work.</div><div>There is=
 also one that uses pgdump and pg_restore -l to do the comparison.</div><di=
v><br></div><div>I am looking at something like below:</div><div>- connect =
to db01 and db02</div><div>- \dt - select from pg_tables for list of tables=
 and output each table to a file of their own</div><div>
<div>- \di - select from pg_indexes for list of indexes and output each ind=
ex to a file of their own

</div><div>
<div>- doesn&#39;t there is a \ version for constraints but I think I can d=
o select from pg_constraints for list of constraints and output each constr=
aint to a file of their own</div><div>- compare/diff the file content of ea=
ch file</div><div>- will start with just the tables for now</div><div></div=
><br></div></div><div>Regards,</div><div>Ed</div><div><br></div></div>

--000000000000c207f5063cdab051--