在上一篇中我们了解了 PostgreSQL 中角色和用户的概念。PostgreSQL 通过角色来实现数据库用户和组的功能。创建了可以登录的角色（即用户）之后，就可使用这些用户连接到数据库，并执行允许的操作。

本篇我们学习一下如何操作数据库和数据库中的模式。

基本概念

我们先介绍一些基本的概念。

数据库管理系统（DBMS），用于管理数据库的软件系统。常见的关系型 DBMS 有 Oracle、MySQL、SQL Server、PostgreSQL、Db2、SQLite 等等。常见的 NoSQL DBMS 有 Redis、MongoDB、Cassandra、Neo4j 等等。PostgreSQL 荣获了数据库排名网站 DB-Engines 的 2017、2018 以及 2020 年度数据库管理系统称号。

数据库系统，它由实例（Instance）和物理数据库集群（Database Cluster）组成。通常所说的数据库管理系统也就是指数据库系统。

实例（Instance），由 PostgreSQL 后台进程和相关的内存组成。启动服务器进程时创建一个实例，关闭服务器进程时实例随之关闭。启动 PostgreSQL 服务器进程之后，可以通过操作系统的ps命令查询相关的后台进程：

[root@centos7 ~]# ps -ef | grep postgres | grep -v 'grep'
postgres 28003     1  0 Jan04 ?        00:00:01 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
postgres 28011 28003  0 Jan04 ?        00:00:00 postgres: logger   
postgres 28013 28003  0 Jan04 ?        00:00:00 postgres: checkpointer   
postgres 28014 28003  0 Jan04 ?        00:00:01 postgres: background writer   
postgres 28015 28003  0 Jan04 ?        00:00:01 postgres: walwriter   
postgres 28016 28003  0 Jan04 ?        00:00:01 postgres: autovacuum launcher   
postgres 28017 28003  0 Jan04 ?        00:00:02 postgres: stats collector   
postgres 28018 28003  0 Jan04 ?        00:00:00 postgres: logical replication launcher

数据库集群，每个 PostgreSQL 实例管理的都是一个数据库集群，它可以包含多个数据库。需要注意，这里的集群不是多台服务器组成的集群。

数据库（Database），一个数据库由一组相关的对象组成，例如表、索引、视图、存储过程等等。

模式（Schema），数据库中的对象使用模式进行组织。准确地说，一个数据库由多个模式组成，模式由许多对象组成。

表空间（Tablespace），在 PostgreSQL 中，数据库对象（例如表）在文件系统中对应的是文件，表空间指定了这些文件存储的目录。创建数据库对象时，只需要指定存储对象的表空间的名称（或者使用默认值），而不需要指定磁盘上的物理路径。

创建数据库

接下来，我们学习如何管理数据库和模式。先看看默认安装后的数据库，使用操作系统的 postgres 用户登录，通过 psql 工具连接：

[root@centos7 data]# su - postgres
Last login: Sat Jan  5 16:57:07 EST 2019 on pts/3
-bash-4.2$ psql
psql (11.1)
Type "help" for help.

postgres=#

使用 psql 工具的\l命令可以查看当前集群中的数据库：

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

也可以使用 SQL 语句查看已有的数据库：

postgres=# SELECT datname FROM pg_database;
  datname  
-----------
 postgres
 template1
 template0
(3 rows)

系统默认为我们创建了 3 个数据，其中 template0 和 template1 是模板数据库，创建新的数据库时默认基于 template1 进行复制；postgres 数据库是为 postgres 用户创建的默认数据库。

使用 SQL 语句CREATE DATABASE创建一个数据库：

CREATE DATABASE name;

其中的 name 指定了数据库的名称。例如，以下语句创建了一个名为 testdb 的数据库：

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

创建数据库时还可以指定许多选项，例如字符集编码、拥有者、默认表空间、最大连接数等等。参考官方文档中的完整 CREATE DATABASE 语句。

修改数据库

创建之后，可以通过ALTER DATABASE语句修改数据库的属性和配置：

ALTER DATABASE name action;

其中，action 指定了要执行的修改操作，例如修改数据库的名称、所有者、默认表空间、数据库会话变量的默认值等等。

以下语句修改 testdb 的名称：

postgres=# ALTER DATABASE testdb RENAME TO newdb;
ALTER DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 newdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

然后将数据库 newdb 的拥有者修改为 tony：

postgres=# ALTER DATABASE newdb OWNER TO tony;
ALTER DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 newdb     | tony     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

除了修改常见的属性之外，ALTER DATABASE语句还可以用于修改运行时配置变量的会话默认值。

