正文
利用tablespace特性将数据库移动到新磁盘
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
目前开发一台EC2的PostgreSQL服务器的磁盘空间已经严重不足，该磁盘非LVM，所以不考虑磁盘扩容方法，研发希望可以分区/data/02对应的/dev/xvdl1磁盘分担部分数据库的数据，这样也不用另加磁盘，这里研发列出了部分数据库。此处借用tablespace特性将部分数据迁移到新磁盘
[postgres@ec2s-autodenalicontentpoi- ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvde1 .9G .3G .2G % /
none 15G 12K 15G % /dev/shm
/dev/xvdl1 493G 46G 422G % /data/
/dev/xvdk1 .0T .8T 113G % /data/
hq-nfs-.eng.telenav.com:/nfs_home/home/
985G 22G 964G % /nfs/home
查看当前所有数据库大小
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------------------+-------------+----------+-------------+-------------+------------------------------+--------+------------+--------------------------------------------
contrib_regression | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 15 MB | pg_default |
denali | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 357 MB | pg_default |
| | | | | postgres=CTc/postgres +| | |
| | | | | r_denali_readonly=c/postgres | | |
denali_test | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 15 MB | pg_default |
fuse | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 15 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 27 MB | pg_default | default administrative connection database
region_anz | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 17 GB | pg_default |
region_eu | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 447 GB | pg_default |
region_il | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 12 GB | pg_default |
region_mea | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 69 GB | pg_default |
region_na | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 603 GB | pg_default |
region_sa | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 188 GB | pg_default |
region_sea | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 62 GB | pg_default |
regression | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 91 GB | pg_default |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 14 MB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 14 MB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
template_postgis | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 19 MB | pg_default |
test | denaliadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 14 MB | pg_default |
(17 rows)
创建新的表空间
[postgres@ec2s-autodenalicontentpoi-01]$ mkdir -p /data/02/pgsql/data/base postgres=# create tablespace region owner denaliadmin location
'/data/02/pgsql/data/base';
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Description
------------+-------------+--------------------------+-------------------+-------------
pg_default | postgres | | |
pg_global | postgres | | |
region | denaliadmin | /data/02/pgsql/data/base | |
(3 rows)
postgres=# select oid, * from pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
--------+--------------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------------------------------------
1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 12835 | 200001862 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
12835 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 12835 | 200001940 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
12840 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 295302735 | 1 | 1663 |
16384 | template_postgis | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 205319808 | 1 | 1663 |
21627 | denali_test | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 205320018 | 1 | 1663 |
17794 | denali | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 205316770 | 1 | 1663 | {=Tc/postgres,postgres=CTc/postgres,r_denali_readonly=c/postgres}
25419 | contrib_regression | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 295302735 | 1 | 1663 |
71746 | regression | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 187750513 | 1 | 1663 |
103050 | test | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 200001862 | 1 | 1663 |
48729 | region_na | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 190246393 | 1 | 1663 |
153385 | region_sea | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 200001862 | 1 | 1663 |
158397 | fuse | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 200001862 | 1 | 1663 |
81870 | region_eu | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 192495454 | 1 | 1663 |
93796 | region_sa | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 200778866 | 1 | 1663 |
99928 | region_mea | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 190246488 | 1 | 1663 |
101209 | region_il | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 199337179 | 1 | 1663 |
101862 | region_anz | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 199763417 | 1 | 1663 |
(17 rows)
postgres=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
--------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
271240 | region | 16513 | |
(3 rows)
将部分数据库迁移到新的表空间
postgres=# alter database region_il set tablespace region;
postgres=# alter database region_anz set tablespace region;
postgres=# alter database region_mea set tablespace region;
postgres=# alter database region_sa set tablespace region; postgres=# select d.datname as database, t.spcname as tablespace from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
database | tablespace
--------------------+------------
template1 | pg_default
template0 | pg_default
postgres | pg_default
template_postgis | pg_default
denali_test | pg_default
denali | pg_default
contrib_regression | pg_default
regression | pg_default
test | pg_default
region_na | pg_default
region_sea | pg_default
region_il | region
region_anz | region
region_mea | region
region_sa | region
fuse | pg_default
region_eu | pg_default
(17 rows)
