PostgreSQL 创建数据库命令详解 – wiki基地

作者： /

PostgreSQL 数据库创建命令详解：CREATE DATABASE

在 PostgreSQL 中，CREATE DATABASE 命令是用于在数据库集群中创建一个新的数据库的核心指令。一个 PostgreSQL 数据库集群（或称实例）可以包含多个独立的数据库，每个数据库拥有自己的用户、表、视图、索引、函数等对象集合。理解如何正确使用 CREATE DATABASE 命令对于数据库管理员和开发者来说至关重要，因为它涉及到新数据库的命名、所有权、字符集、排序规则、存储位置等基础设置。

本文将深入探讨 CREATE DATABASE 命令的各个方面，包括其基本语法、常用选项、参数详解、高级用法、注意事项以及相关的概念。

1. CREATE DATABASE 的基本概念

在 PostgreSQL 中，一个“数据库”是一个独立的命名空间，它包含了一系列数据库对象（如表、索引、函数等）。不同的数据库之间通常是隔离的，通过连接字符串指定连接到哪个数据库。CREATE DATABASE 命令就是在现有的 PostgreSQL 服务器实例上创建一个新的、空的（或者基于模板的）数据库。

默认情况下，一个新创建的数据库会继承其模板数据库（通常是 template1）中的所有标准对象和配置。这使得每个新数据库在创建后即可使用大多数标准的 PostgreSQL 功能。

2. 基本语法

CREATE DATABASE 命令的最简单形式只需要指定新数据库的名称：

sql
CREATE DATABASE dbname;

其中 dbname 是你想要创建的数据库的名称。数据库名称必须符合 PostgreSQL 的标识符规则，通常以字母开头，可以包含字母、数字和下划线，但不能是 PostgreSQL 的保留字，且区分大小写（虽然通常建议使用小写）。

例如，创建一个名为 mydatabase 的数据库：

sql
CREATE DATABASE mydatabase;

执行此命令后，如果一切顺利，一个名为 mydatabase 的新数据库就会被创建出来，并可以使用 \c mydatabase (在 psql 客户端中) 或通过应用程序的连接字符串连接到它。

3. CREATE DATABASE 的完整语法与选项

CREATE DATABASE 命令提供了多种选项，允许你在创建时定制数据库的各种属性。其完整的语法结构如下：

sql
CREATE DATABASE name
[ [ WITH ] option [ ... ] ]

这里的 option 可以是以下任意一个或多个：

  • OWNER = user_name
  • TEMPLATE = template
  • ENCODING = encoding
  • LC_COLLATE = lc_collate
  • LC_CTYPE = lc_ctype
  • TABLESPACE = tablespace_name
  • CONNECTION LIMIT = limit
  • IS_TEMPLATE = is_template

接下来，我们将详细解释每一个选项。

3.1 OWNER = user_name

  • 作用： 指定新数据库的所有者。数据库所有者对该数据库拥有最高权限，可以删除数据库、修改其配置、管理其中的对象等。
  • 默认值： 如果未指定，新数据库的所有者将是执行 CREATE DATABASE 命令的用户。
  • 如何指定： 使用 OWNER = user_name，其中 user_name 是一个现有的数据库用户（角色）的名称。
  • 示例：
    sql
    CREATE DATABASE salesdb OWNER = admin_user;
    这将创建一个名为 salesdb 的数据库，并将其所有者设置为 admin_user 用户。请注意，执行此命令的用户必须有权限创建数据库，并且 admin_user 必须是一个已存在的角色。

