一、背景
在作業中，可能會遇到將表從一個表空間移動另一個表空間，例如
* 對資料進行冷處理
* 表空間所在的磁盤空間不足
* 建表時分配錯了表空間
以上等等，可能需要你將一個表移動表空間，
二、表空間介紹
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)
限制之處：
- 執行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
- 移動的表需要有主鍵或者唯一索引；這個取決于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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1175.html
標籤：PostgreSQL