1PGCENTER(1)                 General Commands Manual                PGCENTER(1)
2
3
4

NAME

6       pgcenter - is the PostgreSQL administration console with top-like moni‐
7       toring.
8

SYNOPSIS

10       pgcenter [OPTION]... [DBNAME [USERNAME]]
11

DESCRIPTION

13       PostgreSQL provides various statistics which includes information about
14       tables,  indexes, functions and other database objects and their usage.
15       Moreover, statistics has information about connections, current queries
16       and  database  operations (INSERT/DELETE/UPDATE). But most of this sta‐
17       tistics are provided as permanently incremented counters. The  pgcenter
18       provides convenient interface to this statistics and allow viewing sta‐
19       tistics changes in time interval, eg. per second. The pgcenter provides
20       fast access for database management task, such as editing configuration
21       files, reloading services, viewing log files and canceling or terminat‐
22       ing  database  backends  (by  pid or using state mask). However if need
23       execute some specific operations, pgcenter can start psql  session  for
24       this purposes.
25
26       The  pgcenter  uses screens and contexts.  Screen is a special area for
27       displaying statistics. Number of screen limited by  eight.  Context  is
28       the  type  of displayed statistics: table statistics, index statistics,
29       functions statistics, replication statistics, etc.
30
31       All operations in pgcenter realized through hotkeys.
32
33       When pgcenter starts it using  the  connection  parameters  transmitted
34       directly through the arguments, or use a connection file that describes
35       the connections options. For each connection is  allocated  a  separate
36       screen.  When the arguments and connections file used together, connect
37       options from arguments always starts in the firts screen. If the  argu‐
38       ments  or  connections file is not specified, pgcenter use default con‐
39       nection settings.
40
41       The pgcenter main window divided into 3 window: SUMMARY, CMDLINE, DBRE‐
42       SULT  and  optional  LOGTAIL window which hidden by default and appears
43       only by user command.
44
45

COMMAND-LINE OPTIONS

47       The command-line syntax for pgcenter consists of:
48
49       -h, --host=HOSTNAME
50              PostgreSQL server host or socket directory (default: "/tmp").
51
52       -p, --port=PORT
53              PostgreSQL server port (default: "5432").
54
55       -U, --username=USERNAME
56              PostgerSQL user name (default: "current logged user").
57
58       -d, --dbname=DBNAME
59              PostgreSQL administrative database name (default: "pgbouncer").
60
61       -f, --file=FILENAME
62              Use connections information from file. By default, pgcenter when
63              starting,  trying  read ~/.pgcenterrc connections file. This be‐
64              haviour can be overriden with this option.
65
66       -w, --no-password
67              Never prompt for password.
68
69       -W, --password
70              Force password prompt (should happen automatically).
71
72       -?, --help
73              Show this help, then exit.
74
75       -V, --version
76              Print version, then exit.
77
78

SUMMARY WINDOW

80       Summary window always displayed and provides various information  about
81       system load and current connected PostgreSQL.
82
83
84       Current Time and Load Average
85              Line  1-1 shows program name, current time, system load avg over
86              the last 1, 5 and 15 minutes.
87
88
89       CPU Usage
90              Line 1-2 shows CPU usage percentages based on the interval since
91              the last refresh.
92
93              us, user
94                     Percentage of CPU utilization that occurred while execut‐
95                     ing at the user level (application).
96
97              sy, system
98                     Percentage of CPU utilization that occurred while execut‐
99                     ing at the system level (kernel).
100
101              ni, nice
102                     Percentage of CPU utilization that occurred while execut‐
103                     ing at the user level with nice priority.
104
105              id, idle
106                     Percentage of time that the CPU or CPUs were idle and the
107                     system did not have an outstanding disk I/O request.
108
109              wa, iowait
110                     Percentage  of time that the CPU or CPUs were idle during
111                     which the system had an outstanding disk I/O request.
112
113              hi, hardirq
114                     Percentage of time that the CPU or CPUS  spent  servicing
115                     hardware interrupts.
116
117              si, softirq
118                     Percentage  of  time  thatthe CPU or CPUS spent servicing
119                     software interrupts.
120
121              st, steal
122                     Percentage of time spent in involuntary wait by the  vir‐
123                     tual  CPU  or  CPUs  while  the  hypervisor was servicing
124                     another virtual processor.
125
126
127       CPU Usage
128              Line 1-3 shows memory and line 1-4 shows swap usage based on the
129              interval since the last refresh.
130
131              total
132                     Total amount of RAM or swap in MiB (mebibyte = 1024 KiB =
133                     1,048,576 bytes).
134
135              free
136                     Total amount of free RAM or swap in MiB (mebibyte =  1024
137                     KiB = 1,048,576 bytes).
138
139              used
140                     Total  amount of used RAM or swap in MiB (mebibyte = 1024
141                     KiB = 1,048,576 bytes).
142
143              buff/cached
144                     Total amount of cached and buffered RAM in MiB  (mebibyte
145                     = 1024 KiB = 1,048,576 bytes).
146
147              dirty/writeback
148                     Total  ammount  of  dirty  memory  which is waiting to be
149                     written back to the disk.  And  total  amount  of  memory
150                     actively  being  written  back to the disk. Values are in
151                     MiB (mebibyte = 1024 KiB = 1,048,576 bytes).
152
153
154       Connection information
155              Line 2-1 shows connection information to the current PostgreSQL:
156
157              conn
158                     Current screen number (from 1 to 8) and  connection  sta‐
159                     tus.
160
161              conninfo
162                     Current  connection  information  with  following format:
163                     host:port username@dbname.
164
165              postgresql version
166                     Version of the PostgreSQL.
167
168              uptime
169                     Uptime of PostgreSQL service.
170                     Used query: SELECT now() - pg_postmaster_start_time();
171
172
173       Summary activity
174              Line 2-2 shows current PostgreSQL connections grouped  by  their
175              states.
176
177              total
178                     Total connections number.
179                     Used query: SELECT count(*) FROM pg_stat_activity;
180
181              idle
182                     Number  of  backends in idle state which is waiting for a
183                     new client command.
184                     Used query: SELECT count(*) FROM pg_stat_activity
185                                 WHERE state = 'idle';
186
187              idle_in_xact
188                     Number of backends in idle in transaction means the back‐
189                     end is in a transaction, but is not currently executing a
190                     query, and idle in transaction (aborted) which is similar
191                     to  idle  in transaction, except one of the statements in
192                     the transaction caused an error.
193                     Used query: SELECT count(*) FROM pg_stat_activity
194                                 WHERE state IN ('idle in transaction', 'idle in transaction (aborted)');
195
196              active
197                     Number of backends in active state when backends is  exe‐
198                     cuting a queries.
199                     Used query: SELECT count(*) FROM pg_stat_activity
200                                 WHERE state = 'active';
201
202              waiting
203                     Number of backends which is currently waiting on a lock.
204                     Used query: SELECT count(*) FROM pg_stat_activity
205                                 WHERE waiting;
206
207              others
208                     Number  of  backends  with  fastpath  function call state
209                     which executes fast-path  functions  and  disabled  state
210                     which is reported only if track_activities is disabled in
211                     backend.
212                     Used query: SELECT count(*) FROM pg_stat_activity
213                                 WHERE state IN ('fastpath function call','disabled');
214
215
216       Autovacuum activity
217              Line 2-3 shows current PostgreSQL autovacuum activity.
218
219              workers
220                     Total number of autovacuum workers currently running.
221
222              max
223                     Max number of allowed autovacuum workers.
224
225              manual
226                     Total number of vacuum workers started by user.
227
228              wraparound
229                     Total number of workers  currently  running  and  perform
230                     anti wraparound vacuum.
231
232              avw_maxtime
233                     Execution  time  of the oldest autovacuum or user-started
234                     worker.
235
236
237       Statements activity
238              Line 2-4 shows statements activity. Some information provided by
239              pg_stat_statements   contrib   module  which  not  installed  by
240              default. For proper work, pg_stat_statements must  be  installed
241              into database. For more information see URLS sections below.
242
243              stmt/s
244                     Number of statements per second. This value calculated as
245                     difference between two values within 1 second interval.
246                     Used query: SELECT sum(calls) FROM pg_stat_statements;
247
248              stmt_avgtime
249                     Average queries duration in  milisecond.  Result  defines
250                     here  present the average time since the start of statis‐
251                     tics collection. Therefore, to obtain a result that  cor‐
252                     responds  to the current time, pg_stat_statements statis‐
253                     tics should be periodically reset (once a day, for  exam‐
254                     ple).
255                     Used query: SELECT (sum(total_time) / sum(calls))::numeric(6,3) AS avg_query
256                                 FROM pg_stat_statements;
257
258              xact_maxtime
259                     Execution  time of the oldest transaction (not a query or
260                     session).
261                     Used query: SELECT coalesce(date_trunc('seconds', max(now() - xact_start)), '00:00:00')
262                                 FROM pg_stat_activity;
263
264

