postgresql-数据库与模式

基本概念

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

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

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

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

数据库（Database），一个数据库由一组相关的对象组成，例如表、索引、视图、存储
过程
模式（Schema），数据库中的对象使用模式进行组织。准确地说，一个数据库由多个模式
组成，模式由许多对象组成
表空间（Tablespace），在 PostgreSQL 中，数据库对象（例如表）在文件系统中对应的是文
件，表空间指定了这些文件存储的目录。创建数据库对象时，只需要指定存储对象的表空间的名
称（或者使用默认值），而不需要指定磁盘上的物理路径

创建数据库

使用 SQL 语句查看已有的数据库

select datname from pg_database;
1

--使用 SQL 语 CREATE DATABASE 创建一个数据库：
CREATE DATABASE name;
1
2

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

修改数据库

ALTER DATABASE name action;
1

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

-- 修改 testdb 的名称为newdb
alter database testdb rename to newdb;
1
2

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

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

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

-- 以下语句将会默认禁用数据库 newdb 中的索引扫描
alter database newdb set enable_indexscan to off;
1
2

-- 数据库newdb开启索引扫描
ALTER DATABASE newdb RESET enable_indexscan;
1
2

alter database 详细配置

删除数据库

-- 使用 DROP DATABASE 语句删除一个数据库 name表示数据库名称
DROP DATABASE [ IF EXISTS] name;
1
2

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

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

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

管理模式

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

-- 查看当前数据库中的模式
select * from pg_namespace ;
1
2

PostgreSQL 也提供了管理模式的语句：

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

-- 创建模式时还可以指定它的拥有者：
 CREATE SCHEMA app AUTHORIZATION tony;
1
2

以 pg_开头的名称是系统保留的模式名称，用户无法创建这样的模式。
创建了模式之后，我们就可以在模式中创建各种数据库对象，例如表、数据类型、函数以及
运算符(模式相当于表空间)

-- 如果需要修改已有模式的属性 ，可以使用 ALTER SCHEMA 语句：
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
1
2
3

-- 如果模式中没有任何对象，使用以下语句即可删除该模式：
DROP SCHEMA name;
1
2

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

DROP SCHEMA name CASCADE;
1

级联删除可能会删除一些我们意料之外的对象，使用时需要小心。
数据库中的大多数对象都位于某个模式之中，这样设计的好处在于：

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