前言

PostgreSQL 介紹

PostgreSQL? 是一個(gè)高度穩(wěn)定和功能豐富的開(kāi)源關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)。其功能集、擴(kuò)展性和開(kāi)源特性使其成為各種類(lèi)型應(yīng)用的理想選擇，它支持高級(jí)特性如事務(wù)處理、擴(kuò)展插件和多種編程語(yǔ)言接口，適用于從 web 應(yīng)用到企業(yè)級(jí)系統(tǒng)的各種場(chǎng)景。無(wú)論是小型項(xiàng)目還是大型企業(yè)級(jí)應(yīng)用 PostgreSQL 都提供可靠、高性能的數(shù)據(jù)管理解決方案，助力創(chuàng)新和業(yè)務(wù)增長(zhǎng)。

PostgreSQL 核心特性如下：

- SQL 兼容：PostgreSQL 支持大部分 SQL 標(biāo)準(zhǔn)并提供了許多其他先進(jìn)功能，比如復(fù)雜查詢(xún)、外鍵、觸發(fā)器、視圖、事務(wù)完整性以及多版本并發(fā)控制 (MVCC)。

- 擴(kuò)展性：用戶可以通過(guò)添加新的數(shù)據(jù)類(lèi)型、函數(shù)、操作符或者索引方法來(lái)擴(kuò)展 PostgreSQL。

- 高度并發(fā)：PostgreSQL 的 MVCC 特性允許數(shù)據(jù)在讀取時(shí)不被鎖定，從而增加了并發(fā)性能和讀寫(xiě)操作的效率。

- 多種索引技術(shù)：包括 B-tree, hash, GiST, SP-GiST, BRIN 和 GIN 等多種索引技術(shù)，使得 PostgreSQL 在不同類(lèi)型的查詢(xún)中表現(xiàn)出優(yōu)異的性能。

- 可靠性和持久性：它支持點(diǎn)對(duì)點(diǎn)復(fù)制和異步復(fù)制以增強(qiáng)數(shù)據(jù)的安全性和可靠性。

- 程序化：支持多種存儲(chǔ)過(guò)程語(yǔ)言，如 PL/pgSQL（類(lèi)似 Oracle 的 PL/SQL）、PL/Python、PL/Perl、PL/Ruby 等。

- JSON 支持：提供對(duì) JSON 數(shù)據(jù)格式的原生支持，允許直接在數(shù)據(jù)庫(kù)中處理 JSON 數(shù)據(jù)。

使用可觀測(cè)性工具對(duì) PostgreSQL 數(shù)據(jù)庫(kù)進(jìn)行監(jiān)控和跟蹤至關(guān)重要，因?yàn)樗鼈兲峁┝藢?duì)數(shù)據(jù)庫(kù)性能、健康狀況和運(yùn)行狀況的深入洞察。通過(guò)可觀測(cè)性，數(shù)據(jù)庫(kù)管理員和開(kāi)發(fā)人員能夠?qū)崟r(shí)檢測(cè)和診斷問(wèn)題，從而快速響應(yīng)并解決潛在的性能瓶頸、查詢(xún)效率低下或系統(tǒng)錯(cuò)誤。此外，可觀測(cè)性還有助于預(yù)測(cè)未來(lái)的資源需求，優(yōu)化數(shù)據(jù)庫(kù)配置，以及確保數(shù)據(jù)的安全性和合規(guī)性?？傊?，可觀測(cè)性是確保 PostgreSQL 數(shù)據(jù)庫(kù)穩(wěn)定、高效運(yùn)行的關(guān)鍵因素。

觀測(cè)云介紹

作為一款具備可觀測(cè)性的統(tǒng)一實(shí)時(shí)監(jiān)測(cè)應(yīng)用，觀測(cè)云可以幫助用戶快速實(shí)現(xiàn)系統(tǒng)可觀測(cè)性，統(tǒng)一滿足云平臺(tái)、云原生、應(yīng)用及業(yè)務(wù)相關(guān)的監(jiān)測(cè)需求，為每一個(gè)完整的應(yīng)用構(gòu)建全鏈路的可觀測(cè)性。觀測(cè)云數(shù)據(jù)的采集是通過(guò) DataKit 來(lái)實(shí)現(xiàn)的，只需要安裝 DataKit，開(kāi)通 PostgreSQL 采集器，即可從 PostgreSQL 實(shí)例中采集實(shí)例運(yùn)行狀態(tài)指標(biāo)，并將指標(biāo)采集到觀測(cè)云，幫助監(jiān)控分析 PostgreSQL 各種異常情況。以下是安裝 DataKit 并開(kāi)啟配置 PostgreSQL 采集器的介紹。

