PostgreSQL的DB在表空間之間遷移

Background

懷柔ssl適用于網(wǎng)站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：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://jinyejixie.com/article28/ppejjp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供、網(wǎng)站導航、云服務器、手機網(wǎng)站建設、服務器托管、響應式網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

PostgreSQL的DB在表空間之間遷移