1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Standard Unix Tools</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.3.13 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Monitoring Database Activity"
HREF="monitoring.html"><LINK
REL="PREVIOUS"
TITLE="Monitoring Database Activity"
HREF="monitoring.html"><LINK
REL="NEXT"
TITLE="The Statistics Collector"
HREF="monitoring-stats.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2016-05-09T21:13:26"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.13 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Monitoring Database Activity"
HREF="monitoring.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="monitoring.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 27. Monitoring Database Activity</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="The Statistics Collector"
HREF="monitoring-stats.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="MONITORING-PS"
>27.1. Standard Unix Tools</A
></H1
><P
> On most Unix platforms, <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> modifies its
command title as reported by <TT
CLASS="COMMAND"
>ps</TT
>, so that individual server
processes can readily be identified. A sample display is
</P><PRE
CLASS="SCREEN"
>$ ps auxww | grep ^postgres
postgres 15551 0.0 0.1 57536 7132 pts/0 S 18:02 0:00 postgres -i
postgres 15554 0.0 0.0 57536 1184 ? Ss 18:02 0:00 postgres: writer process
postgres 15555 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: checkpointer process
postgres 15556 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: wal writer process
postgres 15557 0.0 0.0 58504 2244 ? Ss 18:02 0:00 postgres: autovacuum launcher process
postgres 15558 0.0 0.0 17512 1068 ? Ss 18:02 0:00 postgres: stats collector process
postgres 15582 0.0 0.0 58772 3080 ? Ss 18:04 0:00 postgres: joe runbug 127.0.0.1 idle
postgres 15606 0.0 0.0 58772 3052 ? Ss 18:07 0:00 postgres: tgl regression [local] SELECT waiting
postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl regression [local] idle in transaction</PRE
><P>
(The appropriate invocation of <TT
CLASS="COMMAND"
>ps</TT
> varies across different
platforms, as do the details of what is shown. This example is from a
recent Linux system.) The first process listed here is the
master server process. The command arguments
shown for it are the same ones used when it was launched. The next five
processes are background worker processes automatically launched by the
master process. (The <SPAN
CLASS="QUOTE"
>"stats collector"</SPAN
> process will not be present
if you have set the system not to start the statistics collector; likewise
the <SPAN
CLASS="QUOTE"
>"autovacuum launcher"</SPAN
> process can be disabled.)
Each of the remaining
processes is a server process handling one client connection. Each such
process sets its command line display in the form
</P><PRE
CLASS="SCREEN"
>postgres: <TT
CLASS="REPLACEABLE"
><I
>user</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>database</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>host</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>activity</I
></TT
></PRE
><P>
The user, database, and (client) host items remain the same for
the life of the client connection, but the activity indicator changes.
The activity can be <TT
CLASS="LITERAL"
>idle</TT
> (i.e., waiting for a client command),
<TT
CLASS="LITERAL"
>idle in transaction</TT
> (waiting for client inside a <TT
CLASS="COMMAND"
>BEGIN</TT
> block),
or a command type name such as <TT
CLASS="LITERAL"
>SELECT</TT
>. Also,
<TT
CLASS="LITERAL"
>waiting</TT
> is appended if the server process is presently waiting
on a lock held by another session. In the above example we can infer
that process 15606 is waiting for process 15610 to complete its transaction
and thereby release some lock. (Process 15610 must be the blocker, because
there is no other active session. In more complicated cases it would be
necessary to look into the
<A
HREF="view-pg-locks.html"
><TT
CLASS="STRUCTNAME"
>pg_locks</TT
></A
>
system view to determine who is blocking whom.)
</P
><P
> If you have turned off <A
HREF="runtime-config-statistics.html#GUC-UPDATE-PROCESS-TITLE"
>update_process_title</A
> then the
activity indicator is not updated; the process title is set only once
when a new process is launched. On some platforms this saves a measurable
amount of per-command overhead; on others it's insignificant.
</P
><DIV
CLASS="TIP"
><BLOCKQUOTE
CLASS="TIP"
><P
><B
>Tip: </B
> <SPAN
CLASS="PRODUCTNAME"
>Solaris</SPAN
> requires special handling. You must
use <TT
CLASS="COMMAND"
>/usr/ucb/ps</TT
>, rather than
<TT
CLASS="COMMAND"
>/bin/ps</TT
>. You also must use two <TT
CLASS="OPTION"
>w</TT
>
flags, not just one. In addition, your original invocation of the
<TT
CLASS="COMMAND"
>postgres</TT
> command must have a shorter
<TT
CLASS="COMMAND"
>ps</TT
> status display than that provided by each
server process. If you fail to do all three things, the <TT
CLASS="COMMAND"
>ps</TT
>
output for each server process will be the original <TT
CLASS="COMMAND"
>postgres</TT
>
command line.
</P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="monitoring.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="monitoring-stats.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Monitoring Database Activity</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="monitoring.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>The Statistics Collector</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
|