postgresql 的三類日誌

fiona8953發表於2016-08-02
SQL
一、PostgreSQL有3種日誌： 
1）pg_log（資料庫執行日誌）   內容可讀    預設關閉的，需要設定引數啟動 
2）pg_xlog（WAL 日誌，即重做日誌）    內容一般不具有可讀性        強制開啟 
3）pg_clog（事務提交日誌，記錄的是事務的後設資料）  內容一般不具有可讀性    強制開啟 

pg_xlog和pg_clog一般是在$PGDATA/下面的資料夾下，pg_log預設路徑是$PGDATA/pg_log，通常可自定義路徑和檔名

[root@pg ~]# cd /home/pgsql/9.1/data/ 
You have mail in /var/spool/mail/root 
[root@pg data]# ll 
total 460 
drwx------ 19 postgres postgres   4096 Nov 27 17:58 base 
drwx------  2 postgres postgres   4096 Jan 13 18:58 global 
drwx------  2 postgres postgres   4096 Jan 14 02:36 pg_clog     --clog 
-rw-------  1 postgres postgres   3513 Dec  4 09:36 pg_hba.conf 
-rw-------  1 postgres postgres   1636 Mar 31  2015 pg_ident.conf 
drwx------  2 postgres postgres   4096 Jan 14 04:35 pg_log         --log 
drwx------  4 postgres postgres   4096 Mar 31  2015 pg_multixact 
drwx------  2 postgres postgres   4096 Dec 10 10:37 pg_notify 
drwx------  2 postgres postgres   4096 Mar 31  2015 pg_serial 
drwx------  2 postgres postgres   4096 Jan 14 10:23 pg_stat_tmp 
drwx------  2 postgres postgres  20480 Jan 14 09:56 pg_subtrans 
drwx------  3 postgres postgres   4096 Mar 31  2015 pg_tblspc 
drwx------  2 postgres postgres   4096 Mar 31  2015 pg_twophase 
-rw-------  1 postgres postgres      4 Mar 31  2015 PG_VERSION 
drwx------  3 postgres postgres 360448 Jan 14 10:17 pg_xlog     --xlog 
-rw-------  1 postgres postgres  19278 Jan  7 11:13 postgresql.conf 
-rw-------  1 postgres postgres     56 Dec  4 09:39 postmaster.opts 
-rw-------  1 postgres postgres     70 Dec 10 10:37 postmaster.pid 
-rw-r--r--  1 postgres postgres    434 Mar 31  2015 serverlog 


[postgres@pg data]$ more postgresql.conf 
log_destination = 'stderr'                
logging_collector = on
log_directory = 'pg_log'                   ---可自定義路徑
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'   ---日誌檔名 
log_line_prefix = '%t-%d-%h-%a :'          ---日誌每行的標準格式 

二、各個日誌的作用 
（1）pg_log 
     這個日誌一般是記錄伺服器與DB的狀態，比如各種Error資訊，定位慢查詢SQL，資料庫的啟動關閉資訊，發生checkpoint過於頻繁等的告警資訊，諸如此類。該日誌有.csv格式和.log。建議使用.csv格式，因為它一般會按大小和時間自動切割，畢竟檢視一個巨大的日誌檔案比檢視不同時間段的多個日誌要難得多。pg_log是可以被清理刪除，壓縮打包或者轉移，同時並不影響DB的正常執行。當我們有遇到DB無法啟動或者更改引數沒有生效時，第一個想到的就是檢視這個日誌。 

