PostgreSQL在不同的表空間移動資料檔案

lottu發表於2020-09-01

一、背景

在工作中,可能會遇到將表從一個表空間移動另一個表空間。例如

* 對資料進行冷處理
* 表空間所在的磁碟空間不足
* 建表時分配錯了表空間

以上等等,可能需要你將一個表移動表空間。

二、表空間介紹

PostgreSQL的表空間

三、移動資料檔案方法

我們先打造下環境;建立兩個表空間

postgres=# CREATE TABLESPACE tsp01 OWNER lottu LOCATION '/data/pg6000/tsp01';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE tsp02 OWNER lottu LOCATION '/data/pg6000/tsp02';
CREATE TABLESPACE

檢視資料庫預設表空間

postgres=# \c lottu lottu
You are now connected to database "lottu" as user "lottu".
lottu=> select d.datname,p.spcname from pg_database d, pg_tablespace p where d.datname='lottu' and p.oid = d.dattablespace;
 datname |  spcname   
---------+------------
 lottu   | pg_default
(1 row)

接下來我們在表空間tsp01建表tbl_lottu

lottu=> create table tbl_lottu(id int primary key, info text) TABLESPACE tsp01;
CREATE TABLE
lottu=> insert into tbl_lottu select generate_series(1,1000) ,md5(random()::text);
INSERT 0 1000
lottu=> \d tbl_lottu
              Table "lottu.tbl_lottu"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 info   | text    |           |          | 
Indexes:
    "tbl_lottu_pkey" PRIMARY KEY, btree (id)
Tablespace: "tsp01"

而表tbl_lottu資料檔案的位置

lottu=> select pg_relation_filepath('tbl_lottu');
pg_relation_filepath
---------------------------------------------
pg_tblspc/90618/PG_12_201909212/24750/90620
(1 row)

3.1、alter table

將表從一個表空間移到另一個表空間

lottu=> ALTER TABLE tbl_lottu SET TABLESPACE tsp02;
ALTER TABLE

我們再檢視錶;已經成功移動表空間tsp02

lottu=> \d tbl_lottu 
              Table "lottu.tbl_lottu"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 info   | text    |           |          | 
Indexes:
    "tbl_lottu_pkey" PRIMARY KEY, btree (id)
Tablespace: "tsp02"

lottu=> select pg_relation_filepath('tbl_lottu');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/90619/PG_12_201909212/24750/90629
(1 row)

不足之處:在alter table這個過程中是鎖表的;若是大表;執行時間久,在這個時間內表在dml操作一直處在等待。那有沒有不鎖表,或者鎖表的時間極短的方法呢?

3.2、create + copy

我們想想,在做表的移動表空間操作步驟可以理解是,

新建一個表在tsp02中,再把資料copy過來,再用新表替換舊錶。該方式操作太複雜了,同時也不能保證資料的完整性。在確保在這段時間內不對錶進行任何操作;可以試下。

3.3、pg_repack

在生產環境處理表膨脹,我們會考慮用pg_repack來取代vacuum full,因為它在處理表膨脹的過程中可以避免表被長期鎖定。pg_repack工作原理:建立一個新表,將資料從舊錶移動到新表。為了避免表被獨佔鎖定,建立了一個額外的日誌表來記錄原始表的改動,還新增了一個把INSERT / UPDATE / DELETE操作記錄到日誌表的觸發器。當原始表中的資料全部匯入到新表中,索引重建完畢,日誌表的改動全部完成,pg_repack會連同新索引,用新表替換舊錶,並將原舊錶Drop掉。整個過程非常簡單,非常可靠。同時它還支援在不同表空間遷移表和索引

我們開始實際操作下:

第一步:先建立表tbl_repack;

lottu=# create table tbl_repack(id int primary key, info text) TABLESPACE tsp01;
CREATE TABLE
lottu=# insert into tbl_repack select generate_series(1,1000000) ,md5(random()::text);
INSERT 0 1000000
lottu=# select pg_relation_filepath('tbl_repack');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/90618/PG_12_201909212/24750/90681
(1 row)

第二步:執行pg_repack

[postgres@lottu ~]$ pg_repack -t lottu.tbl_repack -s tsp02 -j 2 -d lottu -U lottu
NOTICE: Setting up workers.conns
INFO: repacking table "lottu.tbl_repack"

執行的同時;我們對錶tbl_repack執行delete操作;實驗是否堵塞dml操作

lottu=# delete from tbl_repack where id < 100;
DELETE 99

第三步:檢視是否移動成功

lottu=# select pg_relation_filepath('tbl_repack');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/90619/PG_12_201909212/24750/90704
(1 row)
lottu=# \d tbl_repack;
             Table "lottu.tbl_repack"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 info   | text    |           |          | 
Indexes:
    "tbl_repack_pkey" PRIMARY KEY, btree (id)
Tablespace: "tsp02"
lottu=# select count(1) from tbl_repack ;
 count  
--------
 999901
(1 row)

限制之處:

  1. 執行pg_repack操作需要超級使用者
[postgres@lottu ~]$ pg_repack -t tbl_repack -s tsp02 -j 2 -d lottu -U lottu
NOTICE: Setting up workers.conns
ERROR: pg_repack failed with error: You must be a superuser to use pg_repack
  1. 移動的表需要有主鍵或者唯一索引;這個取決於pg_repack實現原理;大家可以去檢視官方文件
[postgres@lottu ~]$ pg_repack -t lottu.tbl_repack -s tsp02 -j 2 -d lottu -U lottu
NOTICE: Setting up workers.conns
WARNING: relation "lottu.tbl_repack" must have a primary key or not-null unique keys

相關文章