postgresql中快速對系統資料表實現vacuum full

     vacuum full會鎖表，而且效率很低，在實際中不可能使用vacuum來縮小pg_class，這樣會有很長的停機時間。
     其實要實現vacuum full最簡單的方法就是將一個表重新複製一遍，create table b as select * from a;然後再使用b表代替a表使用就可以了。
     鑒於pg_class是所有表的基礎，我們就算將其拷貝也無法將其取代掉。這樣，我們可以以另外一種方式來實現，替換底層資料檔案。由於pg_class有一個系統列，oid，這一個列我們無法簡單的直接copy，所以我們採用一種迂迴的方法。
1.建立一個表with oid， create table cxf with oids as select * from pg_class limit 0;
   在這個表中建立跟pg_class一樣的索引，因為如果我們將底層資料檔案替換掉，而還是用老的索引檔案的話，會錯亂的
2.將整個pg_class使用copy命令導成文本（使用參數with oids）將oid也匯出
3.然後將這個資料檔案用copy with oids命令存入到第一步建的表中
4.停止資料庫（讓所有在緩衝的資料全部寫入到檔案中）
5.替換底層的資料檔案跟索引檔案
6.重啟資料庫即可

 

使用這種方法使pg_class的資料檔案大小從1.4G變成了63M，pg_attribute從1.5G變成602M，實現了vacuum full的效果，具體步驟如下：

1.查看一下關於pg_class的表以及索引資訊
aligputf8=# select oid,relname,relfilenode from pg_class where relname like '%pg_class%';
   oid    |          relname           | relfilenode
----------+----------------------------+-------------
     1259 | pg_class                   |        1259
     2662 | pg_class_oid_index         |    15687137
     2663 | pg_class_relname_nsp_index |    15687138

2.在資料庫base目錄下查看這幾個檔案
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>ll -h 1259 1259.* 15687137 15687137.* 15687138 15687138.*
ls: 15687137.*: No such file or directory
ls: 15687138.*: No such file or directory
-rw------- 1 gpadmin gpadmin 1.0G Dec 11 20:14 1259
-rw------- 1 gpadmin gpadmin 395M Dec 11 20:16 1259.1
-rw------- 1 gpadmin gpadmin  20M Dec 11 20:16 15687137
-rw------- 1 gpadmin gpadmin  83M Dec 11 20:16 15687138

3.建立一個表，結構跟pg_class一致，建表的時候必須加上with oids
aligputf8=# create table cxf with oids as select * from pg_class limit 0;
SELECT 0
aligputf8=# create index cxf_pg_class_oid_index on cxf(oid);
CREATE INDEX
aligputf8=# create index cxf_pg_class_relname_nsp_index on cxf(relname, relnamespace);
CREATE INDEX
建立索引，由於啟動資料庫的時候他會去找pg_class，然後通過索引去尋找記錄，所以這裡我們需要重建索引，最後也一起把底層檔案給覆蓋掉
   oid    |            relname             | relfilenode
----------+--------------------------------+-------------
 19317362 | cxf                            |    19317362
 19317367 | cxf_pg_class_oid_index         |    19317367
 19317368 | cxf_pg_class_relname_nsp_index |    19317368
(3 rows)

可以看出兩個表的欄位資訊跟欄位內容是一致的
aligputf8=# select count(*) from pg_attribute where attrelid = 19317362;
 count
-------
    38
(1 row)

aligputf8=# select count(*) from pg_attribute where attrelid = 1259;
 count
-------
    38
(1 row)

4.查看pg_class現在的資料量
aligputf8=# select count(*) from pg_class;
 count 
--------
 331799
(1 row)

5.將pg_class 匯出成檔案，然後再匯入到cxf中
aligputf8=# copy pg_class to '/tmp/pg_class_cxf' with null as '' delimiter E'/5' oids;
COPY 331799
aligputf8=# copy cxf from '/tmp/pg_class_cxf' with null as '' delimiter E'/5' oids;
COPY 331799

6.關閉資料庫，備份現有的pg_class資料檔案跟索引檔案，以免發生意外，然後替換底層的資料檔案（必須關閉資料庫，如果不關閉資料庫，剛剛copy回去的資訊可能還沒有刷到硬碟中，這個時候覆蓋原有的檔案會有問題的，我之前試過，結果由於資料丟失，連pg_class表也找不到了，整個資料庫都不能用了）。
$GPHOME/bin/pg_ctl -w -D /home/gpadmin/cxf/aligp-1/ -o "   -E  -i -p 5132 --silent-mode=true " stop
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>ll -h 19317362 19317367 19317368
-rw------- 1 gpadmin gpadmin  63M Dec 11 20:39 19317362
-rw------- 1 gpadmin gpadmin 9.8M Dec 11 20:39 19317367
-rw------- 1 gpadmin gpadmin  47M Dec 11 20:39 19317368

gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>mv 1259 1259.bak
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>mv 1259.1 1259.1.bak
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>mv 15687137 15687137.bak
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>mv 15687138 15687138.bak
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>cp 19317362 1259
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>cp 19317367 15687137
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>cp 19317368 15687138
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>

7.重啟資料庫，驗證
gpadmin@hadoop5:/home/gpadmin/cxf/aligp-1/base/16384>PGOPTIONS="-c gp_session_role=utility" psql -E
psql (8.2.13)
Type "help" for help.

aligputf8=# select count(*) from pg_class;
 count 
--------
 331799
(1 row)

aligputf8=# explain select * from pg_class where oid = 1259;
                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.00..200.58 rows=1 width=268)
   Index Cond: oid = 1259::oid
(2 rows)

8.使用同樣的方法給pg_attribute，這個時候直接insert就可以了，不用copy成外部表格，因為這個表沒有oid。
資料量由1.5G變成602M