PostgreSQL 17 管理实战：单机与集群、用户权限、数据库与表管理、远程访问和故障排查

PostgreSQL 是一款功能完整的开源关系型数据库。本文以 PostgreSQL 17.x 为基准，梳理开发和日常运维最常用的管理工作，并给出单机与主备集群的实际操作路径。

适用前提

• 以下以 PostgreSQL 17.x 社区版、Linux 环境和 psql 客户端为准；数据库默认端口以 5432 为例，生产环境以实际配置为准。
• 所有 IP、主机名、路径、库名、角色名和口令均为示例。请先在测试环境验证，再用于生产。
• 带 DROP、TRUNCATE、REVOKE、终止会话、切换主库、停止/重启服务的命令可能删除数据或中断业务。
• 数据库不要直接暴露公网；远程访问优先内网/VPN、最小授权、TLS 和来源 IP 白名单。
• 本文是基于 PostgreSQL 官方文档整理的操作指南，不宣称所有命令都已在你的生产环境实测。

一、PostgreSQL 17 的版本适用范围

PostgreSQL 的大版本为 14、15、16、17、18，小版本为 17.1、17.2 等。通常同一个大版本下的小版本沿用同一套运维接口；跨大版本则必须检查变更和兼容性。

截至 2026 年 10 月 22 日，官方版本支持策略如下：

PostgreSQL 版本	官方维护状态	本文操作是否可参考	必须注意
18	支持中	大部分可参考	核对 18 的安装包、参数、系统视图、新特性
17	支持中	本文基准版本	建议安装 17 最新安全补丁版本
16	支持中	多数常规管理操作可参考	不要直接照抄 17 新增的选项与视图
15	支持中	多数常规管理操作可参考	默认 public Schema 的 CREATE 权限策略与更早版本存在差异
14	支持中，2026-11-12 结束维护	核心操作可参考	应规划升级；Locale 提供者等元数据字段可能不同
13	2025-11-13 已停止维护	部分 SQL/命令相似，不建议新部署	安全补丁已停止；不能以兼容为由继续长期运行
12 及更早	已停止维护	仅作迁移参考	旧主备机制、参数名和系统目录差异更大

跨版本相对稳定：psql、pg_ctl 基本用法、CREATE/ALTER/DROP ROLE、GRANT/REVOKE、CREATE/ALTER/DROP DATABASE、常规表 DDL、pg_stat_activity、pg_locks、pg_hba.conf 思路。

不可直接照搬：发行版服务名称、安装路径、复制/归档参数、pg_stat_* 视图列、Collation/Locale 元数据、高可用组件、部分 SQL 新特性。尤其 PostgreSQL 12 起主备恢复配置改为 standby.signal 等机制，不应照抄更早版本的 recovery.conf 教程。

注意：17.x 到 17.y 属于小版本更新；16 到 17 是大版本升级，需要使用 pg_upgrade 或逻辑迁移等正确流程，不能仅更换二进制就直接启动旧数据目录。

官方依据：版本支持与生命周期 · PostgreSQL 17 文档 · 升级说明。

二、安装 PostgreSQL 17

2.1 先确认 Linux、硬件和环境

cat /etc/os-release
uname -m
hostname -f
free -h
df -h
timedatectl

建议检查磁盘容量、数据目录独立挂载、主备节点之间的网络、时间同步、备份存储、监控和安全组。

重要：不要因为已有旧版本 PostgreSQL 就直接覆盖它的安装/数据目录。特别是 CentOS 7 已停止维护，不应将它当作 PostgreSQL 17 的推荐生产操作系统；应使用官方仓库支持的 Debian/Ubuntu、RHEL/Rocky/AlmaLinux 等适配版本。

2.2 Ubuntu / Debian：安装 PostgreSQL 17

发行版默认软件源里的 postgresql 包不一定是 17。先按官方页面配置 PGDG APT 仓库，再指定版本安装：

# 以下在已按 PostgreSQL 官方指引配置 PGDG APT 仓库后执行
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17

# Debian/Ubuntu 的 postgresql-common 管理工具
pg_lsclusters
psql --version

PGDG APT 仓库配置参考：官方 Ubuntu 安装页 / 官方 Debian 安装页。

多数 Debian/Ubuntu 的包安装会创建名为 main 的数据库集群；但如果已有配置、修改过安装策略，实际集群名和端口应以 pg_lsclusters 为准。

2.3 RHEL / Rocky Linux / AlmaLinux：安装 PostgreSQL 17

同样先按 官方 Red Hat 家族安装页 配置与系统版本、CPU 架构匹配的 PGDG RPM 仓库。之后使用版本化包名：

sudo dnf install -y postgresql17 postgresql17-server

# 仅在首次安装、尚未初始化数据目录时执行
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

sudo systemctl enable --now postgresql-17
sudo systemctl status postgresql-17

initdb 不是日常启动命令，只能对尚未初始化的空数据目录执行；已有数据目录千万不要再次初始化。

2.4 查询安装版本、监听端口与配置位置

psql --version
pg_isready -h 127.0.0.1 -p 5432

# 常见本机管理员连接方式（以实际系统认证配置为准）
sudo -u postgres psql -d postgres

SELECT version();
SHOW server_version;
SHOW server_version_num;
SHOW port;
SHOW data_directory;
SHOW config_file;
SHOW hba_file;
SHOW listen_addresses;

