Help with terminology to describe what my software does please?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with terminology to describe what my software does please?

Neil Anderson-2
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?

Thanks,
Neil


--
Neil Anderson
[hidden email]
https://www.postgrescompare.com



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

Melvin Davidson-5


On Sun, May 28, 2017 at 9:51 AM, Adrian Klaver <[hidden email]> 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
[hidden email]



--
Sent via pgsql-general mailing list ([hidden email])
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.

Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Neil Anderson-2
Neil Anderson <[hidden email]> writes:
> 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?

FWIW, I think it's pretty common to use "schema" in an abstract way
to mean "the structure of your database", ie everything but the data.
(It's unfortunate that the SQL standard commandeered the word to
mean a database namespace; but it's not like there are no other words
with more than one meaning.)

So I don't see any big problem with calling your tool a schema comparator.
You could maybe make your docs a bit clearer if you consistently refer
to the namespace objects as "SQL schemas", reserving the generic term
for the generic meaning.

                        regards, tom lane


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by Melvin Davidson-5
On 05/28/2017 07:53 AM, Melvin Davidson wrote:
>
>

>

>
> Cluster comparison would only occur if you have two or more clusters on
> the same server, although it's possible to compare across servers,

Explain, because as I understand it a server = one cluster:

https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html

"The init or initdb mode creates a new PostgreSQL database cluster. A
database cluster is a collection of databases that are managed by a
single server instance. This mode invokes the initdb command. See initdb
for details."

> 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

And anything different below the above, I am thinking checking a dev
cluster against a production cluster.



--
Adrian Klaver
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

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

Neil Anderson-2
In reply to this post by Melvin Davidson-5

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.




--
Reply | Threaded
Open this post in threaded view
|

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

Neil Anderson-2
In reply to this post by Adrian Klaver-4
>> Cluster comparison would only occur if you have two or more clusters on
>> the same server, although it's possible to compare across servers,
>
>
> Explain, because as I understand it a server = one cluster:
>

I think he was using server in the server=one machine sense, ie a
single machine/server can have multiple clusters/database servers.

> https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html
>
> "The init or initdb mode creates a new PostgreSQL database cluster. A
> database cluster is a collection of databases that are managed by a single
> server instance. This mode invokes the initdb command. See initdb for
> details."
>
>> 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
>
>
> And anything different below the above, I am thinking checking a dev cluster
> against a production cluster.
>
>
>
> --
> Adrian Klaver
> [hidden email]

--
Neil Anderson
[hidden email]
https://www.postgrescompare.com



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general