[postgres@pg pg_log]$ ll 
total 155848 
-rw------- 1 postgres postgres 10485794 Jan 11 05:43 postgresql-2016-01-11_032149.log 
-rw------- 1 postgres postgres 10485800 Jan 11 08:47 postgresql-2016-01-11_054328.log 
-rw------- 1 postgres postgres 10485815 Jan 11 09:34 postgresql-2016-01-11_084732.log 
-rw------- 1 postgres postgres 10485818 Jan 11 12:34 postgresql-2016-01-11_093423.log 
-rw------- 1 postgres postgres 10485774 Jan 11 16:51 postgresql-2016-01-11_123409.log 
-rw------- 1 postgres postgres  7924560 Jan 11 23:59 postgresql-2016-01-11_165153.log 
-rw------- 1 postgres postgres 10485850 Jan 12 03:48 postgresql-2016-01-12_000000.log 
-rw------- 1 postgres postgres 10485801 Jan 12 09:50 postgresql-2016-01-12_034808.log 
-rw------- 1 postgres postgres 10485990 Jan 12 13:41 postgresql-2016-01-12_095036.log 
-rw------- 1 postgres postgres  9972298 Jan 12 23:58 postgresql-2016-01-12_134142.log 
-rw------- 1 postgres postgres 10485773 Jan 13 10:35 postgresql-2016-01-13_000000.log 
-rw------- 1 postgres postgres 10485851 Jan 13 16:00 postgresql-2016-01-13_103558.log 
-rw------- 1 postgres postgres 10485783 Jan 13 21:33 postgresql-2016-01-13_160057.log 
-rw------- 1 postgres postgres  3997576 Jan 14 00:00 postgresql-2016-01-13_213345.log 
-rw------- 1 postgres postgres 10485855 Jan 14 04:35 postgresql-2016-01-14_000000.log 
-rw------- 1 postgres postgres 10485808 Jan 14 10:25 postgresql-2016-01-14_043543.log 
-rw------- 1 postgres postgres  1303365 Jan 14 11:00 postgresql-2016-01-14_102534.log 

[postgres@pg pg_log]$ head -3 postgresql-2016-01-14_102534.log 
2016-01-14 10:25:34 CST-tinadb-192.168.12.22-[unknown] :LOG:  duration: 2920.522 ms  statement: select package_name_statistics_single('com.hdc') 
2016-01-14 10:25:35 CST-tinadb-192.168.12.166-[unknown] :LOG:  duration: 637.073 ms  statement: SELECT  id FROM t_sample_state ; 
2016-01-14 10:25:35 CST-tinadb-192.168.12.22-[unknown] :LOG:  duration: 4395.549 ms  statement: select t_sfa_sample_tmp_cron_data_singer('DBM',1) 

（2）pg_xlog 
    這個日誌是記錄的Postgresql的WAL資訊，也就是一些事務日誌資訊(transaction log)。預設單個大小是16M，原始碼安裝的時候可以更改其大小（./configure --with-wal-segsize=target_value 引數，即可設定）這些日誌會在定時回滾恢復(PITR)， 流複製(Replication Stream)以及歸檔時能被用到，這些日誌是非常重要的，記錄著資料庫發生的各種事務資訊，不得隨意刪除或者移動這類日誌檔案，不然你的資料庫會有無法恢復的風險 
    WAL：PostgreSQL在將快取的資料刷入到磁碟之前，先寫日誌, 這就是PostgreSQL WAL ( Write-Ahead Log )方式，也就是預寫日誌方式 

[postgres@pg pg_xlog]$ ll 
... 
-rw------- 1 postgres postgres 16777216 Jan 13 12:05 0000000100000F310000009D 
-rw------- 1 postgres postgres 16777216 Jan 13 12:15 0000000100000F310000009E 
-rw------- 1 postgres postgres 16777216 Jan 13 12:15 0000000100000F310000009F 
-rw------- 1 postgres postgres 16777216 Jan 13 12:13 0000000100000F31000000A0 
-rw------- 1 postgres postgres 16777216 Jan 13 12:15 0000000100000F31000000A1 
---每一個大小都是16M 
drwx------ 2 postgres postgres   499712 Jan 14 11:18 archive_status 
[postgres@pg pg_xlog]$ cd archive_status 
-rw------- 1 postgres postgres 0 Jan 14 14:39 0000000100000F310000002D.done 
-rw------- 1 postgres postgres 0 Jan 14 14:37 0000000100000F310000002C.done 
-rw------- 1 postgres postgres 0 Jan 14 14:35 0000000100000F310000002B.done 
-rw------- 1 postgres postgres 0 Jan 14 14:32 0000000100000F310000002A.done 
-rw------- 1 postgres postgres 0 Jan 14 14:31 0000000100000F3100000029.done 
--每個pg_xlog完成了歸檔後，都會在這裡面生成一個.done的檔案 


流複製主庫pg_xlog 
[root@pg pg_xlog]# ll -t |head -5 
total 21004780 
-rw------- 1 postgres postgres 16777216 Jan 14 14:37 0000000100000F310000002D  
-rw------- 1 postgres postgres 16777216 Jan 14 14:37 0000000100000F310000002C 
-rw------- 1 postgres postgres 16777216 Jan 14 14:35 0000000100000F310000002B 
-rw------- 1 postgres postgres 16777216 Jan 14 14:32 0000000100000F310000002A 
-rw------- 1 postgres postgres 16777216 Jan 14 14:31 0000000100000F3100000029 