避免根据教程推测配置目录：Debian/Ubuntu 常将配置放在 /etc/postgresql/17/<cluster>/，RPM PGDG 常见在 /var/lib/pgsql/17/data/，源代码/独立实例可能完全不同。实际以 SHOW config_file、SHOW data_directory 为准。

三、启动、停止、重启：单机与集群

PostgreSQL 的“数据库集群（database cluster）”在某些官方文档中是指由一个实例/数据目录管理的一组数据库，不代表多节点高可用集群。本文把单个服务器称为单机实例，把 Primary + Standby 称为主备集群。

3.1 单机：Debian / Ubuntu 安装包

# 假设版本 17、集群名 main
pg_lsclusters

sudo pg_ctlcluster 17 main start
sudo pg_ctlcluster 17 main status
sudo pg_ctlcluster 17 main stop
sudo pg_ctlcluster 17 main restart
sudo pg_ctlcluster 17 main reload

# 或查看具体实例的 systemd 单元
sudo systemctl status postgresql@17-main

不要简单以 systemctl start postgresql 当作精准操作：在多版本/多集群主机上，聚合服务与具体实例的对应关系可能不同。

3.2 单机：RPM PGDG 安装包

sudo systemctl start postgresql-17
sudo systemctl status postgresql-17
sudo systemctl stop postgresql-17
sudo systemctl restart postgresql-17
sudo systemctl reload postgresql-17
sudo systemctl enable postgresql-17

停止或重启前，应先确认应用连接、业务写入、备份任务和维护窗口。

3.3 独立管理的实例：pg_ctl

如果是由 initdb 创建、且不是正在被 systemd/pg_ctlcluster/Patroni 等管理的独立数据目录，可以作为 OS 的 postgres 用户执行：

# PGDATA 是示例路径，必须替换为当前实例的数据目录
export PGDATA=/srv/postgresql/17/data

pg_ctl -D "$PGDATA" status
pg_ctl -D "$PGDATA" start -l /srv/postgresql/17/server.log
pg_ctl -D "$PGDATA" stop -m fast
pg_ctl -D "$PGDATA" restart -m fast
pg_ctl -D "$PGDATA" reload

停止模式	含义	建议
smart	等现有连接正常退出	适合可以等到会话结束的场景
fast	终止正在进行的事务并断开连接，正常关闭	常见维护选择，但会影响业务
immediate	类似崩溃式终止，重启需恢复	只在明确紧急故障策略下使用

参数生效差异：例如 pg_hba.conf 规则、部分日志设置可 reload；listen_addresses、shared_buffers、部分复制参数须 restart。以 pg_settings.pending_restart 判断是否待重启：

SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

3.4 主备集群：部署形态和启停边界

PostgreSQL 17 提供物理流复制（Streaming Replication），常见结构：

应用 ──> 当前 Primary（可读写） ─── WAL 流复制 ──> Standby（通常只读）
                          │
                          └── 备份 / 归档 / 监控

若需要自动故障转移：
Patroni / DCS（如 etcd）+ 代理或服务发现 + 正确的仲裁和旧主隔离

主备的“启动/停止/重启”应理解为对每一个具体节点操作，不等同于像 openGauss OM 那样执行一个通用的 gs_om -t restart。如果集群交给 Patroni 管理，应通过 Patroni/其运维工具完成计划内切换和重启，不要绕过它直接 systemctl restart postgresql。

原生流复制 ≠ 自动 HA：两节点复制本身没有分布式仲裁、自动切主或业务入口自动切换能力。手动提升备库前必须确认旧主已经隔离或停止，否则存在双主/脑裂和数据分叉风险。

3.5 实战示例：配置一主一备物理流复制

下面仅以新建的独立实例为例，避免误操作系统安装包自动创建的数据目录：

节点	地址	角色	数据目录
db01	10.10.10.11	Primary	/srv/postgresql/17/data
db02	10.10.10.12	Standby	/srv/postgresql/17/data

前提：两台服务器都安装 PostgreSQL 17 的相同主版本二进制，数据库系统用户均为 postgres；以下 initdb、pg_ctl、pg_basebackup 均使用 17 的版本化二进制，并确保该独立目录不受已有包服务管理。数据目录必须属于 postgres，权限符合 PostgreSQL 要求。

步骤 A：在主节点创建全新实例

# root 建目录并交给 postgres 用户，演示用
sudo install -d -o postgres -g postgres -m 700 /srv/postgresql/17/data
sudo install -d -o postgres -g postgres -m 700 /srv/postgresql/17/log
sudo -iu postgres

# 确认运行的是 PostgreSQL 17 对应的 initdb
initdb --version

# 仅首次初始化空目录
initdb -D /srv/postgresql/17/data \
  --auth-local=peer --auth-host=scram-sha-256

如果 initdb 不在 PATH，可使用安装目录下的绝对路径；Debian/Ubuntu 常为 /usr/lib/postgresql/17/bin/，PGDG RPM 常为 /usr/pgsql-17/bin/。

编辑主节点的 /srv/postgresql/17/data/postgresql.conf：

listen_addresses = 'localhost,10.10.10.11'
port = 5432
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# postgres 用户，在 db01 启动
pg_ctl -D /srv/postgresql/17/data \
  -l /srv/postgresql/17/log/server.log start

