PostgreSQL中快速對(duì)系統(tǒng)表實(shí)現(xiàn)vacuumfull

PostgreSQL中快速對(duì)系統(tǒng)表實(shí)現(xiàn)vacuum full

vacuum full會(huì)鎖表，而且效率很低，在實(shí)際中不可能使用vacuum來(lái)縮小pg_class，，這樣會(huì)有很長(zhǎng)的停機(jī)時(shí)間。
其實(shí)要實(shí)現(xiàn)vacuum full最簡(jiǎn)單的方法就是將一個(gè)表重新復(fù)制一遍，create table b as select * from a;然后再使用b表代替a表使用就可以了。
鑒于pg_class是所有表的基礎(chǔ)，我們就算將其拷貝也無(wú)法將其取代掉。這樣，我們可以以另外一種方式來(lái)實(shí)現(xiàn)，替換底層數(shù)據(jù)文件。由于pg_class有一個(gè)系統(tǒng)列，oid，這一個(gè)列我們無(wú)法簡(jiǎn)單的直接copy，所以我們采用一種迂回的方法。
1.新建一個(gè)表with oid， create table cxf with oids as select * from pg_class limit 0;
在這個(gè)表中建立跟pg_class一樣的索引，因?yàn)槿绻覀儗⒌讓訑?shù)據(jù)文件替換掉，而還是用老的索引文件的話，會(huì)錯(cuò)亂的
2.將整個(gè)pg_class使用copy命令導(dǎo)成文本（使用參數(shù)with oids）將oid也導(dǎo)出
3.然后將這個(gè)數(shù)據(jù)文件用copy with oids命令存入到第一步建的表中
4.停止數(shù)據(jù)庫(kù)（讓所有在緩存的數(shù)據(jù)全部寫入到文件中）
5.替換底層的數(shù)據(jù)文件跟索引文件
6.重啟數(shù)據(jù)庫(kù)即可

使用這種方法使pg_class的數(shù)據(jù)文件大小從1.4G變成了63M，pg_attribute從1.5G變成602M，實(shí)現(xiàn)了vacuum full的效果，具體步驟如下：

1.查看一下關(guān)于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.在數(shù)據(jù)庫(kù)base目錄下查看這幾個(gè)文件
@linuxidc:/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.創(chuàng)建一個(gè)表，結(jié)構(gòu)跟pg_class一致，建表的時(shí)候必須加上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
創(chuàng)建索引，由于啟動(dòng)數(shù)據(jù)庫(kù)的時(shí)候他會(huì)去找pg_class，然后通過(guò)索引去查找記錄，所以這里我們需要重建索引，最后也一起把底層文件給覆蓋掉
oid | relname | relfilenode
----------+--------------------------------+-------------
19317362 | cxf | 19317362
19317367 | cxf_pg_class_oid_index | 19317367
19317368 | cxf_pg_class_relname_nsp_index | 19317368
(3 rows)

可以看出兩個(gè)表的字段信息跟字段內(nèi)容是一致的
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現(xiàn)在的數(shù)據(jù)量
aligputf8=# select count(*) from pg_class;
count
--------
331799
(1 row)

5.將pg_class 導(dǎo)出成文件，然后再導(dǎo)入到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.關(guān)閉數(shù)據(jù)庫(kù)，備份現(xiàn)有的pg_class數(shù)據(jù)文件跟索引文件，以免發(fā)生意外，然后替換底層的數(shù)據(jù)文件（必須關(guān)閉數(shù)據(jù)庫(kù)，如果不關(guān)閉數(shù)據(jù)庫(kù)，剛剛copy回去的信息可能還沒有刷到硬盤中，這個(gè)時(shí)候覆蓋原有的文件會(huì)有問(wèn)題的，我之前試過(guò)，結(jié)果由于數(shù)據(jù)丟失，連pg_class表也找不到了，整個(gè)數(shù)據(jù)庫(kù)都不能用了）。
$GPHOME/bin/pg_ctl -w -D /home/gpadmin/cxf/aligp-1/ -o " -E -i -p 5132 --silent-mode=true " stop
@linuxidc:/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

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

7.重啟數(shù)據(jù)庫(kù)，驗(yàn)證
@linuxidc:/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，這個(gè)時(shí)候直接insert就可以了，不用copy成外部表，因?yàn)檫@個(gè)表沒有oid。
數(shù)據(jù)量由1.5G變成602M

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com