主庫歸檔日誌： 
[root@pg pg_xlog]# cd /home/pgsql/backup_new/archived_log/    ---自定義的歸檔路徑 
[root@pg archived_log]# ll -t |head -6 
total 53182464 
-rw------- 1 postgres postgres 16777216 Jan 14 14:39 0000000100000F310000002D    
-rw------- 1 postgres postgres 16777216 Jan 14 14:37 0000000100000F310000002C 
-rw------- 1 postgres postgres 16777216 Jan 14 14:35 0000000100000F310000002B 
-rw------- 1 postgres postgres 16777216 Jan 14 14:32 0000000100000F310000002A 
-rw------- 1 postgres postgres 16777216 Jan 14 14:31 0000000100000F3100000029 
---其實也是上面的pg_xlog，當已經複製到歸檔路徑，就算完成了歸檔，archive_status裡面就會有一個同名狀態檔案.done生成（對比上面的.done時間一致） 

流複製從庫pg_xlog 
[root@pg-ro pg_xlog]# ll -t |head -5 
total 1146884 
-rw------- 1 postgres postgres 16777216 Jan 14 14:36 0000000100000F310000002D 
-rw------- 1 postgres postgres 16777216 Jan 14 14:36 0000000100000F310000002C 
-rw------- 1 postgres postgres 16777216 Jan 14 14:34 0000000100000F310000002B 
-rw------- 1 postgres postgres 16777216 Jan 14 14:31 0000000100000F310000002A 
-rw------- 1 postgres postgres 16777216 Jan 14 14:29 0000000100000F3100000029 
---可以看到，每個時間都比主庫晚1-2分鐘，主庫生成後傳到從庫的 

[root@pg data]# du -sh * 
285G base 
1.2M global 
48M pg_clog 
4.0K pg_hba.conf 
4.0K pg_ident.conf 
158M pg_log 
240K pg_multixact 
12K   pg_notify 
4.0K pg_serial 
700K pg_stat_tmp 
28M  pg_subtrans 
3.5G pg_tblspc 
4.0K pg_twophase 
4.0K PG_VERSION 
21G pg_xlog       ---除了base目錄，這個pg_xlog日誌佔的空間最大 
20K postgresql.conf 
4.0K postmaster.opts 
4.0K postmaster.pid 
4.0K serverlog 

說明：當你的歸檔或者流複製發生異常的時候，事務日誌會不斷地生成，有可能會造成你的磁碟空間被塞滿，最終導致DB掛掉或者起不來。遇到這種情況不用慌，可以先關閉歸檔或者流複製功能，備份pg_xlog日誌到其他地方，但不要刪除。然後刪除較早時間的的pg_xlog，有一定空間後再試著啟動Postgres。 


WAL補充： 
1.說明 
   postgresql資料庫可以透過調整WAL引數控制日誌寫入磁碟的先後順序。先將日誌寫入磁碟能夠完全保證資料的完整性，在崩潰時可以恢復最近的事務；後寫入磁碟，很難保證在崩潰時事務能夠得到恢復，資料的結果也很難保證是真實正確的。 

2.WAL相關引數（參考網路） 
fsync = on                      # turns forced synchronization on or off 
   該引數直接控制日誌是否先寫入磁碟。預設值是ON(先寫入)。配置該引數為OFF，更新資料寫入磁碟完全不用等待WAL的寫入完成， 
   節省了時間，提高了效能。其直接隱患是無法保證在系統崩潰時最近的事務能夠得到恢復，也就無法保證相關資料的真實與正確性。 

synchronous_commit = on         # synchronization level; on, off, or local 
   該參數列明是否等待WAL完成後才返回給使用者事務的狀態資訊，預設值是ON.因引數只是控制事務的狀態反饋，因此對於資料的一致性不存在風險。 
   但事務的狀態資訊影響著資料庫的整個狀態。該引數可以靈活的配置，對於業務沒有嚴謹要求的事務可以配置為OFF，能夠為系統的效能帶來不小的提升。 