步骤 B：创建复制账号并配置白名单

在主节点通过本机 psql 连接：

CREATE ROLE repl_user WITH LOGIN REPLICATION;
-- 使用 psql 的交互式命令设置强密码，避免明文进入 SQL 历史：
\password repl_user

在主节点的 pg_hba.conf 加入：

# 仅允许 db02 作为复制客户端
host replication repl_user 10.10.10.12/32 scram-sha-256

pg_ctl -D /srv/postgresql/17/data reload

确保主节点防火墙只对备节点开放所需 TCP 5432（或你的实际业务端口）。

步骤 C：从备节点拉取基础备份并自动生成待机配置

在备节点，先确保实例没有运行，并准备一个完全空且权限正确的独立数据目录。不要对正在使用的数据目录执行清空、覆盖或重新初始化操作。

# db02，postgres 用户；复制连接时将交互式要求输入密码
pg_basebackup \
  -h 10.10.10.11 -p 5432 -U repl_user \
  -D /srv/postgresql/17/data \
  -Fp -Xs -P -R -W

解释：

• -D：目标空目录；
• -Fp：普通文件格式；
• -Xs：在备份期间通过另一复制连接传输 WAL；
• -P：显示进度；
• -R：创建 standby.signal，并写入 primary_conninfo 等恢复/复制设置。

注意：-W 只是在执行备份时提示输入密码，不等于保证备库后续 WAL Receiver 能自动认证。推荐为 postgres 系统用户配置权限为 0600 的安全 ~/.pgpass，匹配主库地址、端口、复制连接、账号，再启动备库；不要把口令嵌入仓库或脚本。还应验证备库能够在重启后重新连接到主库。

# db02，postgres 用户
pg_ctl -D /srv/postgresql/17/data \
  -l /srv/postgresql/17/log/server.log start

步骤 D：验证主备角色与流复制

在主节点：

SELECT pg_is_in_recovery();  -- 预期 false：主节点

SELECT application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

在备节点：

SELECT pg_is_in_recovery();  -- 预期 true：备节点

SELECT status, sender_host, received_lsn, latest_end_lsn
FROM pg_stat_wal_receiver;

SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

默认流复制为异步，备节点短时间落后属于可能发生的情况；业务若要求更严格的数据一致性，需要研究同步复制、故障转移、复制槽与 WAL 归档的完整方案。

必须关注 WAL 堆积：复制槽和 wal_keep_size 有助于短期断线恢复，但断线过久可能造成 WAL 累积撑满磁盘；部署高可用也不能取代定期备份、归档与恢复演练。

3.6 主备启停与人工切换规则

• 重启 Standby：在复制正常时，可按既定维护方案停止/启动备节点，恢复后验证 pg_stat_wal_receiver 和复制延迟。
• 重启 Primary：可能中断写入，备节点也可能暂时失去复制源；确认维护窗口、重连机制或使用高可用方案中的计划切换流程。
• 人工提升备库：确认旧主彻底隔离后，独立实例可使用 pg_ctl -D <standby-PGDATA> promote；提升前应评估尚未复制的事务。
• 原主回归：不能简单地把旧主重新启动并让两个节点都对外写入，应使用 pg_rewind（满足前提时）或重新基础备份，按正确的新拓扑加入。

官方参考：高可用与复制 · Warm/Hot Standby · pg_basebackup · pg_ctl。

四、用户管理：查看、新增、重置、禁用、删除

PostgreSQL 的用户本质上是具有 LOGIN 能力的 Role；CREATE USER 默认包含 LOGIN，而 CREATE ROLE 默认不包含。Role 是整个 PostgreSQL 实例级别的对象，并不是每个数据库各一份用户。

4.1 查询用户与角色

-- psql 元命令
\du
\du+

-- 查询登录、建库、建角色、复制及口令过期属性
SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole,
       rolreplication, rolconnlimit, rolvaliduntil
FROM pg_roles
ORDER BY rolname;

不要为了“列出密码”而查询敏感内部表；pg_roles 不暴露可直接使用的密码。

4.2 创建用户与安全设置密码

-- 在具备 CREATEROLE 权限的管理员会话执行
CREATE USER app_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE USER report_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 在 psql 中交互设置密码：不会把明文直接写进 SQL 文件
\password app_user
\password report_user

-- 若确有业务需求，可创建具备建库属性的专用账号
CREATE USER db_creator WITH LOGIN CREATEDB;
\password db_creator

注意：CREATEDB、CREATEROLE、SUPERUSER 是实例级属性，不是某个数据库的普通 GRANT 权限。绝大多数应用账号都不需要这些能力。

4.3 重置密码、禁用/启用登录

-- 推荐在 psql 中以交互方式重置：
\password app_user

-- SQL 也可修改密码，但明文可能进入客户端历史/审计/日志：
-- ALTER ROLE app_user PASSWORD '替换为真实强密码';

-- 禁止新登录（不自动杀掉已建立的连接）
ALTER ROLE app_user NOLOGIN;

-- 恢复登录
ALTER ROLE app_user LOGIN;

-- 设置连接数量上限（-1 表示不限）
ALTER ROLE app_user CONNECTION LIMIT 30;

-- 需要的话再撤销建库属性
ALTER ROLE db_creator NOCREATEDB;