CMDLINE WINDOW

266       Cmdline window used for displaying diagnostic  messages  or  when  need
267       additional input from user.
268
269

DBRESULT WINDOW

271       Dbresult  window  used  for displaying statistics from PostgreSQL. Here
272       pgcenter uses statistics contexts, which determines what type  of  sta‐
273       tistics will be shown.
274
275
276       pg_stat_database context
277              Statistics  from  pg_stat_database  system view which show data‐
278              base-wide statistics. Note, when new database created or  exist‐
279              ing  database  dropped,  pgcenter  resets it's own counters (not
280              postgresql statistics counters) to zero.
281              Used query: SELECT
282                              datname,
283                              xact_commit AS commit, xact_rollback AS rollback,
284                              blks_read AS reads, blks_hit AS hits,
285                              tup_returned AS returned, tup_fetched AS fetched,
286                              tup_inserted AS inserts, tup_updated AS updates, tup_deleted AS deletes,
287                              conflicts, deadlocks,
288                              temp_files AS tmp_files, temp_bytes AS tmp_bytes,
289                              blk_read_time AS read_t, blk_write_time AS write_t
290                          FROM pg_stat_database ORDER BY datname;
291
292              datname
293                     Name of this database.
294
295              commit
296                     Number of transactions per second in this  database  that
297                     have been committed.
298
299              rollback
300                     Number  of  transactions per second in this database that
301                     have been rolled back.
302
303              reads
304                     Number of disk blocks read per second in this database.
305
306              hits
307                     Number of times per second when disk  blocks  were  found
308                     already  in the buffer cache, so that a read was not nec‐
309                     essary (this only includes hits in the PostgreSQL  buffer
310                     cache, not the operating system's file system cache).
311
312              returned
313                     Number  of rows returned by queries in this database (per
314                     second).
315
316              fetched
317                     Number of rows fetched by queries in this  database  (per
318                     second).
319
320              inserts
321                     Number  of rows inserted by queries in this database (per
322                     second).
323
324              updates
325                     Number of rows updated by queries in this  database  (per
326                     second).
327
328              deletes
329                     Number  of  rows deleted by queries in this database (per
330                     second).
331
332              conflicts
333                     Number of queries per second canceled  due  to  conflicts
334                     with  recovery  in this database. Conflicts occur only on
335                     standby servers.
336
337              deadlocks
338                     Number of deadlocks detected in this database  (per  sec‐
339                     ond).
340
341              tmp_files
342                     Number  of  temporary  files  created  by queries in this
343                     database (pre second). All temporary files  are  counted,
344                     regardless  of  why the temporary file was created (e.g.,
345                     sorting or hashing), and regardless of the log_temp_files
346                     setting.
347
348              tmp_bytes
349                     Total  amount  of  data  written  to  temporary  files by
350                     queries in this  database  (per  second).  All  temporary
351                     files  are  counted, regardless of why the temporary file
352                     was created, and regardless of  the  log_temp_files  set‐
353                     ting.
354
355              read_t
356                     Time  spent  reading data file blocks by backends in this
357                     database, in milliseconds.
358
359              write_t
360                     Time spent writing data file blocks by backends  in  this
361                     database, in milliseconds.
362
363
364       pg_stat_replication context
365              Statistics  from pg_stat_replication system view which will con‐
366              tain one row per WAL sender process,  showing  statistics  about
367              replication  to  that  sender's  connected  standby server. Only
368              directly connected standbys are listed; no information is avail‐
369              able about downstream standby servers.
370              Used query: SELECT
371                           client_addr AS client, usename AS user, application_name AS name,
372                        state, sync_state AS mode,
373                        (pg_xlog_location_diff(pg_current_xlog_location(),sent_location) / 1024)::int as pending,
374                           (pg_xlog_location_diff(sent_location,write_location) / 1024)::int as write,
375                        (pg_xlog_location_diff(write_location,flush_location) / 1024)::int as flush,
376                           (pg_xlog_location_diff(flush_location,replay_location) / 1024)::int as replay,
377                        (pg_xlog_location_diff(pg_current_xlog_location(),replay_location))::int / 1024 as total_lag
378                       FROM pg_stat_replication"
379
380              client
381                     IP address of the client connected to this WAL sender. If
382                     this field is null, it indicates that the client is  con‐
383                     nected via a Unix socket on the server machine.
384
385              user
386                     Name of the user logged into this WAL sender process.
387
388              name
389                     Name  of  the  application  that is connected to this WAL
390                     sender.
391
392              state
393                     Current WAL sender state.
394
395              mode
396                     Synchronous state of this standby server.
397
398              pending
399                     Difference between XLOG current position  and  XLOG  sent
400                     position,  shows amount of XLOG in kilobytes which should
401                     be sent over network.
402
403              write
404                     Difference between XLOG  sent  position  and  XLOG  write
405                     position,  shows  amount  of XLOG in kilobytes which sent
406                     over network, but not written yet on remote host.
407
408              flush
409                     Difference between XLOG write  position  and  XLOG  flush
410                     position,  shows  amount of XLOG in kilobytes which writ‐
411                     ten, but not flushed yet on remote host.
412
413              replay
414                     Difference between XLOG flush position  and  XLOG  replay
415                     position,   shows  amount  of  XLOG  in  kilobytes  which
416                     flushed, but not replayed yet on remote host.
417
418              total_lag
419                     Difference between XLOG current position  on  the  master
420                     server  and XLOG replay position onremote host, shows the
421                     total amount of XLOG in kilobytes, the standby is behind.
422
423
424       pg_stat_tables context
425              Statistics from pg_stat_user_tables or pg_stat_all_tables  views
426              which  will  contain one row for each table in the current data‐
427              base (including TOAST tables), showing statistics about accesses
428              to  that  specific  table.  pg_stat_user_tables used by default,
429              and pgcenter can  be  switched  to  pg_stat_all_tables  using  ,
430              hotkey.
431              Used query: SELECT
432                              schemaname || '.' || relname AS relation,
433                              seq_scan, seq_tup_read AS seq_read,
434                              idx_scan, idx_tup_fetch AS idx_fetch,
435                              n_tup_ins AS inserts, n_tup_upd AS updates,
436                              n_tup_del AS deletes, n_tup_hot_upd AS hot_updates,
437                              n_live_tup AS live, n_dead_tup AS dead
438                          FROM pg_stat_user_tables ORDER BY 1;
439
440              relation
441                     Name of this table including schemaname.
442
443              seq_scan
444                     Number  of  sequential scans initiated on this table (per
445                     second).
446
447              seq_read
448                     Number of live rows fetched by sequential scans (per sec‐
449                     ond).
450
451              idx_scan
452                     Number  of  index scans initiated on this table (per sec‐
453                     ond).
454
455              idx_fetch
456                     Number of live rows fetched by index scans (per second).
457
458              inserts
459                     Number of rows inserted (per second).
460
461              updates
462                     Number of rows updated (per second).
463
464              deletes
465                     Number of rows deleted (per second).
466
467              hot_updates
468                     Number of rows HOT updated means with no  separate  index
469                     update required (per second).
470
471              live
472                     Estimated number of live rows (per second).
473
474              dead
475                     Estimated number of dead rows (per second).
476
477
478       pg_statio_tables context
479              Show    statistics   from   pg_statio_user_tables   or   pg_sta‐
480              tio_all_tables views which showing statistics about I/O on  that
481              specific  table  in the current database.  pg_statio_user_tables
482              used by  default,  and  pgcenter  can  be  switched  to  pg_sta‐
483              tio_all_tables using , hotkey.
484              Used query: SELECT
485                              schemaname ||'.'|| relname AS relation,
486                              heap_blks_read * (SELECT current_setting('block_size')::int / 1024) AS heap_read,
487                              heap_blks_hit * (SELECT current_setting('block_size')::int / 1024) AS heap_hit,
488                              idx_blks_read * (SELECT current_setting('block_size')::int / 1024) AS idx_read,
489                              idx_blks_hit * (SELECT current_setting('block_size')::int / 1024) AS idx_hit,
490                              toast_blks_read * (SELECT current_setting('block_size')::int / 1024) AS toast_read,
491                              toast_blks_hit * (SELECT current_setting('block_size')::int / 1024) AS toast_hit,
492                              tidx_blks_read * (SELECT current_setting('block_size')::int / 1024) AS tidx_read,
493                              tidx_blks_hit * (SELECT current_setting('block_size')::int / 1024) AS tidx_hit
494                          FROM pg_statio_user_tables ORDER BY 1;
495
496              relation
497                     Name of this table including schemaname.
498
499              heap_read
500                     Number  of  data  read from disk related to this table in
501                     kilobytes per second.
502
503              heap_hit
504                     Number of buffer hits in this table in kilobytes per sec‐
505                     ond.
506
507              idx_read
508                     Number  of  data  read  from disk in kilobytes per second
509                     related to all indexes on this table.
510
511              idx_hit
512                     Number of buffer hits in all indexes  on  this  table  in
513                     kilobytes per second.
514
515              toast_read
516                     Number  of  data  read  from disk in kilobytes per second
517                     from this table's TOAST table (if any).
518
519              toast_hit
520                     Number of buffer hits in this  table's  TOAST  table  (if
521                     any) in kilobytes per second.
522
523              tidx_read
524                     Number  of  data  read  from disk in kilobytes per second
525                     from this table's TOAST table indexes (if any).
526
527              tidx_hit
528                     Number of buffer hits in this table's TOAST table indexes
529                     (if any) in kilobytes per second.
530
531
532       pg_stat_indexes context
533              Show    statistics   from   pg_stat_user_indexes   and   pg_sta‐
534              tio_user_indexes    (or    pg_stat_all_indexes    and    pg_sta‐
535              tio_all_indexes).   The  pg_stat_user_indexes  view will contain
536              one row for each index in the current database, showing  statis‐
537              tics   about   accesses   to   that   specific  index.   pg_sta‐
538              tio_user_indexes view will contain one row for each index in the
539              current  database, showing statistics about I/O on that specific
540              index.
541
542              Note: Indexes can be used via either simple index scans or "bit‐
543              map" index scans. In a bitmap scan the output of several indexes
544              can be combined via AND or OR rules, so it is difficult to asso‐
545              ciate  individual  heap row fetches with specific indexes when a
546              bitmap scan is used. Therefore, a  bitmap  scan  increments  the
547              pg_stat_all_indexes.idx_tup_read  count(s)  for the index(es) it
548              uses, and  it  increments  the  pg_stat_all_tables.idx_tup_fetch
549              count    for    the    table,    but    it   does   not   affect
550              pg_stat_all_indexes.idx_tup_fetch.    The    idx_tup_read    and
551              idx_tup_fetch  counts  can  be different even without any use of
552              bitmap  scans,  because  idx_tup_read   counts   index   entries
553              retrieved  from  the  index while idx_tup_fetch counts live rows
554              fetched from the table. The latter will be less if any  dead  or
555              not-yet-committed  rows  are  fetched using the index, or if any
556              heap fetches are avoided by means of an index-only scan.
557              Used query: SELECT
558                              s.schemaname ||'.'|| s.relname as relation, s.indexrelname AS index,
559                              s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
560                              i.idx_blks_read * (SELECT current_setting('block_size')::int / 1024) AS idx_read,
561                              i.idx_blks_hit * (SELECT current_setting('block_size')::int / 1024) AS idx_hit
562                          FROM
563                              pg_stat_user_indexes s,
564                              pg_statio_user_indexes i
565                          WHERE s.indexrelid = i.indexrelid ORDER BY 1;
566
567              relation
568                     Name of the table for this index including schemaname.
569
570              index
571                     Name of this index.
572
573              idx_scan
574                     Number of index scans per second initiated on this index.
575
576              idx_tup_read
577                     Number of index entries returned per second by  scans  on
578                     this index.
579
580              idx_tup_fetch
581                     Number  of  live  table rows fetched per second by simple
582                     index scans using this index.
583
584              idx_read
585                     Number of disk blocks read per second from this index.
586
587              idx_hit
588                     Number of buffer hits per second in this index.
589
590
591       pg_tables_size context
592              Show statistics about tables sizes. For taking information about
593              size     of     relations     use     pg_stat_user_tables    (or
594              pg_stat_all_tables) view, pg_class system catalog  and  pg_rela‐
595              tion_size(), pg_total_relation_size() functions.
596              Used query: SELECT
597                      s.schemaname ||'.'|| s.relname AS relation,
598                      pg_total_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024 AS total_size,
599                      pg_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024 AS rel_size,
600                      (pg_total_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024) -
601                          (pg_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024) AS idx_size,
602                      pg_total_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024 AS total_change,
603                      pg_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024 AS rel_change,
604                      (pg_total_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024) -
605                          (pg_relation_size((s.schemaname ||'.'|| s.relname)::regclass) / 1024) AS idx_change
606                      FROM pg_stat_user_tables s, pg_class c WHERE s.relid = c.oid ORDER BY 1;
607
608              relation
609                     Name of this table including schema name.
610
611              total_size
612                     Total size of relation including indexes, in kilobytes.
613
614              rel_size
615                     Size of relation without indexes, in kilobytes.
616
617              idx_size
618                     Size of all indexes of relation, in kilobytes.
619
620              total_change
621                     Relation  and  indexes size changes in kilobytes per sec‐
622                     ond.
623
624              rel_change
625                     Only relation size changes in kilobytes per second.
626
627              idx_change
628                     Only indexes size changes in kilobytes per second.
629
630
631       pg_stat_activity context
632              Show process activity from pg_stat_activity which have  one  row
633              per  server  process, showing information related to the current
634              activity of that process. Show  process  which  runs  too  long.
635              Default  threshold  is  10  seconds,  and  can be changed with A
636              hotkey. Idle process not displaying. Note: The waiting and state
637              columns are independent. If a backend is in the active state, it
638              may or may not be waiting. If the state is active and waiting is
639              true,  it  means  that  a  query is being executed, but is being
640              blocked by a lock somewhere in the system.
641              Used query: SELECT
642                              pid, client_addr AS cl_addr, client_port AS cl_port,
643                              datname, usename, state, waiting,
644                              date_trunc('seconds', clock_timestamp() - xact_start) AS xact_age,
645                              date_trunc('seconds', clock_timestamp() - query_start) AS query_age,
646                              date_trunc('seconds', clock_timestamp() - state_change) AS change_age,
647                              query
648                          FROM pg_stat_activity
649                          WHERE ((clock_timestamp() - xact_start) > '00:00:10'::interval
650                                 OR (clock_timestamp() - query_start) > '00:00:10'::interval)
651                          AND state <> 'idle' AND pid <> pg_backend_pid()
652                          ORDER BY COALESCE(xact_start, query_start);
653
654              pid
655                     Process ID of this backend.
656
657              cl_addr
658                     IP address of the client connected to  this  backend.  If
659                     this  field  is null, it indicates either that the client
660                     is connected via a Unix socket on the server  machine  or
661                     that this is an internal process such as autovacuum.
662
663              cl_port
664                     TCP  port  number that the client is using for communica‐
665                     tion with this backend, or -1 if a Unix socket is used.
666
667              datname
668                     Name of the database this backend is connected to.
669
670              usename
671                     Name of the user logged into this backend.
672
673              state
674                     Current overall state of this  backend.  Possible  values
675                     are:
676
677                     active
678                            The backend is executing a query.
679
680                     idle
681                            The backend is waiting for a new client command.
682
683                     idle in transaction
684                            The  backend  is in a transaction, but is not cur‐
685                            rently executing a query.
686
687                     idle in transaction (aborted)
688                            This state is  similar  to  idle  in  transaction,
689                            except  one  of  the statements in the transaction
690                            caused an error.
691
692                     fastpath function call
693                            The backend is executing a fast-path function.
694
695                     disabled
696                            This state is reported if track_activities is dis‐
697                            abled in this backend.
698
699              waiting
700                     True if this backend is currently waiting on a lock.
701
702              xact_age
703                     Age  of  transaction  started  within current process, or
704                     null if no transaction is active. If the current query is
705                     the first of its transaction, this column is equal to the
706                     query_age column.
707
708              query_age
709                     Age of currently active query, or if state is not active,
710                     when the last query was started.
711
712              change_age
713                     Age since when the state was last changed.
714
715              query
716                     Text  of  this  backend's  most recent query. If state is
717                     active this field shows the currently executing query. In
718                     all  other  states, it shows the last query that was exe‐
719                     cuted.
720
721
722       pg_stat_functions context
723              Show statistics from pg_stat_user_function  view  which  contain
724              one row for each tracked function, showing statistics about exe‐
725              cutions of that function. The track_functions parameter in post‐
726              gresql.conf controls exactly which functions are tracked.
727              Used query: SELECT
728                              funcid, schemaname ||'.'||funcname AS function,
729                              calls AS total_calls, calls AS calls,
730                              date_trunc('seconds', total_time / 1000 * '1 second'::interval) AS total_t,
731                              date_trunc('seconds', self_time / 1000 * '1 second'::interval) AS self_t,
732                              round((total_time / calls)::numeric, 4) AS avg_t,
733                              round((self_time / calls)::numeric, 4) AS avg_self_t
734                          FROM pg_stat_user_functions ORDER BY x DESC;
735
736              funcid
737                     Unique OID of a function.
738
739              function
740                     Name of this function, including schema name.
741
742              total_calls
743                     Number of times this function has been called.
744
745              calls
746                     Number of times this function has been called per second.
747
748              total_t
749                     Total time spent in this function and all other functions
750                     called by it.
751
752              self_t
753                     Total time spent in this function itself,  not  including
754                     other functions called by it.
755
756              avg_t
757                     Average  time  spent in this function and all other func‐
758                     tions called by it, in milliseconds.
759
760              avg_self_t
761                     Average time spent in this function itself, not including
762                     other functions called by it, in milliseconds.
763
764
765       pg_stat_statements_timing context
766              Show  timing  statistics  from  pg_stat_statements  module which
767              tracking execution statistics of all SQL statements executed  by
768              a server. The module must be loaded by adding pg_stat_statements
769              to shared_preload_libraries in postgresql.conf.  This means that
770              a  server  restart  is  needed to add or remove the module. When
771              module loaded, pg_stat_statement extension must be installed  in
772              target  database.  For  more  information  see  http://www.post
773              gresql.org/docs/9.4/static/pgstatstatements.html.   Additionally
774              used  pg_authid and pg_database views. Different versions of the
775              PostgreSQL may have different number of fields.
776              Used query: SELECT
777                      a.rolname AS user, d.datname AS database,
778                      date_trunc('seconds', round(sum(p.total_time)) / 1000 * '1 second'::interval) AS t_all_t,
779                      date_trunc('seconds', round(sum(p.blk_read_time)) / 1000 * '1 second'::interval) AS t_read_t,
780                      date_trunc('seconds', round(sum(p.blk_write_time)) / 1000 * '1 second'::interval) AS t_write_t,
781                      date_trunc('seconds', round((sum(p.total_time) - (sum(p.blk_read_time) + sum(p.blk_write_time)))) / 1000 * '1 second'::interval) AS tot_cpu_t,
782                      round(sum(p.total_time)) AS all_t,
783                      round(sum(p.blk_read_time)) AS read_t,
784                      round(sum(p.blk_write_time)) AS write_t,
785                      round((sum(p.total_time) - (sum(p.blk_read_time) + sum(p.blk_write_time)))) AS cpu_t,
786                      sum(p.calls) AS calls,
787                      left(md5(d.datname || a.rolname || p.query ), 10) AS queryid,
788                      regexp_replace(
789                      regexp_replace(
790                      regexp_replace(
791                      regexp_replace(
792                      regexp_replace(p.query,
793                          E'\\?(::[a-zA-Z_]+)?( *, *\\?(::[a-zA-Z_]+)?)+', '?', 'g'),
794                          E'\\$[0-9]+(::[a-zA-Z_]+)?( *, *\\$[0-9]+(::[a-zA-Z_]+)?)*', '$N', 'g'),
795                          E'--.*$', '', 'ng'),
796                          E'/\\*.*?\\*\/', '', 'g'),
797                          E'\\s+', ' ', 'g') AS query
798                  FROM pg_stat_statements p
799                  JOIN pg_authid a ON a.oid=p.userid
800                  JOIN pg_database d ON d.oid=p.dbid
801                  WHERE d.datname != 'postgres' AND calls > 50
802                  GROUP BY a.rolname, d.datname, query;
803
804              user
805                     User who executed the statement.
806
807              database
808                     Database in which the statement was executed.
809
810              t_all_t
811                     Total time spent in the statement since last stats reset.
812
813              t_read_t
814                     Total  time  the  statement  spent  reading  blocks   (if
815                     track_io_timing  is  enabled,  otherwise zero) since last
816                     stats reset.
817
818              t_write_t
819                     Total  time  the  statement  spent  writing  blocks   (if
820                     track_io_timing  is  enabled,  otherwise zero) since last
821                     stats reset.
822
823              t_cpu_t
824                     Total  time  the  statement  spent  in  CPU  cycling  (if
825                     track_io_timing  is  enabled,  otherwise  equals total_t)
826                     since last stats reset.
827
828              all_t
829                     Current time spent in the statement in ms.
830
831              read_t
832                     Current time  the  statement  spent  reading  blocks  (if
833                     track_io_timing is enabled, otherwise zero) in ms.
834
835              write_t
836                     Total   time  the  statement  spent  writing  blocks  (if
837                     track_io_timing is enabled, otherwise zero) in ms.
838
839              cpu_t
840                     Total time  the  statement  spent  in  CPU  cycling,  (if
841                     track_io_timing  is enabled, otherwise equals total_t) in
842                     ms.
843
844              calls
845                     Current number of query calls.
846
847              queryid
848                     Query ID generated with MD5 hash function  and  truncated
849                     to  10  symbols. Hash based on username, dbname and query
850                     text.
851
852              query
853                     Text of a representative statement.
854
855
856       pg_stat_statements_general context
857              Show general statistics about rows and calls from pg_stat_state‐
858              ments  module  which  tracking  execution  statistics of all SQL
859              statements executed by a server. The module must  be  loaded  by
860              adding  pg_stat_statements  to shared_preload_libraries in post‐
861              gresql.conf.  This means that a server restart is needed to  add
862              or  remove  the  module.  When  module loaded, pg_stat_statement
863              extension must be installed in target database. For more  infor‐
864              mation   see   http://www.postgresql.org/docs/9.4/static/pgstat
865              statements.html. Additionally  used  pg_authid  and  pg_database
866              views.
867              Used query: SELECT
868                      a.rolname AS user, d.datname AS database,
869                      sum(p.calls) AS t_calls, sum(p.rows) as t_rows,
870                      sum(p.calls) AS calls, sum(p.rows) as rows,
871                      left(md5(d.datname || a.rolname || p.query ), 10) AS queryid,
872                      regexp_replace(
873                      regexp_replace(
874                      regexp_replace(
875                      regexp_replace(
876                      regexp_replace(p.query,
877                          E'\\?(::[a-zA-Z_]+)?( *, *\\?(::[a-zA-Z_]+)?)+', '?', 'g'),
878                          E'\\$[0-9]+(::[a-zA-Z_]+)?( *, *\\$[0-9]+(::[a-zA-Z_]+)?)*', '$N', 'g'),
879                          E'--.*$', '', 'ng'),
880                          E'/\\*.*?\\*\/', '', 'g'),
881                          E'\\s+', ' ', 'g') AS query
882                  FROM pg_stat_statements p
883                  JOIN pg_authid a ON a.oid=p.userid
884                  JOIN pg_database d ON d.oid=p.dbid
885                  WHERE d.datname != 'postgres' AND calls > 50
886                  GROUP BY a.rolname, d.datname, query;
887
888              user
889                     User who executed the statement.
890
891              database
892                     Database in which the statement was executed.
893
894              t_calls
895                     Total number of times the statement was executed.
896
897              t_rows
898                     Total  number of rows retrieved or affected by the state‐
899                     ment.
900
901              calls
902                     Number of times the statement was executed per second.
903
904              rows
905                     Number of rows retrieved or affected by the statement per
906                     second.
907
908              queryid
909                     Query  ID  generated with MD5 hash function and truncated
910                     to 10 symbols. Hash based on username, dbname  and  query
911                     text.
912
913              query
914                     Text of a representative statement
915
916
917       pg_stat_statements_io context
918              Show  statistics  about  buffer input/output from pg_stat_state‐
919              ments module which tracking  execution  statistics  of  all  SQL
920              statements  executed  by  a server. The module must be loaded by
921              adding pg_stat_statements to shared_preload_libraries  in  post‐
922              gresql.conf.   This means that a server restart is needed to add
923              or remove the  module.  When  module  loaded,  pg_stat_statement
924              extension  must be installed in target database. For more infor‐
925              mation   see   http://www.postgresql.org/docs/9.4/static/pgstat
926              statements.html.  Additionally  used  pg_authid  and pg_database
927              views.
928              Used query: SELECT
929                      a.rolname AS user, d.datname AS database,
930                      (sum(p.shared_blks_hit) + sum(p.local_blks_hit))
931                          * (SELECT current_setting('block_size')::int / 1024) as t_hits,
932                      (sum(p.shared_blks_read) + sum(p.local_blks_read))
933                          * (SELECT current_setting('block_size')::int / 1024) as t_reads,
934                      (sum(p.shared_blks_dirtied) + sum(p.local_blks_dirtied))
935                          * (SELECT current_setting('block_size')::int / 1024) as t_dirtied,
936                      (sum(p.shared_blks_written) + sum(p.local_blks_written))
937                          * (SELECT current_setting('block_size')::int / 1024) as t_written,
938                      (sum(p.shared_blks_hit) + sum(p.local_blks_hit))
939                          * (SELECT current_setting('block_size')::int / 1024) as hits,
940                      (sum(p.shared_blks_read) + sum(p.local_blks_read))
941                          * (SELECT current_setting('block_size')::int / 1024) as reads,
942                      (sum(p.shared_blks_dirtied) + sum(p.local_blks_dirtied))
943                          * (SELECT current_setting('block_size')::int / 1024) as dirtied,
944                      (sum(p.shared_blks_written) + sum(p.local_blks_written))
945                          * (SELECT current_setting('block_size')::int / 1024) as written,
946                      sum(p.calls) AS calls,
947                      left(md5(d.datname || a.rolname || p.query ), 10) AS queryid,
948                      regexp_replace(
949                      regexp_replace(
950                      regexp_replace(
951                      regexp_replace(
952                      regexp_replace(p.query,
953                          E'\\?(::[a-zA-Z_]+)?( *, *\\?(::[a-zA-Z_]+)?)+', '?', 'g'),
954                          E'\\$[0-9]+(::[a-zA-Z_]+)?( *, *\\$[0-9]+(::[a-zA-Z_]+)?)*', '$N', 'g'),
955                          E'--.*$', '', 'ng'),
956                          E'/\\*.*?\\*\/', '', 'g'),
957                          E'\\s+', ' ', 'g') AS query
958                  FROM pg_stat_statements p
959                  JOIN pg_authid a ON a.oid=p.userid
960                  JOIN pg_database d ON d.oid=p.dbid
961                  WHERE d.datname != 'postgres' AND calls > 50
962                  GROUP BY a.rolname, d.datname, query;
963
964              user
965                     User who executed the statement.
966
967              database
968                     Database in which the statement was executed.
969
970              t_hits
971                     Total amount of shared and local data hits from cache  by
972                     the statement since stats reset, in Kbytes.
973
974              t_reads
975                     Total  amount of shared and local data read by the state‐
976                     ment since stats reset, in Kbytes.
977
978              t_dirtied
979                     Total amount of shared and  local  data  dirtied  by  the
980                     statement since stats reset, in Kbytes.
981
982              t_written
983                     Total  amount  of  shared  and  local data written by the
984                     statement since stats reset, in Kbytes.
985
986              hits
987                     Amount of shared and local data hits from  cache  by  the
988                     statement per interval (default 1 second), in Kbytes.
989
990              reads
991                     Amount of shared and local data read by the statement per
992                     interval (default 1 second), in Kbytes.
993
994              dirtied
995                     Amount of shared and local data dirtied by the  statement
996                     per interval (default 1 second), in Kbytes.
997
998              written
999                     Amount  of shared and local data written by the statement
1000                     per interval (default 1 second), in Kbytes.
1001
1002              calls
1003                     Number of rows retrieved or affected by the statement per
1004                     interval.
1005
1006              queryid
1007                     Query  ID  generated with MD5 hash function and truncated
1008                     to 10 symbols. Hash based on username, dbname  and  query
1009                     text.
1010
1011              query
1012                     Text of a representative statement
1013
1014
1015       pg_stat_statements_temp context
1016              Show  statistics  about buffers input/output related with tempo‐
1017              rary buffers. Stats read from  pg_stat_statements  module  which
1018              tracking  execution statistics of all SQL statements executed by
1019              a server. The module must be loaded by adding pg_stat_statements
1020              to shared_preload_libraries in postgresql.conf.  This means that
1021              a server restart is needed to add or  remove  the  module.  When
1022              module  loaded, pg_stat_statement extension must be installed in
1023              target  database.  For  more  information  see  http://www.post
1024              gresql.org/docs/9.4/static/pgstatstatements.html.   Additionally
1025              used pg_authid and pg_database views.
1026              Used query: SELECT
1027                      a.rolname AS user, d.datname AS database,
1028                      sum(p.temp_blks_read)
1029                          * (SELECT current_setting('block_size')::int / 1024) as t_tmp_read,
1030                      sum(p.temp_blks_written)
1031                          * (SELECT current_setting('block_size')::int / 1024) as t_tmp_write,
1032                      sum(p.temp_blks_read)
1033                          * (SELECT current_setting('block_size')::int / 1024) as tmp_read,
1034                      sum(p.temp_blks_written)
1035                          * (SELECT current_setting('block_size')::int / 1024) as tmp_write,
1036                      sum(p.calls) AS calls,
1037                      left(md5(d.datname || a.rolname || p.query ), 10) AS queryid,
1038                      regexp_replace(
1039                      regexp_replace(
1040                      regexp_replace(
1041                      regexp_replace(
1042                      regexp_replace(p.query,
1043                          E'\\?(::[a-zA-Z_]+)?( *, *\\?(::[a-zA-Z_]+)?)+', '?', 'g'),
1044                          E'\\$[0-9]+(::[a-zA-Z_]+)?( *, *\\$[0-9]+(::[a-zA-Z_]+)?)*', '$N', 'g'),
1045                          E'--.*$', '', 'ng'),
1046                          E'/\\*.*?\\*\/', '', 'g'),
1047                          E'\\s+', ' ', 'g') AS query
1048                  FROM pg_stat_statements p
1049                  JOIN pg_authid a ON a.oid=p.userid
1050                  JOIN pg_database d ON d.oid=p.dbid
1051                  WHERE d.datname != 'postgres' AND calls > 50
1052                  GROUP BY a.rolname, d.datname, query;
1053
1054              user
1055                     User who executed the statement.
1056
1057              database
1058                     Database in which the statement was executed.
1059
1060              t_tmp_read
1061                     Total amount of temp data read  by  the  statement  since
1062                     last stats reset, in Kbytes.
1063
1064              t_tmp_write
1065                     Total  amount  of temp data written by the statement sice
1066                     last stats reset, in Kbytes.
1067
1068              tmp_read
1069                     Amount of temp data read by the statement  per  interval,
1070                     in Kbytes.
1071
1072              tmp_write
1073                     Amount  of  temp data written by the statement per inter‐
1074                     val, in Kbytes.
1075
1076              calls
1077                     Number of times the statement was executed per second.
1078
1079              queryid
1080                     Query ID generated with MD5 hash function  and  truncated
1081                     to  10  symbols. Hash based on username, dbname and query
1082                     text.
1083
1084              query
1085                     Text of a representative statement
1086
1087
1088       pg_stat_statements_local context
1089              Show statistics about  local  input/output  (backends  that  use
1090              local  memory  and  not  shared buffers) from pg_stat_statements
1091              module which tracking execution statistics of all SQL statements
1092              executed  by  a  server.  The  module  must  be loaded by adding
1093              pg_stat_statements   to   shared_preload_libraries   in    post‐
1094              gresql.conf.   This means that a server restart is needed to add
1095              or remove the  module.  When  module  loaded,  pg_stat_statement
1096              extension  must be installed in target database. For more infor‐
1097              mation   see   http://www.postgresql.org/docs/9.4/static/pgstat
1098              statements.html.  Additionally  used  pg_authid  and pg_database
1099              views.
1100              Used query: SELECT
1101                      a.rolname AS user, d.datname AS database,
1102                      (sum(p.local_blks_hit)) * (SELECT current_setting('block_size')::int / 1024) as t_lo_hits,
1103                      (sum(p.local_blks_read)) * (SELECT current_setting('block_size')::int / 1024) as t_lo_reads,
1104                      (sum(p.local_blks_dirtied)) * (SELECT current_setting('block_size')::int / 1024) as t_lo_dirtied,
1105                      (sum(p.local_blks_written)) * (SELECT current_setting('block_size')::int / 1024) as t_lo_written,
1106                      (sum(p.local_blks_hit)) * (SELECT current_setting('block_size')::int / 1024) as lo_hits,
1107                      (sum(p.local_blks_read)) * (SELECT current_setting('block_size')::int / 1024) as lo_reads,
1108                      (sum(p.local_blks_dirtied)) * (SELECT current_setting('block_size')::int / 1024) as lo_dirtied,
1109                      (sum(p.local_blks_written)) * (SELECT current_setting('block_size')::int / 1024) as lo_written,
1110                      sum(p.calls) AS calls,
1111                      left(md5(d.datname || a.rolname || p.query ), 10) AS queryid,
1112                      regexp_replace(
1113                      regexp_replace(
1114                      regexp_replace(
1115                      regexp_replace(
1116                      regexp_replace(p.query,
1117                          E'\\?(::[a-zA-Z_]+)?( *, *\\?(::[a-zA-Z_]+)?)+', '?', 'g'),
1118                          E'\\$[0-9]+(::[a-zA-Z_]+)?( *, *\\$[0-9]+(::[a-zA-Z_]+)?)*', '$N', 'g'),
1119                          E'--.*$', '', 'ng'),
1120                          E'/\\*.*?\\*\/', '', 'g'),
1121                          E'\\s+', ' ', 'g') AS query
1122                  FROM pg_stat_statements p
1123                  JOIN pg_authid a ON a.oid=p.userid
1124                  JOIN pg_database d ON d.oid=p.dbid
1125                  WHERE d.datname != 'postgres' AND calls > 50
1126                  GROUP BY a.rolname, d.datname, query;
1127
1128              user
1129                     User who executed the statement.
1130
1131              database
1132                     Database in which the statement was executed.
1133
1134              t_lo_hits
1135                     Total amount of data hits from backends local  memory  by
1136                     the statement since stats reset, in Kbytes.
1137
1138              t_lo_reads
1139                     Total  amount  of data read from backends local memory by
1140                     the statement since stats reset, in Kbytes.
1141
1142              t_lo_dirtied
1143                     Total amount of data dirtied in backends local memory  by
1144                     the statement since stats reset, in Kbytes.
1145
1146              t_lo_written
1147                     Total  amount of data written to backends local memory by
1148                     the statement since stats reset, in Kbytes.
1149
1150              lo_hits
1151                     Amount of data hits from backends  local  memory  by  the
1152                     statement per interval (default 1 second), in Kbytes.
1153
1154              lo_reads
1155                     Amount  of  data  read  from backends local memory by the
1156                     statement per interval (default 1 second), in Kbytes.
1157
1158              lo_dirtied
1159                     Amount of data dirtied in backends local  memory  by  the
1160                     statement per interval (default 1 second), in Kbytes.
1161
1162              lo_written
1163                     Amount  of  data  written to backends local memory by the
1164                     statement per interval (default 1 second), in Kbytes.
1165
1166              calls
1167                     Number of rows retrieved or affected by the statement per
1168                     interval.
1169
1170              queryid
1171                     Query  ID  generated with MD5 hash function and truncated
1172                     to 10 symbols. Hash based on username, dbname  and  query
1173                     text.
1174
1175              query
1176                     Text of a representative statement
1177
1178
1179       pg_stat_progress_vacuum context
1180              Whenever  VACUUM  is  running,  the pg_stat_progress_vacuum view
1181              will contain one row  for  each  backend  (including  autovacuum
1182              worker  processes) that is currently vacuuming. The tables below
1183              describe the information  that  will  be  reported  and  provide
1184              information about how to interpret it. Progress reporting is not
1185              currently supported for VACUUM FULL and backends running  VACUUM
1186              FULL will not be listed in this view. This statistics are avail‐
1187              able  since   PostgreSQL   9.6,   for   more   information   see
1188              https://www.postgresql.org/docs/9.6/static/progress-report
1189              ing.html. Additionally used pg_stat_activity view.
1190              Used query:
1191                  SELECT
1192                      a.pid,
1193                      date_trunc('seconds', clock_timestamp() - xact_start) AS xact_age,
1194                      v.datname, v.relid::regclass AS relation,
1195                      a.state, v.phase,
1196                      v.heap_blks_total * (SELECT current_setting('block_size')::int / 1024) AS total,
1197                      v.heap_blks_scanned * (SELECT current_setting('block_size')::int / 1024) AS scanned,
1198                      v.heap_blks_vacuumed * (SELECT current_setting('block_size')::int / 1024) AS vacuumed,
1199                      a.wait_event_type AS wait_etype, a.wait_event,
1200                      a.query
1201                  FROM pg_stat_progress_vacuum v
1202                  JOIN pg_stat_activity a ON v.pid = a.pid
1203                  ORDER BY COALESCE(a.xact_start, a.query_start)
1204
1205              pid
1206                     Process ID of vacuum.
1207
1208              xact_age
1209                     Age of vacuum task.
1210
1211              datname
1212                     Database name where vacuum worker is connected.
1213
1214              state
1215                     Postgres backend  state  (see  pg_stat_activity  backends
1216                     states).
1217
1218              phase
1219                     Current  processing  phase of vacuum. See VACUUM phases -
1220                     https://www.postgresql.org/docs/9.6/static/progress-
1221                     reporting.html#VACUUM-PHASES.
1222
1223              total
1224                     Total  size  of  the  table  in  Kbytes.  This  number is
1225                     reported as of the beginning of the scan; size  increased
1226                     during  vacuum  will  not  be (and need not be) shown and
1227                     this space will not be visited by this VACUUM.
1228
1229              scanned
1230                     Size of table in Kbytes that is scanned. Because the vis‐
1231                     ibility  map  is used to optimize scans, some blocks will
1232                     be  skipped  without  inspection;  skipped   blocks   are
1233                     included  in this total, so that this number will eventu‐
1234                     ally become equal to total when the vacuum  is  complete.
1235                     This  counter  only  advances  when the phase is scanning
1236                     heap.
1237
1238              vacuumed
1239                     Size of table in Kbytes that is vacuumed. Unless the  ta‐
1240                     ble  has  no indexes, this counter only advances when the
1241                     phase is vacuuming heap.  Blocks  that  contain  no  dead
1242                     tuples  are  skipped,  so  the counter may sometimes skip
1243                     forward in large increments.
1244
1245              wait_etype
1246                     The type of event for which the backend  is  waiting,  if
1247                     any; otherwise NULL.
1248
1249              wait_event
1250                     Wait  event  name if backend is currently waiting, other‐
1251                     wise NULL.
1252
1253              query
1254                     Text of a representative statement.
1255
1256

