PostgreSQL

PostgreSQL 是功能强大的开源关系型数据库管理系统，以其可靠性、数据完整性和丰富的功能集著称。本文将介绍在 EL 系发行版上安装 PostgreSQL、初始化数据库、配置认证、用户与数据库管理、psql 使用、备份恢复以及远程访问。

各发行版中的版本

版本数据实时来自 pkgseek.com

安装 PostgreSQL

使用系统默认仓库（EL 9）

EL 9 AppStream 通过模块化（modularity）管理 PostgreSQL 版本，常见默认流为 13，也可以启用 15 / 16 / 18 等其他流（以 dnf module list postgresql 的输出为准）：

查看可用的 PostgreSQL 版本（仅 EL 9）

sudo dnf module list postgresql

安装 PostgreSQL 服务端（EL 9，默认模块流）

sudo dnf install -y postgresql-server postgresql

EL 10 用户

EL 10 不再分发 modular 内容，dnf module 命令已弃用（无可用模块流）。EL 10 系统仓库直接提供 PostgreSQL 16，运行 sudo dnf install postgresql-server postgresql 即可安装；需要更新版本时请使用下方的官方仓库。

使用 PostgreSQL 官方仓库（推荐，EL 9 & 10 通用）

官方仓库提供最新稳定版本（当前为 PostgreSQL 18），且 URL 自动适配 EL 版本：

安装 PostgreSQL 官方仓库

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

EL 9：禁用系统内置模块并安装 PostgreSQL 18

# 仅 EL 9 需要禁用内置模块：
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql18-server postgresql18

EL 10：直接安装 PostgreSQL 18（无需 module disable）

sudo dnf install -y postgresql18-server postgresql18

初始化数据库（initdb）

安装后必须先初始化数据库集群才能启动服务。

系统仓库版本初始化

初始化数据库（系统仓库版本）

sudo postgresql-setup --initdb

官方仓库版本初始化

初始化数据库（官方仓库版本）

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

启动服务

启动并启用 PostgreSQL（系统仓库版本）

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

启动并启用 PostgreSQL（官方仓库版本）

sudo systemctl start postgresql-18
sudo systemctl enable postgresql-18
sudo systemctl status postgresql-18

数据目录说明

版本	数据目录	配置文件目录
系统仓库版	/var/lib/pgsql/data/	/var/lib/pgsql/data/
官方仓库版 (18)	/var/lib/pgsql/18/data/	/var/lib/pgsql/18/data/

配置 pg_hba.conf

pg_hba.conf（Host-Based Authentication）控制客户端的连接认证方式，是 PostgreSQL 安全配置的核心。

查看 pg_hba.conf 位置

sudo -u postgres psql -c "SHOW hba_file;"

认证方式说明

方式	说明
peer	使用操作系统用户名匹配数据库用户（仅本地 Unix 连接）
ident	类似 peer，通过 ident 服务器验证（TCP 连接）
md5	使用 MD5 加密的密码验证
scram-sha-256	使用 SCRAM-SHA-256 加密验证（推荐）
trust	无需密码直接信任（仅限测试环境）
reject	拒绝连接

编辑 pg_hba.conf

编辑 pg_hba.conf（系统仓库版）

sudo vi /var/lib/pgsql/data/pg_hba.conf

典型的配置示例：

pg_hba.conf 配置示例

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 本地 Unix 套接字连接
local   all             postgres                                peer
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

# 允许特定网段远程连接
host    all             all             192.168.1.0/24          scram-sha-256

# 允许特定用户连接特定数据库
host    mydb            myuser          10.0.0.0/8              scram-sha-256

修改后重新加载配置：

重新加载 pg_hba.conf

sudo systemctl reload postgresql

创建用户与数据库

使用命令行工具

创建数据库用户

sudo -u postgres createuser --interactive --pwprompt myuser

创建数据库

sudo -u postgres createdb --owner=myuser mydb

使用 SQL 语句

进入 PostgreSQL 控制台

sudo -u postgres psql

使用 SQL 创建用户和数据库

-- 创建用户
CREATE USER myuser WITH PASSWORD 'StrongPassword123!';

-- 创建数据库并指定所有者
CREATE DATABASE mydb OWNER myuser;

-- 设置字符编码
CREATE DATABASE mydb_utf8
    OWNER myuser
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8'
    TEMPLATE template0;

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO myuser;

-- 授予 schema 权限
\c mydb
GRANT USAGE ON SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

用户管理

用户管理操作

-- 查看所有用户
\du

-- 修改用户密码
ALTER USER myuser WITH PASSWORD 'NewPassword456!';

-- 赋予超级用户权限（谨慎使用）
ALTER USER myuser WITH SUPERUSER;

-- 撤销超级用户权限
ALTER USER myuser WITH NOSUPERUSER;

-- 删除用户（需先撤销其拥有的对象）
DROP OWNED BY myuser;
DROP USER myuser;

psql 基础操作

psql 是 PostgreSQL 的交互式终端工具。

连接数据库

不同方式连接数据库

# 以 postgres 系统用户登录
sudo -u postgres psql

# 指定用户和数据库
psql -U myuser -d mydb

# 指定主机连接
psql -U myuser -d mydb -h 127.0.0.1 -p 5432

常用 psql 元命令

psql 元命令速查

