Hello:
I have a server that has many more postgres processes (postgres -D
<datadir>, owned by postgres, listed with an ps -ef) than connections in
pg_stat_activity. How can I find out what those processes are?
I have been going through the docs and found a reference to if the
autovacuum launcher should show up in pg_stat_activity, but it looks
like that was implemented in 8.3.
I'm running 8.3.5 on solaris 10.
Thanks so much for the help!
Mija


CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential, proprietary, and/or privileged information protected by
law. If you are not the intended recipient, you may not use, copy, or
distribute this e-mail message or its attachments. If you believe you
have received this e-mail message in error, please contact the sender by
reply e-mail and destroy all copies of the original message.


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

On Thu, Sep 3, 2009 at 5:26 PM, Lee, Mija<mija [at] scharp.org> wrote:
> Hello:
> I have a server that has many more postgres processes (postgres -D
> <datadir>, owned by postgres, listed with an ps -ef) than connections in
> pg_stat_activity. How can I find out what those processes are?
> I have been going through the docs and found a reference to if the
> autovacuum launcher should show up in pg_stat_activity, but it looks
> like that was implemented in 8.3.
> I'm running 8.3.5 on solaris 10.
> Thanks so much for the help!
> Mija

What does:
$ ps ax|grep postgres
say?

I get something like this at idle:

312 pts/0 S+ 0:00 /usr/lib/postgresql/8.3/bin/psql
313 ? Ss 0:00 postgres: smarlowe smarlowe [local] idle
2784 ? S 0:01 /usr/lib/postgresql/8.3/bin/postgres -D
/var/lib/postgresql/8.3/main -c
config_file=/etc/postgresql/8.3/main/postgresql.conf
2787 ? Ss 0:03 postgres: writer process
2788 ? Ss 0:02 postgres: wal writer process
2789 ? Ss 0:00 postgres: autovacuum launcher process
2790 ? Ss 0:00 postgres: stats collector process
32621 pts/1 S+ 0:00 grep postgres

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

Hi -
Thanks for the response.
I'm on solaris so the ps ax doesn't work for me. I think the equivalent
is ps -ef which shows:
postgres 6939 22678 0 15:49:49 ? 1:33
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22678 16072 0 Aug 06 ? 5:22
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 5074 22678 0 Aug 12 ? 710:08
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 8677 22678 0 Aug 13 ? 894:39
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 8347 22678 0 Aug 25 ? 357:27
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 343 22678 0 Aug 13 ? 368:30
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 8678 22678 0 Aug 13 ? 695:15
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 24793 22678 0 Aug 12 ? 2048:13
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22690 22678 0 Aug 06 ? 106:15
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22686 22678 0 Aug 06 ? 3:09
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22691 22678 0 Aug 06 ? 1145:42
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 350 22678 0 Aug 13 ? 262:39
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 16593 22678 0 Aug 12 ? 731:57
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22688 22678 0 Aug 06 ? 104:59
/services/8.3.5/bin/postgres -D /services/pgdata2

If I'm wrong about the ps commands, please correct me.
When I cross reference this against pg_stat_activity, 5 of them don't
show up, and a top shows the same processes using 0% cpu but almost a
gig of memory. Are these internal postgres processes that don't make a
connection?
Mija





-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe [at] gmail.com]
Sent: Thursday, September 03, 2009 7:54 PM
To: Lee, Mija
Cc: pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] postgres processes not reflected in
pg_stat_activity

On Thu, Sep 3, 2009 at 5:26 PM, Lee, Mija<mija [at] scharp.org> wrote:
> Hello:
> I have a server that has many more postgres processes (postgres -D
> <datadir>, owned by postgres, listed with an ps -ef) than connections
in
> pg_stat_activity. How can I find out what those processes are?
> I have been going through the docs and found a reference to if the
> autovacuum launcher should show up in pg_stat_activity, but it looks
> like that was implemented in 8.3.
> I'm running 8.3.5 on solaris 10.
> Thanks so much for the help!
> Mija

