数据库的创建与管理

CREATE DATABASE 看似简单，实则藏着一组只有建库那一次机会的固化属性：编码、排序规则、字符分类规则、模板库。本节把这条语句的每个参数讲透，并给出「重建数据库」这一生产高频操作的安全流程。

前置知识

阅读本节前，建议先了解：中文全文搜索

基础概念

在 PostgreSQL 里，数据库（Database）是物理隔离与备份的最小单位。它不是一个「目录」，而是一份拥有独立系统目录（pg_catalog）的数据集合。这意味着：

• 跨数据库查询不被支持。写 shop.public.orders 会报错，public.orders 只是当前库内的表。
• 备份、恢复、pg_dump、逻辑复制订阅，粒度都是数据库。
• 每个数据库内部的表、索引、序列，物理上都存在同一个数据目录里，只有表空间（Tablespace）能把它们分开存放。

关键的几个概念：

• 模板数据库（Template Database）：新库不是「从零创建」的，而是复制一个已有数据库。template1 是可写母版，template0 是原始只读母版。这是 PostgreSQL 特有的设计，也解释了很多「为什么新库自带某张表」的疑问。
• 排序规则（Collation）：决定字符串 ORDER BY 与比较运算的顺序。它由 LC_COLLATE 固化到数据库上（PostgreSQL 15 起也可按列/表达式指定）。
• 字符分类（Character Classification）：由 LC_CTYPE 决定，影响 upper()、lower()、initcap()、正则的 \w 等行为。
• 连接限制（Connection Limit）：每个数据库独立的并发连接上限，-1 表示不限制。它是防止单库占满连接池的第一道闸门。

实例（Instance）                       ← 一份 data 目录 + 一个 postmaster 进程
├── template0   （只读原始母版，datallowconn = false）
├── template1   （可写母版，新库默认从这里复制）
├── postgres    （默认维护库）
└── shop        （业务库）
    ├── pg_catalog
    ├── information_schema
    └── public

语法与用法

完整语法骨架（省略了部分冷门参数）：

CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ]                    -- WAL_LOG | FILE_COPY（PostgreSQL 15+）
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ICU_LOCALE [=] icu_locale ]                -- PostgreSQL 15+ / 16+ 增强
           [ LOCALE_PROVIDER [=] provider ]             -- libc | icu | builtin（PostgreSQL 15+）
           [ COLLATION_VERSION [=] version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

最常用的调用形态：

-- 在 postgres 库中执行；不能在目标库自身执行，也不能放在事务块里
CREATE DATABASE demo
  WITH
    OWNER            = postgres,
    ENCODING         = 'UTF8',
    LC_COLLATE       = 'zh_CN.UTF-8',
    LC_CTYPE         = 'zh_CN.UTF-8',
    TEMPLATE         = template0,
    TABLESPACE       = pg_default,
    CONNECTION LIMIT = 100,
    IS_TEMPLATE      = false;

CREATE DATABASE

参数速查表：

参数	默认值	取值范围 / 说明	是否可后改
OWNER	执行者	任意角色名	可（ALTER DATABASE ... OWNER TO）
TEMPLATE	template1	任意数据库名	不可
ENCODING	继承模板	如 UTF8、LATIN1	不可（且不能是 SQL_ASCII 混用）
LC_COLLATE	继承模板	依赖系统 locale，如 zh_CN.UTF-8、C	不可
LC_CTYPE	继承模板	同上	不可
LOCALE_PROVIDER	libc	libc / icu（PG15+）、builtin（PG17+）	不可
TABLESPACE	pg_default	已有表空间名	可
ALLOW_CONNECTIONS	true	布尔	可
CONNECTION LIMIT	-1	整数，-1 为无限	可
IS_TEMPLATE	false	布尔，true 后可被他人用作模板	可
STRATEGY	WAL_LOG	WAL_LOG / FILE_COPY（PG15+）	不适用

详细说明

template0 与 template1：必须搞清的一对母版

新建数据库时，PostgreSQL 会物理复制一个模板库。两个模板的区别只有三条，但影响很大：

对比项	template0	template1
允许连接	否（datallowconn = f）	是
内容	原始、干净、只读语义	可被 DBA 修改（装扩展、建表）
能否指定不同编码/locale	可以	不可以，会报错 new encoding is incompatible with the template database
新库是否继承其内容	否（干净）	是（连同你装过的扩展一起）

因此选择规则非常明确：

-- 场景 A：需要指定与实例默认不同的编码或 locale → 必须用 template0
CREATE DATABASE demo
  ENCODING   = 'UTF8'
  LC_COLLATE = 'zh_CN.UTF-8'
  LC_CTYPE   = 'zh_CN.UTF-8'
  TEMPLATE   = template0;

-- 场景 B：希望新库自带公司统一装的扩展（如 pg_stat_statements、uuid-ossp）
--          先在 template1 里装好，之后所有新库自动继承
CREATE DATABASE demo2 TEMPLATE = template1;

-- 错误！template1 的编码/locale 已被固化，无法覆盖
CREATE DATABASE bad
  LC_COLLATE = 'zh_CN.UTF-8'
  TEMPLATE   = template1;

ERROR:  new collation (zh_CN.UTF-8) is incompatible with the collation of the template database (C)
HINT:  Use the same collation as in the template database, or use template0 as template.

生产建议：建库脚本一律显式写 TEMPLATE template0，把编码与 locale 写死。否则一旦实例以 C locale 初始化（容器镜像常见），建出来的库在中文排序上会与预期不符，且事后无法修改。

修改数据库：ALTER DATABASE 能改什么

ALTER DATABASE name
    [ [ WITH ] ALLOW_CONNECTIONS allowconn ]
    [ [ WITH ] CONNECTION LIMIT connlimit ]
    [ [ WITH ] IS_TEMPLATE istemplate ]
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
ALTER DATABASE name REFRESH COLLATION VERSION        -- PostgreSQL 15+

能改的与不能改的一目了然：

属性	可改	语句
属主	是	ALTER DATABASE ... OWNER TO
连接限制	是	ALTER DATABASE ... CONNECTION LIMIT
是否允许连接	是	ALTER DATABASE ... ALLOW_CONNECTIONS
数据库级参数	是	ALTER DATABASE ... SET search_path
表空间	是	ALTER DATABASE ... SET TABLESPACE
名称	是	ALTER DATABASE ... RENAME TO
编码 / locale	否	只能 pg_dump + 重建

-- 数据库级参数：对新连接生效，已在运行的连接不受影响
ALTER DATABASE demo SET search_path TO app, public;
ALTER DATABASE demo SET timezone TO 'Asia/Shanghai';
ALTER DATABASE demo SET statement_timeout TO '30s';
ALTER DATABASE demo SET idle_in_transaction_session_timeout TO '60s';

-- 撤销某项设置
ALTER DATABASE demo RESET statement_timeout;

-- 查看数据库上挂了哪些参数
SELECT d.datname, unnest(s.setconfig) AS setting
FROM pg_db_role_setting s
JOIN pg_database d ON d.oid = s.setdatabase
WHERE d.datname = 'demo';

 datname |                     setting
---------+--------------------------------------------------
 demo    | search_path=app, public
 demo    | timezone=Asia/Shanghai
 demo    | statement_timeout=30s
(3 rows)

参数的作用域优先级从低到高：

postgresql.conf  →  ALTER DATABASE ... SET  →  ALTER ROLE ... SET  →  SET（会话级）  →  SET LOCAL（事务级）

查看数据库：\l 与 pg_database

-- psql 元命令，人读友好
\l

                                                             List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   |  Size   | Tablespace | Description
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------+---------+------------+-------------
 demo      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            |                       | 8421 kB | pg_default |
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            |                       | 8621 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | =c/postgres          +| 8525 kB | pg_default | unmodifiable empty database
           |          |          |             |             |            |                 | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | =c/postgres          +| 8621 kB | pg_default | default template for new databases
           |          |          |             |             |            |                 | postgres=CTc/postgres |         |            |
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | en_US.UTF-8| icu             |                       | 9100 kB | pg_default |
(5 rows)

程序化查询用 pg_database，字段与含义：

字段	含义
datname	库名
datdba	属主 OID，用 pg_get_userbyid() 转名字
encoding	编码的数字编号，用 pg_encoding_to_char() 转字符串
datcollate / datctype	排序与字符分类规则
datistemplate	是否可作为模板
datallowconn	是否允许连接
datconnlimit	连接上限，-1 为无限
dattablespace	默认表空间 OID

-- 一次拿到可读的完整清单，并按大小排序
SELECT
  d.datname                                   AS db_name,
  pg_get_userbyid(d.datdba)                   AS owner,
  pg_encoding_to_char(d.encoding)             AS encoding,
  d.datcollate                                AS collate,
  d.datconnlimit                              AS conn_limit,
  d.datistemplate                             AS is_template,
  d.datallowconn                              AS allow_conn,
  pg_size_pretty(pg_database_size(d.datname)) AS size
FROM pg_database d
ORDER BY pg_database_size(d.datname) DESC;

 db_name   |  owner   | encoding |   collate   | conn_limit | is_template | allow_conn |  size
-----------+----------+----------+-------------+------------+-------------+------------+---------
 testdb    | postgres | UTF8     | en_US.UTF-8 |         -1 | f           | t          | 9100 kB
 postgres  | postgres | UTF8     | zh_CN.UTF-8 |         -1 | f           | t          | 8621 kB
 template1 | postgres | UTF8     | zh_CN.UTF-8 |         -1 | t           | t          | 8621 kB
 template0 | postgres | UTF8     | zh_CN.UTF-8 |         -1 | t           | f          | 8525 kB
 demo      | postgres | UTF8     | zh_CN.UTF-8 |        100 | f           | t          | 8421 kB
(5 rows)

-- 查看当前连接的库与当前用户
SELECT current_database() AS db, current_user AS "user", current_schema() AS schema;

-- 查看库中所有对象占用的空间（表 + 索引 + TOAST）
SELECT
  relname                                     AS object_name,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind IN ('r', 'm', 'i')
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 5;

删除数据库与强制断开连接

DROP DATABASE 有三条铁律：

1. 不能删除当前正在连接的库（cannot drop the currently open database）。
2. 有活动连接时会失败（database "x" is being accessed by other users）。
3. 不能放在事务块里，且没有 ROLLBACK 的机会。

PostgreSQL 13 提供了两种解决方案：

-- 方案一：DROP DATABASE ... WITH (FORCE)（PostgreSQL 13+）
-- 它会终止所有连接后再删除，适合自动化脚本
DROP DATABASE demo WITH (FORCE);

DROP DATABASE

-- 方案二：手工踢连接后再删（兼容旧版本，也更可控）
SELECT pg_terminate_backend(pid) AS killed_pid
FROM pg_stat_activity
WHERE datname = 'demo'
  AND pid <> pg_backend_pid();       -- 别把自己踢了

 killed_pid
------------
      18234
      18237
(2 rows)

-- 顺序很重要：先踢连接，再删除
DROP DATABASE IF EXISTS demo;

# 命令行等价写法
dropdb --if-exists --force demo

查看当前有哪些连接阻塞了删除：

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  now() - backend_start AS conn_age,
  left(query, 60)       AS last_query
FROM pg_stat_activity
WHERE datname = 'demo'
ORDER BY backend_start;

  pid  | usename  | application_name | client_addr | state  |   conn_age   |                          last_query
-------+----------+------------------+-------------+--------+--------------+--------------------------------------------------------------
 18234 | app_user | pgAdmin 4        | 10.0.3.21   | idle   | 02:14:33.12  | SELECT * FROM orders WHERE id = $1
 18237 | app_user | psql             | 10.0.3.45   | active | 00:00:12.44  | SELECT count(*) FROM orders
(2 rows)

重建数据库的标准流程

编码/locale 改不了、库被写坏了、需要彻底清理——这些场景都要「重建」。关键是：重建不是 DROP 再 CREATE 那么简单，因为重建会丢权限（GRANT）与数据库级参数。

一次安全的重建分四步：

第 1 步  导出结构（含权限、注释、数据库级参数）
第 2 步  踢连接 → DROP → CREATE（带完整参数）
第 3 步  回灌结构
第 4 步  核对对象数量与权限

完整脚本见「实战示例」。

实战示例

场景：demo 库因历史原因用了 C locale，中文姓名排序错乱（按字节序而非拼音），需要重建为 zh_CN.UTF-8。

第 1 步：确认现有库的问题

SELECT datname, datcollate, datctype, pg_encoding_to_char(encoding) AS encoding
FROM pg_database WHERE datname = 'demo';

 datname | datcollate | datctype | encoding
---------+------------+----------+----------
 demo    | C          | C        | UTF8
(1 row)

-- 中文排序按字节序，结果与拼音顺序不符
SELECT name FROM demo_names ORDER BY name;

 name
------
 张三
 李四
 王五
 陈六
(4 rows)

（期望顺序是「陈六、李四、王五、张三」按拼音，但 C locale 下按 UTF-8 字节序排成了上面的样子。）

第 2 步：导出结构与权限

# 只导结构（含 GRANT/COMMENT），不导数据
pg_dump -d demo --schema-only --no-owner -f /tmp/demo_schema.sql

# 单独记录数据库级参数
psql -d postgres -Atc "
  SELECT unnest(setconfig) FROM pg_db_role_setting s
  JOIN pg_database d ON d.oid = s.setdatabase WHERE d.datname = 'demo';" \
> /tmp/demo_db_settings.txt

cat /tmp/demo_db_settings.txt

search_path=app, public
timezone=Asia/Shanghai
statement_timeout=30s

第 3 步：踢连接、删除、按正确 locale 重建

-- 3.1 阻止新连接（先关闸门，再踢旧连接，避免边踢边进）
ALTER DATABASE demo ALLOW_CONNECTIONS false;

-- 3.2 踢掉残留连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'demo' AND pid <> pg_backend_pid();

-- 3.3 重命名为备份库（比直接 DROP 安全：出问题还能改名回来）
ALTER DATABASE demo RENAME TO demo_old_20260924;

ALTER DATABASE

-- 3.4 用正确的 locale 重建
CREATE DATABASE demo
  WITH
    OWNER            = postgres,
    ENCODING         = 'UTF8',
    LC_COLLATE       = 'zh_CN.UTF-8',
    LC_CTYPE         = 'zh_CN.UTF-8',
    TEMPLATE         = template0,
    CONNECTION LIMIT = 100;

CREATE DATABASE

第 4 步：回灌结构、恢复参数、核对

psql -d demo -v ON_ERROR_STOP=1 -f /tmp/demo_schema.sql

-- 4.1 恢复数据库级参数
ALTER DATABASE demo SET search_path TO app, public;
ALTER DATABASE demo SET timezone   TO 'Asia/Shanghai';
ALTER DATABASE demo SET statement_timeout TO '30s';

-- 4.2 恢复连接开关
ALTER DATABASE demo ALLOW_CONNECTIONS true;

-- 4.3 核对：两边对象数量与编码
SELECT
  (SELECT count(*) FROM pg_class WHERE relkind = 'r')                       AS table_count,
  (SELECT count(*) FROM pg_class WHERE relkind = 'i')                       AS index_count,
  (SELECT datcollate FROM pg_database WHERE datname = 'demo')               AS collate;

 table_count | index_count |   collate
-------------+-------------+-------------
          12 |          21 | zh_CN.UTF-8
(1 row)

第 5 步：数据回灌与验证

# 数据用 COPY 或 pg_restore 从备份库回灌（此处用 pg_dump 直连两个库）
pg_dump -d demo_old_20260924 --data-only | psql -d demo

-- 验证中文排序已按拼音
SELECT name FROM demo_names ORDER BY name;

 name
------
 陈六
 李四
 王五
 张三
(4 rows)

第 6 步：确认无误后清理旧库

-- 观察一周后再执行
DROP DATABASE demo_old_20260924;

注意事项

• CREATE DATABASE / DROP DATABASE 不能在事务块或函数中执行。 想在脚本里用 DO $$ ... $$ 动态建库也不行，必须由客户端逐条发语句。

-- 错误！cannot run inside a transaction block
BEGIN;
CREATE DATABASE demo2;
COMMIT;

• 不能在目标库上操作它自己。 创建 demo 时必须连在 postgres 或别的库上；删除 demo 时同理。

-- 当前连在 demo 上
DROP DATABASE demo;

ERROR:  cannot drop the currently open database

• DROP DATABASE 不可回退。 没有「回收站」，也没有事务保护。生产环境建议先用 ALTER DATABASE ... RENAME TO xxx_old_YYYYMMDD 改名，观察一段时间后再真正删除。

• WITH (FORCE) 会强杀正在跑的查询。 它等价于「踢连接 + 立即删除」，若有长事务正在写入，会造成业务中断且可能丢未提交数据。生产上应先在业务低峰期手工确认连接再操作。

• 编码与 locale 一旦建库就无法修改。 只能重建。这是最容易在项目早期埋下隐患的一处配置。

• LC_COLLATE = 'C' 在中文环境几乎总是错的。 它按字节序排序，中文会乱；但它的比较速度最快、且不依赖系统 locale 安装情况。若确定只存 ASCII（如订单号、UUID），用 C 反而更好。

• datistemplate = true 的库可以被任何有 CREATEDB 权限的用户复制。 别把业务库设成模板，否则别人可能以它为母版建出巨型库。

• CONNECTION LIMIT 不是连接池。 它只是兜底上限，超过会报 too many clients already / remaining connection slots are reserved。真正的连接管理靠应用侧连接池与 max_connections。

-- 查看各库当前的连接占用
SELECT datname, count(*) AS conns
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname
ORDER BY conns DESC;

• ALTER DATABASE ... SET 只对新连接生效。 已有连接需要 SELECT pg_reload_conf() 之后再重连，或直接手工 SET。

• 模板库 template1 里装扩展会「污染」所有新库。 这是特性也是风险：一次误装的调试扩展会进入后续每个新库。必要时用 template0 建库，再单独装扩展。

• 库名长度上限 63 字节（NAMEDATALEN - 1），超出会被静默截断，导致脚本里的名字与实际名字不一致。

最佳实践

• 建库脚本写成带幂等判断的模板，纳入版本管理：

#!/usr/bin/env bash
set -euo pipefail

DB_NAME="${1:?usage: create-db.sh <dbname>}"
DB_OWNER="${DB_OWNER:-app_user}"
DB_LOCALE="${DB_LOCALE:-zh_CN.UTF-8}"

psql -d postgres -v ON_ERROR_STOP=1 <<SQL
SELECT 'CREATE DATABASE ${DB_NAME}
          OWNER ${DB_OWNER}
          ENCODING ''UTF8''
          LC_COLLATE ''${DB_LOCALE}''
          LC_CTYPE ''${DB_LOCALE}''
          TEMPLATE template0
          CONNECTION LIMIT 200'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = '${DB_NAME}')\gexec
SQL

echo "database ${DB_NAME} is ready"

\gexec 是 psql 的一个技巧：把查询结果当作 SQL 再执行一次，从而实现「不存在才创建」的幂等建库。

• 一律 TEMPLATE template0 + 显式 ENCODING/LC_COLLATE/LC_CTYPE。 不要依赖实例默认值——它取决于 initdb 时的环境变量，容器镜像里常是 C。

• 给每个库设置 CONNECTION LIMIT，防止单个库被 N+1 或连接泄漏占满整个实例。

• 给每个库固化 search_path、timezone、statement_timeout、idle_in_transaction_session_timeout，让应用连接开箱即用，不依赖客户端拼参数。

ALTER DATABASE demo SET search_path = app, public;
ALTER DATABASE demo SET timezone = 'Asia/Shanghai';
ALTER DATABASE demo SET statement_timeout = '30s';
ALTER DATABASE demo SET idle_in_transaction_session_timeout = '60s';

• 重建数据库前先 ALTER DATABASE ... ALLOW_CONNECTIONS false，堵住新连接，再踢旧连接，避免出现「刚踢完又连进来」的循环。

• 重建流程用「改名备份」而非直接 DROP：ALTER DATABASE demo RENAME TO demo_old_YYYYMMDD。这样出问题能一次改名回滚，代价只是几毫秒的元数据更新。

• 把「结构」与「数据」分开导出：--schema-only 用于回灌结构（含权限、注释），--data-only 用于回灌数据。后者可以用 COPY 或 pg_restore -j 并行加速。

• 定期核对 pg_database 与资产清单，清理无人使用的库，避免磁盘被遗忘的测试库吃掉。

-- 找出 30 天无人连接的库
SELECT d.datname, max(a.backend_start) AS last_seen
FROM pg_database d
LEFT JOIN pg_stat_activity a ON a.datname = d.datname
WHERE NOT d.datistemplate AND d.datallowconn
GROUP BY d.datname
ORDER BY last_seen NULLS FIRST;

下一节

继续学习：模式（Schema）管理

参考链接

• PostgreSQL 官方文档：CREATE DATABASE
• PostgreSQL 官方文档：ALTER DATABASE
• PostgreSQL 官方文档：DROP DATABASE
• PostgreSQL 官方文档：管理数据库（Managing Databases）