PostgreSQL

Appearance

PostgreSQL 数据库集群创建指南 ​

概述 ​

在使用 PostgreSQL 之前，必须在磁盘上初始化一个数据库存储区域，我们称之为数据库集群。数据库集群是由单个 PostgreSQL 服务器实例管理的数据库集合，它解决了数据存储和组织管理的核心问题。

INFO

术语说明 SQL 标准使用术语"目录集群"，但 PostgreSQL 社区通常称之为"数据库集群"。

数据库集群的核心概念 ​

什么是数据库集群 ​

数据库集群本质上是一个文件系统目录，包含：

  • postgres 数据库：默认数据库，供实用程序、用户和第三方应用程序使用
  • template1 数据库：用作创建新数据库的模板
  • template0 数据库：原始模板，不应修改

业务场景应用 ​

在实际业务中，数据库集群的设计解决了以下问题：

  1. 多租户应用：每个租户可以拥有独立的数据库，但共享同一个服务器实例
  2. 开发环境隔离：开发、测试、生产环境可以使用不同的数据库
  3. 业务模块分离：用户管理、订单系统、库存管理可以使用不同的数据库

数据目录选择与规划 ​

推荐的目录位置 ​

环境类型推荐路径说明
生产环境/var/lib/pgsql/data标准生产环境路径
开发环境/usr/local/pgsql/data开发测试环境
自定义环境/data/postgresql/独立磁盘挂载点

数据目录的选择对性能和可维护性有重要影响，建议选择独立的磁盘分区。

初始化数据库集群 ​

方法一：使用 initdb 命令 ​

基本语法 ​

bash
# 基本初始化命令
initdb -D /usr/local/pgsql/data

# 使用环境变量方式
export PGDATA=/usr/local/pgsql/data
initdb

完整的初始化示例 ​

问题场景：为新的 PostgreSQL 服务器创建生产环境的数据库集群

解决方案：

bash
# 1. 创建必要的目录结构（需要 root 权限）
sudo mkdir -p /usr/local/pgsql
sudo chown postgres:postgres /usr/local/pgsql

# 2. 切换到 postgres 用户
sudo su - postgres

# 3. 初始化数据库集群
initdb -D /usr/local/pgsql/data \
       --encoding=UTF8 \
       --locale=zh_CN.UTF-8 \
       --data-checksums \
       -W

参数说明：

  • -D：指定数据目录位置
  • --encoding=UTF8：设置字符编码
  • --locale=zh_CN.UTF-8：设置区域设置
  • --data-checksums：启用数据校验和
  • -W：提示设置超级用户密码

预期输出：

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".

Data page checksums are enabled.

creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /usr/local/pgsql/data -l logfile start

方法二：使用 pg_ctl 程序 ​

bash
# 使用 pg_ctl 初始化
pg_ctl -D /usr/local/pgsql/data initdb

TIP

建议如果计划使用 pg_ctl 来启动和停止服务器，建议也使用它来初始化，保持工具的一致性。

权限和安全配置 ​

文件系统权限 ​

初始化完成后，PostgreSQL 会自动设置适当的文件权限：

访问类型目录权限文件权限适用场景
仅所有者07000600高安全性环境
组读取07500640允许备份用户访问
bash
# 检查权限设置
ls -la /usr/local/pgsql/data/
# drwx------ 19 postgres postgres 4096 Jun  5 10:30 .

身份验证配置 ​

默认配置的安全风险：默认情况下，PostgreSQL 允许任何本地用户连接并成为超级用户，这在生产环境中是不安全的。

安全加固方案：

bash
# 1. 初始化时设置超级用户密码
initdb -D /usr/local/pgsql/data -W -A scram-sha-256

# 2. 或在初始化后修改认证方法
# 编辑 pg_hba.conf 文件
vim /usr/local/pgsql/data/pg_hba.conf

pg_hba.conf 安全配置示例：

sql
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 本地连接使用密码认证
local   all             all                                     scram-sha-256

# IPv4 本地连接
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 本地连接
host    all             all             ::1/128                 scram-sha-256

# 生产环境远程连接（限制特定 IP）
host    all             all             192.168.1.0/24          scram-sha-256

区域设置和字符编码 ​

区域设置的重要性 ​

区域设置影响：

  • 排序规则：影响 ORDER BY 和索引的行为
  • 字符分类：影响大小写转换和模式匹配
  • 数字和日期格式：影响数据的显示格式

