keeping track of connections
I would love a way to keep track of the connections/attempted
connections to the postmaster. I'm thinking that when the postmaster
accept()s a connection, it can just insert a record into a table
(system catalog or not) with the information, which can be updated
after the authentication succeeds/fails or whatnot.
something like 'smbstatus' for the samba system.
So, my question is: how should I go about doing this? should I look
into SPI, which I know nothing about? or, what.. I don't think the
catalog cache stuff needs to be changed, it isn't as if this info
needs to be immediately accessible.
I would love a way to keep track of the connections/attempted
connections to the postmaster. I'm thinking that when the postmaster
accept()s a connection, it can just insert a record into a table
(system catalog or not) with the information, which can be updated
after the authentication succeeds/fails or whatnot.something like 'smbstatus' for the samba system.
So, my question is: how should I go about doing this? should I look
into SPI, which I know nothing about? or, what.. I don't think the
catalog cache stuff needs to be changed, it isn't as if this info
needs to be immediately accessible.
Good question. Postmaster does not have access to the system tables, so
it can't access them. You could add a debug option to show it in the
server logs, or add it to the -d2 debug option that already shows SQL
statements.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)On Wed, 3 June 1998, at 00:11:01, Bruce Momjian wrote:
Good question. Postmaster does not have access to the system tables, so
it can't access them. You could add a debug option to show it in the
server logs, or add it to the -d2 debug option that already shows SQL
statements.
How about something like this: a pool of shared memory where this
information is stored, and then a view which calls a set of functions
to return the information from the shared memory?
Apache does something similar.
Brett M writes:
On Wed, 3 June 1998, at 00:11:01, Bruce Momjian wrote:
Good question. Postmaster does not have access to the system tables, so
it can't access them. You could add a debug option to show it in the
server logs, or add it to the -d2 debug option that already shows SQL
statements.How about something like this: a pool of shared memory where this
information is stored, and then a view which calls a set of functions
to return the information from the shared memory?Apache does something similar.
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?
Here we are discussion solutions, but I don't even know what the problem
is. So, please describe what is needed in terms of requirements/functionality.
Thanks
-dg
David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Don't worry about people stealing your ideas. If your ideas are any
good, you'll have to ram them down people's throats." -- Howard Aiken
On Wed, 3 June 1998, at 01:05:17, David Gould wrote:
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?
The problem is that when I do a process listing for the postgres user,
I see many backends. There's no (convenient) way to see what those
backends are doing, what db they're connected to or the remote
host/postgres user.
My required functionality is this: a list of all backends and
connection details. IP, queries issued, listens/notifications
requested/served, bytes transfered, postgres user, db, current query,
client version, etcetcetc.
What problem am I trying to solve? It is purely a desire for this
information. I also feel it will help be debug problems. It would be
nice to track down my clients that are now failing because of password
authentication, but I do admit that this would not help much.
What I shall be doing is hacking libpq to report the name of the
process and related information like environment when connecting to a
database. This would let me track down those programs. As it is, I
have programs failing, and I don't know which ones. Obviously they
aren't very crucial, but it would be nice to know how much more it is
than me typing 'psql' on the host and expecting to connect.
Obviously, this is unrelated. But it is purely a desire for
information. The more info the better. The debug log is quite
henious when trying to figure out what's going on, especially with
lots of connections.
On another unrelated note, the postmaster has been dying lately,
leaving children hanging about. I thought something might be
corrupted (disk full at one point) so I did a dump/reload. We'll see
what happens.
Call it a feature.
On Wed, 3 June 1998, at 00:11:01, Bruce Momjian wrote:
Good question. Postmaster does not have access to the system tables, so
it can't access them. You could add a debug option to show it in the
server logs, or add it to the -d2 debug option that already shows SQL
statements.How about something like this: a pool of shared memory where this
information is stored, and then a view which calls a set of functions
to return the information from the shared memory?Apache does something similar.
Yes, that would work. Are you looking for something to show current
backend status. What type of info would be in there?
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)On Wed, 3 June 1998, at 01:05:17, David Gould wrote:
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?The problem is that when I do a process listing for the postgres user,
I see many backends. There's no (convenient) way to see what those
backends are doing, what db they're connected to or the remote
host/postgres user.My required functionality is this: a list of all backends and
connection details. IP, queries issued, listens/notifications
requested/served, bytes transfered, postgres user, db, current query,
client version, etcetcetc.
That's a lot of info. One solution for database and username would be
to modify argv[1] and argv[2] for the postgres backend so it shows this
information on the ps command line. As long as these args are already
used as part of startup ( and they are when started under the
postmaster), we could set argv to whatever values we are interested in,
and clear the rest of them so the output would look nice.
This would be easy to do, and I would be glad to do it.
What problem am I trying to solve? It is purely a desire for this
information. I also feel it will help be debug problems. It would be
nice to track down my clients that are now failing because of password
authentication, but I do admit that this would not help much.
I think you need a log entry for that, and it would be a good idea.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Date: Wed, 3 Jun 1998 02:37:58 -0700 (PDT)
From: Brett McCormick <brett@work.chicken.org>
Cc: maillist@candle.pha.pa.us, pgsql-hackers@hub.org
Sender: owner-pgsql-hackers@hub.org
On Wed, 3 June 1998, at 01:05:17, David Gould wrote:
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?The problem is that when I do a process listing for the postgres user,
I see many backends. There's no (convenient) way to see what those
backends are doing, what db they're connected to or the remote
host/postgres user.My required functionality is this: a list of all backends and
connection details. IP, queries issued, listens/notifications
requested/served, bytes transfered, postgres user, db, current query,
client version, etcetcetc.
....
Can backend monitoring be compatible with one or more extant
monitoring techniques?
1. syslog
2. HTML (like Apache's real time status)
3. SNMP/SMUX/AgentX
Can backend monitoring be compatible with one or more extant
monitoring techniques?1. syslog
2. HTML (like Apache's real time status)
3. SNMP/SMUX/AgentX
Oooh. An SNMP agent for Postgres. That would be slick...
- Tom
On Wed, 3 Jun 1998, Hal Snyder wrote:
Date: Wed, 3 Jun 1998 02:37:58 -0700 (PDT)
From: Brett McCormick <brett@work.chicken.org>
Cc: maillist@candle.pha.pa.us, pgsql-hackers@hub.org
Sender: owner-pgsql-hackers@hub.orgOn Wed, 3 June 1998, at 01:05:17, David Gould wrote:
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?The problem is that when I do a process listing for the postgres user,
I see many backends. There's no (convenient) way to see what those
backends are doing, what db they're connected to or the remote
host/postgres user.My required functionality is this: a list of all backends and
connection details. IP, queries issued, listens/notifications
requested/served, bytes transfered, postgres user, db, current query,
client version, etcetcetc.....
Can backend monitoring be compatible with one or more extant
monitoring techniques?1. syslog
2. HTML (like Apache's real time status)
I like this method the best...it makes it easier for clients to
monitor as well, without having access to the machines...but does it pose
any security implications?
Hi,
On Wed, 3 June 1998, at 01:05:17, David Gould wrote:
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?The problem is that when I do a process listing for the postgres user,
I see many backends. There's no (convenient) way to see what those
backends are doing, what db they're connected to or the remote
host/postgres user.My required functionality is this: a list of all backends and
connection details. IP, queries issued, listens/notifications
requested/served, bytes transfered, postgres user, db, current query,
client version, etcetcetc.
Perhaps a wild guess ...
Massimo had a patch, which added the pid in the first field of the
debug output (and I guess a timestamp). So you can easily
sort/grep/trace the debug output.
Perhaps this would help and should be really easy.
BTW., I think this feature is so neat, it should be integrated even
if it doesn't solve *your* problem ;-)
Ciao
Ulrich
Ulrich Voss \ \ / /__ / ___|__ _| |
VoCal web publishing \ \ / / _ \| | / _` | |
voss@vocalweb.de \ V / (_) | |__| (_| | |
http://www.vocalweb.de \_/ \___/ \____\__,_|_|
Tel: (++49) 203-306-1560 web publishing
Ulrich Voss writes:
Massimo had a patch, which added the pid in the first field of the
debug output (and I guess a timestamp). So you can easily
sort/grep/trace the debug output.Perhaps this would help and should be really easy.
BTW., I think this feature is so neat, it should be integrated even
if it doesn't solve *your* problem ;-)
This is very very helpful when trying to debug interactions between backends
too. For example if something blows up in the lock manager this can give
a record of who did what to who when.
Great idea.
-dg
David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Don't worry about people stealing your ideas. If your ideas are any
good, you'll have to ram them down people's throats." -- Howard Aiken
Hal Synder writes:
Can backend monitoring be compatible with one or more extant
monitoring techniques?1. syslog
2. HTML (like Apache's real time status)
3. SNMP/SMUX/AgentX
In Illustra, we use (gasp) SQL for this.
select * from procs;
procc_pid |proc_xid |proc_database|proc_locktab |proc_locktid |proc_locktype|proc_prio |proc_licenseid|proc_status |proc_user |proc_host |proc_display |proc_spins |proc_buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|4787 |0 |58.201e |tables |(7,0) |R |0 |0 |lock wait |miadmin |warbler.illustra.com|/dev/pts/4 |[] |[] |
|3997 |0 |58.201e |- |(-1,0) | |0 |0 |client input |miadmin |warbler.illustra.com|/dev/pts/11 |[] |[] |
|29597 |1320638 |58.201e |- |(-1,0) | |0 |0 |running *|miadmin |warbler.illustra.com|/dev/pts/5 |[] |[] |
|4790 |1320646 |58.7 |- |(-1,0) | |0 |0 |running *|miadmin |warbler.illustra.com|/dev/pts/4 |[6] |[] |
-------------------------------------------------------------------------------
"procs" is a pseudo-table that is generated on the fly from the process data
structures in the shared memory when queried. There are also pseudo-tables
for locks and traces and other information.
The advantage of using SQL is that the data can be selected into other tables,
grouped, projected, joined or whatever. The other advantage is that all the
exiting clients can take advantage of the data. So if you wanted to write
a graphical status monitor, you could do so quite simply in pgtcl.
Illustra also provides a set of prewritten functions (which are just sql
funcs) to provide convenient access to many kinds of common catalog queries.
I often see posts on this list that overlook the fact that postgresql is
a "relational database system" and also "an SQL system". Relational
systems are meant to be both "complete" and "reflexive". That is, the
query language (SQL) should suffice to do _any_ task needed. And any
meta-information about the system itself should be available and manageable
through the query language.
That is why we have system catalogs describing things like columns, tables,
types, indexes etc. The system maintains its metadata by doing queries and
updates to the catalogs in the same way that a user can query the catalogs.
This reflexivity is the main reason relational systems have such power.
So, whenever you are thinking about managing information related to a
database system, think about using the system itself to do it. Managing
information is what database systems are _for_. That is, if the current
SQL facilities cannot implement your feature, extend the SQL system,
don't invent some other _kind_ of facility.
The observation that Apache provides status in HTML means that the Apache
team _understand_ that *Apache is a web server*. The natural form of
interaction with a web server is HTML.
Postgres is a SQL database server. The natural form of interaction with
a database server is relational queries and tuples.
Sorry if this is a bit of a rant, but I really think we will have a much
better system if we understand what our system _is_ and try to extend it
in ways that make it better at that rather than to let it go all shapeless
and bloated with unrelated features and interfaces.
-dg
David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
If simplicity worked, the world would be overrun with insects.
Sorry if this is a bit of a rant, but I really think we will have a much
better system if we understand what our system _is_ and try to extend it
in ways that make it better at that rather than to let it go all shapeless
and bloated with unrelated features and interfaces.
I'll wait for this discussion to come down to earth, thanks. :-)
Meaning, wow, that sounds nice, but sounds pretty hard too.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)On Wed, 3 Jun 1998, David Gould wrote:
Hal Synder writes:
Can backend monitoring be compatible with one or more extant
monitoring techniques?1. syslog
2. HTML (like Apache's real time status)
3. SNMP/SMUX/AgentXIn Illustra, we use (gasp) SQL for this.
select * from procs;
procc_pid |proc_xid |proc_database|proc_locktab |proc_locktid |proc_locktype|proc_prio |proc_licenseid|proc_status |proc_user |proc_host |proc_display |proc_spins |proc_buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|4787 |0 |58.201e |tables |(7,0) |R |0 |0 |lock wait |miadmin |warbler.illustra.com|/dev/pts/4 |[] |[] |
|3997 |0 |58.201e |- |(-1,0) | |0 |0 |client input |miadmin |warbler.illustra.com|/dev/pts/11 |[] |[] |
|29597 |1320638 |58.201e |- |(-1,0) | |0 |0 |running *|miadmin |warbler.illustra.com|/dev/pts/5 |[] |[] |
|4790 |1320646 |58.7 |- |(-1,0) | |0 |0 |running *|miadmin |warbler.illustra.com|/dev/pts/4 |[6] |[] |
-------------------------------------------------------------------------------"procs" is a pseudo-table that is generated on the fly from the process
data structures in the shared memory when queried. There are also
pseudo-tables for locks and traces and other information.The advantage of using SQL is that the data can be selected into other
tables, grouped, projected, joined or whatever. The other advantage is
that all the exiting clients can take advantage of the data. So if you
wanted to write a graphical status monitor, you could do so quite simply
in pgtcl.Illustra also provides a set of prewritten functions (which are just sql
funcs) to provide convenient access to many kinds of common catalog
queries.
I definitely like this...it keeps us self-contained as far as the
data is concerned, and everyone that is using it knows enough about SQL
(or should) to be able to gleam information as required...
What would it take to do this though? The 'postmaster' itself,
unless I've misunderstand a good many of the conversations on this, can't
access the tables themselves, only 'flat files' (re: the password issue),
so it would have to be done in the fork'd process itself. That, IMHO,
would pose a possible inconsequential problem though...what if the backend
dies? Its 'record' in the proc table wouldn't be removed, which would be
like having our own internal 'process zombies'...
I think this does bear further discussion though...one 'branch' of
this would be to have a dynamic table for 'live' processes, but also one
that contains a history of past ones...?
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Wed, 3 Jun 1998, Bruce Momjian wrote:
I would love a way to keep track of the connections/attempted
connections to the postmaster. I'm thinking that when the postmaster
accept()s a connection, it can just insert a record into a table
(system catalog or not) with the information, which can be updated
after the authentication succeeds/fails or whatnot.something like 'smbstatus' for the samba system.
So, my question is: how should I go about doing this? should I look
into SPI, which I know nothing about? or, what.. I don't think the
catalog cache stuff needs to be changed, it isn't as if this info
needs to be immediately accessible.Good question. Postmaster does not have access to the system tables, so
it can't access them. You could add a debug option to show it in the
server logs, or add it to the -d2 debug option that already shows SQL
statements.
Here's one for you...and don't laugh at me, eh? :)
postmaster starts up to listen for connections, and then starts up its own
backend to handle database queries? So, on a quiet system, you would have
two processes running, one postmaster, and one postgres...
basically, the idea is that postmaster can't talk to a table, only
postgres can...so, setup postmaster the same way that any other interface
is setup...connect to a backend and pass its transactions through that
way...
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Wed, 3 Jun 1998, David Gould wrote:
I am curious, what is it you are trying to accomplish with this? Are you
trying to build a persistant log that you can query later for billing
or load management/capacity planning information? Are you trying to monitor
login attempts for security auditing? Are you trying to catch logins in
real time for some sort of middleware integration?Here we are discussion solutions, but I don't even know what the problem
is. So, please describe what is needed in terms of
requirements/functionality.
I think the uses could be many. Keep track, on a per 'backend'
basis, max memory used during the life of the process, so that you can
estimate memory requirements/upgrades. Average query times for the
duration of the process? Or maybe even bring it down to a 'per query'
logging, so that you know what the query was, how long it took, and what
resources were required? Tie that to a table of processes, maybe with a
timestamp for when the process started up and when it started.
Then, using a simple query, you could figure out peak times for
processes, or number of processes per hour, or...
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Here's one for you...and don't laugh at me, eh? :)
postmaster starts up to listen for connections, and then starts up its own
backend to handle database queries? So, on a quiet system, you would have
two processes running, one postmaster, and one postgres...basically, the idea is that postmaster can't talk to a table, only
postgres can...so, setup postmaster the same way that any other interface
is setup...connect to a backend and pass its transactions through that
way...
So have the postmaster use the libpq library to open a database
connection and execute queries. Sounds interesting.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)that's a really cool idea. I think I'll try that.
On Wed, 3 June 1998, at 18:46:02, The Hermit Hacker wrote:
Show quoted text
postmaster starts up to listen for connections, and then starts up its own
backend to handle database queries? So, on a quiet system, you would have
two processes running, one postmaster, and one postgres...basically, the idea is that postmaster can't talk to a table, only
postgres can...so, setup postmaster the same way that any other interface
is setup...connect to a backend and pass its transactions through that
way...
On Wed, 3 June 1998, at 18:40:10, The Hermit Hacker wrote:
select * from procs;
<stuff deleted>
"procs" is a pseudo-table that is generated on the fly from the process
data structures in the shared memory when queried. There are also
pseudo-tables for locks and traces and other information.
That's exactly what I envision. PRobably not what I articulated.
The advantage of using SQL is that the data can be selected into other
tables, grouped, projected, joined or whatever. The other advantage is
that all the exiting clients can take advantage of the data. So if you
wanted to write a graphical status monitor, you could do so quite simply
in pgtcl.
Exactly.