密码过期可以采用 ALTER ROLE ... VALID UNTIL 配置，但应与企业认证、安全策略和连接池更换密钥的流程一致。

4.4 删除用户

DROP ROLE report_user;
-- 或 DROP USER report_user;

若因该用户拥有数据库对象、Schema、表或存在授权依赖而失败，应先核查归属并在涉及的各个数据库分别处理：

-- 以下是顺序示例，需先确认接管者 app_owner 存在且具备权限
REASSIGN OWNED BY report_user TO app_owner;

-- 危险：DROP OWNED 会删除此用户仍拥有的对象，
-- 并撤销本数据库内授予该用户的权限。
-- 因此仅在 REASSIGN OWNED 和依赖检查完成后慎用：
-- DROP OWNED BY report_user;

只有在所有数据库相关依赖清理完成后才能最终 DROP ROLE。不要无脑使用 CASCADE 来“解决”依赖。

官方：数据库角色 · 角色属性。

五、用户权限：查看、授予、撤销与创建时授权

5.1 先弄清三个主要权限层级

作用范围	常见权限	含义
Database	CONNECT、CREATE、TEMPORARY	连接数据库、创建 Schema、临时表
Schema	USAGE、CREATE	访问命名空间、在 Schema 中建对象
Table / View	SELECT、INSERT、UPDATE、DELETE、TRUNCATE 等	表数据访问及操作
Sequence	USAGE、SELECT、UPDATE	序列访问或推进
Role	GRANT role TO user	继承/切换已定义角色的授权

只授予 CONNECT 不代表能读取表；只授予表 SELECT 却没有 Schema 的 USAGE，也不能正常访问该对象。

PostgreSQL 15 起，新建数据库中 public Schema 不再默认授予 PUBLIC 创建对象的权利；旧版本或升级后的权限状态可能不同。无论版本，生产项目都建议使用专用 Schema 和显式授权。

5.2 查看用户的有效权限和角色成员关系

-- psql 查看角色、数据库、schema、表授权
\du+
\l+
\dn+
\dp app.*

-- 实际生效权限（比单看原始 ACL 更实用）
SELECT
  has_database_privilege('app_user', 'appdb', 'CONNECT') AS can_connect,
  has_database_privilege('app_user', 'appdb', 'CREATE') AS can_create_schema;

-- 连接到 appdb 以后执行
SELECT
  has_schema_privilege('app_user', 'app', 'USAGE') AS can_use_schema,
  has_schema_privilege('app_user', 'app', 'CREATE') AS can_create_object;

SELECT
  has_table_privilege('app_user', 'app.orders', 'SELECT') AS can_select,
  has_table_privilege('app_user', 'app.orders', 'INSERT') AS can_insert,
  has_table_privilege('app_user', 'app.orders', 'UPDATE') AS can_update,
  has_table_privilege('app_user', 'app.orders', 'DELETE') AS can_delete;

如果示例库或表还没创建，先创建相应对象后再运行权限检查。

SELECT child.rolname AS member, parent.rolname AS member_of
FROM pg_auth_members m
JOIN pg_roles child ON child.oid = m.member
JOIN pg_roles parent ON parent.oid = m.roleid
ORDER BY child.rolname, parent.rolname;

pg_database.datacl、pg_namespace.nspacl、pg_class.relacl 可以查看原始 ACL，但 NULL 不等同于任何人都有权访问；解析权限时优先使用 has_*_privilege 函数确认有效结果。

5.3 新建用户时一并完成授权：推荐角色化方案

最佳实践是职责角色 NOLOGIN + 业务用户 LOGIN + 具体对象 GRANT。PostgreSQL 不支持在一条 CREATE USER 中直接声明“对某数据库所有表具有 SELECT”；这需要后续 GRANT。

以下命令按照管理员操作和目标库操作分别执行：

-- 角色/用户属于实例级对象
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;

CREATE USER app_user WITH LOGIN NOSUPERUSER NOCREATEDB;
CREATE USER report_user WITH LOGIN NOSUPERUSER NOCREATEDB;
\password app_user
\password report_user

GRANT app_readwrite TO app_user;
GRANT app_readonly TO report_user;

-- 创建数据库不能在普通事务块中执行
CREATE DATABASE appdb OWNER app_owner;

-- 若要隔离连接，应先撤销默认 PUBLIC CONNECT
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_readonly, app_readwrite;

已有账号与数据库时，跳过重复的 CREATE。随后连接至 appdb：

\c appdb

-- 需要管理员或具备建 Schema 权限的账号执行
CREATE SCHEMA app AUTHORIZATION app_owner;

-- 应用只访问 app 中的业务对象
GRANT USAGE ON SCHEMA app TO app_readonly, app_readwrite;

-- 对现有表授权（如果还没有表，可先配置默认权限）
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app TO app_readwrite;

-- 若应用确实要建表，才额外授予 CREATE
-- GRANT CREATE ON SCHEMA app TO app_readwrite;

对基于 serial 或独立 sequence 的业务表，通常还需按实际用法授权：

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

对象所有者非常重要：对 ALTER TABLE、DROP TABLE 等 DDL，通常需要成为对象所有者（或拥有足够管理能力）；不能指望一个普通 GRANT UPDATE 就授予修改表结构的能力。

5.4 给未来创建的新表自动赋权

只用 GRANT ... ON ALL TABLES 会遗漏后续新增的表。此时在 appdb 中由对象创建者或有权代表创建者的管理员执行：

