1PGCENTER(1) General Commands Manual PGCENTER(1)
2
3
4
6 pgcenter - is the PostgreSQL administration console with top-like moni‐
7 toring.
8
10 pgcenter [OPTION]... [DBNAME [USERNAME]]
11
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
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
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
266 Cmdline window used for displaying diagnostic messages or when need
267 additional input from user.
268
269
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
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
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
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
1686 To report bugs, use http://github.com/lesovsky/pgbconsole/issues page
1687
1688
1690 Alexey Lesovsky, <lesovsky@gmail.com>
1691
1692
1694
1695
1696
1697
1698 August, 2015 PGCENTER(1)