Metrics
PostgreSQL is a powerful, open-source relational database system. Monitoring its metrics is vital to ensure performance, stability, and reliability. The following is a list of essential PostgreSQL metrics in PDS. Understanding these metrics will help administrators optimize performance, troubleshoot issues, and ensure the PostgreSQL database runs smoothly.
Access metrics for PostgreSQL
To fetch the above listed metrics for PostgreSQL deployments in PDS, you can connect to the metrics exporter using the following details:
Metrics port
For PostgreSQL deployment, the data service metrics are accessible on port 5555.
Access metrics
In a Kubernetes-based PDS deployment, the Postgres exporter is exposed either internally or externally. Here are a few common ways to access it:
Internal cluster access
-
Look for the pod name that runs your PostgreSQL instance or the exporter sidecar container:
kubectl get pods -n <your-namespace> -
Map your local machine’s port 5555 to the pod’s port 5555:
kubectl port-forward -n <your-namespace> <pod-name> 5555:5555 -
Open a browser or use
curlto accesshttp://localhost:5555/metrics. You should see a text-based Prometheus metrics output.
External NodePort or LoadBalancer
-
Look for a Service (for example,
<release-name>-postgres-exporter) with either NodePort or LoadBalancer type:kubectl get svc -n <your-namespace>- If NodePort, you will see a port in the PORT(S) column (for example, 5555:32001/TCP).
- If LoadBalancer, check the
EXTERNAL-IPcolumn for your load balancer endpoint.
-
Access NodePort
http://<node-ip>:<nodeport>/metricsor the LoadBalancerhttp://<loadbalancer-ip>:5555/metricsto view metrics.
Verify metrics
You can verify the metrics manually using curl:
curl http://<host>:5555/metrics
If you are using:
- Prometheus UI: In the Prometheus expression browser, search for any metric beginning with
pg_(refer to the above metrics table) to verify data is being scraped successfully. - Grafana or other dashboards: If you have Grafana or a similar tool connected to Prometheus, open your dashboard to check that PostgreSQL metrics are populating.
PostgreSQL Metrics
| Metric Name | Metric Type |
|---|---|
| pg_database_connection_limit | gauge |
| pg_database_size_bytes | gauge |
| pg_exporter_last_scrape_duration_seconds | gauge |
| pg_exporter_last_scrape_error | gauge |
| pg_exporter_scrapes_total | counter |
| pg_locks_count | gauge |
| pg_replication_is_replica | gauge |
| pg_replication_lag_seconds | gauge |
| pg_roles_connection_limit | gauge |
| pg_scrape_collector_duration_seconds | gauge |
| pg_scrape_collector_success | gauge |
| pg_settings_allow_in_place_tablespaces | gauge |
| pg_settings_allow_system_table_mods | gauge |
| pg_settings_archive_timeout_seconds | gauge |
| pg_settings_array_nulls | gauge |
| pg_settings_authentication_timeout_seconds | gauge |
| pg_settings_autovacuum | gauge |
| pg_settings_autovacuum_analyze_scale_factor | gauge |
| pg_settings_autovacuum_analyze_threshold | gauge |
| pg_settings_autovacuum_freeze_max_age | gauge |
| pg_settings_autovacuum_max_workers | gauge |
| pg_settings_autovacuum_multixact_freeze_max_age | gauge |
| pg_settings_autovacuum_naptime_seconds | gauge |
| pg_settings_autovacuum_vacuum_cost_delay_seconds | gauge |
| pg_settings_autovacuum_vacuum_cost_limit | gauge |
| pg_settings_autovacuum_vacuum_insert_scale_factor | gauge |
| pg_settings_autovacuum_vacuum_insert_threshold | gauge |
| pg_settings_autovacuum_vacuum_scale_factor | gauge |
| pg_settings_autovacuum_vacuum_threshold | gauge |
| pg_settings_autovacuum_work_mem_bytes | gauge |
| pg_settings_backend_flush_after_bytes | gauge |
| pg_settings_bgwriter_delay_seconds | gauge |
| pg_settings_bgwriter_flush_after_bytes | gauge |
| pg_settings_bgwriter_lru_maxpages | gauge |
| pg_settings_bgwriter_lru_multiplier | gauge |
| pg_settings_block_size | gauge |
| pg_settings_bonjour | gauge |
| pg_settings_check_function_bodies | gauge |
| pg_settings_checkpoint_completion_target | gauge |
| pg_settings_checkpoint_flush_after_bytes | gauge |
| pg_settings_checkpoint_timeout_seconds | gauge |
| pg_settings_checkpoint_warning_seconds | gauge |
| pg_settings_client_connection_check_interval_seconds | gauge |
| pg_settings_commit_delay | gauge |
| pg_settings_commit_siblings | gauge |
| pg_settings_cpu_index_tuple_cost | gauge |
| pg_settings_cpu_operator_cost | gauge |
| pg_settings_cpu_tuple_cost | gauge |
| pg_settings_cursor_tuple_fraction | gauge |
| pg_settings_data_checksums | gauge |
| pg_settings_data_directory_mode | gauge |
| pg_settings_data_sync_retry | gauge |
| pg_settings_db_user_namespace | gauge |
| pg_settings_deadlock_timeout_seconds | gauge |
| pg_settings_debug_assertions | gauge |
| pg_settings_debug_discard_caches | gauge |
| pg_settings_debug_pretty_print | gauge |
| pg_settings_debug_print_parse | gauge |
| pg_settings_debug_print_plan | gauge |
| pg_settings_debug_print_rewritten | gauge |
| pg_settings_default_statistics_target | gauge |
| pg_settings_default_transaction_deferrable | gauge |
| pg_settings_default_transaction_read_only | gauge |
| pg_settings_effective_cache_size_bytes | gauge |
| pg_settings_effective_io_concurrency | gauge |
| pg_settings_enable_async_append | gauge |
| pg_settings_enable_bitmapscan | gauge |
| pg_settings_enable_gathermerge | gauge |
| pg_settings_enable_hashagg | gauge |
| pg_settings_enable_hashjoin | gauge |
| pg_settings_enable_incremental_sort | gauge |
| pg_settings_enable_indexonlyscan | gauge |
| pg_settings_enable_indexscan | gauge |
| pg_settings_enable_material | gauge |
| pg_settings_enable_memoize | gauge |
| pg_settings_enable_mergejoin | gauge |
| pg_settings_enable_nestloop | gauge |
| pg_settings_enable_parallel_append | gauge |
| pg_settings_enable_parallel_hash | gauge |
| pg_settings_enable_partition_pruning | gauge |
| pg_settings_enable_partitionwise_aggregate | gauge |
| pg_settings_enable_partitionwise_join | gauge |
| pg_settings_enable_seqscan | gauge |
| pg_settings_enable_sort | gauge |
| pg_settings_enable_tidscan | gauge |
| pg_settings_escape_string_warning | gauge |
| pg_settings_exit_on_error | gauge |
| pg_settings_extra_float_digits | gauge |
| pg_settings_from_collapse_limit | gauge |
| pg_settings_fsync | gauge |
| pg_settings_full_page_writes | gauge |
| pg_settings_geqo | gauge |
| pg_settings_geqo_effort | gauge |
| pg_settings_geqo_generations | gauge |
| pg_settings_geqo_pool_size | gauge |
| pg_settings_geqo_seed | gauge |
| pg_settings_geqo_selection_bias | gauge |
| pg_settings_geqo_threshold | gauge |
| pg_settings_gin_fuzzy_search_limit | gauge |
| pg_settings_gin_pending_list_limit_bytes | gauge |
| pg_settings_hash_mem_multiplier | gauge |
| pg_settings_hot_standby | gauge |
| pg_settings_hot_standby_feedback | gauge |
| pg_settings_huge_page_size_bytes | gauge |
| pg_settings_idle_in_transaction_session_timeout_seconds | gauge |
| pg_settings_idle_session_timeout_seconds | gauge |
| pg_settings_ignore_checksum_failure | gauge |
| pg_settings_ignore_invalid_pages | gauge |
| pg_settings_ignore_system_indexes | gauge |
| pg_settings_in_hot_standby | gauge |
| pg_settings_integer_datetimes | gauge |
| pg_settings_jit | gauge |
| pg_settings_jit_above_cost | gauge |
| pg_settings_jit_debugging_support | gauge |
| pg_settings_jit_dump_bitcode | gauge |
| pg_settings_jit_expressions | gauge |
| pg_settings_jit_inline_above_cost | gauge |
| pg_settings_jit_optimize_above_cost | gauge |
| pg_settings_jit_profiling_support | gauge |
| pg_settings_jit_tuple_deforming | gauge |
| pg_settings_join_collapse_limit | gauge |
| pg_settings_krb_caseins_users | gauge |
| pg_settings_lo_compat_privileges | gauge |
| pg_settings_lock_timeout_seconds | gauge |
| pg_settings_log_autovacuum_min_duration_seconds | gauge |
| pg_settings_log_checkpoints | gauge |
| pg_settings_log_connections | gauge |
| pg_settings_log_disconnections | gauge |
| pg_settings_log_duration | gauge |
| pg_settings_log_executor_stats | gauge |
| pg_settings_log_file_mode | gauge |
| pg_settings_log_hostname | gauge |
| pg_settings_log_lock_waits | gauge |
| pg_settings_log_min_duration_sample_seconds | gauge |
| pg_settings_log_min_duration_statement_seconds | gauge |
| pg_settings_log_parameter_max_length_bytes | gauge |
| pg_settings_log_parameter_max_length_on_error_bytes | gauge |
| pg_settings_log_parser_stats | gauge |
| pg_settings_log_planner_stats | gauge |
| pg_settings_log_recovery_conflict_waits | gauge |
| pg_settings_log_replication_commands | gauge |
| pg_settings_log_rotation_age_seconds | gauge |
| pg_settings_log_rotation_size_bytes | gauge |
| pg_settings_log_statement_sample_rate | gauge |
| pg_settings_log_statement_stats | gauge |
| pg_settings_log_temp_files_bytes | gauge |
| pg_settings_log_transaction_sample_rate | gauge |
| pg_settings_log_truncate_on_rotation | gauge |
| pg_settings_logging_collector | gauge |
| pg_settings_logical_decoding_work_mem_bytes | gauge |
| pg_settings_maintenance_io_concurrency | gauge |
| pg_settings_maintenance_work_mem_bytes | gauge |
| pg_settings_max_connections | gauge |
| pg_settings_max_files_per_process | gauge |
| pg_settings_max_function_args | gauge |
| pg_settings_max_identifier_length | gauge |
| pg_settings_max_index_keys | gauge |
| pg_settings_max_locks_per_transaction | gauge |
| pg_settings_max_logical_replication_workers | gauge |
| pg_settings_max_parallel_maintenance_workers | gauge |
| pg_settings_max_parallel_workers | gauge |
| pg_settings_max_parallel_workers_per_gather | gauge |
| pg_settings_max_pred_locks_per_page | gauge |
| pg_settings_max_pred_locks_per_relation | gauge |
| pg_settings_max_pred_locks_per_transaction | gauge |
| pg_settings_max_prepared_transactions | gauge |
| pg_settings_max_replication_slots | gauge |
| pg_settings_max_slot_wal_keep_size_bytes | gauge |
| pg_settings_max_stack_depth_bytes | gauge |
| pg_settings_max_standby_archive_delay_seconds | gauge |
| pg_settings_max_standby_streaming_delay_seconds | gauge |
| pg_settings_max_sync_workers_per_subscription | gauge |
| pg_settings_max_wal_senders | gauge |
| pg_settings_max_wal_size_bytes | gauge |
| pg_settings_max_worker_processes | gauge |
| pg_settings_min_dynamic_shared_memory_bytes | gauge |
| pg_settings_min_parallel_index_scan_size_bytes | gauge |
| pg_settings_min_parallel_table_scan_size_bytes | gauge |
| pg_settings_min_wal_size_bytes | gauge |
| pg_settings_old_snapshot_threshold_seconds | gauge |
| pg_settings_parallel_leader_participation | gauge |
| pg_settings_parallel_setup_cost | gauge |
| pg_settings_parallel_tuple_cost | gauge |
| pg_settings_pg_stat_statements_max | gauge |
| pg_settings_pg_stat_statements_save | gauge |
| pg_settings_pg_stat_statements_track_planning | gauge |
| pg_settings_pg_stat_statements_track_utility | gauge |
| pg_settings_port | gauge |
| pg_settings_post_auth_delay_seconds | gauge |
| pg_settings_pre_auth_delay_seconds | gauge |
| pg_settings_quote_all_identifiers | gauge |
| pg_settings_random_page_cost | gauge |
| pg_settings_recovery_min_apply_delay_seconds | gauge |
| pg_settings_recovery_target_inclusive | gauge |
| pg_settings_remove_temp_files_after_crash | gauge |
| pg_settings_restart_after_crash | gauge |
| pg_settings_row_security | gauge |
| pg_settings_segment_size_bytes | gauge |
| pg_settings_seq_page_cost | gauge |
| pg_settings_server_version_num | gauge |
| pg_settings_shared_buffers_bytes | gauge |
| pg_settings_ssl | gauge |
| pg_settings_ssl_passphrase_command_supports_reload | gauge |
| pg_settings_ssl_prefer_server_ciphers | gauge |
| pg_settings_standard_conforming_strings | gauge |
| pg_settings_statement_timeout_seconds | gauge |
| pg_settings_superuser_reserved_connections | gauge |
| pg_settings_synchronize_seqscans | gauge |
| pg_settings_syslog_sequence_numbers | gauge |
| pg_settings_syslog_split_messages | gauge |
| pg_settings_tcp_keepalives_count | gauge |
| pg_settings_tcp_keepalives_idle_seconds | gauge |
| pg_settings_tcp_keepalives_interval_seconds | gauge |
| pg_settings_tcp_user_timeout_seconds | gauge |
| pg_settings_temp_buffers_bytes | gauge |
| pg_settings_temp_file_limit_bytes | gauge |
| pg_settings_trace_notify | gauge |
| pg_settings_trace_sort | gauge |
| pg_settings_track_activities | gauge |
| pg_settings_track_activity_query_size_bytes | gauge |
| pg_settings_track_commit_timestamp | gauge |
| pg_settings_track_counts | gauge |
| pg_settings_track_io_timing | gauge |
| pg_settings_track_wal_io_timing | gauge |
| pg_settings_transaction_deferrable | gauge |
| pg_settings_transaction_read_only | gauge |
| pg_settings_transform_null_equals | gauge |
| pg_settings_unix_socket_permissions | gauge |
| pg_settings_update_process_title | gauge |
| pg_settings_vacuum_cost_delay_seconds | gauge |
| pg_settings_vacuum_cost_limit | gauge |
| pg_settings_vacuum_cost_page_dirty | gauge |
| pg_settings_vacuum_cost_page_hit | gauge |
| pg_settings_vacuum_cost_page_miss | gauge |
| pg_settings_vacuum_defer_cleanup_age | gauge |
| pg_settings_vacuum_failsafe_age | gauge |
| pg_settings_vacuum_freeze_min_age | gauge |
| pg_settings_vacuum_freeze_table_age | gauge |
| pg_settings_vacuum_multixact_failsafe_age | gauge |
| pg_settings_vacuum_multixact_freeze_min_age | gauge |
| pg_settings_vacuum_multixact_freeze_table_age | gauge |
| pg_settings_wal_block_size | gauge |
| pg_settings_wal_buffers_bytes | gauge |
| pg_settings_wal_compression | gauge |
| pg_settings_wal_init_zero | gauge |
| pg_settings_wal_keep_size_bytes | gauge |
| pg_settings_wal_log_hints | gauge |
| pg_settings_wal_receiver_create_temp_slot | gauge |
| pg_settings_wal_receiver_status_interval_seconds | gauge |
| pg_settings_wal_receiver_timeout_seconds | gauge |
| pg_settings_wal_recycle | gauge |
| pg_settings_wal_retrieve_retry_interval_seconds | gauge |
| pg_settings_wal_segment_size_bytes | gauge |
| pg_settings_wal_sender_timeout_seconds | gauge |
| pg_settings_wal_skip_threshold_bytes | gauge |
| pg_settings_wal_writer_delay_seconds | gauge |
| pg_settings_wal_writer_flush_after_bytes | gauge |
| pg_settings_work_mem_bytes | gauge |
| pg_settings_zero_damaged_pages | gauge |
| pg_stat_activity_count | gauge |
| pg_stat_activity_max_tx_duration | gauge |
| pg_stat_archiver_archived_count | counter |
| pg_stat_archiver_failed_count | counter |
| pg_stat_archiver_last_archive_age | gauge |
| pg_stat_bgwriter_buffers_alloc_total | counter |
| pg_stat_bgwriter_buffers_backend_fsync_total | counter |
| pg_stat_bgwriter_buffers_backend_total | counter |
| pg_stat_bgwriter_buffers_checkpoint_total | counter |
| pg_stat_bgwriter_buffers_clean_total | counter |
| pg_stat_bgwriter_checkpoint_sync_time_total | counter |
| pg_stat_bgwriter_checkpoint_write_time_total | counter |
| pg_stat_bgwriter_checkpoints_req_total | counter |
| pg_stat_bgwriter_checkpoints_timed_total | counter |
| pg_stat_bgwriter_maxwritten_clean_total | counter |
| pg_stat_bgwriter_stats_reset_total | counter |
| pg_stat_database_active_time_seconds_total | counter |
| pg_stat_database_blk_read_time | counter |
| pg_stat_database_blk_write_time | counter |
| pg_stat_database_blks_hit | counter |
| pg_stat_database_blks_read | counter |
| pg_stat_database_conflicts | counter |
| pg_stat_database_conflicts_confl_bufferpin | counter |
| pg_stat_database_conflicts_confl_deadlock | counter |
| pg_stat_database_conflicts_confl_lock | counter |
| pg_stat_database_conflicts_confl_snapshot | counter |
| pg_stat_database_conflicts_confl_tablespace | counter |
| pg_stat_database_deadlocks | counter |
| pg_stat_database_numbackends | gauge |
| pg_stat_database_stats_reset | counter |
| pg_stat_database_temp_bytes | counter |
| pg_stat_database_temp_files | counter |
| pg_stat_database_tup_deleted | counter |
| pg_stat_database_tup_fetched | counter |
| pg_stat_database_tup_inserted | counter |
| pg_stat_database_tup_returned | counter |
| pg_stat_database_tup_updated | counter |
| pg_stat_database_xact_commit | counter |
| pg_stat_database_xact_rollback | counter |
| pg_stat_replication_pg_current_wal_lsn_bytes | gauge |
| pg_stat_replication_pg_wal_lsn_diff | gauge |
| pg_stat_replication_reply_time | untyped |
| pg_static | untyped |
| pg_up | gauge |
| pg_wal_segments | gauge |
| pg_wal_size_bytes | gauge |
| postgres_exporter_build_info | gauge |
| postgres_exporter_config_last_reload_success_timestamp_seconds | gauge |
| postgres_exporter_config_last_reload_successful | gauge |
| process_cpu_seconds_total | counter |
| process_max_fds | gauge |
| process_network_receive_bytes_total | counter |
| process_network_transmit_bytes_total | counter |
| process_open_fds | gauge |
| process_resident_memory_bytes | gauge |
| process_start_time_seconds | gauge |
| process_virtual_memory_bytes | gauge |
| process_virtual_memory_max_bytes | gauge |
| promhttp_metric_handler_requests_in_flight | gauge |
| promhttp_metric_handler_requests_total | counter |
Set resource limits using annotations
Use the deployments.pds.portworx.com/prometheus-exporter-resource-limits annotation to set appropriate CPU and memory limits. The minimum limit that can be set for CPU is 40m, and for memory is 100M. If limits provided are below minimum, a warning is logged and the minimum values are enforced. When the limits are provided, requests are set to 50% of the limits.
For example, set the annotation with custom values:
kubectl annotate postgresql pg-test-jdhsdb deployments.pds.portworx.com/prometheus-exporter-resource-limits=500m,1Gi
Here, CPU limit is set to 500m with request set to 250m, and memory limit is set to 1Gi with request set to 512Mi.
Default resources (without annotation): CPU 10m/40m, Memory 25M/100M, Ephemeral Storage 100Mi/1Gi.