Re: [GENERAL] Help with terminology to describe what my software does please? - Mailing list pgsql-general

Cluster comparison would only occur if you have two or more clusters on the same server, although it's possible to compare across servers,
but that would involve a lot more work. AFAIK, the only differences for a cluster would be:
1. PostgreSQL version
2. path to database
3. database users (note: it is also possible to make users database specific)
4. list of defined databases

Database comparison would involve db names, owners, encodings, tablespaces and acl's
You might also want to include sizes. You can use the following two queries to help
with that

SELECT db.datname,
       au.rolname as datdba,
       pg_encoding_to_char(db.encoding) as encoding,
       db.datallowconn,
       db.datconnlimit,
       db.datfrozenxid,
       tb.spcname as tblspc,
       db.datacl
  FROM pg_database db
  JOIN pg_authid au ON au.oid = db.datdba
  JOIN pg_tablespace tb ON tb.oid = db.dattablespace
 ORDER BY 1;

SELECT datname,
       pg_size_pretty(pg_database_size(datname))as size_pretty,
       pg_database_size(datname) as size,
       (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint) 
          FROM pg_database)  AS total,
       ((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname)) 
                                       FROM pg_database) ) * 100)::numeric(6,3) AS pct
  FROM pg_database
  ORDER BY datname;
 
 schema comparison is a lot more complication as it involves comparing
 collations
 domains
 functions
 trigger functions
 sequences
 tables
 types
 views
 
--