Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.