SUBSCREENS

1258       Subscreens it's a additional screens which presents auxilary data which
1259       not  directly  related  with  the PostgreSQL but may be useful in trou‐
1260       bleshoot.
1261
1262
1263       Logtail subscreen
1264              Opens logfile in subscreen and  tail  this  log.  Used  only  if
1265              pgcenter  and PostgreSQL running on the same host. All multiline
1266              log entries truncates to end of line.  Requires  database  supe‐
1267              ruser privileges.
1268
1269
1270       iostat subscreen
1271              Report  input/output  statistics for devices and partitions. The
1272              iostat subscreen is  used  for  monitoring  system  input/output
1273              device  loading  by observing the time the devices are active in
1274              relation to their average transfer rates. The first report  gen‐
1275              erated  by  the  iostat subscreen provides statistics concerning
1276              the time since the system was booted.   Each  subsequent  report
1277              covers the time since the previous report. Iostat subscreen sim‐
1278              ilar to iostat utility from sysstat package and  /proc/diskstats
1279              interface.  For  the proper iostat work /proc filesystem must be
1280              mounted for iostat to work. Kernels older  than  2.6.x  are  not
1281              supported.
1282
1283              Device
1284                     Block device registered in a system.
1285
1286              rrqm/s
1287                     The  number  of read requests merged per second that were
1288                     queued to the device.
1289
1290              wrqm/s
1291                     The number of write requests merged per second that  were
1292                     queued to the device.
1293
1294              r/s
1295                     The  number (after merges) of read requests completed per
1296                     second for the device.
1297
1298              w/s
1299                     The number (after merges) of write requests completed per
1300                     second for the device.
1301
1302              rMB/s
1303                     The number of megabytes read from the device per second.
1304
1305              wMB/s
1306                     The number of megabytes written to the device per second.
1307
1308              avgrq-sz
1309                     The  average  size (in sectors) of the requests that were
1310                     issued to the device.
1311
1312              avgqu-sz
1313                     The average queue length of the requests that were issued
1314                     to the device.
1315
1316              await
1317                     The  average  time  (in  milliseconds)  for  I/O requests
1318                     issued to the device to be served. This includes the time
1319                     spent by the requests in queue and the time spent servic‐
1320                     ing them.
1321
1322              r_await
1323                     The average time  (in  milliseconds)  for  read  requests
1324                     issued to the device to be served. This includes the time
1325                     spent by the requests in queue and the time spent servic‐
1326                     ing them.
1327
1328              w_await
1329                     The  average  time  (in  milliseconds) for write requests
1330                     issued to the device to be served. This includes the time
1331                     spent by the requests in queue and the time spent servic‐
1332                     ing them.
1333
1334              %util
1335                     Percentage of elapsed time during which I/O requests were
1336                     issued  to  the  device  (bandwidth  utilization  for the
1337                     device). Device saturation  occurs  when  this  value  is
1338                     close to 100% for devices serving requests serially.  But
1339                     for devices serving requests in parallel,  such  as  RAID
1340                     arrays  and  modern  SSDs,  this  number does not reflect
1341                     their performance limits.
1342
1343
1344       nicstat subscreen
1345              Print network traffic statistics for all network  cards  (NICs),
1346              including  packets,  kilobytes  per second, average packet sizes
1347              and more. Nicstat subscreen similar to nicstat utility  and  use
1348              /proc/net/dev interface.
1349
1350              Interface
1351                     Network interface registered in a system.
1352
1353              rMbps
1354                     Megabits per interval read (received).
1355
1356              wMbps
1357                     Megabits per interval written (transmitted).
1358
1359              rPk/s
1360                     Packets per interval read (received).
1361
1362              wPk/s
1363                     Packets per interval written (transmitted).
1364
1365              rAvs
1366                     Average size of packets read (received).
1367
1368              wAvs
1369                     Average size of packets written (transmitted).
1370
1371              IErr
1372                     Packets received that could not be processed because they
1373                     contained errors.
1374
1375              OErr
1376                     Packets that were not successfully transmitted because of
1377                     errors.
1378
1379              Coll
1380                     Ethernet collisions during transmit.
1381
1382              Sat
1383                     Saturation. This the number of errors/second seen for the
1384                     interface - an indicator the interface may be approaching
1385                     saturation.  This  statistic is combined from a number of
1386                     kernel statistics.
1387
1388              %rUtil %wUtil
1389                     Percentage  utilization  for  bytes  read  and   written,
1390                     respectively.
1391
1392              %Util
1393                     Percentage utilization of the interface.  For full-duplex
1394                     interfaces, this is the greater of rMB/s or  wMB/s  as  a
1395                     percentage of the interface speed. For half-duplex inter‐
1396                     faces, rMB/s and wMB/s are summed.
1397
1398

