DB of PostgreSQL migrates between tablespaces
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-0907: 31:57 1831857418 region_anz_20180907.psql.gz
2018-07-0907: 33:57 1615345844 region_il_20180907.psql.gz
2018-07-0907: 37:05 8780321291 region_mea_20180907.psql.gz
2018-07-0907: 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-09 07-38 purl 34.935179-04 | 2018-07-09 07-09 07-43 purse 04.894374-04 | 2018-07-09 07 purl 43purl 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 07psql 34purl 45.688671-04 | | 2018-07-09 07JV 37psql 37.758388-04 | 2018-07-09 07MAV 37psql 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 | 271,240 | × × 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