wal_writer_delay = 200ms 
   WAL writer程式的間歇時間。預設值是200ms。準確的配置應該根據自身系統的執行狀況。如果時間過長可能造成WAL buffer的記憶體不足；反之過小將會引起WAL的不斷的寫入，對磁碟的IO也是很大考驗。 

commit_delay: 
   一個已經提交的資料在WAL buffer中存放的時間，單位ms，預設值是0，不用延遲。非0值表示可能存在多個事務的WAL同時寫入磁碟。 
   如果設定為非0，表明了某個事務執行commit後不會立即寫入WAL中，而仍存放在WAL buffer中，這樣對於後面的事務申請WAL buffer時非常不利，尤其是提交事務較多的高峰期，可能引起WAL buffer記憶體不足。如果記憶體足夠大，可以儘量延長該引數值，能夠使資料集中寫入這樣降低了系統的IO，提高了效能。同樣如果此時崩潰資料面臨著丟失的危險。個人建議採用預設值，同時將WAL檔案存放在IO效能好的磁碟上。 

3.WAL日誌的個數 
3.1先看幾個相關的引數 
checkpoint_segments = 128               # in logfile segments, min 1, 16MB each 
checkpoint_timeout = 20min              # range 30s-1h 
checkpoint_completion_target = 0.5      # checkpoint target duration, 0.0 - 1.0 
wal_keep_segments = 1024 

理論上合理的pg_xlog一般在(2+checkpoint_completion_target)*checkpoint_segment+1左右浮動，但是高併發環境下如果checkpoint_segment設定比較大，且checkpoint較少被觸發的時候，pg_xlog下會存有很多的事務日誌，嚴重會耗掉磁碟儲存，所以設定checkpoint_segment的大小要視磁碟容量和每個pg_xlog檔案的大小來看，有些時候需要手工調整引數來收縮。 

checkpoint執行控制: 
1）資料量達到checkpoint_segments*16M時，系統自動觸發； 
2）時間間隔達到checkpoint_timeout引數值時; 
3）使用者發出checkpoint命令時。 

說明： 
1）checkpoint_segments 值預設為 3，這個值較小，建議設定成32以上，如果業務很繁忙，這個引數還應該調大，當然在恢復時也意味著恢復時間較長，這個需要綜合考慮。 
2）checkpoint_timeout 預設5分鐘，系統自動執行checkpoint之間的最大時間間隔，同樣間隔越大介質恢復的時間越長。 
3）checkpoint_completion_target 默讀值為 0.5,這個通常保持預設值即可。表示每個checkpoint需要在checkpoints間隔時間的50%內完成。 

3.2 最大的日誌資料估計方法（網上介紹的，只能是個大概值，也有可能會超過） 
    通常地說，WAL segment 最大個數不超過  (2+checkpoint_completion_target)*checkpoint_segments + 1 
    在流複製環境下， WAL最大數不超過 wal_keep_segments+checkpoint_segments+1 

3.3 主機 pg_xlog 日誌數 
[root@pg pg_xlog]# ll |wc -l 
1284 

3.4 清理pg_xlog 
修改引數: 
wal_keep_segments = 512 

reload 配置檔案: 
pg_ctl reload -D $PGDATA 

執行一次checkpoint 

部分pg_xlog 日誌已被刪除，空間使用率降下去了，我們可以不手動操作，因為checkpoint運算元據庫會自動執行，執行頻率由引數checkpoint_timeout控制。 

---記住千萬不要直接物理刪除rm之類的。 

（3）pg_clog 
   pg_clog這個檔案也是事務日誌檔案，但與pg_xlog不同的是它記錄的是事務的後設資料(metadata)，這個日誌告訴我們哪些事務完成了，哪些沒有完成。這個日誌檔案一般非常小，但是重要性也是相當高，不得隨意刪除或者對其更改資訊。 

[root@pg-ro pg_clog]# ll -t |head -10 
total 48904 
-rw------- 1 postgres postgres  24576 Jan 14 14:41 0962 
-rw------- 1 postgres postgres 262144 Jan 14 14:01 0961 
-rw------- 1 postgres postgres 262144 Jan 14 04:19 0960 
-rw------- 1 postgres postgres 262144 Jan 13 17:02 095F 
-rw------- 1 postgres postgres 262144 Jan 13 06:02 095E 
-rw------- 1 postgres postgres 262144 Jan 12 11:03 095D 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26477398/viewspace-2122832/，如需轉載，請註明出處，否則將追究法律責任。

相關文章