PG 管理表空间

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接： https://blog.csdn.net/horses/article/details/87371469

文章目录

创建数据库或者数据表（包括索引）的时候，可以为其指定一个表空间（tablespace）。表空间决定了这些对象在文件系统中的存储路径。现在我们来学习一些关于表空间的知识。

基本概念

在 PostgreSQL 中，表空间（tablespace）表示数据文件的存放目录，这些数据文件代表了数据库的对象，例如表或索引。当我们访问表时，系统通过它所在的表空间定位到对应数据文件所在的位置。
PostgreSQL tablespace

PostgreSQL 中的表空间与其他数据库系统不太一样，它更偏向于一个物理上的概念。

表空间的引入为 PostgreSQL 的管理带来了以下好处：

  • 如果数据库集群所在的初始磁盘分区或磁盘卷的空间不足，又无法进行扩展，可以在其他分区上创建一个新的表空间以供使用。
  • 管理员可以根据数据库对象的使用统计优化系统的性能。例如，可以将访问频繁的索引存放到一个非常快速且可靠的磁盘上，比如昂贵的固态硬盘。与此同时，将很少使用或者对性能要求不高的归档数据表存储到廉价的低速磁盘上。

PostgreSQL 在集群初始化时将所有的数据文件和配置文件存储到它的数据目录中，通常是环境变量 PGDATA 的值。默认创建了两个表空间：

  • pg_default， template1 和 template0 默认的表空间，也是创建其他数据库时的默认表空间；对应的目录为 PGDATA/base。
  • pg_global，用于存储一些集群级别的共享系统表（system catalogs），例如 pg_database、pg_control；对应的目录为 PGDATA/global。

初始安装后，使用 psql 查询默认创建的表空间：

postgres=# \db
              List of tablespaces
    Name    |  Owner   |        Location        
------------+----------+------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

   

   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

同时也可以通过操作系统命令查看相应的目录：

-bash-4.2$ ls -l /var/lib/pgsql/11/data/
total 60
drwx------. 8 postgres postgres    80 Jan  8 05:53 base
-rw-------. 1 postgres postgres    30 Jan 13 00:00 current_logfiles
drwx------. 2 postgres postgres  4096 Jan 12 16:28 global
drwx------. 2 postgres postgres   188 Jan 10 00:00 log
drwx------. 2 postgres postgres     6 Jan  4 16:56 pg_commit_ts
drwx------. 2 postgres postgres     6 Jan  4 16:56 pg_dynshmem
-rw-------. 1 postgres postgres  4305 Jan  4 17:58 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Jan  4 16:56 pg_ident.conf
drwx------. 4 postgres postgres    68 Jan 13 13:55 pg_logical
drwx------. 4 postgres postgres    36 Jan  4 16:56 pg_multixact
drwx------. 2 postgres postgres    18 Jan  4 17:58 pg_notify
drwx------. 2 postgres postgres     6 Jan  4 16:56 pg_replslot
drwx------. 2 postgres postgres     6 Jan  4 16:56 pg_serial
drwx------. 2 postgres postgres     6 Jan  4 16:56 pg_snapshots
drwx------. 2 postgres postgres     6 Jan  4 17:58 pg_stat
drwx------. 2 postgres postgres   105 Jan 13 16:57 pg_stat_tmp
drwx------. 2 postgres postgres    18 Jan  4 16:56 pg_subtrans
drwx------. 2 postgres postgres    19 Jan 12 15:42 pg_tblspc
drwx------. 2 postgres postgres     6 Jan  4 16:56 pg_twophase
-rw-------. 1 postgres postgres     3 Jan  4 16:56 PG_VERSION
drwx------. 3 postgres postgres    60 Jan  4 16:56 pg_wal
drwx------. 2 postgres postgres    18 Jan  4 16:56 pg_xact
-rw-------. 1 postgres postgres    88 Jan  4 16:56 postgresql.auto.conf
-rw-------. 1 postgres postgres 23800 Jan  4 17:50 postgresql.conf
-rw-------. 1 postgres postgres    58 Jan  4 17:58 postmaster.opts
-rw-------. 1 postgres postgres   109 Jan  4 17:58 postmaster.pid

   

   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

其中的 base 和 global 目录分别对应表空间 pg_default 和 pg_global。关于这些文件和目录的具体介绍，可以参考官方文档。

创建表空间

创建新的表空间使用CREATE TABLESPACE语句：

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION 'directory';

   

   
  • 1
  • 2
  • 3

表空间的名称不能以 ‘pg_’ 开头，它们是系统表空间的保留名称；LOCATION 参数必须指定绝对路径名，指定的目录必须是一个已经存在的空目录，PostgreSQL 操作系统用户（postgres）必须是该目录的拥有者，以便能够进行文件的读写。

接下来，我们使用目录 /var/lib/pgsql/ 创建一个新的表空间 app_tbs。先创建所需的目录：