\l          -- 列出所有数据库
\c dbname   -- 切换数据库
\dt         -- 列出当前数据库的所有表
\d tablename -- 查看表结构
\du         -- 列出所有用户/角色
\dn         -- 列出所有 schema
\df         -- 列出所有函数
\di         -- 列出所有索引
\dx         -- 列出已安装的扩展
\timing     -- 开启/关闭查询计时
\x          -- 开启/关闭扩展显示模式
\q          -- 退出 psql
\?          -- 显示帮助
\h SQL命令  -- 显示 SQL 命令帮助

基本 SQL 操作

常用 SQL 操作示例

-- 创建表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO articles (title, content, author) VALUES
    ('PostgreSQL 入门', '这是一篇入门教程...', '张三'),
    ('数据库优化', '优化技巧总结...', '李四');

-- 查询数据
SELECT * FROM articles;
SELECT title, author FROM articles WHERE author = '张三';

-- 更新数据
UPDATE articles SET title = 'PostgreSQL 进阶' WHERE id = 1;

-- 删除数据
DELETE FROM articles WHERE id = 2;

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('articles'));

pg_dump 备份与恢复

备份单个数据库

备份数据库（SQL 格式）

pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d_%H%M%S).sql

备份数据库（自定义压缩格式，推荐）

pg_dump -U postgres -Fc mydb > /backup/mydb_$(date +%Y%m%d_%H%M%S).dump

备份所有数据库

备份全部数据库

pg_dumpall -U postgres > /backup/all_databases_$(date +%Y%m%d_%H%M%S).sql

只备份表结构

只备份表结构（不含数据）

pg_dump -U postgres --schema-only mydb > /backup/mydb_schema.sql

只备份数据

只备份数据（不含结构）

pg_dump -U postgres --data-only mydb > /backup/mydb_data.sql

恢复数据库

从 SQL 文件恢复

# 先创建目标数据库
sudo -u postgres createdb mydb_restored

# 恢复
psql -U postgres mydb_restored < /backup/mydb_20260324.sql

从自定义格式恢复

pg_restore -U postgres -d mydb_restored /backup/mydb_20260324.dump

恢复时清除已有对象

pg_restore -U postgres -d mydb --clean --if-exists /backup/mydb_20260324.dump

自动备份脚本

创建 PostgreSQL 自动备份脚本

sudo tee /usr/local/bin/pg-backup.sh << 'SCRIPT'
#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# 备份所有数据库（自定义压缩格式）
for DB in $(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';"); do
    sudo -u postgres pg_dump -Fc "$DB" > "${BACKUP_DIR}/${DB}_${DATE}.dump"
done

# 备份全局对象（角色、表空间）
sudo -u postgres pg_dumpall --globals-only > "${BACKUP_DIR}/globals_${DATE}.sql"

# 清理旧备份
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete
find "$BACKUP_DIR" -name "*.sql" -mtime +${RETENTION_DAYS} -delete

echo "PostgreSQL backup completed at ${DATE}"
SCRIPT

sudo chmod +x /usr/local/bin/pg-backup.sh

添加定时备份任务

echo "0 3 * * * root /usr/local/bin/pg-backup.sh" | sudo tee /etc/cron.d/pg-backup

远程访问配置

修改 postgresql.conf

修改 PostgreSQL 监听地址：

配置监听地址（系统仓库版）

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /var/lib/pgsql/data/postgresql.conf

如果只需监听特定 IP：

监听特定 IP

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = 'localhost,192.168.1.10'/" /var/lib/pgsql/data/postgresql.conf

修改 pg_hba.conf

在 pg_hba.conf 中添加远程访问规则：

添加远程访问规则

echo "host    all    all    192.168.1.0/24    scram-sha-256" | sudo tee -a /var/lib/pgsql/data/pg_hba.conf

重启服务并开放防火墙

重启 PostgreSQL 并开放端口

sudo systemctl restart postgresql
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

验证监听端口

确认 PostgreSQL 监听状态

ss -tlnp | grep 5432

测试远程连接

从远程客户端连接

psql -U myuser -d mydb -h 服务器IP地址 -p 5432

安全建议

• 在 pg_hba.conf 中精确指定允许的 IP 范围，避免使用 0.0.0.0/0
• 使用 scram-sha-256 认证方式替代 md5
• 考虑通过 SSH 隧道连接，避免直接暴露 5432 端口
• 启用 SSL 连接加密

通过 SSH 隧道连接 PostgreSQL

# 在本地建立隧道
ssh -L 5432:127.0.0.1:5432 user@服务器IP地址

# 通过隧道连接
psql -U myuser -d mydb -h 127.0.0.1

常用运维命令速查

PostgreSQL 日常运维命令

# 启动 / 停止 / 重启 / 重载
sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql

# 查看版本
psql --version

# 查看数据库大小
sudo -u postgres psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"

# 查看活跃连接
sudo -u postgres psql -c "SELECT pid, usename, datname, client_addr, state, query FROM pg_stat_activity WHERE state = 'active';"

# 查看配置文件位置
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

# 查看当前连接数
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

延伸阅读

• MySQL / MariaDB — 另一个流行的数据库
• 防火墙 — 配置数据库访问规则

EL 10 注意事项

EL 10 与 EL 9 的系统仓库版本管理方式不同：

版本	EL 9 系统仓库	EL 10 系统仓库
PostgreSQL	13（常见默认流），可切换 15 / 16 / 18 等模块流	16（无模块化，直接安装）

使用 PostgreSQL 官方仓库（推荐）时，版本选择更加灵活，当前最新稳定版为 PostgreSQL 18：

在 EL 10 上安装 PostgreSQL 官方仓库

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

官方仓库会自动根据 %{rhel} 变量适配 EL 10。