3.2 TEMPLATE = template

  • 作用： 指定创建新数据库时所基于的模板数据库。新数据库将复制模板数据库中的所有对象和数据。
  • 默认值： 如果未指定，默认使用 template1 数据库作为模板。
  • 如何指定： 使用 TEMPLATE = template，其中 template 是一个现有的数据库名称。

  • 示例：
    sql
    CREATE DATABASE testdb TEMPLATE = production_template;
    这将创建一个名为 testdb 的数据库，它是 production_template 数据库的一个精确副本（在创建时）。

  • 重要的模板数据库：template1 和 template0

    • template1: 这是默认的模板数据库。它通常包含 PostgreSQL 的标准系统目录以及在安装或初始化集群时添加的任何扩展或对象。当你使用 CREATE DATABASE dbname; 时，实际上是基于 template1 创建的。如果你在 template1 中创建了自定义对象（不推荐这样做，因为它会污染所有未来的数据库），这些对象也会出现在新数据库中。
    • template0: 这是一个“干净”的模板数据库，它只包含标准的 PostgreSQL 系统目录，不包含任何用户定义的对象或安装时添加的扩展。最重要的是，template0 会忽略模板数据库的编码和区域设置，而是使用 CREATE DATABASE 命令中指定的编码和区域设置（如果未指定，则使用集群的默认设置）。 这是创建具有不同于 template1 的编码或区域设置的新数据库的唯一安全方式。
    • 使用 template0 的场景： 当你需要创建一个具有特定编码或区域设置的数据库，而这些设置与 template1 不同时，必须使用 template0 作为模板。例如：
      sql
      CREATE DATABASE mydatabase_utf8_turkish
      TEMPLATE = template0
      ENCODING = 'UTF8'
      LC_COLLATE = 'tr_TR.UTF-8'
      LC_CTYPE = 'tr_TR.UTF-8';
      如果你尝试基于 template1（假设它是英文环境）创建具有土耳其语区域设置的数据库，可能会遇到错误或产生意外的行为，因为区域设置是模板数据库的一个属性，并且不兼容的区域设置不能直接通过基于 template1 的方式更改。

3.3 ENCODING = encoding

  • 作用： 指定新数据库的字符集编码。这决定了数据库可以存储哪些字符以及字符如何编码。
  • 默认值： 如果未指定，默认使用模板数据库的编码。如果使用 template0 且未指定编码，则使用集群初始化时指定的默认编码。
  • 如何指定： 使用 ENCODING = encoding，其中 encoding 是一个字符串字面量或标识符，表示支持的字符集编码，如 'UTF8'、'LATIN1' 等。推荐使用 'UTF8'，因为它支持几乎所有语言的字符。
  • 示例：
    sql
    CREATE DATABASE internationaldb ENCODING = 'UTF8';
  • 重要注意事项：
    • 数据库创建后，其编码通常不能更改。因此，在创建时选择正确的编码至关重要。
    • 客户端连接的编码、服务器的编码、数据库的编码需要协调一致，以避免乱码问题。PostgreSQL 服务器通常会自动进行编码转换，但这依赖于正确的配置。
    • SQL_ASCII 编码是一个特殊的编码，它实际上不执行任何编码转换。它假设所有字符都是 ASCII，或者客户端和服务器知道如何处理非 ASCII 字节。使用 SQL_ASCII 可能会导致存储无效的非 ASCII 字符序列，并在排序和字符串处理时产生非预期的结果。通常不推荐使用 SQL_ASCII，除非你清楚自己在做什么并且有非常特定的遗留系统需求。

3.4 LC_COLLATE = lc_collate 和 LC_CTYPE = lc_ctype

  • 作用：
    • LC_COLLATE: 控制字符串的排序规则。这影响 ORDER BY 子句的结果、索引的顺序以及任何依赖于字符顺序的操作（如范围查询）。
    • LC_CTYPE: 控制字符的分类，例如哪些字符被视为大写、小写、数字、空白符等。这影响 UPPER()、LOWER() 函数的行为以及正则表达式匹配等。
  • 默认值： 如果未指定，默认使用模板数据库的区域设置。如果使用 template0 且未指定区域设置，则使用集群初始化时指定的默认区域设置。
  • 如何指定： 使用 LC_COLLATE = lc_collate 和 LC_CTYPE = lc_ctype，其中 lc_collate 和 lc_ctype 是有效的操作系统区域设置名称，如 'en_US.UTF-8'、'fr_FR.UTF-8'、'zh_CN.UTF-8' 等。这两个参数通常需要设置为相同的值，除非你有特殊需求。
  • 示例：
    sql
    CREATE DATABASE frenchdb
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'fr_FR.UTF-8'
    LC_CTYPE = 'fr_FR.UTF-8';
  • 重要注意事项：
    • 区域设置是数据库创建后不能更改的属性。
    • 区域设置会影响索引的效率，特别是对于文本列。非 C 或 POSIX 区域设置可能会导致 B-tree 索引的膨胀或性能下降。在需要高性能的场景下，可以考虑使用 C 或 POSIX 区域设置，并在应用程序层面处理复杂的排序需求，或者使用 PostgreSQL 提供的特殊功能（如 ICU 排序规则，如果可用）。
    • 'C' 或 'POSIX' 区域设置提供最快的排序和字符处理性能，它们使用基于字符编码的简单字节值顺序进行比较，而不是复杂的语言规则。

3.5 TABLESPACE = tablespace_name

  • 作用： 指定新数据库的主表空间。所有在该数据库中创建但未指定特定表空间的表和索引将默认存储在这个表空间中。
  • 默认值： 如果未指定，默认使用 pg_default 表空间。
  • 如何指定： 使用 TABLESPACE = tablespace_name，其中 tablespace_name 是一个已存在的表空间的名称。
  • 示例：
    sql
    CREATE DATABASE archivedb TABLESPACE = slow_storage;
    这将创建一个名为 archivedb 的数据库，其所有对象默认存储在名为 slow_storage 的表空间中。这通常用于将不经常访问或对性能要求不高的数据库存储在较慢或容量更大的存储介质上。
  • 相关概念：
    • 表空间是 PostgreSQL 中用于将数据库对象（如表和索引）存储在文件系统中的特定位置的机制。通过创建和使用不同的表空间，可以将数据库的不同部分分布在不同的磁盘或存储系统上，从而优化 I/O 性能、管理存储容量或应对硬件限制。
    • 在创建数据库之前，指定的表空间必须已经存在。可以使用 CREATE TABLESPACE 命令来创建新的表空间。

3.6 CONNECTION LIMIT = limit

  • 作用： 限制可以同时连接到此数据库的并发连接数。
  • 默认值： 如果未指定，默认值为 -1，表示没有限制（受限于服务器的总连接数限制 max_connections）。
  • 如何指定： 使用 CONNECTION LIMIT = limit，其中 limit 是一个非负整数。
  • 示例：
    sql
    CREATE DATABASE appdb CONNECTION LIMIT = 100;
    这将创建一个名为 appdb 的数据库，最多允许100个并发连接。当达到这个限制时，新的连接尝试将被拒绝，直到现有连接关闭。
  • 用途： 这可以用来防止某个特定数据库耗尽服务器的所有可用连接，从而影响其他数据库的可用性。

3.7 IS_TEMPLATE = is_template

  • 作用： 将新创建的数据库标记为一个模板数据库。被标记为模板的数据库可以作为 CREATE DATABASE 命令的 TEMPLATE 选项的值来使用。
  • 默认值： 如果未指定，默认值为 false。
  • 如何指定： 使用 IS_TEMPLATE = true 或 IS_TEMPLATE = false。
  • 示例：
    sql
    CREATE DATABASE my_custom_template
    IS_TEMPLATE = true
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';
    这将创建一个名为 my_custom_template 的数据库，并将其标记为模板。然后，你可以基于这个自定义模板创建新的数据库：
    sql
    CREATE DATABASE new_app_db TEMPLATE = my_custom_template;
  • 重要事项： 当一个数据库被标记为 IS_TEMPLATE = true 时，任何用户都可以检查其内容并将其用作 TEMPLATE。要限制这种能力，可以使用 REVOKE CONNECT ON DATABASE dbname FROM public; 命令。默认情况下，只有超级用户和数据库所有者可以删除模板数据库。

4. 执行 CREATE DATABASE 命令的权限要求

要执行 CREATE DATABASE 命令，执行命令的用户必须拥有 CREATEDB 数据库角色属性。超级用户默认拥有此权限。

你可以通过以下 SQL 命令授予或撤销用户的 CREATEDB 权限：

“`sql
— 授予用户 user_name 创建数据库的权限
ALTER USER user_name CREATEDB;

— 撤销用户 user_name 创建数据库的权限
ALTER USER user_name NOCREATEDB;
“`

如果你尝试创建一个数据库但没有足够的权限，你将收到一个错误，例如 permission denied for database creation。

5. 示例汇总

下面是一些结合不同选项的 CREATE DATABASE 示例：

  • 创建一个简单的数据库：
    sql
    CREATE DATABASE myapp_db;
    (默认所有者为当前用户，基于 template1，继承其编码、区域设置和表空间)

  • 指定所有者和连接限制：
    sql
    CREATE DATABASE user_data_db OWNER = data_analyst CONNECTION LIMIT = 50;

  • 使用 template0 创建一个带有特定编码和区域设置的数据库：
    sql
    CREATE DATABASE spanish_app_db
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'es_ES.UTF-8'
    LC_CTYPE = 'es_ES.UTF-8';
    这是创建具有与 template1 不同区域设置或编码的数据库的标准且安全的方法。

  • 将数据库创建到特定的表空间：
    (假设 fast_ssd 表空间已存在)
    sql
    CREATE DATABASE performance_db TABLESPACE = fast_ssd;

  • 创建一个自定义模板数据库：
    sql
    CREATE DATABASE base_template_v1
    IS_TEMPLATE = true
    ENCODING = 'UTF8'
    LC_COLLATE = 'C' -- 使用 C 区域设置以获得最佳性能
    LC_CTYPE = 'C';
    然后可以在 base_template_v1 中添加一些常用的扩展、函数或表结构，以便将来基于它创建具有这些预设的新数据库。

  • 一个更复杂的例子，结合多个选项：
    sql
    CREATE DATABASE project_x_prod
    OWNER = project_x_admin
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = project_x_data_ts
    CONNECTION LIMIT = 200;

6. 关于模板数据库的更多细节

模板数据库是一个非常强大的功能，它允许你在创建新数据库时预设一些内容。

  • template1 的用途和风险： template1 是默认模板，它使得开箱即用。但如果在 template1 中创建了用户对象或安装了用户级别的扩展，这些对象将自动复制到所有基于 template1 创建的新数据库中。这可能不是你想要的，因为它会污染新数据库，使其不再是一个“干净”的状态。修改 template1 应该非常谨慎，并且只用于确实需要所有新数据库都拥有的全局对象（如某些广泛使用的扩展）。
  • template0 的优势： template0 的主要优势在于它的纯净性以及它允许你在创建时自由指定编码和区域设置，而无需担心与模板数据库的设置冲突。当需要创建具有非默认编码或区域设置的数据库时，template0 是唯一的正确选择。
  • 创建自定义模板的用途： 创建自定义模板（使用 IS_TEMPLATE = true）允许你建立一个标准的数据库基础，例如包含公司所有项目都需要的基本表、函数、扩展（如 pgcrypto、uuid-ossp 等）。然后，所有新项目数据库都可以基于这个自定义模板创建，确保了一致性和减少了重复设置的工作。在创建自定义模板后，你可以在其中执行 SQL 语句来添加需要的对象。

7. 数据库创建过程的底层机制（简化版）

当执行 CREATE DATABASE 命令时，PostgreSQL 服务器会执行以下主要步骤：

  1. 检查权限和名称冲突： 验证执行命令的用户是否有 CREATEDB 权限，并检查数据库名称是否已存在。
  2. 确定模板数据库： 根据 TEMPLATE 选项确定使用的模板数据库（默认为 template1）。
  3. 确定编码和区域设置： 根据 ENCODING、LC_COLLATE、LC_CTYPE 选项以及模板数据库或集群的默认设置确定新数据库的编码和区域设置。
  4. 在文件系统中创建目录： 在数据目录下为新数据库创建一个唯一的子目录。如果指定了 TABLESPACE，则会在对应表空间的物理位置创建链接或子目录。
  5. 复制模板数据库文件： 将模板数据库文件系统目录下的所有文件（除了某些特定文件）复制到新数据库的目录中。这是为什么新数据库会包含模板数据库中对象的原因。
  6. 在系统目录中记录信息： 在全局系统目录（如 pg_database 表）中插入新数据库的元数据，包括其名称、所有者、编码、区域设置、表空间等。
  7. 生成新的 OID： 为新数据库分配一个唯一的 OID (Object Identifier)。
  8. 完成创建： 标记新数据库为可用状态。

这个过程是原子性的，如果在任何阶段失败，整个创建操作将回滚。

8. 可能遇到的问题及故障排除

在创建数据库时，可能会遇到各种错误。一些常见的问题包括：

  • 权限不足 (permission denied)： 确保执行命令的用户具有 CREATEDB 权限。
  • 数据库已存在 (database "dbname" already exists)： 你不能创建同名数据库。如果需要同名，必须先删除现有数据库（DROP DATABASE dbname;）。
  • 无效的编码或区域设置 (invalid encoding name "...", invalid locale name "...")： 确保指定的编码和区域设置是你的操作系统和 PostgreSQL 支持的。可以通过查询 pg_encoding_names 和检查操作系统文档来获取支持的名称。
  • 模板数据库不存在 (template database "..." does not exist)： 确保你指定的模板数据库名称是正确的且已存在。
  • 表空间不存在或没有权限 (tablespace "..." does not exist, could not create directory "...")： 确保指定的表空间已存在，并且 PostgreSQL 服务器进程有权限在该表空间的物理位置创建目录。
  • 目录创建失败 (could not create directory "...")： 通常与表空间相关的物理目录权限问题或磁盘空间不足有关。
  • 基于 template1 尝试更改区域设置/编码导致的错误： 正如前面提到的，如果尝试基于 template1 创建具有不兼容编码或区域设置的数据库，可能会收到错误或警告。解决方法是使用 template0 作为模板。

当遇到错误时，仔细阅读错误消息是解决问题的第一步。错误消息通常会指示问题的类型和发生在哪里。检查 PostgreSQL 服务器的日志文件 (postgresql.log) 也能提供更详细的上下文信息。

9. 与 CREATE DATABASE 相关的配置参数

有一些 postgresql.conf 参数可以影响 CREATE DATABASE 命令的默认行为：

  • default_tablespace: 如果 CREATE DATABASE 命令没有指定 TABLESPACE 选项，新数据库将使用此参数指定的表空间（默认为 pg_default）。
  • lc_messages, lc_monetary, lc_numeric, lc_time: 这些区域设置参数不会直接影响数据的存储和排序（由 LC_COLLATE 和 LC_CTYPE 控制），但会影响服务器向客户端发送的错误消息、货币格式、数字格式、日期/时间格式等。它们通常继承自操作系统的环境或集群初始化时的设置。
  • max_connections: 这是整个 PostgreSQL 服务器实例允许的最大并发连接数。CONNECTION LIMIT 是对单个数据库的限制，它不能超过 max_connections。

10. 删除数据库：DROP DATABASE

与 CREATE DATABASE 相对应的是 DROP DATABASE 命令，用于永久删除一个数据库及其所有内容。

sql
DROP DATABASE dbname;

  • 重要警告： DROP DATABASE 是一个破坏性操作，一旦执行，数据库中的所有数据将丢失且无法恢复。
  • 在删除数据库之前，必须确保没有活跃的连接到该数据库。如果有连接，DROP DATABASE 命令会失败并报错。
  • 可以使用 DROP DATABASE dbname WITH (FORCE); (PostgreSQL 13+) 或 DROP DATABASE dbname CONCURRENTLY; (PostgreSQL 14+) 来尝试强制断开现有连接并删除数据库，但这应该谨慎使用，因为它会突然终止连接。在旧版本中，通常需要手动查找并终止连接（使用 pg_terminate_backend() 函数）。

11. 总结

CREATE DATABASE 是 PostgreSQL 中用于创建新数据库的基石命令。掌握其各种选项和参数对于有效地管理和配置你的 PostgreSQL 环境至关重要。从简单的数据库创建到指定所有者、选择模板、设置编码和区域规则、分配表空间以及限制连接数，每一个选项都提供了定制数据库行为以满足特定需求的机会。

理解 template1 和 template0 的区别、编码和区域设置（ENCODING, LC_COLLATE, LC_CTYPE）的不可变性以及它们对排序和字符串处理的影响，是在数据库生命周期早期做出正确决定的关键。同时，了解表空间如何帮助管理存储和性能，以及连接限制如何帮助控制资源分配，都能帮助你构建更健壮和可管理的数据库系统。

虽然基本的 CREATE DATABASE dbname; 命令足以启动一个数据库，但深入了解 WITH 子句中的各种选项，能够让你根据应用程序的需求、数据特性和性能考量，更精确地初始化数据库，为后续的开发和运维打下坚实的基础。记住在创建前仔细规划数据库的编码、区域设置和表空间，因为这些设置在数据库创建后很难或不可能修改。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部