What does:
$ ps ax|grep postgres
say?

I get something like this at idle:

312 pts/0 S+ 0:00 /usr/lib/postgresql/8.3/bin/psql
313 ? Ss 0:00 postgres: smarlowe smarlowe [local] idle
2784 ? S 0:01 /usr/lib/postgresql/8.3/bin/postgres -D
/var/lib/postgresql/8.3/main -c
config_file=3D/etc/postgresql/8.3/main/postgresql.conf
2787 ? Ss 0:03 postgres: writer process
2788 ? Ss 0:02 postgres: wal writer process
2789 ? Ss 0:00 postgres: autovacuum launcher process
2790 ? Ss 0:00 postgres: stats collector process
32621 pts/1 S+ 0:00 grep postgres

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

On Fri, Sep 4, 2009 at 10:32 AM, Lee, Mija<mija [at] scharp.org> wrote:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:
> postgres =A06939 22678 =A0 0 15:49:49 ? =A0 =A0 =A0 =A0 =A0 1:33
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22678 16072 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 =A0 5:22
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A05074 22678 =A0 0 =A0 Aug 12 ? =A0 =A0 =A0 =A0 710:08
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A08677 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 894:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A08347 22678 =A0 0 =A0 Aug 25 ? =A0 =A0 =A0 =A0 357:27
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A0 343 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 368:30
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A08678 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 695:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 24793 22678 =A0 0 =A0 Aug 12 ? =A0 =A0 =A0 =A02048:13
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22690 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 106:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22686 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 =A0 3:09
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22691 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A01145:42
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A0 350 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 262:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 16593 22678 =A0 0 =A0 Aug 12 ? =A0 =A0 =A0 =A0 731:57
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22688 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 104:59
> /services/8.3.5/bin/postgres -D /services/pgdata2
>
> If I'm wrong about the ps commands, please correct me.
> When I cross reference this against pg_stat_activity, 5 of them don't
> show up, and a top shows the same processes using 0% cpu but almost a
> gig of memory. Are these internal postgres processes that don't make a
> connection?

Got me, I've never been real handy on Solaris. There's likely a
command that'll show something closer to what I posted. Note that in
the list I posted there are several processes that are NOT pg
backends, but are helper processes (stats collector etc) and it's
normal to have a handful of these processes on a pg server.

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

Lee, Mija escribi=F3:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:

hmm, did you try /usr/ucb/ps?

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

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

2009/9/4 Lee, Mija <mija [at] scharp.org>:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:
> postgres =C2=A06939 22678 =C2=A0 0 15:49:49 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 1:33
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22678 16072 =C2=A0 0 =C2=A0 Aug 06 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 5:22
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =C2=A05074 22678 =C2=A0 0 =C2=A0 Aug 12 ? =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 710:08
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =C2=A08677 22678 =C2=A0 0 =C2=A0 Aug 13 ? =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 894:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =C2=A08347 22678 =C2=A0 0 =C2=A0 Aug 25 ? =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 357:27
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =C2=A0 343 22678 =C2=A0 0 =C2=A0 Aug 13 ? =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 368:30
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =C2=A08678 22678 =C2=A0 0 =C2=A0 Aug 13 ? =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 695:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 24793 22678 =C2=A0 0 =C2=A0 Aug 12 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
2048:13
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22690 22678 =C2=A0 0 =C2=A0 Aug 06 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
106:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22686 22678 =C2=A0 0 =C2=A0 Aug 06 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 3:09
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22691 22678 =C2=A0 0 =C2=A0 Aug 06 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
1145:42
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =C2=A0 350 22678 =C2=A0 0 =C2=A0 Aug 13 ? =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 262:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 16593 22678 =C2=A0 0 =C2=A0 Aug 12 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
731:57
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22688 22678 =C2=A0 0 =C2=A0 Aug 06 ? =C2=A0 =C2=A0 =C2=A0 =C2=A0=
104:59
> /services/8.3.5/bin/postgres -D /services/pgdata2
>
> If I'm wrong about the ps commands, please correct me.
> When I cross reference this against pg_stat_activity, 5 of them don't
> show up, and a top shows the same processes using 0% cpu but almost a
> gig of memory. Are these internal postgres processes that don't make a
> connection?
> Mija
>

