Ubuntu中PostgreSQL的日志管理技巧

一、日志配置基礎

1. 修改配置文件
   通過postgresql.conf設置日志參數（路徑：/etc/postgresql/<版本>/main/）：

   • logging_collector = on：啟用日志收集。
   • log_directory = '/var/log/postgresql'：指定日志目錄。
   • log_filename = 'postgresql-%Y-%m-%d.log'：按日期命名日志文件。
   • log_min_duration_statement = 1000：記錄執行超1秒的慢查詢。
   • log_lock_waits = on：記錄鎖等待信息。

2. 動態調整參數
   使用ALTER SYSTEM命令無需重啟即可修改部分參數，如：

   ALTER SYSTEM SET log_min_duration_statement = '500ms';
   

二、日志輪轉與清理

1. 使用logrotate工具
   創建配置文件/etc/logrotate.d/postgresql，內容示例：

   /var/log/postgresql/postgresql-*.log {
       daily
       rotate 7
       compress
       missingok
       postrotate
           /bin/kill -HUP `cat /var/run/postgresql/*.pid` 2>/dev/null || true
       endscript
   }
   

   • daily：每日輪轉；rotate 7：保留7天日志；compress：壓縮舊日志。

2. 手動觸發輪轉
   執行以下命令手動輪轉日志：

   sudo logrotate -f /etc/logrotate.d/postgresql
   

三、日志分析與監控

1. 實時查看日志

   sudo tail -f /var/log/postgresql/postgresql-*.log  # 實時查看最新日志
   sudo grep 'ERROR' /var/log/postgresql/*.log       # 篩選錯誤日志
   

2. 生成分析報告
   使用pgBadger工具解析日志并生成可視化報告：

   sudo apt-get install pgbadger
   pgbadger /var/log/postgresql/postgresql-*.log -o report.html  # 生成HTML報告
   

   報告可展示慢查詢、鎖等待等關鍵信息。

3. 設置定時任務
   通過crontab每天凌晨生成前一天日志報告：

   0 2 * * * pgbadger /var/log/postgresql/postgresql-$(date -d "yesterday" +%Y-%m-%d).log -o /var/www/html/report.html
   

四、安全與優化建議

• 權限控制：確保日志目錄僅允許postgres用戶讀寫，避免敏感信息泄露。
• 性能平衡：生產環境中log_statement建議設為ddl或mod，避免記錄所有SELECT語句影響性能。
• 日志歸檔：定期將歷史日志歸檔至其他存儲（如對象存儲），釋放磁盤空間。