[root@centos7 ~]# su - postgres
Last login: Wed Jan  9 09:29:19 EST 2019 on pts/0
-bash-4.2$ mkdir /var/lib/pgsql/app_tbs
-bash-4.2$ ll
total 0
drwx------. 4 postgres postgres 51 Jan  4 16:56 11
drwxr-xr-x. 2 postgres postgres  6 Jan 12 15:39 app_tbs

   

   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

注意目录的所有者和权限。然后使用具有 CREATEDB 权限的用户创建表空间，此处我们使用 postgres 执行以下操作：

postgres=# CREATE TABLESPACE app_tbs LOCATION '/var/lib/pgsql/app_tbs';
CREATE TABLESPACE
postgres=# \db
              List of tablespaces
    Name    |  Owner   |        Location        
------------+----------+------------------------
 app_tbs    | postgres | /var/lib/pgsql/app_tbs
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

postgres=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------±-----------±---------±-------±-----------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16470 | app_tbs | 10 | |
(3 rows)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

我们查看一下操作系统中的变化：

-bash-4.2$ ls -l /var/lib/pgsql/app_tbs/
total 0
drwx------. 2 postgres postgres 6 Jan 12 15:42 PG_11_201809051

 

 
  • 1
  • 2
  • 3

在表空间对应的目录中，创建一个特定版本的子目录（PG_‘Major version’_‘Catalogue version number’）。

与此同时，在数据目录下的 pg_tblspc 子目录中，创建了一个指向表空间目录的符号链接，名称为表空间的 OID（16470）：

-bash-4.2$ ls -l /var/lib/pgsql/11/data/pg_tblspc/
total 0
lrwxrwxrwx. 1 postgres postgres 22 Jan 12 15:42 16470 -> /var/lib/pgsql/app_tbs

 

 
  • 1
  • 2
  • 3

默认情况下，执行CREATE TABLESPACE语句的用户为该表空间的拥有者，也可以使用OWNER选项指定拥有者。

对于普通用户，需要授予表空间上的对象创建权限才能使用该表空间。我们为用户 tony 授予表空间 app_tbs 上的使用权限：

postgres=# GRANT CREATE ON TABLESPACE app_tbs TO tony;
GRANT
postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location        |  Access privileges  | Options |  Size   | Description 
------------+----------+------------------------+---------------------+---------+---------+-------------
 app_tbs    | postgres | /var/lib/pgsql/app_tbs | postgres=C/postgres+|         | 0 bytes | 
            |          |                        | tony=C/postgres     |         |         | 
 pg_default | postgres |                        |                     |         | 46 MB   | 
 pg_global  | postgres |                        |                     |         | 574 kB  | 
(3 rows)

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

使用 tony 用户连接到数据库 testdb，然后在表空间 app_tbs 中创建一个新的数据表 t：

testdb=> CREATE TABLE t(id int) tablespace app_tbs;
CREATE TABLE

testdb=> SELECT * FROM pg_tables WHERE tablename=‘t’;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------±----------±-----------±-----------±-----------±---------±------------±------------
public | t | tony | app_tbs | f | f | f | f
(1 row)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

PostgreSQL 支持在CREATE DATABASE、CREATE TABLE、CREATE INDEX以及ADD CONSTRAINT语句中指定 tablespace_name 选项，覆盖默认的表空间（pg_default）。也可以使用相应的ALTER ...语句将对象从一个表空间移到另一个表空间。

如果不想每次创建对象时手动指定表空间，可以使用配置参数 default_tablespace：

testdb=> SET default_tablespace = app_tbs;
SET
testdb=> CREATE TABLE t1(id int);
CREATE TABLE
testdb=> SELECT * FROM pg_tables WHERE tablename='t1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | tony       | app_tbs    | f          | f        | f           | f
(1 row)

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

对于临时表和索引，使用配置参数 temp_tablespaces 进行控制，参考官方文档。

修改表空间

如果需要修改表空间的定义，可以使用 ALTER TABLESPACE 语句：

ALTER TABLESPACE name RENAME TO new_name;

ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER };

ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] );
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] );

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

第一个语句用于表空间的重命名；第二个语句用于修改表空间的拥有者；最后两个语句用于设置表空间的参数。

我们将表空间 app_tbs 重命名为 tony_tbs：

postgres=# \db
              List of tablespaces
    Name    |  Owner   |        Location        
------------+----------+------------------------
 app_tbs    | postgres | /var/lib/pgsql/app_tbs
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

postgres=# ALTER TABLESPACE app_tbs RENAME TO tony_tbs;
ALTER TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------±---------±-----------------------
pg_default | postgres |
pg_global | postgres |
tony_tbs | postgres | /var/lib/pgsql/app_tbs
(3 rows)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

只有表空间的拥有者或超级用户才能修改表空间的定义。

接下来将表空间 tony_tbs 的拥有者修改为 tony：

