PostgreSQL 可觀測性最佳實踐

軟體簡報

PostgreSQL 是乙個開源的關聯式資料庫管理系統 （RDBMS），它提供了許多可觀測性選項來確保資料庫的穩定性和可靠性。

可觀察性

可觀測性是指監控和記錄資料庫狀態和操作，以便在系統出現問題時可以快速診斷和修復問題。

可觀測性雲提供簡單高效的PostgreSQL可觀測性解決方案，幫助客戶快速定位和解決資料庫相關問題。

DataKit是Observation Cloud自研的開源、一體化資料採集代理，提供全平台作業系統支援和全面的資料採集能力，覆蓋主機、容器、中介軟體、鏈路、日誌、安全等多種場景。 通過它攝取 PostgreSQL 資料只需兩個步驟：

步驟 1：安裝 DataKit 資料採集器。

步驟二：通過DataKit內建的PostgreSQL外掛程式採集資料。

配置舉例：

[[inputs.postgresql]] address = "postgres://postgres@localhost/test?sslmode=disable" interval = "60s" [[inputs.postgresql.relations]] relation_regex = "test*" schemas = ["public"] relkind = ["r", "p"] [inputs.postgresql.log] files = ["/var/log/pgsql/*.log""] pipeline = "postgresql.p"

1.pg_stat_database (datakit postgresql)

示例語句：

postgres=# select * from pg_stat_database where datname='postgres';-[record 1 ]-datid | 14486datname | postgresnumbackends | 2xact_commit | 1406600xact_rollback | 20720blks_read | 1558blks_hit | 48043798tup_returned | 289085449tup_fetched | 21237763tup_inserted | 174tup_updated | 5tup_deleted | 41conflicts | 0temp_files | 0temp_bytes | 0deadlocks | 0checksum_failures |checksum_last_failure |blk_read_time | 0blk_write_time | 0session_time | 1030041341.636active_time | 1740209.944idle_in_transaction_time | 879253.682sessions | 15950sessions_abandoned | 2sessions_fatal | 0sessions_killed | 4stats_reset | 2023-04-06 11:04:11.693074+08

當 tup 返回值遠大於獲取的 tup 時，說明資料庫在歷史上執行的很多 SQL 語句都是全表掃瞄，並且有很多 SQL 語句不遵循索引。

當 tup 更新非常高時，說明資料庫已經頻繁更新，這時需要注意真空相關指標和長事務，如果不及時進行垃圾處理，會導致資料膨脹更嚴重，在一定程度上會響應表查詢效率。

當臨時檔案的值較大時，意味著有大量的排序雜湊或聚合操作，通過增加工作記憶體可以減少臨時檔案的生成，這些操作的效能也會大大提高。

2.pg_stat_user_tables (datakit postgresql_stat)

示例語句：

select * from pg_stat_user_tables where relname='test';-[record 1 ]-relid | 16455schemaname | publicrelname | testseq_scan | 1seq_tup_read | 0idx_scan | 0idx_tup_fetch | 0n_tup_ins | 7n_tup_upd | 0n_tup_del | 0n_tup_hot_upd | 0n_live_tup | 7n_dead_tup | 0n_mod_since_analyze | 7n_ins_since_vacuum | 7last_vacuum |last_autovacuum |last_analyze |last_autoanalyze |vacuum_count | 0autovacuum_count | 0analyze_count | 0autoanalyze_count | 0

3.pg_stat_user_indexes (datakit postgresql_index)

示例語句：

select * from pg_stat_user_indexes where relname='test';-[record 1 ]-relid | 16455indexrelid | 16460schemaname | publicrelname | testindexrelname | test_pkeyidx_scan | 0idx_tup_read | 0idx_tup_fetch | 0

4.pg_statio_user_tables (datakit postgresql_statio)

示例語句：

select * from pg_statio_user_tables where relname='test';-[record 1 ]-relid | 16455schemaname | publicrelname | testheap_blks_read | 1heap_blks_hit | 6idx_blks_read | 2idx_blks_hit | 8toast_blks_read | 0toast_blks_hit | 0tidx_blks_read | 0tidx_blks_hit | 0

5.pg_stat_bgwriter (datakit postgresql_bgwriter)

示例語句：

select * from pg_stat_bgwriter;-[record 1 ]-checkpoints_timed | 14438checkpoints_req | 14checkpoint_write_time | 64064checkpoint_sync_time | 83buffers_checkpoint | 656buffers_clean | 0maxwritten_clean | 0buffers_backend | 220buffers_backend_fsync | 0buffers_alloc | 4674stats_reset | 2023-04-06 11:00:39.227749+08

6.pg_stat_replication (datakit postgresql_replication)

示例語句：

select * from pg_stat_replication;-[record 1 ]-pid | 1492usesysid | 12849usename | guanceapplication_name | walreceiverclient_addr | 192.168.0.187client_hostname |client_port | 41760backend_start | 2023-05-12 16:41:09.54947+08backend_xmin |state | streamingsent_lsn | 2/100001b0write_lsn | 2/100001b0flush_lsn | 2/100001b0replay_lsn | 2/100001b0write_lag |flush_lag |replay_lag |sync_priority | 0sync_state | async

例如，同步狀態可以分為：

async：表示備用資料庫處於非同步同步模式。

潛在：表示備用資料庫當前處於非同步同步模式，如果當前同步備用資料庫出現故障，則可以將非同步備用資料庫公升級為同步備用資料庫。

sync ：表示當前備庫處於同步模式。

Quorum：表示備用資料庫是 QuorumStandbys 的候選項。

PostgreSQL 有 3 種型別的日誌，分別是：

PG xlog 和 PG clog 通常位於 PostgreSQL 安裝目錄的資料夾中。

pg log 的預設路徑位於 postgresql 安裝目錄中pg_log，實際路徑可以在postgresql.conf檔案。

1.pg_log

該日誌一般記錄伺服器和資料庫的狀態，如各種錯誤資訊、定位慢查詢SQL、資料庫啟動和關閉資訊、頻繁檢查點等告警資訊等。 日誌有csv 格式和log。建議使用CSV 格式，因為它通常按大小和時間自動拆分，畢竟，檢視乙個巨大的日誌檔案比檢視不同時間段的多個日誌要困難得多。

清理原理：可以使用pg log清理和刪除，壓縮包裝或轉移，同時合併不，它沒有資料庫的正常執行。

2.pg_xlog

該日誌是PostgreSQL的WAL資訊，是一些事務日誌資訊。 這些日誌（如“00000001000000000000000000008e”）包含最近失敗的資料的映象，可用於定期回滾恢復 （PITR）、複製流式處理和存檔。

當存檔或流複製中發生異常時，將持續生成事務日誌，這可能會導致磁碟空間被填滿，最終資料庫將掛起或無法啟動。 在這種情況下，可以先關閉歸檔或流式複製功能，將PG XLOG日誌備份到其他地方，但不要刪除，然後刪除之前的PG XLOG，等到有一定的空間後再嘗試啟動PostgreSQL。

清理原則：這些日誌非常重要，它記錄了資料庫中發生的各種事務的資訊它不能被隨意刪除或者移動這些日誌檔案，否則您的資料庫將包含它們它無法恢復風險。

什麼是沃爾？

PostgreSQL在將快取資料刷寫到磁碟之前寫入日誌，這是PostgreSQL WAL（Write-ahead Log）方法，即預寫日誌方法。

3.pg_clog

pg clog 檔案也是乙個事務日誌檔案，但與 pg xlog 不同的是，它記錄了事務的元資料，它告訴我們哪些事務已完成，哪些沒有完成。

清理原則：但是，此日誌檔案通常非常小重要性它也相當高它不能被隨意刪除或更改有關它的資訊。

可以通過配置檔案來完成postgresql.conf設定它。

主要引數：

logging_collector = on/off

預設情況下，是否將日誌重定向到檔案處於關閉狀態。

log_directory =pg_log

日誌檔案目錄預設為pgdata的相對路徑，即pgdata的相對路徑，即pg log，也可以改為絕對路徑。 日誌檔案可能非常大，建議將日誌重定向到其他目錄或分割槽。 將此配置修改到另乙個目錄時，必須先建立該目錄並修改許可權，以便 Postgres 使用者具有對該目錄的寫入許可權。

log_filename =postgresql-%y-%m-%d*%h%m%s.log

預設情況下，可以對日誌檔案進行命名。

log_rotation_age = 1d

單個日誌檔案的生存期，預設為 1 天，當日誌檔案大小未達到日誌輪換大小時，每天只生成乙個日誌檔案。

log_rotation_size = 10mb

單個日誌檔案的大小，如果時間不超過日誌輪換年齡，乙個日誌檔案最多只能10M，否則會生成乙個新的日誌檔案。

log_truncate_on_rotation = off

當日誌檔案已存在時，如果關閉，則新生成的日誌將追加到檔案末尾，如果開啟，則將覆蓋原始日誌。

log_lock_waits = off

控制當會話等待時間超過死鎖超時並被鎖定時是否生成日誌訊息。 在確定鎖定等待是否會影響效能時很有用，預設值為關閉。

log_statement =none# none, ddl, mod, all

控制記錄哪些 SQL 語句。 none，ddl 記錄所有資料定義命令，例如 create、alter 和 drop 語句。 mod 記錄了所有 DDL 語句，以及 insert、update 等資料修改語句。 ALL 記錄所有已執行的語句，此配置設定為 ALL 以跟蹤在整個資料庫中執行的 SQL 語句。

log_duration = off

記錄每條SQL語句完成執行所花費的時間，並將此配置設定為on，以統計哪些SQL語句耗時較長。

log_min_duration_statement = -1

1 表示記錄已關閉。 0表示記錄所有語句的執行時間，如果值為0（毫秒），則記錄語句的執行時間大於該值。 您可以使用此配置來跟蹤耗時且可能存在效能問題的 SQL 語句。 雖然也可以使用日誌語句和日誌持續時間來統計 SQL 語句和耗時，但 SQL 語句和耗時統計可能彼此不同，或者在不同的檔案中，但日誌最小持續時間語句會在同一行中記錄 SQL 語句和耗時，更易於閱讀。

log_connections = off

是否記錄連線日誌。

log_disconnections = off

是否記錄連線斷開日誌。

log_line_prefix =%m %p %u %d %r

日誌輸出格式（%m、%p實際含義在配置檔案中說明），可根據您的需求進行設定（可記錄時間、使用者名稱、資料庫名稱、客戶端IP和埠，方便定位問題）。

log_timezone =asia/shanghai

日誌時區應與伺服器的時區相同，以便於問題定位。

Observation Cloud 內建了 PostgreSQL 場景檢視，可以直接使用，使用者也可以自定義和修改任何需要的指標檢視。

登入 Observation Cloud 控制台 - 場景 - 儀錶盤 - 建立儀錶盤 - 系統檢視，搜尋 postgresql 並新增。

如果要基於此自定義圖表，可以參考 Observation Cloud 文件：視覺化圖表。