PostgreSQL的DB在表空间之间迁移

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