PostgreSQL的DB在表空间之间迁移_程序开发

PostgreSQL的DB在表空间之间迁移

admin

2023-05-21 03:22:08

0次

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 * 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

上一篇：微乐卡五星挂有吗？确实是有挂的！

下一篇：etcd数据库备份与还原

PostgreSQL的DB在表空间之间迁移

相关内容

热门资讯