前提條件

部署 DataKit

登錄[觀測(cè)云控制臺(tái)](https://console.guance.com/)，點(diǎn)擊「集成」 -「DataKit」 - 「Linux」，復(fù)制命令并執(zhí)行。

PostgreSQL 監(jiān)控帳號(hào)

- PostgreSQL 版本 >= 9.0

- 創(chuàng)建監(jiān)控帳號(hào)

-- PostgreSQL >= 10

create user datakit with password '<PASSWORD>';

grant pg_monitor to datakit;

grant SELECT ON pg_stat_database to datakit;

-- PostgreSQL < 10

create user datakit with password '<PASSWORD>';

grant SELECT ON pg_stat_database to datakit;

操作步驟

指標(biāo)采集

以下主要是通過(guò)主機(jī)的方式進(jìn)行采集，進(jìn)入 DataKit 安裝目錄下的 `conf.d/db` 目錄，復(fù)制 `postgresql.conf.sample` 并命名為 `postgresql.conf`。

注意：address 定義了連接到 postgresql 服務(wù)器的 URI，這里的 datakit 是用戶名，PASSWORD 應(yīng)被替換為實(shí)際的密碼，localhost 是數(shù)據(jù)庫(kù)服務(wù)器地址，`sslmode=disable` 表示不使用 SSL 連接。

`postgresql_lock` ，`postgresql_stat` ，`postgresql_index` ，`postgresql_size` ，`postgresql_statio` 這些指標(biāo)需要開(kāi)啟配置文件中的 relations 字段。

[[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_production

? address = "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 = "products"

? relation_regex = ".*"

? schemas = ["public"]

? relkind = ["r", "p"]

...

日志采集

PostgreSQL 日志輸出

PostgreSQL 日志默認(rèn)是輸出至 `stderr` ，如需開(kāi)啟文件日志，可在 PostgreSQL 的配置文件 `/var/lib/pgsql/15/data/postgresql.conf` 調(diào)整輸出：

logging_collector = on? ? # 開(kāi)啟日志寫(xiě)入文件功能

log_directory = 'pg_log'? # 設(shè)置文件存放目錄，絕對(duì)路徑或相對(duì)路徑（相對(duì) PGDATA）

log_filename = 'pg.log'? # 日志文件名稱(chēng)

log_statement = 'all'? ? # 記錄所有查詢(xún)

#log_duration = on

log_line_prefix= '%m [%p] %d [%a] %u [%h] %c ' # 日志行前綴

log_file_mode = 0644

# For Windows

#log_destination = 'eventlog'

PostgreSQL 采集器開(kāi)啟日志采集

PostgreSQL 采集器默認(rèn)是未開(kāi)啟日志采集功能，可在 `conf.d/db/postgresql.conf` 中將 files 打開(kāi)，并寫(xiě)入 PostgreSQL 日志文件的絕對(duì)路徑。在 `[inputs.postgresql.log]` 模塊下開(kāi)啟日志采集器配置，如下：

[[inputs.postgresql]]

? ...

? [inputs.postgresql.log]

? files = ["/tmp/pgsql/postgresql.log"]

? multiline_match = '''^\S'''

? auto_multiline_detection = true

? auto_multiline_extra_patterns = []

日志 Pipeline 切割

Pipelines 是一種運(yùn)行在 DataKit 上的輕量級(jí)腳本語(yǔ)言，用于對(duì)采集到的數(shù)據(jù)進(jìn)行自定義解析和修改。通過(guò)定義解析規(guī)則，它們能夠?qū)⒉煌N類(lèi)的數(shù)據(jù)細(xì)粒度地切割并轉(zhuǎn)換為結(jié)構(gòu)化的格式，以滿足具體的數(shù)據(jù)管理需求。以下是使用pipeline新建規(guī)則對(duì)日志原文進(jìn)行切割的步驟：

在觀測(cè)云工作空間「日志 > Pipelines」，點(diǎn)擊「新建 Pipeline」，在新建 Pipeline 頁(yè)面，可以先過(guò)濾出想要進(jìn)行文本處理的「數(shù)據(jù)范圍」，然后再定義「解析規(guī)則」，若想要測(cè)試輸入的解析規(guī)則是否正確有效，可以在「樣本解析」測(cè)試輸入對(duì)應(yīng)的數(shù)據(jù)進(jìn)行測(cè)試，測(cè)試通過(guò)后點(diǎn)擊「保存」即可創(chuàng)建 Pipeline 文件。

pipeline 規(guī)則：

add_pattern("application_name", "\\[\\w+ \\d+ - DB:\\w+\\]")

grok(_, '%{TIMESTAMP_ISO8601:time} %{NOTSPACE:timezone} \\[%{NOTSPACE:process_id}\\] %{NOTSPACE:db_name} %{application_name:application_name} %{NOTSPACE:user} \\[%{NOTSPACE:localhost}\\] %{NOTSPACE:session_id} ')

default_time(time)? ? ? # 將 time 字段作為輸出數(shù)據(jù)的時(shí)間戳

日志原文：

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'

切割效果：

PostgreSQL 采集器完整配置

# {"version": "1.34.0", "desc": "do NOT edit this line"}

[[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_production

? address = "postgres://datakit:datakit@localhost/mydb?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 = "products"

? relation_regex = ".*"

? schemas = ["public"]

? relkind = ["r", "p"]

? ## Set true to enable election

? election = 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 = ["/var/lib/pgsql/15/data/log/*.log"]

? # pipeline = "postgresql.p"

? multiline_match = '''^\S'''

? auto_multiline_detection = true

? auto_multiline_extra_patterns = []

? ## Custom tags

? #

? [inputs.postgresql.tags]

? server = "pgsql"

? # more_tag = "some_other_value"

? # ...

重啟 Datakit

Datakit 配置調(diào)整完成后，需要重啟才能生效。

datakit service -R

監(jiān)控視圖

登錄觀測(cè)云控制臺(tái)，點(diǎn)擊「場(chǎng)景」 -「新建儀表板」，輸入 " PostgreSQL "， 選擇" PostgreSQL 監(jiān)控視圖 "，點(diǎn)擊"確定"。

關(guān)鍵指標(biāo)

緩存

通過(guò)對(duì) `blks_hit` 和 `blks_read` 這兩個(gè)關(guān)鍵指標(biāo)的詳細(xì)監(jiān)控和分析，用戶可以全面了解 PostgreSQL 數(shù)據(jù)庫(kù)的緩存命中率和磁盤(pán) I/O 性能。

- `blks_hit` 指標(biāo)記錄了在 SLRU 緩存中成功命中的磁盤(pán)塊數(shù)量，高的 `blks_hit` 值表示大部分?jǐn)?shù)據(jù)請(qǐng)求能夠直接從緩存中獲取，減少了磁盤(pán) I/O 操作，從而提升了數(shù)據(jù)庫(kù)的整體性能和響應(yīng)速度

- `blks_read` 指標(biāo)記錄了從 SLRU 緩存中未命中時(shí)，從磁盤(pán)實(shí)際讀取的磁盤(pán)塊數(shù)量，高的 `blks_read` 值意味著頻繁的磁盤(pán)讀取操作，可能導(dǎo)致響應(yīng)時(shí)間延長(zhǎng)和吞吐量下降

事物活動(dòng)

`xact_commit` 和 `xact_rollback` 這兩個(gè)關(guān)鍵指標(biāo)在監(jiān)控?cái)?shù)據(jù)庫(kù)事務(wù)活動(dòng)、評(píng)估系統(tǒng)穩(wěn)定性和優(yōu)化性能方面有很大作用。