What is the value of update_process_title in your postgresql.conf? By
default is on, so you must see the title off the process.

what about ps auxw?

--
Emanuel Calvo Franco
DBA at: www.siu.edu.ar
www.emanuelcalvofranco.com.ar

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

I got it once I pointed to the ucb ps per Alvaro's suggestion. Thanks! Here=
's what I see with a ps auwx :

postgres 343 4.2 16.627789922714952 ? S Aug 13 371:21 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 8677 0.3 16.727967602735936 ? S Aug 13 895:14 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 16593 0.2 10.617558721731344 ? S Aug 12 734:06 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 22691 0.1 0.115096 9608 ? S Aug 06 1145:55 /services/8.=
3.5/bin/postgres -D /services/pgdata2
postgres 22688 0.0 9.816178161609728 ? S Aug 06 105:03 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 6939 0.0 9.816171921608128 ? S 15:49:49 1:34 /services/=
8.3.5/bin/postgres -D /services/pgdata2
postgres 22690 0.0 9.916256561615744 ? S Aug 06 106:16 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 350 0.0 10.317070961678960 ? S Aug 13 262:39 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 5074 0.0 10.216917041672928 ? S Aug 12 710:07 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 8347 0.0 16.728063602733464 ? S Aug 25 357:26 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 8678 0.0 10.216995281677848 ? S Aug 13 695:14 /services=
/8.3.5/bin/postgres -D /services/pgdata2
postgres 22678 0.0 9.816164721609216 ? S Aug 06 5:22 /services/=
8.3.5/bin/postgres -D /services/pgdata2
postgres 22686 0.0 0.0 6824 2176 ? S Aug 06 3:09 /services/8.3.=
5/bin/postgres -D /services/pgdata2
postgres 24793 0.0 16.928296322763624 ? S Aug 12 2050:48 /service=
s/8.3.5/bin/postgres -D /services/pgdata2

Again, 5 extra processes. 6939 is one example. I'm not sure what you are lo=
oking for from this command that is different from ps -ef?
Update_process_title is turned on.
Any other suggestions are greatly appreciated. I may have to kill these pro=
cesses because they are eating up memory on this machine, but I would reall=
y prefer to know why/what they are before doing so.
Mija

-----Original Message-----
From: Emanuel Calvo Franco [mailto:postgres.arg [at] gmail.com]
Sent: Friday, September 04, 2009 11:01 AM
To: Lee, Mija
Cc: Scott Marlowe; pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] postgres processes not reflected in pg_stat_activity

2009/9/4 Lee, Mija <mija [at] scharp.org>:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:
> postgres =A06939 22678 =A0 0 15:49:49 ? =A0 =A0 =A0 =A0 =A0 1:33
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22678 16072 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 =A0 5:22
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A05074 22678 =A0 0 =A0 Aug 12 ? =A0 =A0 =A0 =A0 710:08
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A08677 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 894:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A08347 22678 =A0 0 =A0 Aug 25 ? =A0 =A0 =A0 =A0 357:27
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A0 343 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 368:30
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A08678 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 695:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 24793 22678 =A0 0 =A0 Aug 12 ? =A0 =A0 =A0 =A02048:13
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22690 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 106:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22686 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 =A0 3:09
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22691 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A01145:42
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres =A0 350 22678 =A0 0 =A0 Aug 13 ? =A0 =A0 =A0 =A0 262:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 16593 22678 =A0 0 =A0 Aug 12 ? =A0 =A0 =A0 =A0 731:57
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22688 22678 =A0 0 =A0 Aug 06 ? =A0 =A0 =A0 =A0 104:59
> /services/8.3.5/bin/postgres -D /services/pgdata2
>
> If I'm wrong about the ps commands, please correct me.
> When I cross reference this against pg_stat_activity, 5 of them don't
> show up, and a top shows the same processes using 0% cpu but almost a
> gig of memory. Are these internal postgres processes that don't make a
> connection?
> Mija
>

