Skip to content

pt-pg-summary

pt-pg-summary collects information about a PostgreSQL cluster.

Usage

pt-pg-summary [OPTIONS] [HOST:[PORT]]

Options

--help, --help-long, --help-man

Shows context-sensitive help. --help-long and --help-man provide more verbose output.

--version

Show application version and exit. |

--databases

Summarizes this comma-separated list of databases.

All if not specified.

-h, --host

Host or local Unix socket for connection.

-W, --password

Password to use when connecting. |

-p, --port

Port number to use for connection. |

--sleep

Seconds to sleep when gathering status counters.

Sleeps 10 seconds if not provided.

-U, --username

User for login if not current user.

--disable-ssl

Disable SSL for the connection.

Enabled by default.

--verbose

Show verbose log.

--debug

Show debug information in the logs.

Experimental Options

--list-encrypted-tables

Include a list of the encrypted tables in all databases.

--ask-pass

Prompt for a password when connecting to PostgreSQL.

--config

Configuration file.

--defaults-file

Only read PostgreSQL options from the given file.

--read-samples

Create a report from the files found in this directory.

--save-samples

Save the data files used to generate the summary in this directory.

Output example

##### --- Database Port and Data_Directory --- ####
+----------------------+----------------------------------------------------+
|         Name         |                      Setting                       |
+----------------------+----------------------------------------------------+
| data_directory       | /var/lib/postgresql/data                           |
+----------------------+----------------------------------------------------+

##### --- List of Tablespaces ---- ######
+----------------------+----------------------+----------------------------------------------------+
|         Name         |         Owner        |               Location                             |
+----------------------+----------------------+----------------------------------------------------+
| pg_default           | postgres             |                                                    |
| pg_global            | postgres             |                                                    |
+----------------------+----------------------+----------------------------------------------------+


##### --- Cluster Information --- ####
+------------------------------------------------------------------------------------------------------+
 Usename        : postgres
 Time           : 2020-04-21 13:38:22.770077 +0000 UTC
 Client Address : 172.19.0.1
 Client Hostname:
 Version        : PostgreSQL 9.6.17 on x86_64-pc-linux-gnu (Debian 9.6.17-2.pgdg90+1), compiled by
 Started        : 2020-04-21 13:36:59.909175 +0000 UTC
 Is Slave       : false
+------------------------------------------------------------------------------------------------------+

##### --- Databases --- ####
+----------------------+------------+
|       Dat Name       |    Size    |
+----------------------+------------+
| postgres             |    7071 kB |
| template1            |    6961 kB |
| template0            |    6961 kB |
+----------------------+------------+

##### --- Index Cache Hit Ratios --- ####

Database: postgres
+----------------------+------------+
|      Index Name      |    Ratio   |
+----------------------+------------+
| index hit rate       |      0.00  |
+----------------------+------------+

##### --- Table Cache Hit Ratios --- ####
Database: postgres
+----------------------+------------+
|      Index Name      |    Ratio   |
+----------------------+------------+
| cache hit rate       |       0.00 |
+----------------------+------------+

##### --- List of Wait_events for the entire Cluster - all-databases --- ####
No stats available

##### --- List of users and client_addr or client_hostname connected to --all-databases --- ####
+----------------------+------------+---------+----------------------+---------+
|   Wait Event Type    |        Client        |         State        |  Count  |
+----------------------+------------+---------+----------------------+---------+
| postgres             | 172.19.0.1/32        | active               |       1 |
+----------------------+------------+---------+----------------------+---------+

##### --- Counters diff after 10 seconds --- ####

+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+
| Database             | Numbackends | XactCommit | XactRollback | BlksRead    | BlksHit    | TupReturned | TupFetched | TupInserted | TupUpdated | TupDeleted | Conflicts | TempFiles | TempBytes | Deadlocks  |
+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+
| postgres             |       0     |       0    |       0      |       0     |       0    |       0     |       0    |       0     |       0    |       0    |       0   |       0   |       0   |       0    |
| template0            |       0     |       0    |       0      |       0     |       0    |       0     |       0    |       0     |       0    |       0    |       0   |       0   |       0   |       0    |
| template1            |       0     |       0    |       0      |       0     |       0    |       0     |       0    |       0     |       0    |       0    |       0   |       0   |       0   |       0    |
+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+

