PostgreSQL的DB在表空间之间迁移

发布时间：2020-12-13 16:36:54 所属栏目：百科来源：网络整理

导读：Background 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_d

Background

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*frompg_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-0907:38:34.935179-04|2018-07-0907:43:04.894374-04|2018-07-0907:43:04.894374-04|2018-07-0907:43:04.894378-04|f|active|select*frompg_stat_activity;
12840|postgres|22809|10|postgres|psql|||-1|2018-07-0907:34:45.688671-04||2018-07-0907:37:37.758388-04|2018-07-0907:37:37.758749-04|f|idle|selectoid,*frompg_tablespace;
(2rows)

$ 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=#selectoid,*frompg_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|
(17rows)

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=#selectoid,*frompg_databasewheredatnamein('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|
(4rows)

/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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!