Postgres 資料庫（一）基本命令

夏之夜發表於2024-04-09
資料庫

一、主備資料一致性

1、透過 wal 檢測兩個資料庫例項資料是否一致（該sql語句需要在主庫上執行）

1.1 pg_stat_replication 可以進行流複製監控（pg_stat_replication檢視顯示WAL傳送程序的詳細資訊）

postgres=#SELECT *  FROM pg_stat_replication ;
-[RECORD 1 ]--—-----+-—----------------------
pid                 |  7683
usesysid            |  16384
usename             |  repuser
application_name    |  node2
client_addr         |  192.168.28.75
client_hostname     |  
client_port         |  57870
backend_start       |  2017-09-05 11:50:31.629468+08
backend_xmin        |  
state               |  streaming          
sent_lsn            |  3/643CB568 
write_lsn           |  3/643CB568
flush_lsn           |  3/643CB488
replay_lsn          |  3/643CB030
write_lag           |  00:00:00.000224
flush_lag           |  00:00:00.001562
replay_lag          |  00:00:00.006596
sync_priority       |  1
sync_state          |  sync

檢視中的主要欄位解釋如下:

  • pid: WAL傳送程序的程序號。
  • usename: WAL傳送程序的資料庫使用者名稱。
  • application_name :連線WAL傳送程序的應用別名，此引數顯示值為備庫recovery.conf配置檔案中primary_conninfo引數application_name選項的值。
  • client_addr:連線到WAL傳送程序的客戶端IP地址，也就是備庫的IP。backend_start: WAL傳送程序的啟動時間。
  • state:顯示WAL傳送程序的狀態，startup表示WAL程序在啟動過程中; catchup表示備庫正在追趕主庫;streaming表示備庫已經追趕上了主庫，並且主庫向備庫傳送WAL日誌流，這個狀態是流複製的常規狀態;backup表示透過pg_basebackup正在進行備份; stopping表示 WAL傳送程序正在關閉。
  • sent_lsn: WAL傳送程序最近傳送的WAL日誌位置。
  • write_Isn :備庫最近寫入的WAL日誌位置，這時WAL日誌流還在作業系統快取中，還沒寫入備庫 WAL日誌檔案。
  • flush_Isn:備庫最近寫入的WAL日誌位置，這時WAL日誌流已寫入備庫WAL日誌檔案。
  • replay_lsn:備庫最近應用的WAL日誌位置。
  • write_lag :主庫上WAL日誌落盤後等待備庫接收WAL日誌(這時WAL日誌流還沒寫入備庫 WAL日誌檔案，還在作業系統快取中）並返回確認資訊的時間。
  • flush_lag:主庫上WAL日誌落盤後等待備庫接收WAL日誌(這時WAL日誌流已寫入備庫WAL日誌檔案，但還沒有應用WAL日誌）並返回確認資訊的時間。
  • replay_lag:主庫上WAL日誌落盤後等待備庫接收WAL日誌(這時WAL日誌流已寫入備庫WAL日誌檔案，並且已應用WAL日誌）並返回確認資訊的時間。
  • sync_priority:基於優先順序的模式中備庫被選中成為同步備庫的優先順序，對於基於quorum的選舉模式此欄位則無影響。
  • sync_state:同步狀態，有以下狀態值，async表示備庫為非同步同步模式; potential表示備庫當前為非同步同步模式，如果當前的同步備庫當機，非同步備庫可升級成為同步備庫;sync表示當前備庫為同步模式;quorum表示備庫為quorum standbys 的候選。

1.2 pg_stat_wal_receiver 監控備庫的資訊（顯示WAL接收程序詳細資訊）

postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-----------------
pid                   |  22573
status                |  streaming
receive_start_lsn     |  3/2D000000
receive_start_tli     |  1
received_lsn          |  3/852DC428
received_tli          |  1
last_msg_send_time    |  2017-09-06 15:35:28.178167+08
last_msg_receipt_time |  2017-09-06 15:35:28.177706+08
latest_end_lsn        |  3/852DC508
latest_end_time       |  2017-09-0615:35:28.178167+08
slot_name             |  
conninfo              |  user=repuser passfile=/home/postgres/.pgpass dbname=replication
    host=192.168.28.74 port=1921 application_name=node2 fallback_application_name=walreceiver sslmode=disable sslcompression=1 target_session_attrs=any

說明：

  • pid:WAL接收程序的程序號。status: WAL接收程序的狀態。
  • receive_start_lsn: WAL接收程序啟動後使用的第一個 WAL日誌位置。received_lsn:最近接收並寫入WAL日誌檔案的WAL位置。
  • last_msg_send_time :備庫接收到傳送程序最後一個訊息後，向主庫發回確認訊息的傳送時間。
  • last_msg_receipt_time:備庫接收到傳送程序最後一個訊息的接收時間。
  • conninfo: WAL接收程序使用的連線串，連線資訊由備庫SPGDATA目錄的recovery.
  • conf配置檔案的 primary_conninfo引數配置

1.3 資料一致性檢測

1.3.1 使用 WAL日誌應用延遲量衡量 監控主備同步資料延遲

select pid ,usename,client_addr,state,pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_depaly,pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_delay,write_lag,flush_lag,replay_lag  from pg_stat_replication;

說明：

  • lsn：在pg中的每寫入一條記錄都會在wal日誌中增加一條wal記錄，寫入這個記錄的位置就是lsn，全稱為Log Sequence Number，lsn的值是wal檔案中位元組偏移量
  • pg_current_wal_lsn()：函式顯示流複製主庫當前WAL日誌檔案寫入的位置
  • pg_wal_Isn_diff()：函式計算兩個WAL日誌位置之間的偏移量，返回單位為位元組數
  • write_Isn :備庫最近寫入的WAL日誌位置，這時WAL日誌流還在作業系統快取中，還沒寫入備庫 WAL日誌檔案。
  • flush_Isn:備庫最近寫入的WAL日誌位置，這時WAL日誌流已寫入備庫WAL日誌檔案。
  • replay_lsn:備庫最近應用的WAL日誌位置。

1.3.2 使用 WAL延遲時間衡量 監控主備同步資料延遲

SELECT pid, usename,client_addr,state,write_lag,flush_lag, replay_lag FROM pg_stat_replication;

說明：

  • write_lag :主庫上WAL日誌落盤後等待備庫接收WAL日誌(這時WAL日誌流還沒寫入備庫 WAL日誌檔案，還在作業系統快取中）並返回確認資訊的時間。
  • flush_lag:主庫上WAL日誌落盤後等待備庫接收WAL日誌(這時WAL日誌流已寫入備庫WAL日誌檔案，但還沒有應用WAL日誌）並返回確認資訊的時間。
  • replay_lag:主庫上WAL日誌落盤後等待備庫接收WAL日誌(這時WAL日誌流已寫入備庫WAL日誌檔案，並且已應用WAL日誌）並返回確認資訊的時間。

write_lag、flush_lag、replay_lag 為 PostgreSQL10版本新增欄位，10版本前pg_statreplication檢視不提供這三個欄位，但是也有辦法監控主備延時，在流複製備庫執行以下SQL：

SELECT EXTRACT(SECOND FROM now ()- pg_last_xact_replay_timestamp());

說明：

pg_last_xact_replay_timestamp函式顯示備庫最近WAL日誌應用時間，透過與當前時間比較可粗略計算主備庫延時，這種方式的優點是即使主庫宕掉，也可以大概判斷主備延時。缺點是如果主庫上只有讀操作，主庫不會傳送WAL日誌流到備庫，pg_last_xact_replay_timestamp 函式返回的結果就是一個靜態的時間，這個公式的判斷結果就不嚴謹了。

1.3.3 透過建立主備延時測算表方式

這種方法在主庫上建立一張主備延時測算表，並定時往表插入資料或更新資料，之後在備庫上計算這條記錄的插入時間或更新時間與當前時間的差異來判斷主備延時，這種方法不是很嚴謹，但很實用，當主庫當機時，這種方式依然可以大概判斷出主備延時。

2、透過 pg_controldata 命令獲取兩個pg例項的時間線來判定兩邊資料是否一致 

$ pg_controldata | grep TimeLineID   時間線檢查

說明：

該命令需要在主備機的 pg 例項上均執行，獲取各自的時間線進行比對。

二、統計資料庫大小

1、查詢所有庫資料大小

postgres=# SELECT pg_database.datname as "Database",
postgres-# pg_size_pretty(pg_database_size(pg_database.datname)) as "Size"
postgres-# FROM pg_database
postgres-# ORDER BY pg_database_size(pg_database.datname) DESC;
     Database     |  Size
------------------+---------
 postgres         | 65 GB
 lm_lmlicensedb   | 14 MB
 svm_svmdb        | 11 MB
 lsm_casdb        | 7933 kB
 mps_mpsdb        | 7773 kB
 isecure_portaldb | 7741 kB
 template0        | 7601 kB
 template1        | 7601 kB
(8 rows)

說明：

  • pg_size_pretty：以人性化角度展示資料庫大小

2、查詢所有資料庫總大小

SELECT pg_size_pretty(sum(pg_database_size(pg_database.datname))) as "Size"
FROM pg_database

3、查詢所有表總大小（也就相當於查詢所有資料庫總大小）

postgres=# select
postgres-#     pg_size_pretty(sum(t.size))
postgres-# from (
postgres(#     SELECT
postgres(#           table_schema || '.' || table_name AS table_full_name
postgres(#         , pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
postgres(#     FROM information_schema.tables
postgres(#     ORDER by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
postgres(#     ) t;
 pg_size_pretty
----------------
 65 GB
(1 row)

參考資料：

PostgreSQL實戰之物理複製和邏輯複製（四）_postgres write_lsn 不動-CSDN部落格

相關文章