Skip to content

Instantly share code, notes, and snippets.

@opan

opan/pgmetrics.json

Created February 25, 2020 03:44
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save opan/f7ee4f8c59b59af3d43c75f93e8fc3ea to your computer and use it in GitHub Desktop.
Save opan/f7ee4f8c59b59af3d43c75f93e8fc3ea to your computer and use it in GitHub Desktop.
Download ZIP
Sample output from pgmetrics tools in JSON format
Raw
pgmetrics.json
{
"meta": {
"version": "1.7",
"at": 1582601656,
"collected_dbs": [
"postgres"
],
"local": true
},
"start_time": 1582527353,
"system_identifier": "",
"checkpoint_lsn": "",
"prior_lsn": "",
"redo_lsn": "",
"timeline_id": 0,
"next_xid": 0,
"oldest_xid": 0,
"oldest_active_xid": 0,
"checkpoint_time": 0,
"wal_flush_lsn": "",
"wal_insert_lsn": "",
"wal_lsn": "",
"is_in_recovery": true,
"is_wal_replay_paused": false,
"last_wal_receive_lsn": "0/525496E8",
"last_wal_replay_lsn": "0/525496E8",
"last_xact_replay_timestamp": 1582601649,
"last_xact_xid": 0,
"last_xact_time": 0,
"wal_archiving": {
"archived_count": 78,
"last_archived_wal": "000000010000000000000051",
"last_archived_time": 1582527655,
"failed_count": 0,
"last_failed_wal": "",
"last_failed_time": 0,
"stats_reset": 1582527353
},
"wal_count": 78,
"wal_ready_count": 0,
"notification_queue_usage": 0,
"bg_writer": {
"checkpoints_timed": 69,
"checkpoints_req": 9,
"checkpoint_write_time": 123529,
"checkpoint_sync_time": 425,
"buffers_checkpoint": 1248,
"buffers_clean": 21333,
"maxwritten_clean": 211,
"buffers_backend": 525258,
"buffers_backend_fsync": 0,
"buffers_alloc": 357140,
"stats_reset": 1582527353
},
"backends": [
{
"db_name": "postgres",
"role_name": "postgres",
"application_name": "pgmetrics",
"pid": 18303,
"client_addr": "",
"backend_start": 1582601657,
"xact_start": 1582601657,
"query_start": 1582601657,
"state_change": 1582601657,
"wait_event_type": "",
"wait_event": "",
"state": "active",
"backend_xid": 0,
"backend_xmin": 1188,
"query": "SELECT COALESCE(datname, ''), COALESCE(usename, ''),\n\t\t\tCOALESCE(application_name, ''), COALESCE(pid, 0),\n\t\t\tCOALESCE(client_hostname::text, client_addr::text, ''),\n\t\t\tCOALESCE(EXTRACT(EPOCH FROM backend_start)::bigint, 0),\n\t\t\tCOALESCE(EXTRACT(EPOCH FROM xact_start)::bigint, 0),\n\t\t\tCOALESCE(EXTRACT(EPOCH FROM query_start)::bigint, 0),\n\t\t\tCOALESCE(EXTRACT(EPOCH FROM state_change)::bigint, 0),\n\t\t\tCOALESCE(waiting, FALSE),\n\t\t\tCOALESCE(state, ''), COALESCE(backend_xid, ''),\n\t\t\tCOALESCE(backend_xmin, ''), COALESCE(query, '')\n\t\t FROM pg_stat_activity\n\t\t ORDER BY pid ASC"
}
],
"roles": [
{
"oid": 10,
"name": "postgres",
"rolsuper": true,
"rolinherit": true,
"rolcreaterole": true,
"rolcreatedb": true,
"rolcanlogin": true,
"rolreplication": true,
"rolbypassrls": true,
"rolconnlimit": -1,
"rolvaliduntil": 0,
"memberof": []
},
{
"oid": 16384,
"name": "default-user",
"rolsuper": true,
"rolinherit": true,
"rolcreaterole": false,
"rolcreatedb": false,
"rolcanlogin": true,
"rolreplication": false,
"rolbypassrls": false,
"rolconnlimit": -1,
"rolvaliduntil": 0,
"memberof": []
},
{
"oid": 16385,
"name": "rep",
"rolsuper": false,
"rolinherit": true,
"rolcreaterole": false,
"rolcreatedb": false,
"rolcanlogin": true,
"rolreplication": true,
"rolbypassrls": false,
"rolconnlimit": -1,
"rolvaliduntil": 0,
"memberof": []
}
],
"databases": [
{
"oid": 12411,
"name": "postgres",
"datdba": 10,
"dattablespace": 1663,
"datconnlimit": -1,
"age_datfrozenxid": 537,
"numbackends": 1,
"xact_commit": 218,
"xact_rollback": 13,
"blks_read": 396,
"blks_hit": 32781,
"tup_returned": 84497,
"tup_fetched": 19768,
"tup_inserted": 0,
"tup_updated": 0,
"tup_deleted": 0,
"conflicts": 0,
"temp_files": 0,
"temp_bytes": 0,
"deadlocks": 0,
"blk_read_time": 0,
"blk_write_time": 0,
"stats_reset": 1582527531,
"size": 7151788
},
{
"oid": 16386,
"name": "transaction-history-service",
"datdba": 16384,
"dattablespace": 1663,
"datconnlimit": -1,
"age_datfrozenxid": 537,
"numbackends": 0,
"xact_commit": 2,
"xact_rollback": 0,
"blks_read": 151,
"blks_hit": 1067,
"tup_returned": 1132,
"tup_fetched": 711,
"tup_inserted": 0,
"tup_updated": 0,
"tup_deleted": 0,
"conflicts": 0,
"temp_files": 0,
"temp_bytes": 0,
"deadlocks": 0,
"blk_read_time": 0,
"blk_write_time": 0,
"stats_reset": 1582527574,
"size": 1575780524
}
],
"tablespaces": [
{
"oid": 1663,
"name": "pg_default",
"owner": "postgres",
"location": "/var/lib/postgresql/9.5/main",
"size": 1597129736,
"disk_used": 5851611136,
"disk_total": 65986711552,
"inodes_used": 59044,
"inodes_total": 4104192
},
{
"oid": 1664,
"name": "pg_global",
"owner": "postgres",
"location": "/var/lib/postgresql/9.5/main",
"size": 474944,
"disk_used": 5851611136,
"disk_total": 65986711552,
"inodes_used": 59044,
"inodes_total": 4104192
}
],
"extensions": [
{
"name": "plpgsql",
"db_name": "postgres",
"default_version": "1.0",
"installed_version": "1.0",
"comment": "PL/pgSQL procedural language"
}
],
"system": {
"cpu_model": "Intel(R) Core(TM) i7-6820HQ CPU @ 2.70GHz",
"num_cores": 1,
"loadavg": 0,
"memused": 131457024,
"memfree": 68751360,
"membuffers": 12328960,
"memcached": 827858944,
"swapused": 11673600,
"swapfree": 1015926784,
"hostname": "slave-01"
},
"settings": {
"DateStyle": {
"setting": "ISO, MDY"
},
"IntervalStyle": {
"setting": "postgres"
},
"TimeZone": {
"setting": "Asia/Jakarta",
"bootval": "GMT",
"source": "/etc/postgresql/9.5/main/postgresql.conf:28"
},
"allow_system_table_mods": {
"setting": "off"
},
"application_name": {
"setting": "pgmetrics",
"source": "client"
},
"archive_command": {
"setting": "cp %p /var/lib/postgresql/9.5/main/archive/%f",
"source": "/etc/postgresql/9.5/main/postgresql.conf:12"
},
"archive_mode": {
"setting": "always",
"bootval": "off",
"source": "/etc/postgresql/9.5/main/postgresql.conf:13"
},
"archive_timeout": {
"setting": "0"
},
"array_nulls": {
"setting": "on"
},
"authentication_timeout": {
"setting": "60"
},
"autovacuum": {
"setting": "on"
},
"autovacuum_analyze_scale_factor": {
"setting": "0.1"
},
"autovacuum_analyze_threshold": {
"setting": "50"
},
"autovacuum_freeze_max_age": {
"setting": "200000000"
},
"autovacuum_max_workers": {
"setting": "3"
},
"autovacuum_multixact_freeze_max_age": {
"setting": "400000000"
},
"autovacuum_naptime": {
"setting": "60"
},
"autovacuum_vacuum_cost_delay": {
"setting": "20"
},
"autovacuum_vacuum_cost_limit": {
"setting": "-1"
},
"autovacuum_vacuum_scale_factor": {
"setting": "0.2"
},
"autovacuum_vacuum_threshold": {
"setting": "50"
},
"autovacuum_work_mem": {
"setting": "-1"
},
"backslash_quote": {
"setting": "safe_encoding"
},
"bgwriter_delay": {
"setting": "200"
},
"bgwriter_lru_maxpages": {
"setting": "100"
},
"bgwriter_lru_multiplier": {
"setting": "2"
},
"block_size": {
"setting": "8192"
},
"bonjour": {
"setting": "off"
},
"bonjour_name": {
"setting": ""
},
"bytea_output": {
"setting": "hex"
},
"check_function_bodies": {
"setting": "on"
},
"checkpoint_completion_target": {
"setting": "0.5"
},
"checkpoint_timeout": {
"setting": "300"
},
"checkpoint_warning": {
"setting": "30"
},
"client_encoding": {
"setting": "UTF8",
"bootval": "SQL_ASCII",
"source": "client"
},
"client_min_messages": {
"setting": "notice"
},
"cluster_name": {
"setting": ""
},
"commit_delay": {
"setting": "0"
},
"commit_siblings": {
"setting": "5"
},
"config_file": {
"setting": "/etc/postgresql/9.5/main/postgresql.conf",
"source": "override"
},
"constraint_exclusion": {
"setting": "partition"
},
"cpu_index_tuple_cost": {
"setting": "0.005"
},
"cpu_operator_cost": {
"setting": "0.0025"
},
"cpu_tuple_cost": {
"setting": "0.01"
},
"cursor_tuple_fraction": {
"setting": "0.1"
},
"data_checksums": {
"setting": "off"
},
"data_directory": {
"setting": "/var/lib/postgresql/9.5/main",
"source": "override"
},
"data_sync_retry": {
"setting": "off"
},
"db_user_namespace": {
"setting": "off"
},
"deadlock_timeout": {
"setting": "1000"
},
"debug_assertions": {
"setting": "off"
},
"debug_pretty_print": {
"setting": "on"
},
"debug_print_parse": {
"setting": "off"
},
"debug_print_plan": {
"setting": "off"
},
"debug_print_rewritten": {
"setting": "off"
},
"default_statistics_target": {
"setting": "100"
},
"default_tablespace": {
"setting": ""
},
"default_text_search_config": {
"setting": "pg_catalog.simple"
},
"default_transaction_deferrable": {
"setting": "off"
},
"default_transaction_isolation": {
"setting": "read committed"
},
"default_transaction_read_only": {
"setting": "off"
},
"default_with_oids": {
"setting": "off"
},
"dynamic_library_path": {
"setting": "$libdir"
},
"dynamic_shared_memory_type": {
"setting": "posix"
},
"effective_cache_size": {
"setting": "524288"
},
"effective_io_concurrency": {
"setting": "1"
},
"enable_bitmapscan": {
"setting": "on"
},
"enable_hashagg": {
"setting": "on"
},
"enable_hashjoin": {
"setting": "on"
},
"enable_indexonlyscan": {
"setting": "on"
},
"enable_indexscan": {
"setting": "on"
},
"enable_material": {
"setting": "on"
},
"enable_mergejoin": {
"setting": "on"
},
"enable_nestloop": {
"setting": "on"
},
"enable_seqscan": {
"setting": "on"
},
"enable_sort": {
"setting": "on"
},
"enable_tidscan": {
"setting": "on"
},
"escape_string_warning": {
"setting": "on"
},
"event_source": {
"setting": "PostgreSQL"
},
"exit_on_error": {
"setting": "off"
},
"external_pid_file": {
"setting": "/var/run/postgresql/9.5-main.pid",
"source": "/etc/postgresql/9.5/main/postgresql.conf:9"
},
"extra_float_digits": {
"setting": "0"
},
"from_collapse_limit": {
"setting": "8"
},
"fsync": {
"setting": "on"
},
"full_page_writes": {
"setting": "on"
},
"geqo": {
"setting": "on"
},
"geqo_effort": {
"setting": "5"
},
"geqo_generations": {
"setting": "0"
},
"geqo_pool_size": {
"setting": "0"
},
"geqo_seed": {
"setting": "0"
},
"geqo_selection_bias": {
"setting": "2"
},
"geqo_threshold": {
"setting": "12"
},
"gin_fuzzy_search_limit": {
"setting": "0"
},
"gin_pending_list_limit": {
"setting": "4096"
},
"hba_file": {
"setting": "/etc/postgresql/9.5/main/pg_hba.conf",
"source": "override"
},
"hot_standby": {
"setting": "on",
"bootval": "off",
"source": "/etc/postgresql/9.5/main/postgresql.conf:16"
},
"hot_standby_feedback": {
"setting": "off"
},
"huge_pages": {
"setting": "try"
},
"ident_file": {
"setting": "/etc/postgresql/9.5/main/pg_ident.conf",
"source": "override"
},
"ignore_checksum_failure": {
"setting": "off"
},
"ignore_system_indexes": {
"setting": "off"
},
"integer_datetimes": {
"setting": "on"
},
"join_collapse_limit": {
"setting": "8"
},
"krb_caseins_users": {
"setting": "off"
},
"krb_server_keyfile": {
"setting": "FILE:/etc/postgresql-common/krb5.keytab"
},
"lc_collate": {
"setting": "en_US.UTF-8",
"bootval": "C",
"source": "override"
},
"lc_ctype": {
"setting": "en_US.UTF-8",
"bootval": "C",
"source": "override"
},
"lc_messages": {
"setting": ""
},
"lc_monetary": {
"setting": "C"
},
"lc_numeric": {
"setting": "C"
},
"lc_time": {
"setting": "C"
},
"listen_addresses": {
"setting": "*",
"bootval": "localhost",
"source": "/etc/postgresql/9.5/main/postgresql.conf:17"
},
"lo_compat_privileges": {
"setting": "off"
},
"local_preload_libraries": {
"setting": ""
},
"lock_timeout": {
"setting": "50",
"bootval": "0",
"source": "client"
},
"log_autovacuum_min_duration": {
"setting": "-1"
},
"log_checkpoints": {
"setting": "on",
"bootval": "off",
"source": "/etc/postgresql/9.5/main/postgresql.conf:18"
},
"log_connections": {
"setting": "on",
"bootval": "off",
"source": "/etc/postgresql/9.5/main/postgresql.conf:19"
},
"log_destination": {
"setting": "stderr"
},
"log_directory": {
"setting": "pg_log"
},
"log_disconnections": {
"setting": "on",
"bootval": "off",
"source": "/etc/postgresql/9.5/main/postgresql.conf:20"
},
"log_duration": {
"setting": "off"
},
"log_error_verbosity": {
"setting": "default"
},
"log_executor_stats": {
"setting": "off"
},
"log_file_mode": {
"setting": "0600",
"bootval": "384",
"source": "default"
},
"log_filename": {
"setting": "postgresql-%Y-%m-%d_%H%M%S.log"
},
"log_hostname": {
"setting": "off"
},
"log_line_prefix": {
"setting": "%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ",
"source": "/etc/postgresql/9.5/main/postgresql.conf:21"
},
"log_lock_waits": {
"setting": "on",
"bootval": "off",
"source": "/etc/postgresql/9.5/main/postgresql.conf:22"
},
"log_min_duration_statement": {
"setting": "70",
"bootval": "-1",
"source": "/etc/postgresql/9.5/main/postgresql.conf:23"
},
"log_min_error_statement": {
"setting": "error"
},
"log_min_messages": {
"setting": "warning"
},
"log_parser_stats": {
"setting": "off"
},
"log_planner_stats": {
"setting": "off"
},
"log_replication_commands": {
"setting": "off"
},
"log_rotation_age": {
"setting": "1440"
},
"log_rotation_size": {
"setting": "10240"
},
"log_statement": {
"setting": "none"
},
"log_statement_stats": {
"setting": "off"
},
"log_temp_files": {
"setting": "0",
"bootval": "-1",
"source": "/etc/postgresql/9.5/main/postgresql.conf:24"
},
"log_timezone": {
"setting": "Asia/Jakarta",
"bootval": "GMT",
"source": "/etc/postgresql/9.5/main/postgresql.conf:25"
},
"log_truncate_on_rotation": {
"setting": "off"
},
"logging_collector": {
"setting": "off"
},
"maintenance_work_mem": {
"setting": "65536"
},
"max_connections": {
"setting": "1000",
"bootval": "100",
"source": "/etc/postgresql/9.5/main/postgresql.conf:26"
},
"max_files_per_process": {
"setting": "1000"
},
"max_function_args": {
"setting": "100"
},
"max_identifier_length": {
"setting": "63"
},
"max_index_keys": {
"setting": "32"
},
"max_locks_per_transaction": {
"setting": "64"
},
"max_pred_locks_per_transaction": {
"setting": "64"
},
"max_prepared_transactions": {
"setting": "0"
},
"max_replication_slots": {
"setting": "0"
},
"max_stack_depth": {
"setting": "2048",
"bootval": "100",
"source": "environment variable"
},
"max_standby_archive_delay": {
"setting": "30000"
},
"max_standby_streaming_delay": {
"setting": "30000"
},
"max_wal_senders": {
"setting": "8",
"bootval": "0",
"source": "/etc/postgresql/9.5/main/postgresql.conf:27"
},
"max_wal_size": {
"setting": "64"
},
"max_worker_processes": {
"setting": "8"
},
"min_wal_size": {
"setting": "5"
},
"operator_precedence_warning": {
"setting": "off"
},
"password_encryption": {
"setting": "on"
},
"port": {
"setting": "5432"
},
"post_auth_delay": {
"setting": "0"
},
"pre_auth_delay": {
"setting": "0"
},
"quote_all_identifiers": {
"setting": "off"
},
"random_page_cost": {
"setting": "4"
},
"restart_after_crash": {
"setting": "on"
},
"row_security": {
"setting": "on"
},
"search_path": {
"setting": "\"$user\", public"
},
"segment_size": {
"setting": "131072"
},
"seq_page_cost": {
"setting": "1"
},
"server_encoding": {
"setting": "UTF8",
"bootval": "SQL_ASCII",
"source": "override"
},
"server_version": {
"setting": "9.5.21"
},
"server_version_num": {
"setting": "90521"
},
"session_preload_libraries": {
"setting": ""
},
"session_replication_role": {
"setting": "origin"
},
"shared_buffers": {
"setting": "1024"
},
"shared_preload_libraries": {
"setting": ""
},
"sql_inheritance": {
"setting": "on"
},
"ssl": {
"setting": "off"
},
"ssl_ca_file": {
"setting": ""
},
"ssl_cert_file": {
"setting": "server.crt"
},
"ssl_ciphers": {
"setting": "HIGH:MEDIUM:+3DES:!aNULL"
},
"ssl_crl_file": {
"setting": ""
},
"ssl_ecdh_curve": {
"setting": "prime256v1"
},
"ssl_key_file": {
"setting": "server.key"
},
"ssl_prefer_server_ciphers": {
"setting": "on"
},
"standard_conforming_strings": {
"setting": "on"
},
"statement_timeout": {
"setting": "5000",
"bootval": "0",
"source": "client"
},
"stats_temp_directory": {
"setting": "/var/run/postgresql/9.5-main.pg_stat_tmp",
"bootval": "pg_stat_tmp",
"source": "/etc/postgresql/9.5/main/postgresql.conf:10"
},
"superuser_reserved_connections": {
"setting": "3"
},
"synchronize_seqscans": {
"setting": "on"
},
"synchronous_commit": {
"setting": "on"
},
"synchronous_standby_names": {
"setting": ""
},
"syslog_facility": {
"setting": "local0"
},
"syslog_ident": {
"setting": "postgres"
},
"tcp_keepalives_count": {
"setting": "0"
},
"tcp_keepalives_idle": {
"setting": "0"
},
"tcp_keepalives_interval": {
"setting": "0"
},
"temp_buffers": {
"setting": "1024"
},
"temp_file_limit": {
"setting": "-1"
},
"temp_tablespaces": {
"setting": ""
},
"timezone_abbreviations": {
"setting": "Default",
"source": "default"
},
"trace_notify": {
"setting": "off"
},
"trace_recovery_messages": {
"setting": "log"
},
"trace_sort": {
"setting": "off"
},
"track_activities": {
"setting": "on"
},
"track_activity_query_size": {
"setting": "1024"
},
"track_commit_timestamp": {
"setting": "off"
},
"track_counts": {
"setting": "on"
},
"track_functions": {
"setting": "none"
},
"track_io_timing": {
"setting": "off"
},
"transaction_deferrable": {
"setting": "off"
},
"transaction_isolation": {
"setting": "read committed",
"bootval": "default",
"source": "override"
},
"transaction_read_only": {
"setting": "on",
"bootval": "off",
"source": "override"
},
"transform_null_equals": {
"setting": "off"
},
"unix_socket_directories": {
"setting": "/var/run/postgresql"
},
"unix_socket_group": {
"setting": ""
},
"unix_socket_permissions": {
"setting": "0777",
"bootval": "511",
"source": "default"
},
"update_process_title": {
"setting": "on"
},
"vacuum_cost_delay": {
"setting": "0"
},
"vacuum_cost_limit": {
"setting": "200"
},
"vacuum_cost_page_dirty": {
"setting": "20"
},
"vacuum_cost_page_hit": {
"setting": "1"
},
"vacuum_cost_page_miss": {
"setting": "10"
},
"vacuum_defer_cleanup_age": {
"setting": "0"
},
"vacuum_freeze_min_age": {
"setting": "50000000"
},
"vacuum_freeze_table_age": {
"setting": "150000000"
},
"vacuum_multixact_freeze_min_age": {
"setting": "5000000"
},
"vacuum_multixact_freeze_table_age": {
"setting": "150000000"
},
"wal_block_size": {
"setting": "8192"
},
"wal_buffers": {
"setting": "32",
"bootval": "-1",
"source": "override"
},
"wal_compression": {
"setting": "off"
},
"wal_keep_segments": {
"setting": "50",
"bootval": "0",
"source": "/etc/postgresql/9.5/main/postgresql.conf:30"
},
"wal_level": {
"setting": "hot_standby",
"bootval": "minimal",
"source": "/etc/postgresql/9.5/main/postgresql.conf:31"
},
"wal_log_hints": {
"setting": "off"
},
"wal_receiver_status_interval": {
"setting": "10"
},
"wal_receiver_timeout": {
"setting": "60000"
},
"wal_retrieve_retry_interval": {
"setting": "5000"
},
"wal_segment_size": {
"setting": "2048"
},
"wal_sender_timeout": {
"setting": "60000"
},
"wal_sync_method": {
"setting": "fdatasync"
},
"wal_writer_delay": {
"setting": "200"
},
"work_mem": {
"setting": "4096"
},
"xmlbinary": {
"setting": "base64"
},
"xmloption": {
"setting": "content"
},
"zero_damaged_pages": {
"setting": "off"
}
},
"locks": [
{
"locktype": "relation",
"db_name": "postgres",
"pid": 18303,
"mode": "AccessShareLock",
"granted": true,
"relation_oid": 11673
},
{
"locktype": "virtualxid",
"pid": 18303,
"mode": "ExclusiveLock",
"granted": true
},
{
"locktype": "virtualxid",
"pid": 16626,
"mode": "ExclusiveLock",
"granted": true
},
{
"locktype": "relation",
"pid": 18303,
"mode": "AccessShareLock",
"granted": true,
"relation_oid": 1262
},
{
"locktype": "relation",
"pid": 18303,
"mode": "AccessShareLock",
"granted": true,
"relation_oid": 2672
},
{
"locktype": "relation",
"pid": 18303,
"mode": "AccessShareLock",
"granted": true,
"relation_oid": 2671
}
],
"highwal": 109
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment