PostgreSQL 创建数据库

Begindev 编辑部 March 31, 2026

1. 创建数据库的基础语法

在 PostgreSQL 中，创建数据库最核心的 SQL 命令是 CREATE DATABASE。这个命令最简单的形式如下：

CREATE DATABASE database_name;

这里的 database_name 就是你想为新数据库起的名字。请注意，这个名称在你的 PostgreSQL 集群（Cluster）中必须是唯一的。

示例：

CREATE DATABASE my_first_database;

执行这条命令后，系统会创建一个名为 my_first_database 的数据库。它会自动继承系统模板数据库（默认通常是 template1）的所有默认设置。

2. CREATE DATABASE 的高级选项与参数

虽然基础语法极其简单，但 CREATE DATABASE 命令提供了丰富的选项，让你能够深度定制新数据库的行为和特征。这些选项允许你控制字符编码、区域设置、表空间等关键属性。

2.1 字符编码 (Encoding)

ENCODING 选项用于指定数据库中存储文本数据所用的字符集编码。常见的编码包括 UTF8（现代应用最强烈推荐的编码）、LATIN1 和 SQL_ASCII。正确指定编码对于确保文本数据不乱码至关重要。

CREATE DATABASE my_database
    ENCODING 'UTF8';

这个例子创建了一个采用 UTF-8 编码的数据库。UTF-8 是一种非常通用的编码方式，能够完美支持全球几乎所有的语言字符。

正例：假设你要开发一个应用，需要存储包含中文、英文、日文和西班牙文的用户姓名和地址。使用 UTF8 编码可以保证所有这些字符都被正确存储，绝不会出现数据损坏或乱码。

反例：如果你（错误地）使用了 SQL_ASCII，那么你的数据库将只能安全地存储基础的英文字符，任何中文或特殊符号在存入时都会丢失或变成乱码。

2.2 区域设置 (Locale: LC_COLLATE 与 LC_CTYPE)

区域设置决定了数据库的排序规则 (LC_COLLATE) 和字符分类规则 (LC_CTYPE)。这些设置会深刻影响字符串的比较方式，以及像 UPPER() (转大写) 和 LOWER() (转小写) 这类函数的行为。

CREATE DATABASE my_database
    ENCODING 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

这个例子不仅设置了 UTF-8 编码，还将区域设置为美国英语 (U.S. English)。

LC_COLLATE 定义了排序顺序，决定了在使用 ORDER BY 查询时字符串如何排列。
LC_CTYPE 影响字符分类，比如系统如何判断一个字符是字母还是数字，以及大小写转换的规则。

正例： 在电商应用中，你想按拼音首字母（针对中文环境）或字母表顺序（针对英文环境）对商品名称进行排序。正确的 LC_COLLATE 设置能确保排序结果符合当地用户的阅读习惯。

反例： 如果一个面向德国用户的数据库，其 LC_COLLATE 被错配成了中文或英文，那么包含德文特殊字符（如 ä, ö, ü）的文本在排序时就会出现严重混乱。

2.3 表空间 (Tablespace)

在 PostgreSQL 中，表空间 (Tablespace) 代表文件系统上的一个物理目录路径，用于存放数据库对象（如数据表和索引）。默认情况下，所有新数据库都创建在默认表空间中。但是，你可以使用 TABLESPACE 选项指定一个自定义的表空间。表空间常被用于性能调优或跨磁盘的存储管理。

要使用自定义表空间，首先需要创建一个表空间（注意：这通常需要超级用户权限）：

CREATE TABLESPACE my_tablespace
    LOCATION '/path/to/my/tablespace';

然后，在创建数据库时指定它：

CREATE DATABASE my_database
    TABLESPACE my_tablespace;

这样，my_database 的所有数据文件就会被物理存储在 my_tablespace 所指向的目录中。

正例：假设你的服务器配备了一块高速 SSD（固态硬盘）和一块大容量但较慢的 HDD（机械硬盘）。你可以把频繁读写的热点数据库的表空间建在 SSD 上，而把用于归档历史数据的数据库表空间建在 HDD 上。这种策略能大幅度优化系统整体的性价比和性能。

反例：将所有数据库都挤在默认的单一磁盘上，当数据量激增或并发极高时，极易造成严重的磁盘 I/O 瓶颈。

2.4 模板 (Template)

在创建新数据库时，PostgreSQL 实际上是通过“复制”一个模板数据库来完成的。默认的模板是 template1。你可以使用 TEMPLATE 选项指定一个不同的模板。这对于需要批量创建具有预设配置或特定插件的数据库非常有用。

CREATE DATABASE my_database
    TEMPLATE template0;

上面的命令使用 template0 作为模板。template0 是 PostgreSQL 提供的一个最原始、最纯净的模板。使用它创建数据库，可以确保你的新数据库不会意外继承 template1 中可能被人为添加的任何自定义设置或垃圾数据。

正例： 你可以专门创建一个模板数据库，在里面安装好常用的扩展（如处理空间数据的 PostGIS），并设定好特定的安全策略。之后所有需要这些功能的新业务数据库，只需指定这个自定义模板即可快速生成。

反例： 如果你在默认的 template1 里随意建表或修改了全局配置，那么未来创建的每一个新数据库都会带着这些（可能是不需要的）表和配置，导致系统极其混乱。

2.5 连接数限制 (Connection Limit)

CONNECTION LIMIT 选项用于设置允许连接到该数据库的最大并发连接数。这是一种非常重要的资源管理和防过载机制。

CREATE DATABASE my_database
    CONNECTION LIMIT 100;

这会将 my_database 的最大并发连接数限制为 100。一旦达到此限制，任何新的连接尝试都会被直接拒绝。

正例：对于一个部署在低配置服务器上的小型应用，设置一个合理的连接限制可以防止数据库被突发的大量请求压垮，从而保证核心服务的稳定性。

反例：将连接限制设置得过低，可能会导致正常用户的请求被频繁拒绝，造成服务中断的假象。如果不设限制（默认情况），恶意攻击或代码 Bug（如连接池泄漏）则可能耗尽系统内存。

2.6 所有者 (Owner)

OWNER 选项指定了新数据库的所有者角色（用户）。数据库所有者拥有极高的特权，包括修改数据库配置以及向其他用户授予权限。

CREATE DATABASE my_database
    OWNER my_user;

这条命令创建了 my_database，并立即将其所有权交给了用户 my_user。

正例：在一个多部门共享的数据库集群中，你可以为每个部门分配特定的用户，并在建库时将对应数据库的 OWNER 指派给该部门的负责人账号，实现权限的物理隔离和下放。

反例：如果不明确指定所有者，数据库将默认属于执行 CREATE DATABASE 命令的用户（通常是超级管理员 postgres）。这违背了最小权限原则，增加了安全风险。

3. 真实场景实战演练

让我们把上述选项结合起来，看看在实际工程中是如何创建数据库的。

场景一：为 Web 应用创建数据库

CREATE DATABASE web_app
    ENCODING 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    OWNER web_user
    CONNECTION LIMIT 50;

这段代码为 Web 应用创建了一个名为 web_app 的数据库。它强制使用通用性最好的 UTF-8 编码，设置了美式英语的区域规则，将管理权限赋予了 web_user，并为了保护资源，将最大并发连接数限制在 50 个。

场景二：为数据仓库 (Data Warehousing) 创建数据库

-- 必须先由超级用户创建指向高速磁盘的表空间
CREATE TABLESPACE fast_disk LOCATION '/mnt/ssd/data';

-- 创建数据仓库数据库
CREATE DATABASE data_warehouse
    ENCODING 'UTF8'
    TABLESPACE fast_disk
    OWNER data_admin;

在这个场景中，为了应对数据仓库海量的数据吞吐需求，我们首先在高速 SSD 硬盘 (/mnt/ssd/data) 上创建了一个名为 fast_disk 的表空间。随后，我们将名为 data_warehouse 的数据库直接建在了这个高速表空间上，并由专员 data_admin 进行管理。

场景三：使用自定义模板创建数据库

-- 1. 创建一个自定义模板数据库 (基于系统默认的 template1)
CREATE DATABASE my_template WITH TEMPLATE template1;

-- 2. 连接到 my_template (需在命令行操作: \c my_template)，然后安装扩展
CREATE EXTENSION postgis; 

-- 3. 使用配置好的模板创建最终的新数据库
CREATE DATABASE new_database WITH TEMPLATE my_template;

通过这种方式创建的 new_database，在诞生之初就已经自动安装好了 postgis 扩展，无需再次手动配置。