- `xact_commit` 指標(biāo)記錄了在指定時(shí)間段內(nèi)，成功提交到數(shù)據(jù)庫(kù)中的事務(wù)總數(shù)，通過(guò)這個(gè)指標(biāo)可以了解業(yè)務(wù)的吞吐量，評(píng)估數(shù)據(jù)庫(kù)的使用狀況，較高的提交事務(wù)數(shù)表示數(shù)據(jù)庫(kù)正在處理大量的操作

- xact_rollback指標(biāo)記錄了在指定時(shí)間段內(nèi)，因各種原因未能成功提交并被回滾的事務(wù)總數(shù)。高的回滾次數(shù)可能反映出系統(tǒng)存在潛在問(wèn)題，如應(yīng)用程序邏輯錯(cuò)誤、數(shù)據(jù)一致性問(wèn)題或并發(fā)事務(wù)沖突

死鎖

`dealocks` 指標(biāo)記錄了在指定時(shí)間段內(nèi)，數(shù)據(jù)庫(kù)中檢測(cè)到的死鎖事件總數(shù)，死鎖事件的頻繁發(fā)生會(huì)導(dǎo)致部分事務(wù)無(wú)法完成，進(jìn)而影響整體數(shù)據(jù)庫(kù)的穩(wěn)定性和可靠性。