##### --- Table access per database --- ####
Database: postgres
+----------------------------------------------------+------+--------------------------------+---------+
|                       Relname                      | Kind |             Datname            |  Count  |
+----------------------------------------------------+------+--------------------------------+---------+
| pg_class                                           |   r  | postgres                       |       1 |
| pg_stat_database                                   |   v  | postgres                       |       1 |
| pg_locks                                           |   v  | postgres                       |       1 |
| pg_class_tblspc_relfilenode_index                  |   i  | postgres                       |       1 |
| pg_class_relname_nsp_index                         |   i  | postgres                       |       1 |
| pg_class_oid_index                                 |   i  | postgres                       |       1 |
+----------------------------------------------------+------+--------------------------------+---------+

##### --- Instance settings --- ####
                      Setting                                            Value
allow_system_table_mods                       : off
application_name                              :
archive_command                               : (disabled)
archive_mode                                  : off
archive_timeout                               : 0
array_nulls                                   : on
authentication_timeout                        : 60
autovacuum                                    : on
autovacuum_analyze_scale_factor               : 0.1
autovacuum_analyze_threshold                  : 50
autovacuum_freeze_max_age                     : 200000000
autovacuum_max_workers                        : 3
autovacuum_multixact_freeze_max_age           : 400000000
autovacuum_naptime                            : 60
autovacuum_vacuum_cost_delay                  : 20
autovacuum_vacuum_cost_limit                  : -1
autovacuum_vacuum_scale_factor                : 0.2
autovacuum_vacuum_threshold                   : 50
autovacuum_work_mem                           : -1
backend_flush_after                           : 0
backslash_quote                               : safe_encoding
bgwriter_delay                                : 200
bgwriter_flush_after                          : 64
bgwriter_lru_maxpages                         : 100
bgwriter_lru_multiplier                       : 2
block_size                                    : 8192
bonjour                                       : off
bonjour_name                                  :
bytea_output                                  : hex
check_function_bodies                         : on
checkpoint_completion_target                  : 0.5
checkpoint_flush_after                        : 32
checkpoint_timeout                            : 300
checkpoint_warning                            : 30
client_encoding                               : UTF8
client_min_messages                           : notice
cluster_name                                  :
commit_delay                                  : 0
commit_siblings                               : 5
config_file                                   : /var/lib/postgresql/data/postgresql.conf
constraint_exclusion                          : partition
cpu_index_tuple_cost                          : 0.005
cpu_operator_cost                             : 0.0025
cpu_tuple_cost                                : 0.01
cursor_tuple_fraction                         : 0.1
data_checksums                                : off
data_directory                                : /var/lib/postgresql/data
data_sync_retry                               : off
DateStyle                                     : ISO, MDY
db_user_namespace                             : off
deadlock_timeout                              : 1000
debug_assertions                              : off
debug_pretty_print                            : on
debug_print_parse                             : off
debug_print_plan                              : off
debug_print_rewritten                         : off
default_statistics_target                     : 100
default_tablespace                            :
default_text_search_config                    : pg_catalog.english
default_transaction_deferrable                : off
default_transaction_isolation                 : read committed
default_transaction_read_only                 : off
default_with_oids                             : off
dynamic_library_path                          : $libdir
dynamic_shared_memory_type                    : posix
effective_cache_size                          : 524288
effective_io_concurrency                      : 1
enable_bitmapscan                             : on
enable_hashagg                                : on
enable_hashjoin                               : on
enable_indexonlyscan                          : on
enable_indexscan                              : on
enable_material                               : on
enable_mergejoin                              : on
enable_nestloop                               : on
enable_seqscan                                : on
enable_sort                                   : on
enable_tidscan                                : on
escape_string_warning                         : on
event_source                                  : PostgreSQL
exit_on_error                                 : off
external_pid_file                             :
extra_float_digits                            : 2
force_parallel_mode                           : off
from_collapse_limit                           : 8
fsync                                         : on
full_page_writes                              : on
geqo                                          : on
geqo_effort                                   : 5
geqo_generations                              : 0
geqo_pool_size                                : 0
geqo_seed                                     : 0
geqo_selection_bias                           : 2
geqo_threshold                                : 12
gin_fuzzy_search_limit                        : 0
gin_pending_list_limit                        : 4096
hba_file                                      : /var/lib/postgresql/data/pg_hba.conf
hot_standby                                   : off
hot_standby_feedback                          : off
huge_pages                                    : try
ident_file                                    : /var/lib/postgresql/data/pg_ident.conf
idle_in_transaction_session_timeout           : 0
ignore_checksum_failure                       : off
ignore_system_indexes                         : off
integer_datetimes                             : on
IntervalStyle                                 : postgres
join_collapse_limit                           : 8
krb_caseins_users                             : off
krb_server_keyfile                            : FILE:/etc/postgresql-common/krb5.keytab
lc_collate                                    : en_US.utf8
lc_ctype                                      : en_US.utf8
lc_messages                                   : en_US.utf8
lc_monetary                                   : en_US.utf8
lc_numeric                                    : en_US.utf8
lc_time                                       : en_US.utf8
listen_addresses                              : *
lo_compat_privileges                          : off
local_preload_libraries                       :
lock_timeout                                  : 0
log_autovacuum_min_duration                   : -1
log_checkpoints                               : off
log_connections                               : off
log_destination                               : stderr
log_directory                                 : pg_log
log_disconnections                            : off
log_duration                                  : off
log_error_verbosity                           : default
log_executor_stats                            : off
log_file_mode                                 : 0600
log_filename                                  : postgresql-%Y-%m-%d_%H%M%S.log
log_hostname                                  : off
log_line_prefix                               :
log_lock_waits                                : off
log_min_duration_statement                    : -1
log_min_error_statement                       : error
log_min_messages                              : warning
log_parser_stats                              : off
log_planner_stats                             : off
log_replication_commands                      : off
log_rotation_age                              : 1440
log_rotation_size                             : 10240
log_statement                                 : none
log_statement_stats                           : off
log_temp_files                                : -1
log_timezone                                  : Etc/UTC
log_truncate_on_rotation                      : off
logging_collector                             : off
maintenance_work_mem                          : 65536
max_connections                               : 100
max_files_per_process                         : 1000
max_function_args                             : 100
max_identifier_length                         : 63
max_index_keys                                : 32
max_locks_per_transaction                     : 64
max_parallel_workers_per_gather               : 0
max_pred_locks_per_transaction                : 64
max_prepared_transactions                     : 0
max_replication_slots                         : 0
max_stack_depth                               : 2048
max_standby_archive_delay                     : 30000
max_standby_streaming_delay                   : 30000
max_wal_senders                               : 0
max_wal_size                                  : 64
max_worker_processes                          : 8
min_parallel_relation_size                    : 1024
min_wal_size                                  : 5
old_snapshot_threshold                        : -1
operator_precedence_warning                   : off
parallel_setup_cost                           : 1000
parallel_tuple_cost                           : 0.1
password_encryption                           : on
port                                          : 5432
post_auth_delay                               : 0
pre_auth_delay                                : 0
quote_all_identifiers                         : off
random_page_cost                              : 4
replacement_sort_tuples                       : 150000
restart_after_crash                           : on
row_security                                  : on
search_path                                   : "$user", public
segment_size                                  : 131072
seq_page_cost                                 : 1
server_encoding                               : UTF8
server_version                                : 9.6.17
server_version_num                            : 90617
session_preload_libraries                     :
session_replication_role                      : origin
shared_buffers                                : 16384
shared_preload_libraries                      :
sql_inheritance                               : on
ssl                                           : off
ssl_ca_file                                   :
ssl_cert_file                                 : server.crt
ssl_ciphers                                   : HIGH:MEDIUM:+3DES:!aNULL
ssl_crl_file                                  :
ssl_ecdh_curve                                : prime256v1
ssl_key_file                                  : server.key
ssl_prefer_server_ciphers                     : on
standard_conforming_strings                   : on
statement_timeout                             : 0
stats_temp_directory                          : pg_stat_tmp
superuser_reserved_connections                : 3
synchronize_seqscans                          : on
synchronous_commit                            : on
synchronous_standby_names                     :
syslog_facility                               : local0
syslog_ident                                  : postgres
syslog_sequence_numbers                       : on
syslog_split_messages                         : on
tcp_keepalives_count                          : 9
tcp_keepalives_idle                           : 7200
tcp_keepalives_interval                       : 75
temp_buffers                                  : 1024
temp_file_limit                               : -1
temp_tablespaces                              :
TimeZone                                      : Etc/UTC
timezone_abbreviations                        : Default
trace_notify                                  : off
trace_recovery_messages                       : log
trace_sort                                    : off
track_activities                              : on
track_activity_query_size                     : 1024
track_commit_timestamp                        : off
track_counts                                  : on
track_functions                               : none
track_io_timing                               : off
transaction_deferrable                        : off
transaction_isolation                         : read committed
transaction_read_only                         : off
transform_null_equals                         : off
unix_socket_directories                       : /var/run/postgresql
unix_socket_group                             :
unix_socket_permissions                       : 0777
update_process_title                          : on
vacuum_cost_delay                             : 0
vacuum_cost_limit                             : 200
vacuum_cost_page_dirty                        : 20
vacuum_cost_page_hit                          : 1
vacuum_cost_page_miss                         : 10
vacuum_defer_cleanup_age                      : 0
vacuum_freeze_min_age                         : 50000000
vacuum_freeze_table_age                       : 150000000
vacuum_multixact_freeze_min_age               : 5000000
vacuum_multixact_freeze_table_age             : 150000000
wal_block_size                                : 8192
wal_buffers                                   : 512
wal_compression                               : off
wal_keep_segments                             : 0
wal_level                                     : minimal
wal_log_hints                                 : off
wal_receiver_status_interval                  : 10
wal_receiver_timeout                          : 60000
wal_retrieve_retry_interval                   : 5000
wal_segment_size                              : 2048
wal_sender_timeout                            : 60000
wal_sync_method                               : fdatasync
wal_writer_delay                              : 200
wal_writer_flush_after                        : 128
work_mem                                      : 4096
xmlbinary                                     : base64
xmloption                                     : content
zero_damaged_pages                            : off

