PostgreSQL collector can collect the running status index from PostgreSQL instance, and collect the index to Guance Cloud to help monitor and analyze various abnormal situations of PostgreSQL.
Go to the conf.d/db directory under the DataKit installation directory, copy postgresql.conf.sample and name it postgresql.conf. Examples are as follows:
[[inputs.postgresql]]## Server address# URI format# postgres://[datakit[:PASSWORD]]@localhost[/dbname]?sslmode=[disable|verify-ca|verify-full]# or simple string# host=localhost user=pqgotest password=... sslmode=... dbname=app_productionaddress="postgres://datakit:PASSWORD@localhost?sslmode=disable"## Ignore databases which are gathered. Do not use with 'databases' option.## ignored_databases = ["db1"]## Specify the list of the databases to be gathered. Do not use with the 'ignored_databases' option.## databases = ["db1"]## Specify the name used as the "server" tag.## outputaddress = "db01"## Collect interval# Time unit: "ns", "us" (or "µs"), "ms", "s", "m", "h"#interval="10s"## Relations config# The list of relations/tables can be specified to track per-relation metrics. To collect relation# relation_name refer to the name of a relation, either relation_name or relation_regex must be set.# relation_regex is a regex rule, only takes effect when relation_name is not set.# schemas used for filtering, ignore this field when it is empty# relkind can be a list of the following options:# r(ordinary table), i(index), S(sequence), t(TOAST table), p(partitioned table),# m(materialized view), c(composite type), f(foreign table)## [[inputs.postgresql.relations]]# relation_name = "<TABLE_NAME>"# relation_regex = "<TABLE_PATTERN>"# schemas = ["public"]# relkind = ["r", "p"]## Set true to enable electionelection=true## Run a custom SQL query and collect corresponding metrics.## [[inputs.postgresql.custom_queries]]# sql = '''# select datname,numbackends,blks_read# from pg_stat_database# limit 10# '''# metric = "postgresql_custom_stat"# tags = ["datname" ]# fields = ["numbackends", "blks_read"]## Log collection## [inputs.postgresql.log]# files = []# pipeline = "postgresql.p"## Custom tags#[inputs.postgresql.tags]# some_tag = "some_value"# more_tag = "some_other_value"# ...
For all of the following data collections, a global tag named host is appended by default (the tag value is the host name of the DataKit), or it can be named by [[inputs.postgresql.tags]] alternative host in the configuration.
Number of queries in this database that have been canceled due to pinned buffers.
int
count
confl_deadlock
Number of queries in this database that have been canceled due to deadlocks.
int
count
confl_lock
Number of queries in this database that have been canceled due to dropped tablespaces. This will occur when a temp_tablespace is dropped while being used on a standby.
int
count
confl_snapshot
Number of queries in this database that have been canceled due to old snapshots.
int
count
confl_tablespace
Number of queries in this database that have been canceled due to dropped tablespaces. This will occur when a temp_tablespace is dropped while being used on a standby.
PostgreSQL logs are output to stderr by default. To open file logs, configure them in postgresql's configuration file /etc/postgresql/<VERSION>/main/postgresql.conf as follows:
logging_collector=on# Enable log writing to fileslog_directory='pg_log'# Set the file storage directory, absolute path or relative path (relative PGDATA)log_filename='pg.log'# Log file namelog_statement='all'# Record all queries#log_duration = onlog_line_prefix='%m [%p] %d [%a] %u [%h] %c '# 日志行前缀log_file_mode=0644# For Windows#log_destination = 'eventlog'
The PostgreSQL collector does not have log collection enabled by default. You can open files in conf.d/db/postgresql.conf and write to the absolute path of the PostgreSQL log file. For example:
2021-05-31 15:23:45.110 CST [74305] test [pgAdmin 4 - DB:postgres] postgres [127.0.0.1] 60b48f01.12241 LOG: statement:
SELECT psd.*, 2^31 - age(datfrozenxid) as wraparound, pg_database_size(psd.datname) as pg_database_size
FROM pg_stat_database psd
JOIN pg_database pd ON psd.datname = pd.datname
WHERE psd.datname not ilike 'template%' AND psd.datname not ilike 'rdsadmin'
AND psd.datname not ilike 'azure_maintenance' AND psd.datname not ilike 'postgres'
Description of the cut field:
Field name
Field Value
Description
application_name
pgAdmin 4 - DB:postgres
The name of the application connecting to the current database
db_name
test
Database accessed
process_id
74305
The client process ID of the current connection
remote_host
127.0.0.1
Address of the client
session_id
60b48f01.12241
ID of the current session
user
postgres
Current Access User Name
status
LOG
Current log level (LOG,ERROR,FATAL,PANIC,WARNING,NOTICE,INFO)
To report these metrics, the relations field in the configuration file needs to be enabled. If some of these metrics are partially missing, it may be because there is no data for the relevant metrics.