-- 假设未来的业务表确实由 app_owner 创建
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

关键限制：

• 默认权限只作用于以后由指定角色创建的对象，对已有表无效。
• 如果实际上由 migration_user、postgres 或别的角色建表，必须针对真实的建表角色配置默认权限。
• 角色成员身份不会自动使所有新建对象使用被继承角色的默认权限。必要时由迁移工具先 SET ROLE app_owner 再建表。

5.5 撤销授权与再次验证

-- 连接 appdb 后回收表写权限
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app FROM app_readwrite;

REVOKE CREATE ON SCHEMA app FROM app_readwrite;
REVOKE USAGE ON SCHEMA app FROM app_readonly;

-- 数据库级连接权限（可以从任意数据库执行）
REVOKE CONNECT ON DATABASE appdb FROM app_readonly;

-- 成员资格
REVOKE app_readonly FROM report_user;

-- 回收未来新表的默认授权
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
REVOKE SELECT ON TABLES FROM app_readonly;

REVOKE 执行成功后仍可能通过其他角色、PUBLIC、对象所有权等获得权限。再次使用 has_database_privilege / has_schema_privilege / has_table_privilege 验证最终实际权限。

官方：GRANT · REVOKE · 默认权限。

六、数据库管理：查看、新建、修改、删除与编码

6.1 查看数据库列表与基本属性

\l
\l+

SELECT datname,
       pg_get_userbyid(datdba) AS owner,
       pg_encoding_to_char(encoding) AS encoding,
       datcollate,
       datctype,
       datlocprovider,
       datconnlimit,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY datname;

上面的 datlocprovider 是 PostgreSQL 17 的数据库 Locale 提供者标识：

• c：libc；
• i：ICU；
• b：builtin（PostgreSQL 17 支持，较旧版本未必存在）。

不要在 PostgreSQL 14 等较早版本直接照抄 datlocprovider 的查询；应先核对该版本系统目录列。

说明：PostgreSQL 17 没有数据库兼容模式。

6.2 新建数据库

-- 最简单的方式
CREATE DATABASE appdb;

-- 使用指定所有者
CREATE DATABASE appdb OWNER app_owner;

-- 指定 UTF8、基础模板和 Locale（环境需支持相应设置）
CREATE DATABASE appdb_utf8
  WITH OWNER = app_owner
       TEMPLATE = template0
       ENCODING = 'UTF8'
       LOCALE = 'C.UTF-8';

PostgreSQL 17 支持的 Locale 提供者包括 libc、ICU、builtin。例如 builtin 的合法 Locale 主要是 C 或 C.UTF-8，但具体是否能按指定方式创建还受当前 PostgreSQL 构建和模板影响。

为什么有时要写 TEMPLATE template0？如果新库的编码/Locale 与默认模板 template1 不一致，通常需要选择 template0，否则数据库无法安全继承原模板的数据/索引。

注意：CREATE DATABASE 不能在事务块内执行，也不能随意指定与 Locale 不兼容的编码。

6.3 修改数据库属性

-- 从 postgres 等其他数据库连接进行数据库改名
ALTER DATABASE appdb RENAME TO appdb_v2;

-- 修改所有者
ALTER DATABASE appdb_v2 OWNER TO app_owner;

-- 限制普通连接数（可能存在边界并发近似值）
ALTER DATABASE appdb_v2 CONNECTION LIMIT 100;

-- 设置数据库级默认参数（新会话中生效）
ALTER DATABASE appdb_v2 SET statement_timeout = '30s';

-- 恢复数据库级默认参数
ALTER DATABASE appdb_v2 RESET statement_timeout;

不能通过简单的 ALTER DATABASE 直接变更现有数据库的编码和 Locale/Collation 提供者。 这类需求通常要新建目标数据库、迁移结构与数据，并验证排序/索引行为。

6.4 删除数据库

-- psql 先切换到其他数据库
\c postgres

-- 查看目标数据库是否仍被连接
SELECT datname, count(*) AS sessions
FROM pg_stat_activity
WHERE datname = 'appdb_v2'
GROUP BY datname;

-- 先确认备份与业务下线，再执行
DROP DATABASE appdb_v2;

PostgreSQL 17 支持 DROP DATABASE ... WITH (FORCE)，但它会尝试终止符合条件的现有连接，而且不能解决所有阻塞条件（例如部分准备事务等）；不要把 FORCE 当成普通删除的默认选择。

官方：CREATE DATABASE · ALTER DATABASE · 数据库系统目录。

七、数据表管理：查看、创建、修改、删除和清空

7.1 查看表、字段、索引及大小

\c appdb

\dt
\dt app.*
\d app.orders
\d+ app.orders
\di app.*

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'app'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'orders'
ORDER BY ordinal_position;

SELECT pg_size_pretty(pg_total_relation_size('app.orders'::regclass));

pg_total_relation_size 包含表及相关索引、TOAST 等所占空间；超大表应同时考虑磁盘、膨胀和维护窗口。

7.2 创建 Schema、表、约束与索引

-- 在数据库 appdb 内，Schema 已存在时省略此句
CREATE SCHEMA app;

