Thread: number of connections to postmaster
Is there any way to find out the number of connections to postmaster? I want to know how many clients. Since the default max connection is 64, can we check that number in C API or system command? Thanks,
Check out techdocs.postgresql.org and download PL/Sh by Peter Eisentraut. You
can do the job with the help of an embedded Shell script.
As far as I know there is no other (easy way) of retrieving that information.
Try something like that:
CREATE OR REPLACE FUNCTION "backends_number"() RETURNS "text" AS '
#!/bin/sh
expr $(ps ax | egrep -e ''postgres:.*local'' | wc -l) - 1
' LANGUAGE 'plsh';
I have not tested this extensively but it should work.
Note: PL/Sh is not a trusted embedded languages (system calls are allowed) ->
use a separate database for that and don't use your production database
(security!!!!).
Hans
"Percy" <percy_yip@yahoo.com> writes: > Is there any way to find out the number of connections to postmaster? In 7.2, "select count(*) from pg_stat_activity" should do it. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html regards, tom lane
Three questions:
1. In a script, how do I set the value of a date attr to the current
date. I've tried a few obvious approaches but nothing works yet.
2.If I do:
psql <db> -c "select current_date;"
I get:
date
------------
2002-02-23
(1 row)
I can use:
psql <db> -c "select current_date;" | tail -3 | head -1
to just get the date value, but is there simpler way?
And lastly, I believe there are a few environmental variables used to
control various PostgreSQL behaviours, eg: date/time format. Where is
there a list of these? (I can't find them in the docs)
Thanks
=============================================================================
_-''-_ <Brent Wood> b.wood@niwa.cri.nz Ph: +64(4)386-0300
'o \/ NIWA, Box 14901,
> ) /\ Kilbirnie, Wellington, New Zealand
`-===-' #include <std_disclaimer>
It looks that you want the date, for usage at the shell level. Why not just do "date +%Y-%m-%d" ? cheers, thalis On Fri, 22 Feb 2002, Brent Wood wrote: > > Three questions: > > > 1. In a script, how do I set the value of a date attr to the current > date. I've tried a few obvious approaches but nothing works yet. > > > > 2.If I do: > psql <db> -c "select current_date;" > I get: > > date > ------------ > 2002-02-23 > (1 row) > > I can use: > psql <db> -c "select current_date;" | tail -3 | head -1 > to just get the date value, but is there simpler way? > > > And lastly, I believe there are a few environmental variables used to > control various PostgreSQL behaviours, eg: date/time format. Where is > there a list of these? (I can't find them in the docs) > > Thanks > > > ============================================================================= > > _-''-_ <Brent Wood> b.wood@niwa.cri.nz Ph: +64(4)386-0300 > 'o \/ NIWA, Box 14901, > > ) /\ Kilbirnie, Wellington, New Zealand > `-===-' #include <std_disclaimer> > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Tom When I try that, I get template1=# select count(*) from pg_stat_activity; ERROR: Relation 'pg_stat_activity' does not exist thanks Pam Wampler -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thursday, February 21, 2002 7:35 PM To: Percy Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] number of connections to postmaster "Percy" <percy_yip@yahoo.com> writes: > Is there any way to find out the number of connections to postmaster? In 7.2, "select count(*) from pg_stat_activity" should do it. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Fri, 22 Feb 2002, Pam Wampler wrote: > Tom > When I try that, I get > > template1=# select count(*) from pg_stat_activity; > ERROR: Relation 'pg_stat_activity' does not exist > Hi, Maybe you are running an old version of PostgreSQL? Tom says, pg_stat_activity comes with v7.2 . Regards, -- Devrim GUNDUZ devrim@oper.metu.edu.tr devrim.gunduz@linux.org.tr devrimg@tr.net Web : http://devrim.oper.metu.edu.tr ------------------------------------------------------------------
Pam Wampler <Pam_Wampler@taylorwhite.com> writes: > Tom > When I try that, I get > > template1=# select count(*) from pg_stat_activity; > ERROR: Relation 'pg_stat_activity' does not exist That would imply you're not running 7.2. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
yes I am running 7.1.3 thanks pam -----Original Message----- From: Devrim GUNDUZ [mailto:devrim@oper.metu.edu.tr] Sent: Friday, February 22, 2002 8:46 AM To: Pam Wampler Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] number of connections to postmaster On Fri, 22 Feb 2002, Pam Wampler wrote: > Tom > When I try that, I get > > template1=# select count(*) from pg_stat_activity; > ERROR: Relation 'pg_stat_activity' does not exist > Hi, Maybe you are running an old version of PostgreSQL? Tom says, pg_stat_activity comes with v7.2 . Regards, -- Devrim GUNDUZ devrim@oper.metu.edu.tr devrim.gunduz@linux.org.tr devrimg@tr.net Web : http://devrim.oper.metu.edu.tr ------------------------------------------------------------------
psql <db> -A -t -c "select current_date;" will do what you appear to want. But, as already pointed out, you are probably better off using the methods provided in your scripting language (shell or whatever). --- Brent Wood <baw@frc.niwa.cri.nz> wrote: > > Three questions: > > > 1. In a script, how do I set the value of a date > attr to the current > date. I've tried a few obvious approaches but > nothing works yet. > > > > 2.If I do: > psql <db> -c "select current_date;" > I get: > > date > ------------ > 2002-02-23 > (1 row) > > I can use: > psql <db> -c "select current_date;" | tail > -3 | head -1 > to just get the date value, but is there simpler > way? > > > And lastly, I believe there are a few environmental > variables used to > control various PostgreSQL behaviours, eg: date/time > format. Where is > there a list of these? (I can't find them in the > docs) > > Thanks > > > ============================================================================= > > _-''-_ <Brent Wood> b.wood@niwa.cri.nz > Ph: +64(4)386-0300 > 'o \/ NIWA, Box 14901, > > ) /\ Kilbirnie, Wellington, New Zealand > > `-===-' #include <std_disclaimer> > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com
Try: psql <db> -A -t -c "select current_date;" > 2.If I do: > psql <db> -c "select current_date;" > I get: > > date > ------------ > 2002-02-23 > (1 row) > > I can use: > psql <db> -c "select current_date;" | tail -3 | head -1 > to just get the date value, but is there simpler way? >
Pam Wampler wrote:
> Tom
> When I try that, I get
>
> template1=# select count(*) from pg_stat_activity;
> ERROR: Relation 'pg_stat_activity' does not exist
Tom said "in 7.2". So why do you expect it to work in a
pre-7.2 version?
And BTW:
SELECT numbackends FROM pg_stat_database ...
could do it too.
Jan
>
>
> thanks
> Pam Wampler
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, February 21, 2002 7:35 PM
> To: Percy
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] number of connections to postmaster
>
>
> "Percy" <percy_yip@yahoo.com> writes:
> > Is there any way to find out the number of connections to postmaster?
>
> In 7.2, "select count(*) from pg_stat_activity" should do it. See
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com