当用户连接数据库时，PostgreSQL 使用配置文件 postgresql.conf 或者启动命令 postgres 中设置的变量值作为默认值。使用ALTER DATABASE语句可以设置指定数据库的这些配置：

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }

对于随后连接的会话，PostgreSQL 将会使用该命令设置的值覆盖 postgresql.conf 文件或者命令行参数的值。注意，只有超级用户或者数据库的拥有者才能修改数据库的默认会话变量。

例如，以下语句将会默认禁用数据库 newdb 中的索引扫描：

ALTER DATABASE newdb SET enable_indexscan TO off;

使用以下命令可以还原默认配置：

ALTER DATABASE newdb RESET enable_indexscan;

详细的修改选项可以参考官方文档中的 ALTER DATABASE 语句。

删除数据库

如果不需要，我们可以使用DROP DATABASE语句删除一个数据库：

DROP DATABASE [ IF EXISTS ] name

如果使用了IF EXISTS，删除一个不存在的数据库时不会产生错误信息。

删除数据库会同时删除该数据库中所有的对象，以及文件系统中的数据目录。只有数据库的拥有者才能够删除数据库。另外，如果数据库上存在用户连接，无法执行删除操作，可以连接到其他数据库执行删除命令。

以下语句可以用于删除 newdb 数据库：

postgres=# DROP DATABASE newdb;
DROP DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(3 rows)

DROP DATABASE命令的删除操作无法恢复，使用时千万小心！

管理模式

创建了数据库之后，还需要创建模式（Schema）才能够存储数据库对象。通常在创建一个新的数据库时，默认会创建一个模式 public。

首先，我们创建一个新的数据库 testdb：

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
public | postgres
(1 row)

其中，\c用于连接到一个数据库；\dn用于查看当前数据库中的模式。也可以使用 SQL 语句查询模式：

testdb=# SELECT nspname FROM pg_namespace;
      nspname       
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
public
 information_schema
(6 rows)

查询结果还显示了系统提供的其他模式。

与数据库的管理类似，PostgreSQL 也提供了管理模式的语句：

CREATE SCHEMA，创建一个新的模式。
ALTER SCHEMA，修改模式的属性。
DROP SCHEMA，删除一个模式。

来看一个简单的例子，首先在 testdb 中创建一个新的模式：

testdb=# CREATE SCHEMA hr;
CREATE SCHEMA
testdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 hr     | postgres
public | postgres
(2 rows)

创建模式时还可以指定它的拥有者：

testdb=# CREATE SCHEMA app AUTHORIZATION tony;
CREATE SCHEMA
testdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 app    | tony
 hr     | postgres
public | postgres
(3 rows)

以pg_开头的名称是系统保留的模式名称，用户无法创建这样的模式。

创建了模式之后，我们就可以在模式中创建各种数据库对象，例如表、数据类型、函数以及运算符等等。这些内容在后面的篇章中再进行介绍。

如果需要修改已有模式的属性，可以使用ALTER SCHEMA语句：

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

以上语句分别用于修改模式的名称和拥有者。以下语句将模式 hr 的拥有者改为 tony：

testdb=# ALTER SCHEMA hr OWNER TO tony;
ALTER SCHEMA
testdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 app    | tony
 hr     | tony
public | postgres
(3 rows)

如果模式中没有任何对象，使用以下语句即可删除该模式：

DROP SCHEMA name;

以下示例将会删除模式 hr：

testdb=# DROP SCHEMA hr;
DROP SCHEMA
testdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 app    | tony
public | postgres
(2 rows)

如果模式中存在其他对象，以上语句无法执行；需要先删除该模式中所有的对象，或者使用以下语句级联删除这些对象：

DROP SCHEMA name CASCADE;

级联删除可能会删除一些我们意料之外的对象，使用时需要小心。

数据库中的大多数对象都位于某个模式之中，这样设计的好处在于：

允许多个用户使用同一个数据库而不会互相干扰，他们可以使用不同的模式来维护自己的数据。
将数据库对象进行逻辑上的分组，便于管理。
第三方应用可以使用单独的模式，不会与系统中的其他对象产生命名冲突。

在我们使用的所有数据库对象中，最主要的就是数据表（table）。下一篇我们介绍一下数据库表的相关管理操作。

ITPub博客

《PostgreSQL 开发指南》第 05 篇数据库与模式

基本概念

创建数据库

修改数据库

删除数据库

管理模式

最新文章

ITPub博客

《PostgreSQL 开发指南》第 05 篇 数据库与模式

基本概念

创建数据库

修改数据库

删除数据库

管理模式

最新文章

《PostgreSQL 开发指南》第 05 篇数据库与模式