CREATE TABLE app.orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL UNIQUE,
    amount NUMERIC(18, 2) NOT NULL CHECK (amount >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_created_at
ON app.orders (created_at);

IDENTITY 是标准化的自增主键用法；这里的 GENERATED ALWAYS 对显式输入 ID 存在限制，应结合数据迁移需求选择 ALWAYS 或 BY DEFAULT。

7.3 修改表结构与数据

ALTER TABLE app.orders ADD COLUMN remark VARCHAR(255);

ALTER TABLE app.orders ALTER COLUMN remark TYPE VARCHAR(500);

ALTER TABLE app.orders ALTER COLUMN status SET DEFAULT 'PENDING';

ALTER TABLE app.orders RENAME COLUMN remark TO note;

-- 数据更新：先确认 WHERE 范围
UPDATE app.orders
SET status = 'DONE'
WHERE order_no = 'ORDER-001';

ALTER TABLE app.orders RENAME TO order_records;

-- 删除列将导致列数据不可用
ALTER TABLE app.order_records DROP COLUMN note;

修改字段类型、增加约束、创建普通索引可能造成较长锁等待或表扫描/重写。线上大表应先确认锁级别、是否可以采用 CREATE INDEX CONCURRENTLY 等策略，再规划维护窗口。

7.4 DELETE、TRUNCATE、DROP 的区别

命令	数据处理范围	典型影响
DELETE ... WHERE	条件匹配行	行级删除，适合可控的业务清理
DELETE FROM table	所有行	逐行删除，可能产生大量 WAL/死元组
TRUNCATE table	清空全部行	通常更快，但会获取强表锁并影响并发
DROP TABLE	删除整个表定义与数据	必须先确认依赖和恢复方案

-- 建议先在事务中验证删除条件
BEGIN;

DELETE FROM app.order_records
WHERE status = 'CANCELLED';

-- 确认结果后选择 COMMIT；这里只演示撤销
ROLLBACK;

-- 高风险：清空整个表
TRUNCATE TABLE app.order_records;

-- 如果还需重置 Identity / Sequence 计数
TRUNCATE TABLE app.order_records RESTART IDENTITY;

-- 高风险：删除整张表
DROP TABLE app.order_records;

PostgreSQL 的 TRUNCATE 具有事务性：在一个事务中执行后，如事务尚未提交，可以通过 ROLLBACK 回退；但它并非不会锁表，也不会触发普通的 ON DELETE 行级触发器。不要忽略外键引用，尤其不要无审核使用 TRUNCATE ... CASCADE。

7.5 表维护：统计信息、VACUUM 和索引

-- 让查询优化器更新统计信息
ANALYZE app.orders;

-- 清理死元组（普通 VACUUM 通常不需要长期独占整表）
VACUUM (ANALYZE) app.orders;

-- 查看表的自动清理与分析统计
SELECT relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY n_dead_tup DESC;

VACUUM FULL 会重写表并需要强锁与额外空间，不要当作日常默认维护命令。常规维护以 autovacuum 为基础，按真实负载调整阈值和监控。

官方：ALTER TABLE · TRUNCATE · 日常维护。

八、如何配置远程访问

远程连接成功至少需要同时满足：

1. PostgreSQL 监听目标网络接口；
2. pg_hba.conf 允许来源 IP、数据库、用户和认证方式；
3. Linux 防火墙/云安全组允许相应来源连接；
4. 登录用户具备对应的 LOGIN、CONNECT 和对象权限。

以下示例：数据库主机 10.10.10.11，应用主机 10.10.10.20，数据库 appdb，用户 app_user。

8.1 找到真实配置文件

SHOW config_file;
SHOW hba_file;
SHOW data_directory;
SHOW listen_addresses;
SHOW port;
SHOW password_encryption;
SHOW ssl;

8.2 配置 listen_addresses

在对应 postgresql.conf 设置：

listen_addresses = 'localhost,10.10.10.11'
port = 5432
password_encryption = 'scram-sha-256'

listen_addresses 需要重启，仅执行 reload 不会让监听地址变化。password_encryption 控制后续新设口令的存储形式，修改此参数本身不等于自动转换已有口令。

8.3 在 pg_hba.conf 中设最小授权

# TYPE DATABASE USER     ADDRESS          METHOD
host   appdb    app_user 10.10.10.20/32  scram-sha-256

规则按顺序检查。若启用 TLS，可以在完成服务器证书配置后使用 hostssl 和客户端证书校验，避免把明文传输和弱认证当成默认方案。

-- 修改 pg_hba.conf 后重载（不必重启）
SELECT pg_reload_conf();

-- 如果规则解析失败，检查解析错误
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

不要开放 0.0.0.0/0 + trust 或者将 PostgreSQL 5432 端口直接暴露互联网。

8.4 检查防火墙与客户端连接

# 数据库服务器上
ss -lntp | grep 5432

# 应用服务器上测试 TCP
nc -vz 10.10.10.11 5432

# 应用服务器上使用 psql 连接，交互输入密码
psql -h 10.10.10.11 -p 5432 -U app_user -d appdb -W

错误分层定位：

现象	优先排查
Connection refused	服务没启动、监听地址/端口不对
Connection timed out	防火墙、安全组、路由或 NAT
no pg_hba.conf entry	实际来源 IP、数据库/账号、规则顺序与 SSL
password authentication failed	口令、登录角色、SCRAM 认证设置
permission denied for schema/table	CONNECT、Schema USAGE、对象 GRANT

客户端 TLS 建议采用 sslmode=verify-full 并配置可信根证书；require 只要求加密连接，不能替代完整的服务器身份校验。

官方：连接与认证 · pg_hba.conf · 安全 TCP 连接。

九、常见故障排查：CPU、内存、锁、死锁、连接数

下面命令适用于 PostgreSQL 17。先收集证据，后终止会话或调参。有主备时需要先确认问题出在主节点、只读备节点还是连接代理层。

9.1 高 CPU：先定位 OS 进程，再关联 SQL

top
ps -eo pid,ppid,%cpu,%mem,etime,args --sort=-%cpu | head -30
pidstat -u 1 5
vmstat 1 5

PostgreSQL 会按连接/后台任务使用不同进程，ps 的 PID 可以与 pg_stat_activity.pid 关联：

SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       now() - query_start AS elapsed, query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start
LIMIT 30;

检查高耗时语句执行计划：

EXPLAIN
SELECT * FROM app.orders WHERE order_no = 'ORDER-001';

普通 EXPLAIN 不真正执行语句；EXPLAIN (ANALYZE, BUFFERS) 会执行实际 SQL，可能产生读写开销，不能未经评估就在生产环境运行危险语句。

进一步排查索引失效、顺序扫描、统计信息陈旧、慢查询和短时间并发突增。

9.2 可选增强：pg_stat_statements

这是扩展，不是每个实例默认都已装好。

在 postgresql.conf 预加载（可能需要把它追加到已有库列表而不是覆盖其他插件）：

shared_preload_libraries = 'pg_stat_statements'

此参数生效需要重启 PostgreSQL。在需要查询的数据库中由合适权限的角色执行：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

说明：这些是 SQL 维度的执行时长和次数统计，不等于逐条的精确 CPU 使用率。需要结合 OS 进程、等待事件和查询计划判断。

9.3 内存上涨、OOM、Swap

free -h
vmstat 1 5
ps -eo pid,rss,vsz,%mem,args --sort=-rss | head -25
dmesg -T | grep -i -E 'oom|out of memory' | tail -30

SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_connections;
SHOW temp_file_limit;
SHOW log_temp_files;

SELECT state, count(*) AS sessions
FROM pg_stat_activity
GROUP BY state
ORDER BY sessions DESC;

SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_written
FROM pg_stat_database
ORDER BY temp_bytes DESC;

如何分析：

• shared_buffers 为共享缓冲，不应与各进程的 RSS 简单相加当成“总数据库内存”；
• work_mem 是单个排序/哈希等执行操作的内存限额语义之一，不是整个 PostgreSQL 实例的总上限。单条 SQL 与并发执行可能同时使用多份；
• 大量并发连接、并行查询、批量排序、临时文件和其他同机服务都可能引起内存压力；
• 出现 OOM 日志时优先确认最近参数/流量变化，不要只靠增加 work_mem 处理。

9.4 锁等待：找到被阻塞会话及阻塞者

-- 当前有 Lock 等待的会话
SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       now() - query_start AS elapsed, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

-- 被阻塞 PID -> 阻塞它的 PID
SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.query AS blocked_sql,
       blocker.query AS blocker_sql,
       now() - blocker.xact_start AS blocker_tx_age
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = bp.pid
ORDER BY blocker_tx_age DESC NULLS LAST;

-- 检查未获批的锁
SELECT locktype, relation::regclass, mode, pid, granted
FROM pg_locks
WHERE NOT granted;

锁等待不等于死锁：前者可能是正常的竞争/长事务；后者存在循环依赖，数据库通常检测后中止其中一个事务。

检查可疑的长事务：

SELECT pid, usename, application_name, state,
       now() - xact_start AS tx_age,
       now() - state_change AS state_age,
       query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

确认业务影响后才考虑取消或终止会话：

-- 请用经核实的真实 PID 替换示例数字
SELECT pg_cancel_backend(12345);    -- 尝试取消当前 SQL
-- SELECT pg_terminate_backend(12345);  -- 断开会话，事务会被回滚

不要直接对 PostgreSQL 进程使用 kill -9 作为常规锁排查手段。

9.5 死锁：如何确认和修复

-- 累计死锁次数（通常为历史统计，非“当前仍存在的死锁数”）
SELECT datname, deadlocks, stats_reset
FROM pg_stat_database
ORDER BY deadlocks DESC;

SHOW deadlock_timeout;
SHOW log_lock_waits;
SHOW log_min_error_statement;

如果出现 deadlock detected，应按时间关联 PostgreSQL 日志与事务执行顺序，重点排查：

1. 两个事务是否按相反顺序修改同一组表/记录；
2. 事务是否包含不必要的 HTTP 调用、远程 RPC、长耗时任务；
3. 业务是否在异常时漏掉 COMMIT / ROLLBACK；
4. 索引和 SQL 是否导致锁范围或持锁时间超出预期。

修复方向是缩短事务、统一资源锁定顺序、合理使用索引、在应用层针对死锁事务进行有界重试。提高 deadlock_timeout 只是调整检测时机，不是从根本上消除死锁。

9.6 连接数用尽 / too many connections

SHOW max_connections;
SHOW superuser_reserved_connections;

SELECT count(*) AS current_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend';

SELECT datname, usename, application_name, state,
       count(*) AS connection_count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, application_name, state
ORDER BY connection_count DESC;

SELECT pid, usename, datname, state,
       now() - xact_start AS tx_age, query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

如果应用使用 HikariCP 等连接池，先计算：

理论最大应用连接数 ≈ 应用实例数 × 每实例连接池最大值
实际还需要预留：运维连接、迁移工具、备份、后台进程及连接代理

处理优先顺序：

• 检查应用是否泄漏连接、是否发生重试风暴或频繁新建连接。
• 核对连接池 maximumPoolSize、连接超时、空闲/最大生命周期、事务超时。
• 确认是否存在长事务、热点锁和慢 SQL 使连接长时间不归还。
• 必要时使用 PgBouncer 等连接池代理，按事务/会话语义合理配置。
• 提升 max_connections 需要同步评估内存，并按需要重启；不是无限增加的安全阀。

还可以用角色/数据库级限制：

ALTER ROLE app_user CONNECTION LIMIT 30;
ALTER DATABASE appdb CONNECTION LIMIT 100;

这限制的是新连接，不会自动释放已经存在的连接。

9.7 磁盘和 WAL 异常

df -h
df -i
du -sh /srv/postgresql/17/data  # 独立实例的示例路径

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database
WHERE datallowconn
ORDER BY pg_database_size(datname) DESC;

SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;

排查 WAL 堆积应看复制槽、长期停滞的备节点、归档失败、长事务与磁盘容量；不要手动删除 pg_wal 目录里的 WAL 文件。主备架构还应同时确认备份是否可恢复、归档是否连续及恢复点目标（RPO/RTO）。

9.8 日志、监控与最小权限

SHOW log_destination;
SHOW logging_collector;
SHOW log_directory;
SHOW log_min_duration_statement;
SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;

可视化工具并不能代替基本排查：建议统一收集慢 SQL、锁等待、连接数、事务年龄、主备延迟、磁盘及备份验证结果。监控账号应使用 pg_monitor 等适当的预定义角色授权，而不是分配 SUPERUSER。

官方：监控统计视图 · pg_stat_statements · 服务器日志。

十、日常 DBA 运维速查表

任务	PostgreSQL 17 推荐入口	注意
版本信息	SELECT version();	确认服务端不是只看 psql 客户端
Debian 17 单机状态	pg_lsclusters、pg_ctlcluster 17 main status	集群名可能不是 main
RPM 17 单机状态	systemctl status postgresql-17	仅适用于对应包服务
独立实例状态	pg_ctl -D "$PGDATA" status	不要绕过现有管理器
主备角色	SELECT pg_is_in_recovery();	false=Primary，true=Recovery/Standby
流复制	pg_stat_replication / pg_stat_wal_receiver	主备分别查看
用户	\du+、pg_roles	高权限账号重点检查
数据库	\l+、pg_database	编码、Locale、大小
Schema 和表	\dn+、\dt app.*、\d+	对象所有权、权限
有效授权	has_*_privilege	不只看 ACL
活跃 SQL	pg_stat_activity	PID、耗时和等待
锁/阻塞	pg_blocking_pids()	先判定阻塞者
死锁历史	pg_stat_database.deadlocks	配合错误日志
连接数	max_connections + pg_stat_activity	检查连接池上限
内存	free -h、shared_buffers、work_mem	不把 work_mem 当全局限制
远程认证	SHOW hba_file、pg_hba_file_rules	细化 IP、账号、TLS
磁盘 / WAL	df -h、pg_replication_slots	避免 WAL 填满磁盘

十一、总结与生产建议

对于 PostgreSQL 17，日常管理可以归纳为六条：

1. 单机启停按安装方式区分：Debian/Ubuntu 通常用 pg_ctlcluster，PGDG RPM 用 postgresql-17 的 systemd 服务，独立实例才用 pg_ctl。
2. 主备复制与高可用是两件事：pg_basebackup -R 可以构建流复制备库，但自动选主、隔离旧主和业务入口切换通常需要额外 HA 方案。
3. 用户和权限分层管理：以 LOGIN 用户 + NOLOGIN 职责角色 + 数据库、Schema、表/序列权限为主，不滥用 SUPERUSER。
4. 数据库属性需要在建库前规划：提前确定编码、Locale/Collation 和业务所需扩展，避免后续迁移成本。
5. 破坏性操作前先备份和评估锁：DROP、TRUNCATE、大表 ALTER、切主、强制断连均可能影响业务。备份不仅要存在，还要定期演练恢复。
6. 排障结合 OS 与 SQL：CPU、内存、连接、等待事件、长事务、WAL/复制状态、慢 SQL 统计一起分析，而不是只调整一个数据库参数。

PostgreSQL 17 官方参考资料

• PostgreSQL 17 官方文档首页
• 版本支持与升级策略
• Linux 安装（Ubuntu） / Linux 安装（Red Hat）
• 启动 PostgreSQL / pg_ctl
• 物理主备和流复制 / pg_basebackup
• 角色属性 / GRANT / 默认权限
• CREATE DATABASE / pg_database
• pg_hba.conf / SSL/TLS
• 监控视图 / pg_stat_statements

版本提醒：本文命令以 17.x 官方接口为基准。14、15、16、18 的常用操作可参考，但首次部署、升级、高可用切换及数据库系统目录查询仍须以实际主版本对应的官方文档为准。