行數(shù)變動(dòng)

- `tup_inserted`：插入的行數(shù)。

- `tup_updated`：更新的行數(shù)。

- `tup_deleted`：刪除的行數(shù)。

這些指標(biāo)反映了數(shù)據(jù)庫(kù)中新增數(shù)據(jù)的速率和量級(jí)、變更的頻率和規(guī)模、數(shù)據(jù)清理的情況和頻率，讀取的負(fù)載和查詢(xún)效率等。這些指標(biāo)反映了數(shù)據(jù)庫(kù)的操作量，識(shí)別哪些業(yè)務(wù)功能最為活躍，以及業(yè)務(wù)高峰期的數(shù)據(jù)庫(kù)需求，從而進(jìn)行更精準(zhǔn)的資源分配和優(yōu)化有助于了解業(yè)務(wù)負(fù)載及其對(duì)數(shù)據(jù)庫(kù)的影響，輔助容量規(guī)劃和性能優(yōu)化。

監(jiān)控器

觀測(cè)云擁有強(qiáng)大的異常監(jiān)測(cè)能力，不僅提供了包括 Docker、Elasticsearch、Host 等一系列監(jiān)控模板，還支持自定義監(jiān)控器。通過(guò)監(jiān)控器配置數(shù)據(jù)指標(biāo)檢測(cè)，從而即時(shí)觸發(fā)告警事件，配合告警通知功能，可及時(shí)發(fā)現(xiàn)幫助您快速發(fā)現(xiàn)問(wèn)題、定位問(wèn)題、解決問(wèn)題。

連接使用率告警

如果 PostgreSQL 連接使用率過(guò)高，可能會(huì)導(dǎo)致新的連接請(qǐng)求被拒絕，從而影響到應(yīng)用程序的正常運(yùn)行。告警可以在連接數(shù)接近數(shù)據(jù)庫(kù)最大連接數(shù)之前，能夠采取相應(yīng)措施，比如增加數(shù)據(jù)庫(kù)連接數(shù)限制、優(yōu)化應(yīng)用程序的連接使用策略或擴(kuò)展硬件資源，以避免性能瓶頸和潛在的服務(wù)中斷。

死鎖數(shù)監(jiān)控

死鎖事件的頻繁發(fā)生會(huì)導(dǎo)致部分事務(wù)無(wú)法完成，進(jìn)而影響整體數(shù)據(jù)庫(kù)的穩(wěn)定性和可靠性。

磁盤(pán)訪問(wèn)效率告警

使用 `blks_hit` 和 `blks_read` ，我們可以計(jì)算緩存命中率，這是衡量緩存效率的一個(gè)重要指標(biāo)。緩存命中率指的是從緩存中獲取數(shù)據(jù)塊的操作占所有數(shù)據(jù)塊獲取操作（包括從磁盤(pán)讀取和從緩存獲?。┑谋壤?。

事物提交/回滾告警

事務(wù)提交/回滾率告警和磁盤(pán)塊訪問(wèn)效率告警，關(guān)注數(shù)據(jù)庫(kù)事務(wù)的健康和數(shù)據(jù)庫(kù)讀取效率

總結(jié)

觀測(cè)云監(jiān)控工具為 PostgreSQL 數(shù)據(jù)庫(kù)提供全面的性能監(jiān)控與管理解決方案，能夠?qū)崟r(shí)收集和分析關(guān)鍵性能指標(biāo)和系統(tǒng)日志，可以確保數(shù)據(jù)庫(kù)的穩(wěn)定運(yùn)行和高效性能。通過(guò)整合和分析來(lái)自不同來(lái)源的數(shù)據(jù)，觀測(cè)云使得數(shù)據(jù)庫(kù)管理更加智能化和自動(dòng)化。