What is the value of update_process_title in your postgresql.conf? By
default is on, so you must see the title off the process.

what about ps auxw?

--
Emanuel Calvo Franco
DBA at: www.siu.edu.ar
www.emanuelcalvofranco.com.ar

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

On Fri, Sep 4, 2009 at 12:17 PM, Lee, Mija<mija [at] scharp.org> wrote:
> I got it once I pointed to the ucb ps per Alvaro's suggestion. Thanks! Here's what I see with a ps auwx :

So what does ps ax|grep postgres say?

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

343 ? S 372:35 /services/8.3.5/bin/postgres -D
/services/pgdata2
350 ? S 262:39 /services/8.3.5/bin/postgres -D
/services/pgdata2
5074 ? S 710:07 /services/8.3.5/bin/postgres -D
/services/pgdata2
6939 ? S 1:35 /services/8.3.5/bin/postgres -D
/services/pgdata2
8347 ? S 357:26 /services/8.3.5/bin/postgres -D
/services/pgdata2
8677 ? S 897:01 /services/8.3.5/bin/postgres -D
/services/pgdata2
8678 ? S 695:14 /services/8.3.5/bin/postgres -D
/services/pgdata2
16593 ? O 736:45 /services/8.3.5/bin/postgres -D
/services/pgdata2
22678 ? S 5:23 /services/8.3.5/bin/postgres -D
/services/pgdata2
22686 ? S 3:09 /services/8.3.5/bin/postgres -D
/services/pgdata2
22688 ? S 105:04 /services/8.3.5/bin/postgres -D
/services/pgdata2
22690 ? S 106:16 /services/8.3.5/bin/postgres -D
/services/pgdata2
22691 ? S 1146:00 /services/8.3.5/bin/postgres -D
/services/pgdata2
24793 ? S 2050:48 /services/8.3.5/bin/postgres -D
/services/pgdata2


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe [at] gmail.com]
Sent: Friday, September 04, 2009 11:21 AM
To: Lee, Mija
Cc: Emanuel Calvo Franco; pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] postgres processes not reflected in
pg_stat_activity

On Fri, Sep 4, 2009 at 12:17 PM, Lee, Mija<mija [at] scharp.org> wrote:
> I got it once I pointed to the ucb ps per Alvaro's suggestion. Thanks!
Here's what I see with a ps auwx :

So what does ps ax|grep postgres say?

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

"Lee, Mija" <mija [at] scharp.org> writes:
> Again, 5 extra processes. 6939 is one example. I'm not sure what you are looking for from this command that is different from ps -ef?
> Update_process_title is turned on.

5 extra processes would be exactly what I'd expect in an 8.3
installation. I don't remember the magic incantation to get Solaris'
ps to show you useful descriptions, but on most platforms what you'd
see is something like this:

postgres 8522 1 0 13:37:37 pts/2 0:00 postmaster
postgres 8525 8522 0 13:37:37 ? 0:00 postgres: writer process
postgres 8526 8522 0 13:37:37 ? 0:00 postgres: wal writer process
postgres 8527 8522 0 13:37:37 ? 0:00 postgres: autovacuum launcher process
postgres 8528 8522 0 13:37:37 ? 0:00 postgres: stats collector process

> Any other suggestions are greatly appreciated. I may have to kill these processes because they are eating up memory on this machine, but I would really prefer to know why/what they are before doing so.

You can't kill them without taking down the database. I will bet large
odds that they aren't "eating memory", either --- you have probably got
Postgres' shared memory area set to about 1GB, and you are looking at
the output of tools that report all of that as belonging to each process
that's connected to the shared memory segment.

regards, tom lane

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