postgres=# ALTER TABLESPACE tony_tbs OWNER TO tony;
ALTER TABLESPACE
postgres=# \db
              List of tablespaces
    Name    |  Owner   |        Location        
------------+----------+------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tony_tbs   | tony     | /var/lib/pgsql/app_tbs
(3 rows)

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

PostgreSQL 支持设置的表空间参数包括 seq_page_cost、random_page_cost 以及 effective_io_concurrency。它们用于查询计划器选择执行计划时的代价评估。

目前，PostgreSQL 还不支持使用语句修改表空间的存储路径。但是，可以通过手动的方式移动表空间的位置：

  1. 停止 PostgreSQL 服务器进程；
  2. 移动文件系统中的数据文件位置；
  3. 修改 PGDATA/pg_tblspc 目录中的符号链接文件（需要提前获取文件名），指向新的目录；
  4. 启动 PostgreSQL 服务器进程。

首先，停止 PostgreSQL 服务器进程：

-bash-4.2$ whoami
postgres
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl stop
waiting for server to shut down.... done
server stopped

 

 
  • 1
  • 2
  • 3
  • 4
  • 5

然后，将操作系统中的 /var/lib/pgsql/app_tbs/ 目录移动到 /var/lib/pgsql/tony_tbs：

-bash-4.2$ mv /var/lib/pgsql/app_tbs/ /var/lib/pgsql/tony_tbs

 

 
  • 1

更新符号链接文件，执行新的目录：

-bash-4.2$ ln -snf /var/lib/pgsql/tony_tbs /var/lib/pgsql/11/data/pg_tblspc/16470
-bash-4.2$ ls /var/lib/pgsql/11/data/pg_tblspc/16470 -l
lrwxrwxrwx. 1 postgres postgres 23 Jan 14 20:21 /var/lib/pgsql/11/data/pg_tblspc/16470 -> /var/lib/pgsql/tony_tbs

 

 
  • 1
  • 2
  • 3

最后，重新启动 PostgreSQL 服务器进程：

-bash-4.2$ /usr/pgsql-11/bin/pg_ctl start
waiting for server to start....2019-01-14 20:23:43.628 EST [20994] LOG:  listening on IPv4 address "192.168.56.103", port 5432
2019-01-14 20:23:43.632 EST [20994] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-01-14 20:23:43.639 EST [20994] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-01-14 20:23:43.661 EST [20994] LOG:  redirecting log output to logging collector process
2019-01-14 20:23:43.661 EST [20994] HINT:  Future log output will appear in directory "log".
 done
server started

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

确认是否修改成功：

postgres=# \db
               List of tablespaces
    Name    |  Owner   |        Location         
------------+----------+-------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tony_tbs   | tony     | /var/lib/pgsql/tony_tbs
(3 rows)

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

注意，在 PostgreSQL 9.1 及更早的版本中，还需要用新的目录更新系统表 pg_tablespace，否则，pg_dump 将继续使用旧的表空间位置。

删除表空间

对于不再需要的表空间，可以使用DROP TABLESPACE语句进行删除：

DROP TABLESPACE [ IF EXISTS ] name;

 

 
  • 1

IF EXISTS可以避免删除不存在的表空间时产生错误信息。

只有表空间的拥有者或超级用户能够删除表空间。删除表空间之前需要确保其中不存在任何数据库对象，否则无法删除。

testdb=> DROP TABLESPACE tony_tbs;
ERROR:  tablespace "tony_tbs" is not empty

 

 
  • 1
  • 2

无法删除表空间 tony_tbs 是因为数据库 testdb 中存在使用该表空间创建的对象。

testdb=> SELECT ts.spcname,
testdb->        cl.relname
testdb->   FROM pg_class cl
testdb->   JOIN pg_tablespace ts ON cl.reltablespace = ts.oid
testdb->  WHERE ts.spcname = 'tony_tbs';
 spcname  | relname 
----------+---------
 tony_tbs | t
 tony_tbs | t1
(2 rows)

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

可以将这些对象删除，或者移动到其他表空间中，然后再删除表空间：

testdb=> DROP TABLE t, t1;
DROP TABLE
testdb=> DROP TABLESPACE tony_tbs;
DROP TABLESPACE
testdb=> \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

 

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

其他数据库中也可能存在依赖于被删除表空间的对象，同样需要先进行处理，才能删除表空间。

删除表空间时，同时会删除文件系统中对应的表空间子目录。

关于表空间，先了解这么多。接下来我们探讨一下如何防止因系统崩溃、硬件故障或者用户错误可能带来的数据丢失，也就是 PostgreSQL 数据库的备份与恢复。

人生本来短暂，你又何必匆匆！点个赞再走吧！

                                </div>
            <link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e9f16cbbc2.css" rel="stylesheet">
                </div>
posted @ 2019-10-30 16:55  运维小九九  阅读(24)  评论(0)  编辑  收藏  举报