##### --- Processes start up command --- ####
No postgres process found

Sections

Output is separated into the following sections:

  • AllDatabases

    Selects datname from pg_database where datistemplate is false.

  • ClusterInfo

    Selects cluster information from pg_stat_activity.

  • ConnectedClients

    Counts the connected clients by selecting from pg_stat_activity.

  • Connections

    Selects state from pg_stat_activity and counts them.

  • Counters

    Selects various counter values from pg_stat_database.

  • DatabaseWaitEvents

    Shows database wait events from pg_locks, pg_stat_database, pg_class, and pg_stat_activity.

  • Databases

    Shows the name and size of databases from pg_stat_database.

  • GlobalWaitEvents

    Shows global wait evens from pg_stat_activity.

  • IndexCacheHitRatio

    Shows index hit ratios from pg_statio_user_indexes.

  • PortAndDatadir

    Shows port and data directory name from pg_settings.

  • ServerVersion

    Shows the value of server_version_num.

  • Setting

    Selects name and setting from pg_settings.

  • SlaveHosts10

    Selects information for PostgreSQL version 10.

  • SlaveHosts96

    Selects information for PostgreSQL version 9.6.

  • TableAccess

    Shows table access information by selecting from pg_locks, pg_stat_database and pg_class.

  • TableCacheHitRatio

    Shows table cache hit ratio information from pg_statio_user_tables.

  • Tablespaces

    Show owner and location from pg_catalog.pg_tablespace.

Authors

Carlos Salguero