Differentiating two clusters with ps?

[D. Joseph Creighton]

The background: two running clusters on one host, each with their own
PGDATA and PGPORT (defined in their own postgresql.conf). All seems fine.

The problem: '/usr/ucb/ps xaugww | grep postgres' gives both running
clusters but I'm not readily able to differentiate which process belongs
to which cluster.

There are some hints: start times of internal processes are generally
all together but a user's connection doesn't seem to be as easily
identified as belonging to one cluster over the other. I'm looking to
find out if there's a consistent way to say, "This user/connection
is happening in this postgres instance."

Any ideas?

--
"Hanlon's Razor: Never attribute to malice that which is adequately
explained by stupidity." - Anon. (via fortune)
D. Joseph Creighton [ESTP] | Info. Technologist, Database Technologies, IST
Joe_Cr...@UManitoba.CA | University of Manitoba Winnipeg, MB, Canada, eh?

[Matthew Woodcraft]

D. Joseph Creighton <d...@cc.umanitoba.ca> wrote:
>The background: two running clusters on one host, each with their own
>PGDATA and PGPORT (defined in their own postgresql.conf). All seems fine.
>
>The problem: '/usr/ucb/ps xaugww | grep postgres' gives both running
>clusters but I'm not readily able to differentiate which process belongs
>to which cluster.
>
>There are some hints: start times of internal processes are generally
>all together but a user's connection doesn't seem to be as easily
>identified as belonging to one cluster over the other. I'm looking to
>find out if there's a consistent way to say, "This user/connection
>is happening in this postgres instance."

You don't mention what platform you're using (some version of Solaris?).

The postgresql backend processes are normally all immediate children of
their main postmaster. On my Debian setup I can list all the backends from
one cluster with 'ps --ppid=<N>'.

I'm not sure of a good way to automatically find the parent processes' pids.
Maybe by reading the pidfiles or from netstat.

-M-

[D. Joseph Creighton]

In the last exciting episode,

Matthew Woodcraft <matt...@chiark.greenend.org.uk> wrote:
}D. Joseph Creighton <d...@cc.umanitoba.ca> wrote:
}>The problem: '/usr/ucb/ps xaugww | grep postgres' gives both running
}>clusters but I'm not readily able to differentiate which process belongs
}>to which cluster.
}>
}>There are some hints: start times of internal processes are generally
}>all together but a user's connection doesn't seem to be as easily
}>identified as belonging to one cluster over the other. I'm looking to
}>find out if there's a consistent way to say, "This user/connection
}>is happening in this postgres instance."
}
}You don't mention what platform you're using (some version of Solaris?).

My oversight: Solaris 10 (Sun5.10).

}The postgresql backend processes are normally all immediate children of
}their main postmaster. On my Debian setup I can list all the backends from
}one cluster with 'ps --ppid=<N>'.

Very nice; wish I had that. 'ps -p #' will only return the process ID that
matches and the ucb version of 'ps #' does the same. Useless, really.

}I'm not sure of a good way to automatically find the parent processes' pids.
}Maybe by reading the pidfiles or from netstat.

I've found that I can select from pg_stat_activity and there is a PID
column listed (procpid) that gives me a correlation between the process
and which postgres is involved.

Although a two step process, I'm thinking this is as close as it gets.

Thanks.
--
"Every program in development at MIT expands until it can read mail."
- The Law of Software Development and Envelopment at MIT

[David Bolen]

d...@cc.umanitoba.ca (D. Joseph Creighton) writes:

> Very nice; wish I had that. 'ps -p #' will only return the process ID that
> matches and the ucb version of 'ps #' does the same. Useless, really.

Though I believe (assuming BSD like options for /usr/ucb/ps) that you
ought to be able to get the PPID in the full status output if you use
the "l" output format rather than "u" in your ps command.

So at worst you can just parse out the PPID column for all of the
postgres processes and correlate them together.

But you might check if /usr/ucb/ps has the "f" option on your system.
That would present an ASCII "tree" format in the COMMAND column which
should make it clear which processes belong to which main cluster
postgres process and parsing it could just look for the "\_" lines.

> I've found that I can select from pg_stat_activity and there is a PID
> column listed (procpid) that gives me a correlation between the process
> and which postgres is involved.
>
> Although a two step process, I'm thinking this is as close as it gets.

I certainly don't think there's a way to correlate to a specific user
within postgres without using the postgres tables for connections.
But yes, once you then have the process(es), you should be able to
extract it out of a ps output in one pass if you like.

-- David