WARNING

注意区域设置一旦确定，就无法在不重建集群的情况下更改。因此，初始选择非常重要。

字符编码选择 ​

bash
# 查看系统支持的编码
initdb --help | grep -A 20 "encoding"

# 常用编码选择
initdb -D /usr/local/pgsql/data --encoding=UTF8    # 通用选择
initdb -D /usr/local/pgsql/data --encoding=SQL_ASCII  # 仅英文环境

高级存储配置 ​

二级文件系统使用 ​

业务场景：生产环境中通常将数据库数据存储在独立的磁盘分区上，以提高性能和可靠性。

最佳实践：

bash
# 1. 挂载独立存储设备
sudo mount /dev/sdb1 /data

# 2. 在挂载点下创建专用目录
sudo mkdir /data/postgresql
sudo chown postgres:postgres /data/postgresql

# 3. 在专用目录中初始化集群
sudo su - postgres
initdb -D /data/postgresql/data

TIP

最佳实践不要直接使用挂载点作为数据目录，而应在挂载点下创建子目录。这样可以避免权限问题，并确保在存储设备离线时能够优雅地处理失败。

NFS 存储配置 ​

使用场景：在云环境或共享存储架构中，可能需要将 PostgreSQL 数据存储在 NFS 文件系统上。

必要配置：

bash
# 1. 使用 hard 选项挂载（必须）
mount -t nfs -o hard,intr nfs-server:/export/pgsql /data/pgsql

# 2. 验证挂载选项
mount | grep pgsql
# nfs-server:/export/pgsql on /data/pgsql type nfs (rw,hard,intr)

NFS 服务器端配置：

bash
# /etc/exports 配置示例
/export/pgsql    192.168.1.0/24(rw,sync,no_subtree_check)
选项说明重要性
hard网络问题时挂起而非失败必须
sync同步写入（服务器端）强烈推荐
intr允许中断挂起的调用推荐

常见问题和故障排除 ​

权限问题 ​

问题现象：

bash
initdb: could not create directory "/usr/local/pgsql": Permission denied

解决方案：

bash
# 1. 检查父目录权限
ls -ld /usr/local/

# 2. 使用 root 权限创建目录
sudo mkdir -p /usr/local/pgsql
sudo chown postgres:postgres /usr/local/pgsql

# 3. 切换到 postgres 用户重新初始化
sudo su - postgres
initdb -D /usr/local/pgsql/data

目录已存在 ​

问题现象：

bash
initdb: directory "/usr/local/pgsql/data" exists but is not empty

解决方案：

bash
# 1. 检查目录内容
ls -la /usr/local/pgsql/data/

# 2. 如果确认可以删除
rm -rf /usr/local/pgsql/data/*

# 3. 或选择新的目录
initdb -D /usr/local/pgsql/data-new

编码不匹配 ​

问题现象：

bash
initdb: encoding mismatch

解决方案：

bash
# 1. 检查系统区域设置
locale

# 2. 明确指定编码和区域
initdb -D /usr/local/pgsql/data \
       --encoding=UTF8 \
       --lc-collate=C \
       --lc-ctype=C

初始化后的验证 ​

验证集群状态 ​

bash
# 1. 检查数据目录结构
ls -la /usr/local/pgsql/data/
# 应该看到 postgresql.conf, pg_hba.conf 等配置文件

# 2. 启动数据库服务器
pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start

# 3. 连接到数据库
psql -h localhost -U postgres

检查默认数据库 ​

sql
-- 连接后查看数据库列表
\l

-- 应该看到以下数据库：
-- postgres  | postgres | UTF8     | ...
-- template0 | postgres | UTF8     | ...
-- template1 | postgres | UTF8     | ...

总结 ​

PostgreSQL 数据库集群的创建是数据库管理的第一步，正确的初始化配置对后续的性能、安全性和可维护性都有重要影响。关键要点包括：

  1. 合理选择数据目录位置，考虑性能和可维护性
  2. 正确设置权限和安全配置，特别是身份验证方法
  3. 谨慎选择区域设置和字符编码，避免后期重建集群
  4. 对于生产环境，考虑使用独立存储和适当的文件系统

通过遵循这些最佳实践，可以为 PostgreSQL 数据库建立一个稳定、安全、高性能的基础环境。