Обсуждение: HELP!!!-----Need to Sql commands to monitoring Postgresql

Hi all,
         i have 5 servers that have been installing postgresql .In order to
know the postgresql working  status and monitor them ,moreover i don't want
to  use the monitor tools .I  want to use the SQL commands to monitoring
postgresql system . please suggest any SQL COMMANDS to work successfully.if
you have some good suggestion ,you can email to me
(charles.xie@sanmina-sci.com) or sky :xqwbx163



best regards

charles_xie




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/HELP-Need-to-Sql-commands-to-monitoring-Postgresql-tp5722548.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

On Tue, Sep 4, 2012 at 12:12 AM, charles_xie <xqwyy163@163.com> wrote:
> Hi all,
>          i have 5 servers that have been installing postgresql .In order to
> know the postgresql working  status and monitor them ,moreover i don't want
> to  use the monitor tools .I  want to use the SQL commands to monitoring
> postgresql system . please suggest any SQL COMMANDS to work successfully.if
> you have some good suggestion ,you can email to me
> (charles.xie@sanmina-sci.com) or sky :xqwbx163

Hello,

You might want to try pgsql-general or the wiki.  The right stuff also
depends on what you are monitoring for.

Basic uptime and information: "SELECT 1" ("can I log in?"), but also
counting the number of connections (select count(*) from
pg_stat_activity), the number of contending connections (select
count(*) from pg_stat_activity where waiting = 't'), the number of
tables (select count(*) from pg_tables), database size (select
pg_database_size(<dbnamehere>)), and database version (select
version()) we find useful.  It's so useful we put it into a very
condensed and cryptic status line (which can optionally have more
information in more exceptional conditions) like:

[100.5GB:140T:7C], (v9.0.6, --other statuses if they occur--)

The space of queries used for tuning and capacity are much larger, but
I find these basic chunks of information a useful fingerprint of most
databases and activity levels in a relatively small amount of space.

--
fdr

On 9/4/12 12:12 AM, charles_xie wrote:
> Hi all,
>          i have 5 servers that have been installing postgresql .In order to
> know the postgresql working  status and monitor them ,moreover i don't want
> to  use the monitor tools .I  want to use the SQL commands to monitoring
> postgresql system . please suggest any SQL COMMANDS to work successfully.if
> you have some good suggestion ,you can email to me
> (charles.xie@sanmina-sci.com) or sky :xqwbx163

Actually, the Nagios extension for PostgreSQL, check_postgres.pl, has a
really good, very complete set of queries in its code.  You could mine
them from there.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Hi，
   Thanks for your advice.i know the basic monitoring skill,because the
postgresql database is used for the factory production , so I hope they can
run  normal and exert more perfect performance. so i need to be considered
from the point of view ,eg : threading ,locks and so on.


Daniel Farina-4 wrote
>
> On Tue, Sep 4, 2012 at 12:12 AM, charles_xie <xqwyy163@> wrote:
>> Hi all,
>>          i have 5 servers that have been installing postgresql .In order
>> to
>> know the postgresql working  status and monitor them ,moreover i don't
>> want
>> to  use the monitor tools .I  want to use the SQL commands to monitoring
>> postgresql system . please suggest any SQL COMMANDS to work
>> successfully.if
>> you have some good suggestion ,you can email to me
>> (charles.xie@) or sky :xqwbx163
>
> Hello,
>
> You might want to try pgsql-general or the wiki.  The right stuff also
> depends on what you are monitoring for.
>
> Basic uptime and information: "SELECT 1" ("can I log in?"), but also
> counting the number of connections (select count(*) from
> pg_stat_activity), the number of contending connections (select
> count(*) from pg_stat_activity where waiting = 't'), the number of
> tables (select count(*) from pg_tables), database size (select
> pg_database_size(<dbnamehere>)), and database version (select
> version()) we find useful.  It's so useful we put it into a very
> condensed and cryptic status line (which can optionally have more
> information in more exceptional conditions) like:
>
> [100.5GB:140T:7C], (v9.0.6, --other statuses if they occur--)
>
> The space of queries used for tuning and capacity are much larger, but
> I find these basic chunks of information a useful fingerprint of most
> databases and activity levels in a relatively small amount of space.
>
> --
> fdr
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/HELP-Need-to-Sql-commands-to-monitoring-Postgresql-tp5722548p5723150.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

On Thu, Sep 6, 2012 at 6:50 PM, charles_xie <xqwyy163@163.com> wrote:
> Hi，
>    Thanks for your advice.i know the basic monitoring skill,because the
> postgresql database is used for the factory production , so I hope they can
> run  normal and exert more perfect performance. so i need to be considered
> from the point of view ,eg : threading ,locks and so on.

I think the key structures you are looking for, then, are queries on
pg_stat_activity, pg_locks, the pg_statio table, and also "bloat" of
tables and indexes (the wiki has several slightly different relatively
large queries that help track bloat).

As others have mentioned, there are existing tools with an impressive
number of detailed queries, but knowing about these can help you
informally categorize what you are looking at.  check_postgres.pl is
especially useful to copy queries from, if not using it in a Nagios
installation entirely.

--
fdr