Re: Help with terminology to describe what my software does please?

On Sun, May 28, 2017 at 9:51 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/28/2017 05:49 AM, Neil Anderson wrote:
>
>> Hi,
>>
>> I'm working on a tool that can compare the properties of Postgres
>> objects from different instances, finding the differences and
>> outputting the update SQL.
>>
>> It can compare objects that are defined at the cluster, database or
>> schema level. As such I'm finding it difficult to describe what the
>> tool does simply and accurately. I've tried 'compares PostgreSQL
>> schemas' but that doesn't capture the database and cluster parts,
>> 'compares PostgreSQL schema and database objects'. That sort of thing.
>> Right now I have a mix of terms on my website and I would prefer to
>> tighten it up.
>>
>> I guess I don't know what is the most common way to say that it
>> compares everything but the data. Any suggestions from your
>> experience?
>>
>
> From above the first sentence of the second paragraph seems to me the best
> description of what you are doing.
>
>
>> Thanks,
>> Neil
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.