INTERACTIVE COMMANDS

1400       The global interactive commands are always available main program mode
1401
1402          1..8  :Switch screen toggle
1403              Switch between already opened screens. Number of screens limited
1404              by eight.
1405
1406          d  :pg_stat_database toggle
1407              Show  statistics  from  pg_stat_database  view.  This statistics
1408              includes per database info about commits/rollbacks, returned and
1409              fetched      tuples,      write      operations      such     as
1410              inserts/deletes/updates, abnormal situations like conflicts  and
1411              deadlocks,  info about temporary files usage and read/write tim‐
1412              ings.
1413
1414          r  :pg_stat_replication toggle
1415              Show statistics from pg_stat_replication view. Statistics  about
1416              streaming  replication  connections,  includes information about
1417              connected standbys and amount of data which  is  sent,  written,
1418              flushed  or  replayed  on  standby  servers. Also available info
1419              about replication lag.
1420
1421          t  :pg_stat_tables toggle
1422              Show statistics from pg_stat_user_tables (or pg_stat_all_tables)
1423              view  about  accesses  to that specific tables. Includes sequen‐
1424              tial/index scans,  number  of  inserted/updated/deleted  tuples,
1425              number  of live/dead tuples. Useful for determine current tables
1426              workload. By default, displayed  only  user  tables,  displaying
1427              system tables can be enabled by pressing V.
1428
1429          T  :pg_statio_tables toggle
1430              Show   statistics   from   pg_statio_user_tables   (or   pg_sta‐
1431              tio_all_tables) view about I/O on tables in  the  current  data‐
1432              base.  Includes  info  about  reads from disk and shared buffers
1433              related to tables, their indexes or TOAST. Useful for  determine
1434              current tables workload. By default, displayed only user tables,
1435              displaying system tables can be enabled by pressing V.
1436
1437          i  :pg_stat_indexes toggle
1438              Show   statistics   from   pg_stat_user_indexes   and    pg_sta‐
1439              tio_user_indexes    (or    pg_stat_all_indexes    and    pg_sta‐
1440              tio_all_indexes) views which showing statistics  about  accesses
1441              to that specific indexes and their I/O. Includes info about num‐
1442              ber of index scans, number of readen and  fetched  tuples,  data
1443              read  from  disk  or  shared buffers. By default, displayed only
1444              user indexes, displaying system indexes can be enabled by press‐
1445              ing V. Useful for determine index usage.
1446
1447          s  :pg_tables_size toggle
1448              Show statistics about tables and their indexes sizes. For taking
1449              information about size of relations use pg_stat_user_tables  (or
1450              pg_stat_all_tables)  view,  pg_class system catalog and pg_rela‐
1451              tion_size(), pg_total_relation_size() functions.
1452
1453          a  :pg_stat_activity toggle
1454              Show statistics about long queries from  pg_stat_activity  view.
1455              Show  information  about client (address, port, name, database),
1456              text of query and his state and age. Default age threshold is 10
1457              seconds, and can be changed with A hotkey.
1458
1459          f  :pg_stat_functions toggle
1460              Show  statistics  from pg_stat_user_functions view about tracked
1461              functions and their executions, such as number of calls and exe‐
1462              cution  time.  The  track_functions parameter in postgresql.conf
1463              controls exactly which functions are tracked.
1464
1465          v  :pg_stat_progress_vacuum toggle
1466              Show statistics from pg_stat_progress_vacuum view  about  vacuum
1467              execution progress. Available since PostgreSQL 9.6.
1468
1469          x  :Switch to next pg_stat_statements screen toggle
1470              Switches  between  pg_stat_statements screens: timings, general,
1471              input/output, temporary input/output, local input/output.
1472
1473          X  :Show pg_stat_statements menu toggle
1474              Open pg_stat_statements menu and allow to choose  pg_stat_state‐
1475              ments screen without switching.
1476
1477          E  :Edit configuration files menu toggle
1478              Open  configuration  files  menu  and edit specific config. Sup‐
1479              ported editing of  postgresql.conf,  pg_hba.conf,  pg_ident.conf
1480              and  recovery.conf. Used only if pgcenter and PostgreSQL running
1481              on the same host. Use $EDITOR  environment  variable  or  vi  by
1482              default. Requires database superuser privileges.
1483
1484          C  :Show current configuration toggle
1485              Show current PostgreSQL configuration from pg_settings view. For
1486              viewing all parameters requires database superuser privileges.
1487
1488          R  :Reload toggle
1489              Reload PostgreSQL service for apply configuration  changes.  The
1490              pgcenter  used pg_reload_conf() function for reloading. Requires
1491              database superuser privileges.
1492
1493          p  :Start psql session toggle
1494              Start psql session with current connection settings.
1495
1496          B  :Open iostat subscreen toggle
1497              Open subscreen with iostat which reporting input/output  statis‐
1498              tics  for  devices  and partitions. Show statistics from current
1499              host.
1500
1501          I  :Open nicstat subscreen toggle
1502              Open subscreen with nicstat which reporting  network  statistics
1503              for  all  network cards (NICs), including packets, kilobytes per
1504              second, average packet sizes and  more..  Show  statistics  from
1505              current host.
1506
1507          L  :Open logtail subscreen toggle
1508              Open  subscreen  and  tail postgresql log. Used only if pgcenter
1509              and PostgreSQL running on the same host. Requires database supe‐
1510              ruser privileges.
1511
1512          l  :Open log file toggle
1513              Open logfile with pager. Use $PAGER environment variable or less
1514              by default. Used only if pgcenter and PostgreSQL running on  the
1515              same host. Requires database superuser privileges.
1516
1517          N  :Open new connection toggle
1518              Open new screen with new connection to PostgreSQL. Specify host,
1519              port, user, dbname options and open new connection.
1520
1521          Ctrl+D  :Close current connection toggle
1522              Close current screen and connection.
1523
1524          W  :Write connection options toggle
1525              Write connection  settings  into  config  file.  By  default  is
1526              ~/.pgcenterrc  is  used. If --file=FILENAME argument was used at
1527              pgcenter start, connection settings will be written at specified
1528              file.
1529
1530          Left, Right  :Change sort toggle
1531              Change column which is used for sort.
1532
1533          /  :Change sort order toggle
1534              Change  sort  order,  descent  or  ascent. Descent order used by
1535              default.
1536
1537          F  :Set filtration toggle
1538              Set filter pattern for a column, or reset filtration with  empty
1539              value.  Note, filter patterns are remebered between tab and con‐
1540              text switches. Filtered column marked with * symbol. No  filtra‐
1541              tion by default.
1542
1543          -  :Cancel backend toggle
1544              Cancel  query  execution  using backend pid with pg_cancel_back‐
1545              end() function. This function allowed only when pg_stat_activity
1546              screen  enabled  by a hotkey. Requires database superuser privi‐
1547              leges.
1548
1549          _  :Terminate backend toggle
1550              Terminate process using backend pid with  pg_terminate_backend()
1551              function.  This  function  allowed  only  when  pg_stat_activity
1552              screen enabled by a hotkey. Requires database  superuser  privi‐
1553              leges.
1554
1555          Del  :Cancel group of backends toggle
1556              Cancel  queries  execution  in  group  of  backends with pg_can‐
1557              cel_backend() function. Group of backends determined using  mask
1558              which  specified by n toggle. Note, queries are canceled without
1559              confirmation. This function allowed only  when  pg_stat_activity
1560              screen  enabled by a hotkey. Unprivileged users can cancel their
1561              own queries. Superusers can cancel  any  queries.  Backend  pids
1562              which  queries will be canceled are selected from pg_stat_activ‐
1563              ity view, select condition  determined  by  mask  which  can  be
1564              defined with n hotkey.
1565
1566          Shift+Del  :Terminate group of backends toggle
1567              Terminate  queries execution in group of backends with pg_termi‐
1568              nate_backend() function. Group of backends determined using mask
1569              which specified by n toggle. Note, backends are terminated with‐
1570              out confirmation. This function allowed only when pg_stat_activ‐
1571              ity screen enabled by a hotkey. Unprivileged users can terminate
1572              their own backends. Superusers can terminate any backends. Back‐
1573              end   pids   which   will   be   terminated  are  selected  from
1574              pg_stat_activity view, select condition determined by mask which
1575              can be defined with n hotkey.
1576
1577          n  :Set new mask toggle
1578              Set  new mask for group cancel/terminate. Type of backends asso‐
1579              ciated with their states:
1580
1581              active
1582                     Backend which are executing a queries.
1583                     Used condition: WHERE state = 'active' AND (
1584                                         (clock_timestamp() - xact_start) > '00:00:10.0'::interval
1585                                          OR
1586                                         (clock_timestamp() - query_start) > '00:00:10.0'::interval
1587                                     ) AND pid <> pg_backend_pid();
1588
1589              idle
1590                     Backends which are do  nothing  and  waiting  for  a  new
1591                     client command.
1592                     Used confition: WHERE state = 'idle' AND (
1593                                         (clock_timestamp() - xact_start) > '00:00:10.0'::interval
1594                                          OR
1595                                         (clock_timestamp() - query_start) > '00:00:10.0'::interval
1596                                     ) AND pid <> pg_backend_pid();
1597
1598              idle_in_xact
1599                     Includes  idle  in  transaction  and idle in transaction:
1600                     (aborted) states. Always a bad state.
1601                     Used condition: WHERE state IN ('idle in transaction (aborted)', 'idle in transaction') AND (
1602                                         (clock_timestamp() - xact_start) > '00:00:10.0'::interval
1603                                          OR
1604                                         (clock_timestamp() - query_start) > '00:00:10.0'::interval)
1605                                     AND pid <> pg_backend_pid();
1606
1607              waiting
1608                     Backends which are currently  waiting  on  a  lock.  Long
1609                     waiting transactions or statements are bad.
1610                     Used condition: WHERE waiting AND (
1611                                         (clock_timestamp() - xact_start) > '00:00:10.0'::interval
1612                                          OR (clock_timestamp() - query_start) > '00:00:10.0'::interval
1613                                     ) AND pid <> pg_backend_pid();
1614
1615              other
1616                     backends which are executing a fast-path function call or
1617                     with disabled state - when track_activities are  disabled
1618                     in those backends.
1619                     Used condition: WHERE state IN ('fastpath function call', 'disabled') AND (
1620                                         (clock_timestamp() - xact_start) > '00:00:10.0'::interval
1621                                          OR (clock_timestamp() - query_start) > '00:00:10.0'::interval
1622                                     ) AND pid <> pg_backend_pid();
1623
1624              A  ten  seconds  interval  used in condition determines query or
1625              transaction age and can be overriden with A hotkey.
1626
1627              Note, waiting is not a backend state. If a  backend  is  in  the
1628              active  state,  it  may  or  may not be waiting. If the state is
1629              active and waiting is true, it means that a query is being  exe‐
1630              cuted,  but  is being blocked by a lock somewhere in the system.
1631              Therefore, if waiting added to the  mask,  all  waiting  queries
1632              will  be  canceled  ot  terminated independently of his backends
1633              states.
1634
1635
1636          m  :Display current mask toggle
1637              Show current mask which will used for group cancel/terminate.
1638
1639          A  :Change age threshold toggle
1640              Change age threshold for long running queries. Queries which age
1641              is never then threshold not displayed. Default threshold 10 sec‐
1642              onds. This function allowed only  when  pg_stat_activity  screen
1643              enabled by a hotkey.
1644
1645          V  :Show system tables toggle
1646              Toggle  on/off system tables and indexes. By default, the pgcen‐
1647              ter  shows  table/index  statistics   for   user   tables   from
1648              pg_stat_user_* views.
1649
1650          K  :Reset postgresql stats toggle
1651              Reset  PostgreSQL  stats  counters  for  the current database to
1652              zero. The pg_stat_statements  counters  also  reseted.  Requires
1653              database superuser privileges.
1654
1655          G  :Get query report toggle
1656              Show  query  report  with  various  information  about specified
1657              query. This function work only in pg_stat_statements_timing  and
1658              pg_stat_statements_general  screens. For specifying query use id
1659              values from queryid column.
1660
1661          z  :Change refresh interval toggle
1662              You will be prompted  to  enter  the  delay  time,  in  seconds,
1663              between display updates. Can not be less that 1 second.
1664
1665          Z  :Change Color Mapping toggle
1666              This key will take you to a separate screen where you can change
1667              the colors for the windows.
1668
1669          space  :Pause program execution toggle
1670
1671          F1  :Help toggle
1672              Show help screen.
1673
1674          q  :Quit
1675
1676

URLS

1678       pg_stat_statements module
1679              http://www.postgresql.org/docs/9.4/static/pgstatstatements.html
1680
1681       PostgreSQL statistics collector
1682              http://www.postgresql.org/docs/9.4/static/monitoring-stats.html
1683
1684

BUGS

1686       To report bugs, use http://github.com/lesovsky/pgbconsole/issues page
1687
1688

AUTHOR

1690       Alexey Lesovsky, <lesovsky@gmail.com>
1691
1692

SEE ALSO

1694
1695
1696
1697
1698                                 August, 2015                      PGCENTER(1)
Impressum