Johnson峰的部落格

壹：前言

因NABRO推播系統中postgresql資料庫的playback_log table歷史資料過多未清(兩千多萬筆)，對資料庫運作效能及報表產生程式皆有不利影響，因此必須清除此table歷史資料。

貳：目標

一、將playback_log的2008及2009年資料撈出存檔後刪除記錄。

二、2008及2009年資料如果有需要，仍能正確倒回資料庫使用。

参：作法過程

一、要達成第一個目標很簡單，下個delete from playback_log where start_time like '2008%'即可，但無法做到第二個目標將刪除資料再倒回資料庫使用。所以需要將刪除資料先select出來到檔案，這也不難辦到。

二、接下來的困難點在於如何將select出來的檔案，再倒回資料庫。其實要在資料庫新增資料語法也很簡單--「INSERT INTO `playback_log` (`column name`, …) VALUES (…);」，以前有利用EXCEL將資料檔案先匯入，再將INSERT INTO的SQL語法快速複製進去，再將此加工過的SQL語法複製貼上GUI介面的SQL視窗執行，但這只適合資料量不大的情況(EXCEL只能吃6萬多筆而已)。但是當挑出來的檔案有幾百萬筆，甚至幾千萬筆的狀況該怎麼做呢？

三、會寫程式的應該就會說寫個程式就可以解決阿！的確是很好的解決方法，但除了寫程式外沒有其他方法？上網查詢又找不到相關資料，好吧，山不轉路轉，只好從書裡去找答案，後來在網路上找到這本電子書「Practical PostgreSQL」網址如後：(http://www.faqs.org/docs/ppbook/book1.htm)。

四、這本書寫的相當不錯，但為原文書，所以讀起來會比較辛苦，中間看的過程就略過，最後看的結果是書裡還是沒有直接答案！感覺洩氣了。先看學到什麼新的東西吧！

1.Copying Values (to/from) External Files with COPY

就是說在pgsql命令列模式可以用COPY指令將table內的全部資料拷貝到外部檔案，或從外部檔案將資料拷貝回table，但這是用在table整個資料，無法下條件子句拷貝部分資料出去。(就差那麼一步，如果可以下條件子句拷貝部分資料，那問題早就解決了)

2.Restructuring with CREATE TABLE AS

意思是可以在pgsql命令列模式下，用CREATE TABLE AS指令，從一個已存在的table複製成另一個新的table，語法如下：

CREATE TABLE new_table [ ( column_name [, ...] ) ] AS query

五、現在整理我們會的指令有哪些：

1.select * from playback_log where start_time like '2008%'

2.delete from playback_log where start_time like '2008%'

3.INSERT INTO `playback_log` (`column name`, …) VALUES (…)

4.Copying Values (to/from) External Files with COPY

5.Restructuring with CREATE TABLE AS

這些指令跟我們想要解決方案的指令似乎無關又有關，難道真的只能靠寫程式嗎？公司人事又已經精簡到不行，只好自力救濟啦，這次，路不轉人轉，其實原理說明了，似乎根本也是很簡單的，程序如後：

a.先用第1、5指令從playback_log CREATE 2008年資料到playback_log_2008

b.用第4指令將playback_log_2008整個COPY到外部檔案。

c.用第2指令將playback_log的2008年資料刪除。

d.用第4指令從2008年外部檔案COPY到playback_log測試是否可正確倒回。

以上程序指令在243備份資料庫測試過可行，完整執行指令如附錄。

肆：結論

書本上教的是基本的，但運用之妙存乎一心，就看是否能融會貫通。這次也是經過多次try & error後得出來的經驗。其實用postgresql內建指令執行的速度還算不錯，以2009年資料量6165910筆來看，CREATE TABLE playback_log_2009花78秒，再將playback_log_2009拷貝到外部檔案約45秒，從playback_log刪除2009年資料約94秒，最後測試從2009年外部檔案拷貝回playback_log要2003秒(33分鐘左右)。最後記得請先在備份資料庫做過ㄧ遍沒問題後，再到正式資料庫執行。

伍：附錄

以下紅色粗體字為指令部份，黑色細體字為執行結果，duration為執行時間。

先用自己帳號ssh登入到243主機。

$ sudo su –           --切換到superuser

# su – postgres      --切換到postgres user

$ psql                  --使用postgresql的命令列模式

Postgres=# \c NabroRepository --切換到NabroRepository資料庫

NabroRepository=# CREATE TABLE playback_log_2008 as select * from playback_log where start_time like '2008%';

LOG:  statement: CREATE TABLE playback_log_2008 as select * from playback_log where start_time like '2008%';

LOG:  duration: 53686.562 ms

SELECT

NabroRepository=# SELECT count (*) from playback_log_2008;

LOG:  statement: SELECT count (*) from playback_log_2008;

LOG:  duration: 4.777 ms

 count

-------

 20528

(1 row)

NabroRepository=# COPY playback_log_2008 TO '/var/lib/pgsql/playback_log_2008.txt';

LOG:  statement: COPY playback_log_2008 TO '/var/lib/pgsql/playback_log_2008.txt';

LOG:  duration: 144.384 ms

COPY 20528

NabroRepository=# DELETE FROM playback_log where start_time like '2008%';

LOG:  statement: DELETE FROM playback_log where start_time like '2008%';

LOG:  duration: 53470.131 ms

DELETE 20528

NabroRepository=# SELECT count (*) from playback_log;

LOG:  statement: SELECT count (*) from playback_log;

LOG:  duration: 7210.239 ms

  count  

----------

 21347214

(1 row)

NabroRepository=# COPY playback_log FROM '/var/lib/pgsql/playback_log_2008.txt';

LOG:  statement: COPY playback_log FROM '/var/lib/pgsql/playback_log_2008.txt';

LOG:  duration: 1958.721 ms

COPY 20528

NabroRepository=# SELECT count (*) from playback_log;

LOG:  statement: SELECT count (*) from playback_log;

LOG:  duration: 7232.225 ms

  count  

----------

 21367742

(1 row)

NabroRepository=# CREATE TABLE playback_log_2009 as select * from playback_log where start_time like '2009%';

LOG:  statement: CREATE TABLE playback_log_2009 as select * from playback_log where start_time like '2009%';

LOG:  duration: 78194.930 ms

SELECT

NabroRepository=# SELECT count (*) from playback_log_2009;

LOG:  statement: SELECT count (*) from playback_log_2009;

LOG:  duration: 2285.198 ms

  count 

---------

 6165910

(1 row)

NabroRepository=# COPY playback_log_2009 TO '/var/lib/pgsql/playback_log_2009.txt';

LOG:  statement: COPY playback_log_2009 TO '/var/lib/pgsql/playback_log_2009.txt';

LOG:  duration: 45813.709 ms

COPY 6165910

NabroRepository=# DELETE FROM playback_log where start_time like '2009%';

LOG:  statement: DELETE FROM playback_log where start_time like '2009%';

LOG:  duration: 94604.313 ms

DELETE 6165910

NabroRepository=# SELECT count (*) from playback_log;

LOG:  statement: SELECT count (*) from playback_log;

LOG:  duration: 8023.077 ms

  count  

----------

 15201832

(1 row)

NabroRepository=# COPY playback_log FROM '/var/lib/pgsql/playback_log_2009.txt';

LOG:  statement: COPY playback_log FROM '/var/lib/pgsql/playback_log_2009.txt';

LOG:  duration: 2003791.433 ms

COPY 6165910

NabroRepository=# SELECT count (*) from playback_log;LOG:  statement: SELECT count (*) from playback_log;

LOG:  duration: 16176.402 ms

  count  

----------

 21367742

(1 row)

NabroRepository=# SELECT count (*) from playback_log where start_time like '2010%';;

LOG:  statement: SELECT count (*) from playback_log where start_time like '2010%';

LOG:  duration: 60504.378 ms

  count  

----------

 15181304

(1 row)

NabroRepository=# DELETE FROM playback_log where start_time like '2008%';

LOG:  statement: DELETE FROM playback_log where start_time like '2008%';

LOG:  duration: 54676.813 ms

DELETE 20528

NabroRepository=# DELETE FROM playback_log where start_time like '2009%';

LOG:  statement: DELETE FROM playback_log where start_time like '2009%';

LOG:  duration: 93285.418 ms

DELETE 6165910

NabroRepository=# SELECT count (*) from playback_log;

LOG:  statement: SELECT count (*) from playback_log;

LOG:  duration: 8600.796 ms

  count  

----------

 15181304

(1 row)