PostgreSQL的DB在表空间之间迁移
Background
怀柔ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18980820575（备注：SSL证书合作）期待与您的合作！
The /data/01 disk space is insufficient, but /data/02 is sufficient, so we migrate some data to /data/02.
1.Backup DB and upload to s3
pg_dump --verbose -Fc --dbname=region_il | gzip > /data/02/backup/region_il_20180907.psql.gz
pg_dump --verbose -Fc --dbname=region_anz | gzip > /data/02/backup/region_anz_20180907.psql.gz
pg_dump --verbose -Fc --dbname=region_mea | gzip > /data/02/backup/region_mea_20180907.psql.gz
pg_dump --verbose -Fc --dbname=region_sa | gzip > /data/02/backup/region_sa_20180907.psql.gz
$ aws s3 cp region_il_20180907.psql.gz s3://dba-backups/
$ aws s3 cp region_anz_20180907.psql.gz s3://dba-backups/
$ aws s3 cp region_mea_20180907.psql.gz s3://dba-backups/
$ aws s3 cp region_sa_20180907.psql.gz s3://dba-backups/
$ aws s3 ls s3://dba-backups/ |grep "20180907.psql.gz"
2018-07-09 07:31:57 1831857418 region_anz_20180907.psql.gz
2018-07-09 07:33:57 1615345844 region_il_20180907.psql.gz
2018-07-09 07:37:05 8780321291 region_mea_20180907.psql.gz
2018-07-09 07:44:52 20429541766 region_sa_20180907.psql.gz
2.Check Session and disk freeable space
postgres=# select * from pg_stat_activity; datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | query -------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------- 12840 | postgres | 23155 | 10 | postgres | psql | | | -1 | 2018-07-09 07:38:34.935179-04 | 2018-07-09 07:43:04.894374-04 | 2018-07-09 07:43:04.894374-04 | 2018-07-09 07:43:04.894378-04 | f | active | select * from pg_stat_activity; 12840 | postgres | 22809 | 10 | postgres | psql | | | -1 | 2018-07-09 07:34:45.688671-04 | | 2018-07-09 07:37:37.758388-04 | 2018-07-09 07:37:37.758749-04 | f | idle | select oid,* from pg_tablespace; (2 rows)
$ df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/xvde1 ext3 9.9G 6.4G 3.0G 69% /
none tmpfs 15G 12K 15G 1% /dev/shm
/dev/xvdl1 ext4 493G 47G 421G 10% /data/02
/dev/xvdk1 ext4 2.0T 1.8T 113G 94% /data/01
3.Create new tablespace location /data/02 disk:
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)
4.Move DB to new Tablespace
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 |
×××8 | 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)
alter database region_il set tablespace region;
alter database region_anz set tablespace region;
alter database region_mea set tablespace region;
alter database region_sa set tablespace region;
postgres=# select oid, * from pg_database where datname in ('region_il','region_anz','region_mea','region_sa');
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
--------+------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
101209 | region_il | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 199337179 | 1 | 271240 |
101862 | region_anz | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 199763417 | 1 | 271240 |
×××8 | region_mea | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 190246488 | 1 | 271240 |
93796 | region_sa | 16513 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12835 | 200778866 | 1 | 271240 |
(4 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)
$ df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/xvde1 ext3 9.9G 6.4G 3.0G 69% /
none tmpfs 15G 12K 15G 1% /dev/shm
/dev/xvdl1 ext4 493G 332G 136G 71% /data/02
/dev/xvdk1 ext4 2.0T 1.5T 399G 79% /data/01
5.Restart Database
pg_ctl stop;
pg_ctl start;
6.Reference
https://www.postgresql.org/docs/9.3/static/sql-createtablespace.html
https://www.postgresql.org/docs/9.3/static/sql-alterdatabase.html
当前题目：PostgreSQL的DB在表空间之间迁移
链接地址：http://myzitong.com/article/ppejjp.html