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
I was considering configuration settings to be at the cluster level too. Stuff from pg_settings or pg_config. Also I think tablespaces are at that level too. What do you think?
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;
That's a great idea! Thanks for the info.
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
