PostgreSQL 运维工程师 “一本通“ ：安装、配置、备份与监控
·
文章目录
PostgreSQL 基础学习
一、PostgreSQL 介绍
1、简介
PostgreSQL 是一个功能强大的开源数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix和Windows。
PostgreSQL 是完全的事务安全性数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GiST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Java、PL/Tcl等。
作为一种企业级数据库，PostgreSQL以它所具有的各种高级功能而自豪，像多版本并发控制( MVCC )、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。它支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。它也在所能管理的大数据量和所允许的大用户量并发访问时间具有完全的高伸缩性。
2、PostgreSQL 的优势
PostgreSQL 数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，至少实现了SQL:2011标准中要求的179项主要功能中的160项。
| 优势 | 说明 |
|---|---|
| 稳定可靠 | 支持事务、WAL、MVCC、PITR、流复制，适合承载核心业务数据 |
| SQL 能力强 | 支持复杂查询、窗口函数、CTE、多种 Join、子查询、聚合分析 |
| 数据类型丰富 | 支持 JSONB、数组、范围类型、UUID、网络地址、几何类型等 |
| 扩展能力强 | 支持插件扩展，例如 pg_stat_statements、PostGIS、pg_trgm 等 |
| 运维观测能力好 | 内置大量系统视图，如 pg_stat_activity、pg_locks、pg_stat_database |
| 开源免费 | 使用类 BSD 协议，适合企业自建和二次集成 |
| 生态成熟 | 支持主流语言和框架，例如 Java、Go、Python、Node.js、PHP 等 |
- 稳定可靠：PostgreSQL是唯一能做到数据零丢失的开源数据库。目前有报道称国内外有部分银行使用PostgreSQL数据库。
- 开源省钱：PostgreSQL数据库是开源的、免费的，而且使用的是类BSD协议，在使用和二次开发上基本没有限制。
- 支持广泛：PostgreSQL 数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl以及PHP等。
- PostgreSQL社区活跃：PostgreSQL基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。
3、PostgreSQL 对比 MySQL
Postgresql和Mysql都是开源数据库。
- 功能强大：支持所有主流的多表连接查询的方式，如“Hash JOIN”“Sort Merge JOIN”等；字段类型还支持数组类型，甚至有一些业务功能都不再需要写代码来实现了，直接使用数据库的功能即可解决问题。
- 性能优化工具与度量信息丰富：PostgreSQL数据库中有大量的性能视图，可以方便地定位问题（比如可以看到正在执行的SQL，可以通过锁视图看到谁在等待，以及哪条记录被锁定等）。PostgreSQL中设计了专门架构和进程用于收集性能数据，既有物理I/O方面的统计，也有表扫描及索引扫描方面的性能数据。
- 在线操作功能好：PostgreSQL增加空值列时，本质上只是在系统表上把列定义上，无须对物理结构做更新，这就让PostgreSQL在加列时可以做到瞬间完成。PostgreSQL还支持在线建索引的功能，在创建索引的过程可以不锁更新操作。
- 从PostgreSQL9.1开始，支持同步复制（synchronous replication）功能，通过Master和Slave之间的复制可以实现零数据丢失的高可用方案。可以方便地写插件来扩展PostgreSQL数据库的功能：支持移动互联网的新功能，如空间索引。如果应用的数据访问很简单，那么后端使用MySQL也是很合适的。但是如果应用复杂，而且不想消耗太多的开发资源，那么PostgreSQL是一个很明智的选择。
PostgreSQL 和 MySQL 都是常见的开源数据库，但两者定位略有不同。
| 对比项 | PostgreSQL | MySQL |
|---|---|---|
| SQL 能力 | 更完整，复杂查询能力强 | 常规 OLTP 场景简单高效 |
| 数据类型 | 类型丰富，支持 JSONB、数组、范围类型等 | 类型相对简单，JSON 能力也可用 |
| 扩展能力 | 插件体系强，适合复杂业务 | 插件生态有，但整体扩展性弱一些 |
| 事务一致性 | 一致性和标准兼容性较强 | 依赖具体存储引擎，InnoDB 为主 |
| 运维复杂度 | 参数、权限、连接、Vacuum 等需要理解 | 相对容易上手 |
| 适用场景 | 复杂业务、强一致、报表、GIS、JSONB、企业系统 | 简单业务、高并发读写、互联网常规业务 |
如果业务只是简单的增删改查，MySQL 使用成本较低；如果业务中存在复杂查询、强一致性、JSONB、报表分析、权限管理、GIS、数据建模等需求，PostgreSQL 更合适。
4、不适用 PostgreSQL
在不安装任何扩展包的情况下，PG需要占用100MB以上的磁盘空间，可以看出它的个头是比较大的，因此在一些存储空间极为有限的小型设备上使用PG是不合适的。因此在一些存储空间极为有限的小型设备上使用PG是不合适的，把PG当成简单的缓存区来用也是不合适的，此时应选用一些更轻量级的数据库。
因为作为一款企业级数据库产品，PG对其安全也是极其重视的，因此，如果你在开发一个把安全管理放到应用层去做的轻量级应用，那么PG完善的安全机制反倒会成为负担，因为它的角色和权限管理非常复杂，会带来不必要的管理复杂度和性能损耗。
鉴于上面的种种，PG数据库一般是会和别的数据库搭配使用，使他们各展所长。一种常见的组合是把Redis当成PG的查询缓存来用，另一种的组合是用PG做主数据库。
5、核心概念
| 概念 | 说明 |
|---|---|
| Cluster | PostgreSQL 实例级概念，Ubuntu 下通常是 版本/集群名，例如 16/main |
| Database | 数据库，一个 PostgreSQL Cluster 内可以有多个 Database |
| Role/User | PostgreSQL 中用户和角色统一为 Role，带 LOGIN 权限的角色就是用户 |
| Schema | 数据库内的命名空间，默认常见为 public |
| Data Directory | 数据目录，存放真实数据库文件 |
| WAL | 预写日志，用于事务恢复、复制和备份 |
| postgresql.conf | 主配置文件，控制监听地址、端口、日志、内存、WAL 等参数 |
| pg_hba.conf | 客户端认证配置文件，控制哪些 IP、用户、数据库可以连接 |
| pg_lsclusters | Ubuntu/Debian 下查看 PostgreSQL 集群状态的工具 |
| pg_ctlcluster | Ubuntu/Debian 下启动、停止、重启指定 PostgreSQL 集群的工具 |
二、PostgreSQL 安装部署
PostgreSQL 官方在 Ubuntu 上主要推荐通过系统自带 APT 源或 PostgreSQL 官方 PGDG APT 仓库安装；Ubuntu 默认源包含 PostgreSQL，但版本会随 Ubuntu 版本固定，而 PGDG 仓库可以提供当前受支持的 PostgreSQL 主版本。
PostgreSQL 在 Ubuntu 环境下常见安装方式有三种：
| 安装方式 | 适用场景 | 特点 | 推荐程度 |
|---|---|---|---|
| Ubuntu 默认 APT 源 | 测试环境、版本无特殊要求 | 安装简单，但版本受 Ubuntu 系统版本限制 | 一般 |
| PostgreSQL PGDG APT 源 | 生产环境、需要指定 PostgreSQL 主版本 | 可安装指定主版本，适合标准化部署 | 推荐 |
| 脚本安装 | 多台机器批量部署、标准化环境 | 自动完成目录、参数、权限、密码、服务配置 | 推荐，但脚本要维护 |
1、Ubuntu apt 源安装
PostgreSQL 官方下载页明确区分了 ready-to-use packages / installers 与 source code archive，源码编译应单独归类为“源码安装”。
本次 PostgreSQL 安装采用 阿里云 PostgreSQL PGDG 源，通过安装脚本完成 PostgreSQL 的安装、初始化、目录规划、参数配置和服务启动。
阿里云源：-https://mirrors.aliyun.com/postgresql/repos/apt
默认安装版本为 PostgreSQL 16，默认目录规划如下：
| 类型 | 路径 |
|---|---|
| 软件目录 | /usr/lib/postgresql/16 |
| 软件软链接 | /usr/local/pgsql |
| 配置目录 | /etc/postgresql/16/main |
| 数据目录 | /data/postgresql/16/main |
| 日志目录 | /data/postgresql/log |
| 备份目录 | /data/postgresql/backup |
| 凭据文件 | /data/postgresql/backup/.postgresql.auth |
| 服务名称 | postgresql@16-main.service |
| 默认端口 | 5432 |
1.1 环境准备
安装依赖包：
apt update
apt install -y ca-certificates curl gnupg lsb-release postgresql-common
1.2 获取apt源
install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
--fail https://mirrors.aliyun.com/postgresql/repos/apt/ACCC4CF8.asc
cat > /etc/apt/sources.list.d/pgdg.sources <<EOF
Types: deb
URIs: https://mirrors.aliyun.com/postgresql/repos/apt
Suites: $(lsb_release -cs)-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF
1.3 安装PostgreSQL
apt update
apt install -y postgresql-16 postgresql-client-16 postgresql-contrib
检查安装结果：
systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2026-05-15 10:20:51 CST; 5min ago
Main PID: 43730 (code=exited, status=0/SUCCESS)
CPU: 930us
May 15 10:20:51 instance-wc5p3ngj systemd[1]: Starting PostgreSQL RDBMS...
May 15 10:20:51 instance-wc5p3ngj systemd[1]: Finished PostgreSQL RDBMS.
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
1.4 登陆验证
切换为postgres用户进行登陆：
su - postgres
psql -U postgres
psql (16.14 (Ubuntu 16.14-1.pgdg22.04+1))
Type "help" for help.
postgres-# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
postgres-# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres-# \q
| 命令 | 作用 |
|---|---|
su - postgres | 切换到 PostgreSQL 默认系统用户 |
psql | 进入 PostgreSQL 命令行 |
\l | 查看数据库列表 |
\du | 查看数据库角色 / 用户 |
\q | 退出 psql |
exit | 退出 postgres 系统用户 |
1.5 备份默认配置文件
cp -a /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf.bak.$(date +%F_%H%M%S)
cp -a /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.bak.$(date +%F_%H%M%S)
| 命令 | 作用 |
|---|---|
备份 postgresql.conf | 防止配置改错后无法恢复 |
备份 pg_hba.conf | 防止认证规则改错导致无法登录 |
1.6 修改基础配置
修改 pg_hba.conf：
PG_CONF="/etc/postgresql/16/main/postgresql.conf"
sed -ri "s|^[#[:space:]]*listen_addresses[[:space:]]*=.*|listen_addresses = '*'|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*port[[:space:]]*=.*|port = 5432|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*password_encryption[[:space:]]*=.*|password_encryption = 'scram-sha-256'|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*timezone[[:space:]]*=.*|timezone = 'Asia/Shanghai'|" "$PG_CONF"
sed -ri "s|^[#[:space:]]*log_timezone[[:space:]]*=.*|log_timezone = 'Asia/Shanghai'|" "$PG_CONF"
grep -q "^log_min_duration_statement" "$PG_CONF" \
&& sed -ri "s|^[#[:space:]]*log_min_duration_statement[[:space:]]*=.*|log_min_duration_statement = 1000|" "$PG_CONF" \
|| echo "log_min_duration_statement = 1000" >> "$PG_CONF"
grep -q "^log_lock_waits" "$PG_CONF" \
&& sed -ri "s|^[#[:space:]]*log_lock_waits[[:space:]]*=.*|log_lock_waits = on|" "$PG_CONF" \
|| echo "log_lock_waits = on" >> "$PG_CONF"
grep -q "^deadlock_timeout" "$PG_CONF" \
&& sed -ri "s|^[#[:space:]]*deadlock_timeout[[:space:]]*=.*|deadlock_timeout = '1s'|" "$PG_CONF" \
|| echo "deadlock_timeout = '1s'" >> "$PG_CONF"
修改配置说明：
| 配置 | 推荐值 | 作用 |
|---|---|---|
listen_addresses | '*' | 监听所有网卡，允许远程连接 |
port | 5432 | PostgreSQL 默认端口 |
password_encryption | 'scram-sha-256' | 设置新密码使用 SCRAM 加密方式 |
timezone | 'Asia/Shanghai' | 数据库时区 |
log_timezone | 'Asia/Shanghai' | 日志时间使用中国时区 |
log_min_duration_statement | 1000 | 记录执行超过 1000ms 的 SQL |
log_lock_waits | on | 记录锁等待事件 |
deadlock_timeout | '1s' | 锁等待超过 1 秒后触发锁等待日志 |
最终修改的配置：
listen_addresses = '*'
port = 5432
password_encryption = 'scram-sha-256'
timezone = 'Asia/Shanghai'
log_timezone = 'Asia/Shanghai'
log_min_duration_statement = 1000
log_lock_waits = on
deadlock_timeout = '1s'
验证配置：
root@instance-wc5p3ngj:/data# grep -E 'listen_addresses|port|password_encryption|timezone|log_timezone|log_min_duration_statement|log_lock_waits|deadlock_timeout' /etc/postgresql/16/main/postgresql.conf
listen_addresses = '*'
port = 5432
password_encryption = 'scram-sha-256'
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
log_min_duration_statement = 1000
log_lock_waits = on
deadlock_timeout = '1s'
重启服务生效配置:
# 重启PostgreSQL 16服务
sudo systemctl restart postgresql@16-main
# 验证服务是否正常运行
sudo systemctl status postgresql@16-main
动态验证服务生效：
root@instance-wc5p3ngj:/data# sudo -u postgres psql
psql (16.14 (Ubuntu 16.14-1.pgdg22.04+1))
Type "help" for help.
postgres=# SHOW listen_addresses;
listen_addresses
------------------
*
(1 row)
postgres=# SHOW port;
port
------
5432
(1 row)
postgres=# SHOW password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)
postgres=# SHOW timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
postgres=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
1s
(1 row)
postgres=# SHOW log_lock_waits;
log_lock_waits
----------------
on
(1 row)
postgres=# SHOW deadlock_timeout;
deadlock_timeout
------------------
1s
(1 row)
postgres=#
1.7 设置 postgres 密码
使用postgres用户进行登陆：
su - postgres
psql
执行SQL语句：
ALTER USER postgres WITH PASSWORD '你的强密码';
\q
退出数据库后退出postgres用户，使用root用户测试登陆。
修改了 postgres 管理员密码，直接在服务器上敲 psql 是无法验证密码的（PostgreSQL 默认本地连接免密），必须用密码认证模式连接，才能验证新密码是否生效。
psql -U postgres -h localhost -d postgres
Password for user postgres:
psql (16.14 (Ubuntu 16.14-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=#
2、脚本安装
2.1 脚本内容
#!/usr/bin/env bash
# PostgreSQL apt 源安装脚本
# Ubuntu/Debian
# 默认所有数据、日志、备份和凭据均放在 /data/postgresql 下
# 使用方式：sudo bash postgresql_pgdg_install.sh install
# 卸载方式：sudo bash postgresql_pgdg_install.sh uninstall
# 查看状态：sudo bash postgresql_pgdg_install.sh status
# 重置密码：sudo bash postgresql_pgdg_install.sh reset-password
set -Eeuo pipefail
umask 027
export DEBIAN_FRONTEND=noninteractive
# PostgreSQL 主版本；PGDG APT 包按主版本安装，例如 postgresql-16
POSTGRES_VERSION="${POSTGRES_VERSION:-16}"
# 官方仓库默认会安装16.13
# PostgreSQL 集群名；Ubuntu/Debian 多版本多实例体系中常用 main
CLUSTER_NAME="${CLUSTER_NAME:-main}"
# PostgreSQL 运行用户和组；PGDG/Ubuntu 包默认使用 postgres
POSTGRES_USER="${POSTGRES_USER:-postgres}"
POSTGRES_GROUP="${POSTGRES_GROUP:-postgres}"
# 所有持久化目录默认都在 /data/postgresql 下
PG_BASE_DIR="${PG_BASE_DIR:-/data/postgresql}"
DATA_DIR="${DATA_DIR:-}"
LOG_DIR="${LOG_DIR:-}"
BACKUP_DIR="${BACKUP_DIR:-}"
AUTH_FILE="${AUTH_FILE:-}"
# PostgreSQL 软件真实安装目录由官方 APT 包固定为 /usr/lib/postgresql/<主版本>
# 这里提供软链接，便于统一运维路径
INSTALL_PREFIX="${INSTALL_PREFIX:-}"
INSTALL_LINK="${INSTALL_LINK:-/usr/local/pgsql}"
# 配置目录；pg_createcluster 默认生成到 /etc/postgresql/<版本>/<集群名>
CONFIG_BASE_DIR="${CONFIG_BASE_DIR:-/etc/postgresql}"
CONFIG_DIR="${CONFIG_DIR:-}"
CONF_FILE="${CONF_FILE:-}"
HBA_FILE="${HBA_FILE:-}"
IDENT_FILE="${IDENT_FILE:-}"
# 运行时目录仍使用 /run/postgresql，这是 PostgreSQL-common/systemd 约定目录，不属于持久化数据
RUN_DIR="${RUN_DIR:-/run/postgresql}"
# PGDG 仓库。默认使用阿里云国内镜像；如需官方源，设为 https://apt.postgresql.org/pub/repos/apt
PGDG_REPO_URL="${PGDG_REPO_URL:-https://mirrors.aliyun.com/postgresql/repos/apt}"
PGDG_KEY="${PGDG_KEY:-/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc}"
PGDG_SOURCE="${PGDG_SOURCE:-/etc/apt/sources.list.d/pgdg.sources}"
DISABLE_AUTO_MAIN_CLUSTER="${DISABLE_AUTO_MAIN_CLUSTER:-true}"
# 监听配置
POSTGRES_PORT="${POSTGRES_PORT:-5432}"
LISTEN_ADDRESSES="${LISTEN_ADDRESSES:-0.0.0.0}"
ALLOW_REMOTE_CIDR="${ALLOW_REMOTE_CIDR:-0.0.0.0/0}"
PASSWORD_ENCRYPTION="${PASSWORD_ENCRYPTION:-scram-sha-256}"
POSTGRES_PASSWORD="${POSTGRES_PASSWORD:-passwd}"
# 是否保留关闭防火墙逻辑；如由安全组/iptables/nftables 管控，可设为 false
CLOSE_FIREWALL="${CLOSE_FIREWALL:-true}"
# 基础生产配置
MAX_CONNECTIONS="${MAX_CONNECTIONS:-1000}"
SHARED_BUFFERS="${SHARED_BUFFERS:-auto}"
SHARED_BUFFERS_PERCENT="${SHARED_BUFFERS_PERCENT:-25}"
EFFECTIVE_CACHE_SIZE="${EFFECTIVE_CACHE_SIZE:-auto}"
EFFECTIVE_CACHE_SIZE_PERCENT="${EFFECTIVE_CACHE_SIZE_PERCENT:-75}"
MAINTENANCE_WORK_MEM="${MAINTENANCE_WORK_MEM:-auto}"
WORK_MEM="${WORK_MEM:-auto}"
HUGE_PAGES="${HUGE_PAGES:-try}"
TIMEZONE="${TIMEZONE:-Asia/Shanghai}"
DEFAULT_TEXT_SEARCH_CONFIG="${DEFAULT_TEXT_SEARCH_CONFIG:-pg_catalog.simple}"
# WAL/checkpoint/复制相关配置
WAL_LEVEL="${WAL_LEVEL:-replica}"
WAL_COMPRESSION="${WAL_COMPRESSION:-on}"
MAX_WAL_SIZE="${MAX_WAL_SIZE:-8GB}"
MIN_WAL_SIZE="${MIN_WAL_SIZE:-1GB}"
CHECKPOINT_COMPLETION_TARGET="${CHECKPOINT_COMPLETION_TARGET:-0.9}"
MAX_WAL_SENDERS="${MAX_WAL_SENDERS:-10}"
MAX_REPLICATION_SLOTS="${MAX_REPLICATION_SLOTS:-10}"
# IO/查询优化配置；默认偏 SSD/NVMe。机械盘建议调高 RANDOM_PAGE_COST 并降低 EFFECTIVE_IO_CONCURRENCY
RANDOM_PAGE_COST="${RANDOM_PAGE_COST:-1.1}"
EFFECTIVE_IO_CONCURRENCY="${EFFECTIVE_IO_CONCURRENCY:-200}"
DEFAULT_STATISTICS_TARGET="${DEFAULT_STATISTICS_TARGET:-100}"
JIT="${JIT:-off}"
# 日志配置
LOG_MIN_DURATION_STATEMENT="${LOG_MIN_DURATION_STATEMENT:-1000}"
LOG_LINE_PREFIX="${LOG_LINE_PREFIX:-%m [%p] %u@%d %r }"
LOG_FILENAME="${LOG_FILENAME:-}"
LOG_ROTATION_AGE="${LOG_ROTATION_AGE:-1d}"
LOG_ROTATION_SIZE="${LOG_ROTATION_SIZE:-100MB}"
LOG_TRUNCATE_ON_ROTATION="${LOG_TRUNCATE_ON_ROTATION:-on}"
LOG_LOCK_WAITS="${LOG_LOCK_WAITS:-on}"
DEADLOCK_TIMEOUT="${DEADLOCK_TIMEOUT:-1s}"
# autovacuum 配置
AUTOVACUUM="${AUTOVACUUM:-on}"
AUTOVACUUM_MAX_WORKERS="${AUTOVACUUM_MAX_WORKERS:-5}"
AUTOVACUUM_NAPTIME="${AUTOVACUUM_NAPTIME:-30s}"
AUTOVACUUM_VACUUM_COST_LIMIT="${AUTOVACUUM_VACUUM_COST_LIMIT:-2000}"
AUTOVACUUM_VACUUM_SCALE_FACTOR="${AUTOVACUUM_VACUUM_SCALE_FACTOR:-0.05}"
AUTOVACUUM_ANALYZE_SCALE_FACTOR="${AUTOVACUUM_ANALYZE_SCALE_FACTOR:-0.02}"
# systemd、limits、sysctl
SERVICE_NAME="${SERVICE_NAME:-}"
LIMIT_NOFILE="${LIMIT_NOFILE:-65535}"
MANAGE_LIMITS="${MANAGE_LIMITS:-true}"
MANAGE_SYSCTL="${MANAGE_SYSCTL:-true}"
SWAPPINESS="${SWAPPINESS:-1}"
SOMAXCONN="${SOMAXCONN:-1024}"
FS_FILE_MAX="${FS_FILE_MAX:-1000000}"
VM_OVERCOMMIT_MEMORY="${VM_OVERCOMMIT_MEMORY:-0}"
# 是否把 PostgreSQL 常用命令软链接到系统 PATH 中
LINK_BINARIES="${LINK_BINARIES:-true}"
BIN_LINK_DIR="${BIN_LINK_DIR:-/usr/local/bin}"
WRITE_PROFILE="${WRITE_PROFILE:-true}"
# 是否创建业务库和业务用户
CREATE_APP_USER="${CREATE_APP_USER:-false}"
APP_DB_NAME="${APP_DB_NAME:-appdb}"
APP_DB_USER="${APP_DB_USER:-appuser}"
APP_DB_PASSWORD="${APP_DB_PASSWORD:-}"
APP_HOST_CIDR="${APP_HOST_CIDR:-0.0.0.0/0}"
APP_PRIVILEGES="${APP_PRIVILEGES:-ALL PRIVILEGES}"
# 卸载行为
PURGE_PACKAGES="${PURGE_PACKAGES:-true}"
REMOVE_POSTGRES_USER="${REMOVE_POSTGRES_USER:-false}"
REMOVE_LOG_FILES="${REMOVE_LOG_FILES:-true}"
OS_ID=""
OS_VERSION_CODENAME=""
PG_MAJOR=""
NEW_CLUSTER_CREATED="false"
log() {
echo "[$(date '+%F %T')] [INFO] $*"
}
warn() {
echo "[$(date '+%F %T')] [WARN] $*" >&2
}
err() {
echo "[$(date '+%F %T')] [ERROR] $*" >&2
}
die() {
err "$*"
exit 1
}
usage() {
cat <<EOF_USAGE
用法：
sudo bash $0 install
sudo bash $0 uninstall
sudo bash $0 status
sudo bash $0 reset-password
不传参数时默认执行 install。
默认目录：
数据目录：/data/postgresql/<主版本>/<集群名>
日志目录：/data/postgresql/log
备份目录：/data/postgresql/backup
凭据文件：/data/postgresql/backup/.postgresql.auth
常用环境变量示例：
POSTGRES_VERSION=17 POSTGRES_PASSWORD='StrongPassword' sudo -E bash $0 install
POSTGRES_VERSION=17 DATA_DIR=/data/postgresql/17/main LOG_DIR=/data/postgresql/log sudo -E bash $0 install
ALLOW_REMOTE_CIDR='10.0.0.0/8' CLOSE_FIREWALL=false sudo -E bash $0 install
SHARED_BUFFERS=8GB EFFECTIVE_CACHE_SIZE=24GB MAX_CONNECTIONS=500 sudo -E bash $0 install
CREATE_APP_USER=true APP_DB_NAME=appdb APP_DB_USER=appuser APP_DB_PASSWORD='AppStrongPassword' sudo -E bash $0 install
PGDG_REPO_URL='https://apt.postgresql.org/pub/repos/apt' sudo -E bash $0 install
EOF_USAGE
}
is_true() {
[[ "${1,,}" == "true" || "${1,,}" == "yes" || "${1}" == "1" ]]
}
command_exists() {
command -v "$1" >/dev/null 2>&1
}
require_root() {
[[ "${EUID}" -eq 0 ]] || die "请使用 root 用户或 sudo 执行。"
}
confirm_yes() {
local prompt="$1"
local answer=""
read -r -p "${prompt} 输入 yes 继续：" answer || return 1
[[ "${answer}" == "yes" ]]
}
sql_escape() {
printf "%s" "$1" | sed "s/'/''/g"
}
conf_quote() {
local escaped
escaped="$(printf "%s" "$1" | sed "s/'/''/g")"
printf "'%s'" "${escaped}"
}
generate_password() {
LC_ALL=C tr -dc 'A-Za-z0-9_@#%+=-' </dev/urandom | head -c 24
echo
}
backup_file() {
local file="$1"
[[ -f "${file}" ]] || return 0
install -d -m 0750 -o root -g root "${BACKUP_DIR}"
cp -a "${file}" "${BACKUP_DIR}/$(basename "${file}").$(date '+%Y%m%d%H%M%S').bak"
}
detect_os() {
[[ -r /etc/os-release ]] || die "无法识别系统：/etc/os-release 不存在。"
. /etc/os-release
OS_ID="${ID,,}"
OS_VERSION_CODENAME="${VERSION_CODENAME:-}"
case "${OS_ID}" in
ubuntu|debian)
;;
*)
die "当前脚本仅支持 Ubuntu/Debian，当前系统：${OS_ID}。"
;;
esac
[[ -n "${OS_VERSION_CODENAME}" ]] || die "无法读取 VERSION_CODENAME。"
log "系统识别完成：${PRETTY_NAME:-${OS_ID}}，codename=${OS_VERSION_CODENAME}。"
}
normalize_version_and_paths() {
if [[ "${POSTGRES_VERSION}" =~ ^[0-9]+\.[0-9]+$ ]]; then
warn "PGDG APT 包按主版本安装，已将 POSTGRES_VERSION=${POSTGRES_VERSION} 规范化为 ${POSTGRES_VERSION%%.*}。"
POSTGRES_VERSION="${POSTGRES_VERSION%%.*}"
fi
[[ "${POSTGRES_VERSION}" =~ ^[0-9]+$ ]] || die "POSTGRES_VERSION 必须是主版本号，例如 16、17、18。"
PG_MAJOR="${POSTGRES_VERSION}"
INSTALL_PREFIX="${INSTALL_PREFIX:-/usr/lib/postgresql/${PG_MAJOR}}"
CONFIG_DIR="${CONFIG_DIR:-${CONFIG_BASE_DIR}/${PG_MAJOR}/${CLUSTER_NAME}}"
CONF_FILE="${CONF_FILE:-${CONFIG_DIR}/postgresql.conf}"
HBA_FILE="${HBA_FILE:-${CONFIG_DIR}/pg_hba.conf}"
IDENT_FILE="${IDENT_FILE:-${CONFIG_DIR}/pg_ident.conf}"
DATA_DIR="${DATA_DIR:-${PG_BASE_DIR}/${PG_MAJOR}/${CLUSTER_NAME}}"
LOG_DIR="${LOG_DIR:-${PG_BASE_DIR}/log}"
BACKUP_DIR="${BACKUP_DIR:-${PG_BASE_DIR}/backup}"
AUTH_FILE="${AUTH_FILE:-${BACKUP_DIR}/.postgresql.auth}"
LOG_FILENAME="${LOG_FILENAME:-postgresql-${PG_MAJOR}-${CLUSTER_NAME}-%Y-%m-%d.log}"
SERVICE_NAME="${SERVICE_NAME:-postgresql@${PG_MAJOR}-${CLUSTER_NAME}.service}"
PGDG_REPO_URL="${PGDG_REPO_URL%/}"
}
validate_inputs() {
[[ "${CLUSTER_NAME}" =~ ^[A-Za-z0-9_]+$ ]] || die "CLUSTER_NAME 只能包含字母、数字、下划线。"
[[ "${POSTGRES_PORT}" =~ ^[0-9]+$ ]] || die "POSTGRES_PORT 必须是数字。"
(( POSTGRES_PORT >= 1 && POSTGRES_PORT <= 65535 )) || die "POSTGRES_PORT 必须在 1 到 65535 之间。"
[[ "${MAX_CONNECTIONS}" =~ ^[0-9]+$ ]] || die "MAX_CONNECTIONS 必须是数字。"
[[ "${SHARED_BUFFERS_PERCENT}" =~ ^[0-9]+$ ]] || die "SHARED_BUFFERS_PERCENT 必须是数字。"
[[ "${EFFECTIVE_CACHE_SIZE_PERCENT}" =~ ^[0-9]+$ ]] || die "EFFECTIVE_CACHE_SIZE_PERCENT 必须是数字。"
case "${PASSWORD_ENCRYPTION}" in
scram-sha-256|md5) ;;
*) die "PASSWORD_ENCRYPTION 只能是 scram-sha-256 或 md5。" ;;
esac
case "${HUGE_PAGES}" in
on|off|try) ;;
*) die "HUGE_PAGES 只能是 on、off 或 try。" ;;
esac
if is_true "${CREATE_APP_USER}"; then
[[ "${APP_DB_NAME}" =~ ^[A-Za-z0-9_]+$ ]] || die "APP_DB_NAME 只能包含字母、数字、下划线。"
[[ "${APP_DB_USER}" =~ ^[A-Za-z0-9_]+$ ]] || die "APP_DB_USER 只能包含字母、数字、下划线。"
fi
}
install_base_dependencies() {
log "安装基础依赖。"
apt-get update -y
apt-get install -y ca-certificates curl gnupg lsb-release procps sysstat acl postgresql-common
}
disable_auto_cluster_creation() {
is_true "${DISABLE_AUTO_MAIN_CLUSTER}" || return 0
local file="/etc/postgresql-common/createcluster.conf"
[[ -f "${file}" ]] || return 0
backup_file "${file}"
if grep -Eq '^[#[:space:]]*create_main_cluster[[:space:]]*=' "${file}"; then
sed -ri 's/^[#[:space:]]*create_main_cluster[[:space:]]*=.*/create_main_cluster = false/' "${file}"
else
echo 'create_main_cluster = false' >> "${file}"
fi
log "已设置 ${file}: create_main_cluster = false，避免安装包自动创建默认数据目录。"
}
setup_pgdg_repo() {
log "配置 PostgreSQL PGDG APT 仓库：${PGDG_REPO_URL}"
install -d -m 0755 "$(dirname "${PGDG_KEY}")"
if curl -fsSL -o "${PGDG_KEY}" "${PGDG_REPO_URL}/ACCC4CF8.asc"; then
log "已从 ${PGDG_REPO_URL} 下载 PostgreSQL 仓库签名密钥。"
else
warn "从 ${PGDG_REPO_URL}/ACCC4CF8.asc 下载密钥失败，尝试官方源。"
curl -fsSL -o "${PGDG_KEY}" https://www.postgresql.org/media/keys/ACCC4CF8.asc
fi
chmod 0644 "${PGDG_KEY}"
cat > "${PGDG_SOURCE}" <<EOF_PGDG
Types: deb
URIs: ${PGDG_REPO_URL}
Suites: ${OS_VERSION_CODENAME}-pgdg
Architectures: $(dpkg --print-architecture)
Components: main ${PG_MAJOR}
Signed-By: ${PGDG_KEY}
EOF_PGDG
rm -f /var/lib/apt/lists/*apt.postgresql.org* 2>/dev/null || true
rm -f /var/lib/apt/lists/*mirrors.aliyun.com*postgresql* 2>/dev/null || true
rm -f /var/lib/apt/lists/*mirrors.cloud.aliyuncs.com*postgresql* 2>/dev/null || true
apt-get update -y
if ! apt-cache show "postgresql-${PG_MAJOR}" >/dev/null 2>&1; then
echo
warn "当前可见的 PostgreSQL 主版本包如下："
apt-cache search '^postgresql-[0-9]+$' || true
echo
die "未找到 postgresql-${PG_MAJOR} 包。请检查 PGDG_REPO_URL=${PGDG_REPO_URL} 是否同步完整，或更换 POSTGRES_VERSION。"
fi
log "已找到 postgresql-${PG_MAJOR}：$(apt-cache policy "postgresql-${PG_MAJOR}" | awk '/Candidate:/ {print $2; exit}')"
}
install_postgresql_packages() {
local packages=()
packages+=("postgresql-${PG_MAJOR}")
packages+=("postgresql-client-${PG_MAJOR}")
if apt-cache show "postgresql-contrib-${PG_MAJOR}" >/dev/null 2>&1; then
packages+=("postgresql-contrib-${PG_MAJOR}")
elif apt-cache show "postgresql-contrib" >/dev/null 2>&1; then
packages+=("postgresql-contrib")
else
warn "未找到 postgresql-contrib 包，跳过 contrib 扩展包安装。"
fi
log "安装 PostgreSQL ${PG_MAJOR} 官方二进制包：${packages[*]}。"
apt-get install -y "${packages[@]}"
[[ -x "/usr/lib/postgresql/${PG_MAJOR}/bin/postgres" ]] || die "PostgreSQL 二进制文件不存在：/usr/lib/postgresql/${PG_MAJOR}/bin/postgres。"
ln -sfn "/usr/lib/postgresql/${PG_MAJOR}" "${INSTALL_LINK}"
if [[ -d "${INSTALL_LINK}/lib" ]]; then
echo "${INSTALL_LINK}/lib" > "/etc/ld.so.conf.d/postgresql-${PG_MAJOR}.conf"
ldconfig || warn "ldconfig 执行失败，请手动检查。"
fi
}
cluster_exists() {
pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
$1 == ver && $2 == name { found=1 }
END { exit found ? 0 : 1 }
'
}
cluster_status() {
pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
$1 == ver && $2 == name { print $4; exit }
'
}
cluster_data_dir() {
pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
$1 == ver && $2 == name { print $6; exit }
'
}
cluster_port() {
pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" '
$1 == ver && $2 == name { print $3; exit }
'
}
check_port_conflict() {
local conflict
conflict="$(pg_lsclusters --no-header 2>/dev/null | awk -v ver="${PG_MAJOR}" -v name="${CLUSTER_NAME}" -v port="${POSTGRES_PORT}" '
$3 == port && !($1 == ver && $2 == name) { print $1 "/" $2 " 使用端口 " $3 }
' || true)"
[[ -z "${conflict}" ]] || die "端口 ${POSTGRES_PORT} 已被其他 PostgreSQL 集群占用：${conflict}。"
}
prepare_dirs() {
log "创建 PostgreSQL 目录。"
install -d -m 0750 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${DATA_DIR}"
install -d -m 0750 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${LOG_DIR}"
install -d -m 0755 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${RUN_DIR}" || true
install -d -m 0750 -o root -g root "${BACKUP_DIR}"
}
ensure_data_dir_safe() {
if [[ -f "${DATA_DIR}/PG_VERSION" ]]; then
return 0
fi
if find "${DATA_DIR}" -mindepth 1 -maxdepth 1 ! -name lost+found | grep -q .; then
die "数据目录 ${DATA_DIR} 非空但未检测到 PostgreSQL 初始化标记 PG_VERSION。为避免误删数据，请清空目录或更换 DATA_DIR。"
fi
}
create_or_validate_cluster() {
check_port_conflict
prepare_dirs
ensure_data_dir_safe
if cluster_exists; then
local existing_dir real_existing real_expected existing_port
existing_dir="$(cluster_data_dir)"
existing_port="$(cluster_port)"
real_existing="$(readlink -f "${existing_dir}")"
real_expected="$(readlink -f "${DATA_DIR}")"
[[ "${real_existing}" == "${real_expected}" ]] || die "已存在集群 ${PG_MAJOR}/${CLUSTER_NAME}，但数据目录为 ${existing_dir}，不是当前配置的 ${DATA_DIR}。"
[[ "${existing_port}" == "${POSTGRES_PORT}" ]] || warn "已存在集群端口为 ${existing_port}，当前配置 POSTGRES_PORT=${POSTGRES_PORT}；稍后会写入配置并重启。"
log "PostgreSQL 集群已存在：${PG_MAJOR}/${CLUSTER_NAME}。"
NEW_CLUSTER_CREATED="false"
else
log "创建 PostgreSQL 集群：${PG_MAJOR}/${CLUSTER_NAME}，数据目录：${DATA_DIR}。"
pg_createcluster "${PG_MAJOR}" "${CLUSTER_NAME}" \
--datadir="${DATA_DIR}" \
--port="${POSTGRES_PORT}" \
--start
NEW_CLUSTER_CREATED="true"
fi
[[ -f "${CONF_FILE}" ]] || die "配置文件不存在：${CONF_FILE}。"
[[ -f "${HBA_FILE}" ]] || die "认证文件不存在：${HBA_FILE}。"
}
calculate_percent_mb() {
local percent="$1"
local min_mb="$2"
local max_mb="$3"
local mem_kb mem_mb result
mem_kb="$(awk '/MemTotal/ {print $2}' /proc/meminfo)"
mem_mb=$(( mem_kb / 1024 ))
result=$(( mem_mb * percent / 100 ))
(( result >= min_mb )) || result="${min_mb}"
if [[ "${max_mb}" -gt 0 && "${result}" -gt "${max_mb}" ]]; then
result="${max_mb}"
fi
echo "${result}MB"
}
calculate_work_mem() {
if [[ "${WORK_MEM}" != "auto" ]]; then
echo "${WORK_MEM}"
return 0
fi
local mem_kb mem_mb result
mem_kb="$(awk '/MemTotal/ {print $2}' /proc/meminfo)"
mem_mb=$(( mem_kb / 1024 ))
result=$(( mem_mb / MAX_CONNECTIONS / 4 ))
(( result >= 4 )) || result=4
(( result <= 64 )) || result=64
echo "${result}MB"
}
postgresql_conf_set() {
local key="$1"
local value="$2"
local file="${CONF_FILE}"
if grep -Eq "^[#[:space:]]*${key}[[:space:]]*=" "${file}"; then
sed -ri "s|^[#[:space:]]*${key}[[:space:]]*=.*|${key} = ${value}|" "${file}"
else
echo "${key} = ${value}" >> "${file}"
fi
}
write_postgresql_conf() {
backup_file "${CONF_FILE}"
local effective_shared_buffers effective_cache effective_maintenance_mem effective_work_mem
if [[ "${SHARED_BUFFERS}" == "auto" ]]; then
effective_shared_buffers="$(calculate_percent_mb "${SHARED_BUFFERS_PERCENT}" 128 0)"
else
effective_shared_buffers="${SHARED_BUFFERS}"
fi
if [[ "${EFFECTIVE_CACHE_SIZE}" == "auto" ]]; then
effective_cache="$(calculate_percent_mb "${EFFECTIVE_CACHE_SIZE_PERCENT}" 256 0)"
else
effective_cache="${EFFECTIVE_CACHE_SIZE}"
fi
if [[ "${MAINTENANCE_WORK_MEM}" == "auto" ]]; then
effective_maintenance_mem="$(calculate_percent_mb 5 64 2048)"
else
effective_maintenance_mem="${MAINTENANCE_WORK_MEM}"
fi
effective_work_mem="$(calculate_work_mem)"
log "写入 PostgreSQL 生产配置：${CONF_FILE}。"
postgresql_conf_set "data_directory" "$(conf_quote "${DATA_DIR}")"
postgresql_conf_set "hba_file" "$(conf_quote "${HBA_FILE}")"
postgresql_conf_set "ident_file" "$(conf_quote "${IDENT_FILE}")"
postgresql_conf_set "external_pid_file" "$(conf_quote "${RUN_DIR}/${PG_MAJOR}-${CLUSTER_NAME}.pid")"
postgresql_conf_set "listen_addresses" "$(conf_quote "${LISTEN_ADDRESSES}")"
postgresql_conf_set "port" "${POSTGRES_PORT}"
postgresql_conf_set "unix_socket_directories" "$(conf_quote "${RUN_DIR}")"
postgresql_conf_set "password_encryption" "$(conf_quote "${PASSWORD_ENCRYPTION}")"
postgresql_conf_set "max_connections" "${MAX_CONNECTIONS}"
postgresql_conf_set "shared_buffers" "${effective_shared_buffers}"
postgresql_conf_set "effective_cache_size" "${effective_cache}"
postgresql_conf_set "maintenance_work_mem" "${effective_maintenance_mem}"
postgresql_conf_set "work_mem" "${effective_work_mem}"
postgresql_conf_set "huge_pages" "${HUGE_PAGES}"
postgresql_conf_set "dynamic_shared_memory_type" "posix"
postgresql_conf_set "timezone" "$(conf_quote "${TIMEZONE}")"
postgresql_conf_set "log_timezone" "$(conf_quote "${TIMEZONE}")"
postgresql_conf_set "default_text_search_config" "$(conf_quote "${DEFAULT_TEXT_SEARCH_CONFIG}")"
postgresql_conf_set "wal_level" "${WAL_LEVEL}"
postgresql_conf_set "wal_compression" "${WAL_COMPRESSION}"
postgresql_conf_set "max_wal_size" "${MAX_WAL_SIZE}"
postgresql_conf_set "min_wal_size" "${MIN_WAL_SIZE}"
postgresql_conf_set "checkpoint_completion_target" "${CHECKPOINT_COMPLETION_TARGET}"
postgresql_conf_set "max_wal_senders" "${MAX_WAL_SENDERS}"
postgresql_conf_set "max_replication_slots" "${MAX_REPLICATION_SLOTS}"
postgresql_conf_set "random_page_cost" "${RANDOM_PAGE_COST}"
postgresql_conf_set "effective_io_concurrency" "${EFFECTIVE_IO_CONCURRENCY}"
postgresql_conf_set "default_statistics_target" "${DEFAULT_STATISTICS_TARGET}"
postgresql_conf_set "jit" "${JIT}"
postgresql_conf_set "logging_collector" "on"
postgresql_conf_set "log_directory" "$(conf_quote "${LOG_DIR}")"
postgresql_conf_set "log_filename" "$(conf_quote "${LOG_FILENAME}")"
postgresql_conf_set "log_rotation_age" "${LOG_ROTATION_AGE}"
postgresql_conf_set "log_rotation_size" "${LOG_ROTATION_SIZE}"
postgresql_conf_set "log_truncate_on_rotation" "${LOG_TRUNCATE_ON_ROTATION}"
postgresql_conf_set "log_min_duration_statement" "${LOG_MIN_DURATION_STATEMENT}"
postgresql_conf_set "log_line_prefix" "$(conf_quote "${LOG_LINE_PREFIX}")"
postgresql_conf_set "log_lock_waits" "${LOG_LOCK_WAITS}"
postgresql_conf_set "deadlock_timeout" "${DEADLOCK_TIMEOUT}"
postgresql_conf_set "log_checkpoints" "on"
postgresql_conf_set "log_autovacuum_min_duration" "1000"
postgresql_conf_set "autovacuum" "${AUTOVACUUM}"
postgresql_conf_set "autovacuum_max_workers" "${AUTOVACUUM_MAX_WORKERS}"
postgresql_conf_set "autovacuum_naptime" "${AUTOVACUUM_NAPTIME}"
postgresql_conf_set "autovacuum_vacuum_cost_limit" "${AUTOVACUUM_VACUUM_COST_LIMIT}"
postgresql_conf_set "autovacuum_vacuum_scale_factor" "${AUTOVACUUM_VACUUM_SCALE_FACTOR}"
postgresql_conf_set "autovacuum_analyze_scale_factor" "${AUTOVACUUM_ANALYZE_SCALE_FACTOR}"
chown "${POSTGRES_USER}:${POSTGRES_GROUP}" "${CONF_FILE}"
chmod 0640 "${CONF_FILE}"
}
write_pg_hba() {
backup_file "${HBA_FILE}"
log "写入 PostgreSQL 认证配置：${HBA_FILE}。"
cat > "${HBA_FILE}" <<EOF_HBA
# 由 postgresql_pgdg_install.sh 生成
# local postgres 使用 peer，便于本机 root/runuser 运维
# 业务访问默认使用 ${PASSWORD_ENCRYPTION}
local all postgres peer
local all all ${PASSWORD_ENCRYPTION}
host all all 127.0.0.1/32 ${PASSWORD_ENCRYPTION}
host all all ::1/128 ${PASSWORD_ENCRYPTION}
host all all ${ALLOW_REMOTE_CIDR} ${PASSWORD_ENCRYPTION}
EOF_HBA
chown "${POSTGRES_USER}:${POSTGRES_GROUP}" "${HBA_FILE}"
chmod 0640 "${HBA_FILE}"
}
configure_limits() {
is_true "${MANAGE_LIMITS}" || return 0
log "配置 PostgreSQL 文件句柄限制。"
cat > "/etc/security/limits.d/99-${POSTGRES_USER}.conf" <<EOF_LIMITS
${POSTGRES_USER} soft nofile ${LIMIT_NOFILE}
${POSTGRES_USER} hard nofile ${LIMIT_NOFILE}
${POSTGRES_USER} soft nproc ${LIMIT_NOFILE}
${POSTGRES_USER} hard nproc ${LIMIT_NOFILE}
EOF_LIMITS
}
configure_sysctl() {
is_true "${MANAGE_SYSCTL}" || return 0
log "写入 PostgreSQL sysctl 参数。"
cat > /etc/sysctl.d/99-postgresql.conf <<EOF_SYSCTL
vm.swappiness = ${SWAPPINESS}
net.core.somaxconn = ${SOMAXCONN}
fs.file-max = ${FS_FILE_MAX}
vm.overcommit_memory = ${VM_OVERCOMMIT_MEMORY}
EOF_SYSCTL
sysctl -p /etc/sysctl.d/99-postgresql.conf >/dev/null || warn "加载 sysctl 参数失败，请手动检查 /etc/sysctl.d/99-postgresql.conf。"
}
write_systemd_override() {
local override_dir="/etc/systemd/system/${SERVICE_NAME}.d"
install -d -m 0755 "${override_dir}"
log "写入 systemd override：${override_dir}/override.conf。"
cat > "${override_dir}/override.conf" <<EOF_SYSTEMD
[Service]
LimitNOFILE=${LIMIT_NOFILE}
OOMScoreAdjust=-500
EOF_SYSTEMD
systemctl daemon-reload
}
link_binaries() {
is_true "${LINK_BINARIES}" || return 0
log "创建 PostgreSQL 常用命令软链接到 ${BIN_LINK_DIR}。"
install -d -m 0755 "${BIN_LINK_DIR}"
local bin
for bin in psql pg_dump pg_restore pg_basebackup pg_isready pg_ctl initdb postgres createdb createuser dropdb dropuser vacuumdb reindexdb clusterdb; do
if [[ -x "${INSTALL_LINK}/bin/${bin}" ]]; then
ln -sfn "${INSTALL_LINK}/bin/${bin}" "${BIN_LINK_DIR}/${bin}"
fi
done
}
write_profile() {
is_true "${WRITE_PROFILE}" || return 0
log "写入 PostgreSQL PATH 配置。"
cat > /etc/profile.d/postgresql.sh <<EOF_PROFILE
# 由 postgresql_pgdg_install.sh 生成
export PGHOME=${INSTALL_LINK}
export PGDATA=${DATA_DIR}
export PGPORT=${POSTGRES_PORT}
export PATH=${INSTALL_LINK}/bin:\$PATH
EOF_PROFILE
chmod 0644 /etc/profile.d/postgresql.sh
}
close_firewall() {
is_true "${CLOSE_FIREWALL}" || return 0
if command_exists ufw && ufw status 2>/dev/null | grep -q "active"; then
log "关闭 ufw 防火墙。"
ufw disable >/dev/null || warn "ufw disable 执行失败。"
fi
if command_exists firewall-cmd && systemctl is-active --quiet firewalld; then
log "关闭 firewalld 防火墙。"
systemctl stop firewalld || true
systemctl disable firewalld >/dev/null 2>&1 || true
fi
}
start_or_restart_cluster() {
log "启动/重启 PostgreSQL 集群：${PG_MAJOR}/${CLUSTER_NAME}。"
install -d -m 2775 -o "${POSTGRES_USER}" -g "${POSTGRES_GROUP}" "${RUN_DIR}"
if [[ "$(cluster_status || true)" == "online" ]]; then
pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" restart
else
pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" start
fi
systemctl enable postgresql >/dev/null 2>&1 || true
local i
for i in $(seq 1 60); do
if pg_isready -h 127.0.0.1 -p "${POSTGRES_PORT}" -U postgres >/dev/null 2>&1; then
log "PostgreSQL 健康检查通过。"
return 0
fi
sleep 1
done
echo
warn "PostgreSQL 健康检查失败，输出最近日志："
pg_lsclusters || true
journalctl -u "postgresql@${PG_MAJOR}-${CLUSTER_NAME}.service" --no-pager -n 120 >&2 || true
find "${LOG_DIR}" -maxdepth 1 -type f -name "postgresql-${PG_MAJOR}-${CLUSTER_NAME}-*.log" -print -exec tail -n 120 {} \; 2>/dev/null || true
die "PostgreSQL 健康检查失败。"
}
set_postgres_password() {
if [[ -z "${POSTGRES_PASSWORD}" ]]; then
POSTGRES_PASSWORD="$(generate_password)"
log "已自动生成 postgres 密码。"
fi
local escaped
escaped="$(sql_escape "${POSTGRES_PASSWORD}")"
log "设置 postgres 用户密码。"
runuser -u "${POSTGRES_USER}" -- "${INSTALL_LINK}/bin/psql" -X -v ON_ERROR_STOP=1 --dbname=postgres <<EOF_SQL
ALTER USER postgres WITH PASSWORD '${escaped}';
EOF_SQL
install -d -m 0750 -o root -g root "$(dirname "${AUTH_FILE}")"
cat > "${AUTH_FILE}" <<EOF_AUTH
PGHOST=127.0.0.1
PGPORT=${POSTGRES_PORT}
PGUSER=postgres
PGPASSWORD=${POSTGRES_PASSWORD}
PGDATABASE=postgres
PGDATA=${DATA_DIR}
PGSERVICE=${SERVICE_NAME}
EOF_AUTH
chmod 0600 "${AUTH_FILE}"
}
run_postgres_sql() {
PGPASSWORD="${POSTGRES_PASSWORD}" "${INSTALL_LINK}/bin/psql" \
-h 127.0.0.1 \
-p "${POSTGRES_PORT}" \
-U postgres \
-d postgres \
-X \
-v ON_ERROR_STOP=1
}
create_app_database() {
is_true "${CREATE_APP_USER}" || return 0
if [[ -z "${APP_DB_PASSWORD}" ]]; then
APP_DB_PASSWORD="$(generate_password)"
log "已自动生成业务用户密码。"
fi
local escaped_app_password
escaped_app_password="$(sql_escape "${APP_DB_PASSWORD}")"
log "创建业务库和业务用户：${APP_DB_NAME} / ${APP_DB_USER}。"
run_postgres_sql <<EOF_SQL
DO \$\$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '${APP_DB_USER}') THEN
CREATE ROLE ${APP_DB_USER} LOGIN PASSWORD '${escaped_app_password}';
ELSE
ALTER ROLE ${APP_DB_USER} WITH LOGIN PASSWORD '${escaped_app_password}';
END IF;
END
\$\$;
SELECT 'CREATE DATABASE ${APP_DB_NAME} OWNER ${APP_DB_USER}'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = '${APP_DB_NAME}')\gexec
GRANT ${APP_PRIVILEGES} ON DATABASE ${APP_DB_NAME} TO ${APP_DB_USER};
EOF_SQL
if ! grep -q '^APP_DB_NAME=' "${AUTH_FILE}" 2>/dev/null; then
cat >> "${AUTH_FILE}" <<EOF_APP_AUTH
APP_DB_NAME=${APP_DB_NAME}
APP_DB_USER=${APP_DB_USER}
APP_DB_PASSWORD=${APP_DB_PASSWORD}
APP_HOST_CIDR=${APP_HOST_CIDR}
EOF_APP_AUTH
chmod 0600 "${AUTH_FILE}"
fi
if ! grep -Eq "^host[[:space:]]+${APP_DB_NAME}[[:space:]]+${APP_DB_USER}[[:space:]]+${APP_HOST_CIDR}[[:space:]]+" "${HBA_FILE}"; then
cat >> "${HBA_FILE}" <<EOF_APP_HBA
host ${APP_DB_NAME} ${APP_DB_USER} ${APP_HOST_CIDR} ${PASSWORD_ENCRYPTION}
EOF_APP_HBA
pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" reload
fi
}
print_result() {
local effective_shared effective_cache effective_work_mem
effective_shared="$(awk -F= '/^[[:space:]]*shared_buffers[[:space:]]*=/{gsub(/[[:space:]]/,"",$2); print $2}' "${CONF_FILE}" | tail -n1)"
effective_cache="$(awk -F= '/^[[:space:]]*effective_cache_size[[:space:]]*=/{gsub(/[[:space:]]/,"",$2); print $2}' "${CONF_FILE}" | tail -n1)"
effective_work_mem="$(awk -F= '/^[[:space:]]*work_mem[[:space:]]*=/{gsub(/[[:space:]]/,"",$2); print $2}' "${CONF_FILE}" | tail -n1)"
cat <<EOF_RESULT
PostgreSQL 安装完成。
版本：${PG_MAJOR}
服务：${SERVICE_NAME}
软件目录：/usr/lib/postgresql/${PG_MAJOR}
软链接：${INSTALL_LINK}
命令目录：${INSTALL_LINK}/bin
配置目录：${CONFIG_DIR}
配置文件：${CONF_FILE}
认证文件：${HBA_FILE}
数据目录：${DATA_DIR}
日志目录：${LOG_DIR}
备份目录：${BACKUP_DIR}
运行目录：${RUN_DIR}
端口：${POSTGRES_PORT}
监听：${LISTEN_ADDRESSES}
允许访问：${ALLOW_REMOTE_CIDR}
shared_buffers：${effective_shared}
effective_cache_size：${effective_cache}
work_mem：${effective_work_mem}
凭据文件：${AUTH_FILE}
常用命令：
systemctl status ${SERVICE_NAME}
systemctl restart ${SERVICE_NAME}
journalctl -u ${SERVICE_NAME} -f
psql -h 127.0.0.1 -p ${POSTGRES_PORT} -U postgres -d postgres
pg_lsclusters
EOF_RESULT
if is_true "${CREATE_APP_USER}"; then
cat <<EOF_APP_RESULT
业务库：
数据库：${APP_DB_NAME}
用户：${APP_DB_USER}
密码：已写入 ${AUTH_FILE}
EOF_APP_RESULT
fi
}
install_postgresql() {
require_root
normalize_version_and_paths
validate_inputs
detect_os
install_base_dependencies
disable_auto_cluster_creation
setup_pgdg_repo
install_postgresql_packages
configure_limits
configure_sysctl
link_binaries
write_profile
close_firewall
create_or_validate_cluster
write_postgresql_conf
write_pg_hba
write_systemd_override
start_or_restart_cluster
set_postgres_password
start_or_restart_cluster
create_app_database
print_result
}
reset_password() {
require_root
normalize_version_and_paths
validate_inputs
command_exists pg_lsclusters || die "未找到 pg_lsclusters，请先安装 PostgreSQL。"
cluster_exists || die "未找到 PostgreSQL 集群 ${PG_MAJOR}/${CLUSTER_NAME}。"
if [[ "$(cluster_status || true)" != "online" ]]; then
pg_ctlcluster "${PG_MAJOR}" "${CLUSTER_NAME}" start
fi
set_postgres_password
log "postgres 密码重置完成，凭据已写入 ${AUTH_FILE}。"
}
status_postgresql() {
require_root
normalize_version_and_paths
detect_os
echo
pg_lsclusters || true
echo
if command_exists systemctl; then
systemctl --no-pager --full status "${SERVICE_NAME}" || true
fi
}
uninstall_postgresql() {
require_root
normalize_version_and_paths
detect_os
echo
warn "即将卸载 PostgreSQL ${PG_MAJOR}/${CLUSTER_NAME}。"
warn "服务：${SERVICE_NAME}"
warn "配置目录：${CONFIG_DIR}"
warn "数据目录：${DATA_DIR}"
warn "日志目录：${LOG_DIR}"
warn "备份目录：${BACKUP_DIR}"
echo
if ! confirm_yes "第一次确认：是否真的卸载 PostgreSQL？"; then
log "用户取消卸载。"
exit 0
fi
if command_exists pg_lsclusters && cluster_exists; then
log "删除 PostgreSQL 集群：${PG_MAJOR}/${CLUSTER_NAME}。"
pg_dropcluster --stop "${PG_MAJOR}" "${CLUSTER_NAME}"
else
warn "未检测到集群 ${PG_MAJOR}/${CLUSTER_NAME}，跳过 pg_dropcluster。"
systemctl stop "${SERVICE_NAME}" 2>/dev/null || true
systemctl disable "${SERVICE_NAME}" 2>/dev/null || true
fi
log "清理 systemd override。"
rm -rf "/etc/systemd/system/${SERVICE_NAME}.d"
systemctl daemon-reload || true
systemctl reset-failed || true
echo
warn "第二次确认将删除 PostgreSQL 相关文件。"
warn "将删除：${INSTALL_LINK}、${CONFIG_DIR}、${DATA_DIR}、${AUTH_FILE}、profile、limits、sysctl、ldconfig、命令软链接。"
if is_true "${REMOVE_LOG_FILES}"; then
warn "还将删除匹配 ${LOG_DIR}/postgresql-${PG_MAJOR}-${CLUSTER_NAME}-*.log 的日志文件。"
fi
echo
if ! confirm_yes "第二次确认：是否删除配置、数据和脚本生成的文件？"; then
log "用户选择保留文件。卸载已完成：集群已停止并移除，文件未删除。"
exit 0
fi
log "清理 PostgreSQL 命令软链接。"
local bin
for bin in psql pg_dump pg_restore pg_basebackup pg_isready pg_ctl initdb postgres createdb createuser dropdb dropuser vacuumdb reindexdb clusterdb; do
if [[ -L "${BIN_LINK_DIR}/${bin}" ]] && readlink "${BIN_LINK_DIR}/${bin}" | grep -q "${INSTALL_LINK}/bin/"; then
rm -f "${BIN_LINK_DIR}/${bin}"
fi
done
log "清理 PostgreSQL 文件。"
rm -f "${INSTALL_LINK}"
rm -rf "${CONFIG_DIR}"
rm -rf "${DATA_DIR}"
rm -f "${AUTH_FILE}"
rm -f /etc/profile.d/postgresql.sh
rm -f "/etc/security/limits.d/99-${POSTGRES_USER}.conf"
rm -f /etc/sysctl.d/99-postgresql.conf
rm -f "/etc/ld.so.conf.d/postgresql-${PG_MAJOR}.conf"
ldconfig || true
if is_true "${REMOVE_LOG_FILES}"; then
find "${LOG_DIR}" -maxdepth 1 -type f -name "postgresql-${PG_MAJOR}-${CLUSTER_NAME}-*.log" -delete 2>/dev/null || true
fi
if is_true "${PURGE_PACKAGES}"; then
log "卸载 PostgreSQL ${PG_MAJOR} 软件包。"
apt-get purge -y "postgresql-${PG_MAJOR}" "postgresql-client-${PG_MAJOR}" || true
apt-get autoremove -y || true
fi
if is_true "${REMOVE_POSTGRES_USER}"; then
warn "删除 postgres 用户可能影响其他 PostgreSQL 版本或集群。"
userdel "${POSTGRES_USER}" 2>/dev/null || true
groupdel "${POSTGRES_GROUP}" 2>/dev/null || true
fi
log "PostgreSQL 卸载完成。"
}
main() {
local action="${1:-install}"
case "${action}" in
install)
install_postgresql
;;
uninstall|remove|purge)
uninstall_postgresql
;;
status)
status_postgresql
;;
reset-password|resetpwd)
reset_password
;;
-h|--help|help)
usage
;;
*)
usage
die "不支持的参数：${action}。"
;;
esac
}
main "$@"
2.2 手动配置脚本参数
脚本中的参数都集中在开头部分，正常情况下只需要关注少量核心参数，不需要修改全部配置。
| 参数 | 默认值 | 作用 |
|---|---|---|
POSTGRES_VERSION | 16 | PostgreSQL 主版本 |
CLUSTER_NAME | main | PostgreSQL 集群名称 |
PG_BASE_DIR | /data/postgresql | PostgreSQL 数据、日志、备份的根目录 |
POSTGRES_PORT | 5432 | PostgreSQL 监听端口 |
LISTEN_ADDRESSES | 0.0.0.0 | PostgreSQL 监听地址 |
ALLOW_REMOTE_CIDR | 0.0.0.0/0 | 允许访问 PostgreSQL 的远程网段 |
POSTGRES_PASSWORD | passwd | postgres 用户密码 |
PASSWORD_ENCRYPTION | scram-sha-256 | 密码认证方式 |
PGDG_REPO_URL | https://mirrors.aliyun.com/postgresql/repos/apt | PostgreSQL APT 源地址 |
CREATE_APP_USER | false | 是否创建业务库和业务用户 |
APP_DB_NAME | appdb | 业务数据库名称 |
APP_DB_USER | appuser | 业务数据库用户 |
APP_DB_PASSWORD | 空 | 业务数据库用户密码 |
如果只做基础安装，一般只需要修改以下几个参数：
POSTGRES_VERSION="${POSTGRES_VERSION:-16}"
POSTGRES_PORT="${POSTGRES_PORT:-5432}"
LISTEN_ADDRESSES="${LISTEN_ADDRESSES:-0.0.0.0}"
ALLOW_REMOTE_CIDR="${ALLOW_REMOTE_CIDR:-0.0.0.0/0}"
POSTGRES_PASSWORD="${POSTGRES_PASSWORD:-passwd}"
2.3 脚本执行流程
脚本执行安装时，整体流程如下：
| 步骤 | 说明 |
|---|---|
| 1 | 检查是否使用 root 或 sudo 执行 |
| 2 | 识别当前系统版本，只支持 Ubuntu / Debian |
| 3 | 安装基础依赖包 |
| 4 | 配置 PostgreSQL PGDG APT 源 |
| 5 | 安装 PostgreSQL 指定版本的软件包 |
| 6 | 创建 PostgreSQL 数据目录、日志目录、备份目录 |
| 7 | 创建或检查 PostgreSQL 集群 |
| 8 | 写入 postgresql.conf 主配置 |
| 9 | 写入 pg_hba.conf 访问控制配置 |
| 10 | 配置 systemd、limits、sysctl 等基础系统参数 |
| 11 | 启动或重启 PostgreSQL 服务 |
| 12 | 设置 postgres 用户密码 |
| 13 | 根据参数决定是否创建业务库和业务用户 |
| 14 | 输出安装结果和常用命令 |
脚本默认执行的是安装流程：
sudo bash postgresql_pgdg_install.sh install
不传参数时，也会默认执行安装：
sudo bash postgresql_pgdg_install.sh
查看状态：
sudo bash postgresql_pgdg_install.sh status
重置 postgres 用户密码：
POSTGRES_PASSWORD='NewStrongPassword' sudo -E bash postgresql_pgdg_install.sh reset-password
卸载 PostgreSQL：
sudo bash postgresql_pgdg_install.sh uninstall
注意：卸载操作会涉及停止集群、删除配置、删除数据目录等动作，生产环境不要随意执行。
2.4 执行脚本测试
./psql_install.sh install
[2026-05-15 10:56:25] [INFO] 系统识别完成：Ubuntu 22.04.5 LTS，codename=jammy。
[2026-05-15 10:56:25] [INFO] 安装基础依赖。
Hit:1 http://mirrors.tencentyun.com/ubuntu jammy InRelease
Hit:2 http://mirrors.tencentyun.com/ubuntu jammy-updates InRelease
Hit:3 http://mirrors.tencentyun.com/ubuntu jammy-security InRelease
...... 自动安装过程省略......
[2026-05-15 10:57:07] [INFO] 写入 PostgreSQL 生产配置：/etc/postgresql/16/main/postgresql.conf。
[2026-05-15 10:57:07] [INFO] 写入 PostgreSQL 认证配置：/etc/postgresql/16/main/pg_hba.conf。
[2026-05-15 10:57:07] [INFO] 写入 systemd override：/etc/systemd/system/postgresql@16-main.service.d/override.conf。
[2026-05-15 10:57:07] [INFO] 启动/重启 PostgreSQL 集群：16/main。
[2026-05-15 10:57:11] [INFO] PostgreSQL 健康检查通过。
[2026-05-15 10:57:11] [INFO] 设置 postgres 用户密码。
ALTER ROLE
[2026-05-15 10:57:11] [INFO] 启动/重启 PostgreSQL 集群：16/main。
[2026-05-15 10:57:14] [INFO] PostgreSQL 健康检查通过。
PostgreSQL 安装完成。
版本：16
服务：postgresql@16-main.service
软件目录：/usr/lib/postgresql/16
软链接：/usr/local/pgsql
命令目录：/usr/local/pgsql/bin
配置目录：/etc/postgresql/16/main
配置文件：/etc/postgresql/16/main/postgresql.conf
认证文件：/etc/postgresql/16/main/pg_hba.conf
数据目录：/data/postgresql/16/main
日志目录：/data/postgresql/log
备份目录：/data/postgresql/backup
运行目录：/run/postgresql
端口：5432
监听：0.0.0.0
允许访问：0.0.0.0/0
shared_buffers：1904MB
effective_cache_size：5714MB
work_mem：4MB
凭据文件：/data/postgresql/backup/.postgresql.auth
常用命令：
systemctl status postgresql@16-main.service
systemctl restart postgresql@16-main.service
journalctl -u postgresql@16-main.service -f
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
pg_lsclusters
安装完成。
如果无法登陆，检查以下内容：
| 检查项 | 命令 |
|---|---|
| 服务是否运行 | pg_lsclusters |
| 端口是否监听 | `ss -lnpt |
| 访问网段是否放行 | cat /data/postgresql/backup/.postgresql.auth 和 cat /etc/postgresql/16/main/pg_hba.conf |
| 防火墙是否放行 | ufw status 或 nft list ruleset |
| 云安全组是否放行 | 检查云厂商安全组 5432 端口 |
| 密码是否正确 | 使用 reset-password 重置密码 |
3、Docker 安装
Docker 安装 PostgreSQL 比较简单，核心就是：
- 指定镜像版本；
- 设置初始化用户、密码、数据库；
- 挂载数据目录，保证容器删除后数据不丢；
- 映射
5432端口；
启动后测试连接。
这里以 PostgreSQL 16 为例。
3.1 准备数据目录
mkdir -p /data/postgresql/docker/data
mkdir -p /data/postgresql/docker/backup
chmod 700 /data/postgresql/docker/data
目录说明：
| 目录 | 作用 |
|---|---|
/data/postgresql/docker/data | PostgreSQL 数据目录 |
/data/postgresql/docker/backup | 后续备份目录 |
3.2 Docker 直接启动
使用 docker run 启动 PostgreSQL：
docker run -d \
--name postgres16 \
--restart always \
-p 5432:5432 \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD='你的强密码' \
-e POSTGRES_DB=postgres \
-e TZ=Asia/Shanghai \
-v /data/postgresql/docker/data:/var/lib/postgresql/data \
postgres:16
参数说明：
| 参数 | 作用 |
|---|---|
--name postgres16 | 容器名称 |
--restart always | 容器异常退出或服务器重启后自动拉起 |
-p 5432:5432 | 将宿主机 5432 映射到容器 5432 |
POSTGRES_USER | 初始化数据库用户 |
POSTGRES_PASSWORD | 初始化数据库用户密码 |
POSTGRES_DB | 初始化默认数据库 |
TZ=Asia/Shanghai | 设置容器时区 |
-v /data/postgresql/docker/data:/var/lib/postgresql/data | 持久化数据库数据 |
postgres:16 | 使用 PostgreSQL 16 镜像 |
查看容器状态：
docker ps | grep postgres16
查看日志：
docker logs -f postgres16
进入容器：
docker exec -it postgres16 bash
进入数据库：
docker exec -it postgres16 psql -U postgres -d postgres
3.3 Docker Compose 启动
如果后续需要长期维护，推荐使用 docker-compose.yml 管理。
创建目录：
mkdir -p /data/postgresql/docker-compose
cd /data/postgresql/docker-compose
编写 docker-compose.yml：
version: "3.8"
services:
postgres:
image: postgres:16
container_name: postgres16
restart: always
ports:
- "5432:5432"
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: "你的强密码"
POSTGRES_DB: postgres
TZ: Asia/Shanghai
volumes:
- /data/postgresql/docker/data:/var/lib/postgresql/data
- /data/postgresql/docker/backup:/backup
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
interval: 10s
timeout: 5s
retries: 5
启动：
docker compose up -d
如果服务器使用的是老版本 docker-compose，则执行：
docker-compose up -d
查看服务：
docker compose ps
查看日志：
docker compose logs -f postgres
停止服务：
docker compose down
重启服务：
docker compose restart postgres
3.4 登录验证
本机连接测试：
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
如果宿主机没有安装 psql 客户端，也可以直接进入容器测试：
docker exec -it postgres16 psql -U postgres -d postgres
进入数据库后执行：
\l
\du
SELECT version();
\q
3.5 创建业务库和业务用户
进入数据库：
docker exec -it postgres16 psql -U postgres -d postgres
创建业务用户和数据库：
CREATE USER appuser WITH PASSWORD '业务用户强密码';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\c appdb
GRANT USAGE, CREATE ON SCHEMA public TO appuser;
ALTER SCHEMA public OWNER TO appuser;
\q
业务连接方式：
psql -h PostgreSQL服务器IP -p 5432 -U appuser -d appdb
3.6 常用 Docker 管理命令
# 查看容器
docker ps | grep postgres16
# 查看日志
docker logs -f postgres16
# 重启容器
docker restart postgres16
# 停止容器
docker stop postgres16
# 启动容器
docker start postgres16
# 进入容器
docker exec -it postgres16 bash
# 进入数据库
docker exec -it postgres16 psql -U postgres -d postgres
3.7 数据备份和恢复
备份数据库：
docker exec -t postgres16 pg_dump -U postgres -d appdb > /data/postgresql/docker/backup/appdb.sql
恢复数据库：
cat /data/postgresql/docker/backup/appdb.sql | docker exec -i postgres16 psql -U postgres -d appdb
备份为自定义格式：
docker exec -t postgres16 pg_dump -U postgres -d appdb -F c > /data/postgresql/docker/backup/appdb.dump
恢复自定义格式：
cat /data/postgresql/docker/backup/appdb.dump | docker exec -i postgres16 pg_restore -U postgres -d appdb
3.8 注意事项
- 不建议使用
postgres:latest，生产环境应固定版本，例如postgres:16。 - 一定要挂载数据目录，否则容器删除后数据会丢失。
- 不建议将
5432直接暴露到公网。 - 生产环境建议使用独立业务用户，不要让业务直接使用
postgres超级用户。 POSTGRES_PASSWORD只在第一次初始化数据目录时生效，如果数据目录已经存在，修改环境变量不会自动修改数据库密码。- 如果需要修改密码，应进入数据库执行：
ALTER USER postgres WITH PASSWORD '新密码';
3.9 Docker 安装方式总结
| 安装方式 | 适用场景 | 特点 |
|---|---|---|
docker run | 临时测试、快速验证 | 命令简单，但后续维护不方便 |
docker compose | 长期运行、标准化部署 | 配置清晰，便于维护和迁移 |
如果只是测试 PostgreSQL，使用 docker run 即可。
如果是长期部署，建议使用 docker compose。
三、PostgreSQL 常用命令与 SQL 语句
1、数据库运维命令
1.1 服务管理命令
# 查看 PostgreSQL 总服务状态
systemctl status postgresql
# 查看 PostgreSQL 16/main 实例状态
systemctl status postgresql@16-main.service
# 启动 PostgreSQL 16/main
systemctl start postgresql@16-main.service
# 停止 PostgreSQL 16/main
systemctl stop postgresql@16-main.service
# 重启 PostgreSQL 16/main
systemctl restart postgresql@16-main.service
# 设置开机自启
systemctl enable postgresql
# 查看 PostgreSQL 服务日志
journalctl -u postgresql@16-main.service -f
| 命令 | 作用 |
|---|---|
systemctl status postgresql | 查看 PostgreSQL 总管理服务状态 |
systemctl status postgresql@16-main.service | 查看具体数据库实例状态 |
systemctl start | 启动 PostgreSQL |
systemctl stop | 停止 PostgreSQL |
systemctl restart | 重启 PostgreSQL |
systemctl enable | 设置开机自启 |
journalctl -u | 查看 systemd 日志 |
1.2 集群管理命令
# 查看当前 PostgreSQL 集群
pg_lsclusters
# 启动指定集群
pg_ctlcluster 16 main start
# 停止指定集群
pg_ctlcluster 16 main stop
# 重启指定集群
pg_ctlcluster 16 main restart
# 重载配置，不中断数据库连接
pg_ctlcluster 16 main reload
| 命令 | 作用 |
|---|---|
pg_lsclusters | 查看当前机器上的 PostgreSQL 集群 |
pg_ctlcluster 16 main start | 启动 PostgreSQL 16/main 集群 |
pg_ctlcluster 16 main stop | 停止 PostgreSQL 16/main 集群 |
pg_ctlcluster 16 main restart | 重启 PostgreSQL 16/main 集群 |
pg_ctlcluster 16 main reload | 重载配置文件，常用于修改 pg_hba.conf 后生效 |
1.3 登陆数据库
# 切换到 postgres 系统用户
su - postgres
# 登录 PostgreSQL
psql
# 指定主机、端口、用户、数据库登录
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
# 退出 psql
\q
| 命令 | 作用 |
|---|---|
su - postgres | 切换到 PostgreSQL 默认系统用户 |
psql | 进入 PostgreSQL 命令行 |
-h | 指定数据库地址 |
-p | 指定端口 |
-U | 指定数据库用户 |
-d | 指定数据库名称 |
\q | 退出 PostgreSQL 命令行 |
1.4 psql 常用内部命令
进入psql库后，可以使用以下命令：
-- 查看数据库列表
\l
-- 查看用户 / 角色
\du
-- 查看当前连接信息
\conninfo
-- 查看当前数据库下的表
\dt
-- 查看表结构
\d 表名
-- 查看所有 schema
\dn
-- 切换数据库
\c 数据库名
-- 查看 psql 帮助
\?
-- 查看 SQL 帮助
\h
-- 退出
\q
| 命令 | 作用 |
|---|---|
\l | 查看数据库列表 |
\du | 查看用户和角色 |
\conninfo | 查看当前连接信息 |
\dt | 查看当前数据库中的表 |
\d 表名 | 查看指定表结构 |
\dn | 查看 schema |
\c 数据库名 | 切换数据库 |
\? | 查看 psql 命令帮助 |
\h | 查看 SQL 语法帮助 |
\q | 退出 psql |
1.5 数据库和用户管理
-- 创建数据库
CREATE DATABASE testdb;
-- 删除数据库
DROP DATABASE testdb;
-- 创建用户
CREATE USER testuser WITH PASSWORD 'StrongPassword';
-- 修改用户密码
ALTER USER testuser WITH PASSWORD 'NewStrongPassword';
-- 给用户授权数据库
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
-- 删除用户
DROP USER testuser;
| SQL | 作用 |
|---|---|
CREATE DATABASE | 创建数据库 |
DROP DATABASE | 删除数据库 |
CREATE USER | 创建数据库用户 |
ALTER USER ... PASSWORD | 修改用户密码 |
GRANT ALL PRIVILEGES | 授权用户访问数据库 |
DROP USER | 删除用户 |
2、数据库增删改查SQL语句
2.1 创建测试表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
| 字段 | 说明 |
|---|---|
id | 自增主键 |
username | 用户名，不能为空 |
age | 年龄 |
email | 邮箱 |
created_at | 创建时间，默认当前时间 |
2.2 插入数据
INSERT INTO users (username, age, email)
VALUES ('zhangsan', 25, 'zhangsan@example.com');
INSERT INTO users (username, age, email)
VALUES
('lisi', 28, 'lisi@example.com'),
('wangwu', 30, 'wangwu@example.com');
| SQL | 作用 |
|---|---|
INSERT INTO | 插入数据 |
VALUES | 指定插入的值 |
多行 VALUES | 一次插入多条数据 |
2.3 查询数据
-- 查询所有数据
SELECT * FROM users;
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
1 | zhangsan | 25 | zhangsan@example.com | 2026-05-15 11:10:37.106878
2 | lisi | 28 | lisi@example.com | 2026-05-15 11:10:37.108108
3 | wangwu | 30 | wangwu@example.com | 2026-05-15 11:10:37.108108
(3 rows)
-- 查询指定字段
SELECT id, username, email FROM users;
id | username | email
----+----------+----------------------
1 | zhangsan | zhangsan@example.com
2 | lisi | lisi@example.com
3 | wangwu | wangwu@example.com
(3 rows)
-- 条件查询
SELECT * FROM users WHERE age > 25;
id | username | age | email | created_at
----+----------+-----+--------------------+----------------------------
2 | lisi | 28 | lisi@example.com | 2026-05-15 11:10:37.108108
3 | wangwu | 30 | wangwu@example.com | 2026-05-15 11:10:37.108108
(2 rows)
-- 模糊查询
SELECT * FROM users WHERE username LIKE '%zhang%';
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
1 | zhangsan | 25 | zhangsan@example.com | 2026-05-15 11:10:37.106878
(1 row)
-- 排序查询
SELECT * FROM users ORDER BY id DESC;
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
3 | wangwu | 30 | wangwu@example.com | 2026-05-15 11:10:37.108108
2 | lisi | 28 | lisi@example.com | 2026-05-15 11:10:37.108108
1 | zhangsan | 25 | zhangsan@example.com | 2026-05-15 11:10:37.106878
(3 rows)
-- 限制返回条数
SELECT * FROM users LIMIT 10;
-- 分页查询
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
| SQL | 作用 |
|---|---|
SELECT * | 查询所有字段 |
WHERE | 条件过滤 |
LIKE | 模糊匹配 |
ORDER BY | 排序 |
LIMIT | 限制返回条数 |
OFFSET | 跳过指定条数，常用于分页 |
2.4 修改数据
-- 修改单个字段
UPDATE users
SET age = 26
WHERE username = 'zhangsan';
-- 验证 25->26
SELECT * FROM users WHERE username LIKE '%zhang%';
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
1 | zhangsan | 26 | zhangsan@example.com | 2026-05-15 11:10:37.106878
(1 row)
-- 修改多个字段
UPDATE users
SET age = 29,
email = 'new_lisi@example.com'
WHERE username = 'lisi';
-- 验证 多条数据改变
SELECT * FROM users WHERE username LIKE '%lisi%';
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
2 | lisi | 29 | new_lisi@example.com | 2026-05-15 11:10:37.108108
(1 row)
| SQL | 作用 |
|---|---|
UPDATE | 修改数据 |
SET | 设置新的字段值 |
WHERE | 指定修改条件 |
执行 UPDATE 时一定要带 WHERE 条件，否则会更新整张表。
2.5 删除数据
-- 删除指定用户
DELETE FROM users
WHERE username = 'wangwu';
-- 验证
SELECT * FROM users;
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
1 | zhangsan | 26 | zhangsan@example.com | 2026-05-15 11:10:37.106878
2 | lisi | 29 | new_lisi@example.com | 2026-05-15 11:10:37.108108
(2 rows)
-- 删除年龄小于 18 的用户
DELETE FROM users
WHERE age < 18;
| SQL | 作用 |
|---|---|
DELETE FROM | 删除数据 |
WHERE | 指定删除条件 |
注意：
执行 DELETE 时一定要确认 WHERE 条件，否则会删除整张表数据。
2.6 修改表结构
-- 增加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 验证
\d users
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
username | character varying(50) | | not null |
age | integer | | |
email | character varying(100) | | |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
phone | character varying(20) | | |
-- 修改字段类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(50);
-- 删除字段
ALTER TABLE users DROP COLUMN phone;
-- 重命名字段
ALTER TABLE users RENAME COLUMN username TO name;
-- 重命名表
ALTER TABLE users RENAME TO user_info;
| SQL | 作用 |
|---|---|
ALTER TABLE ... ADD COLUMN | 增加字段 |
ALTER TABLE ... ALTER COLUMN | 修改字段类型 |
ALTER TABLE ... DROP COLUMN | 删除字段 |
RENAME COLUMN | 重命名字段 |
RENAME TO | 重命名表 |
2.7 创建和删除索引
-- 创建普通索引
CREATE INDEX idx_users_username ON users(username);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- 删除索引
DROP INDEX idx_users_username;
| SQL | 作用 |
|---|---|
CREATE INDEX | 创建普通索引 |
CREATE UNIQUE INDEX | 创建唯一索引 |
DROP INDEX | 删除索引 |
索引可以提高查询速度，但也会增加写入成本。常见适合建索引的字段包括：
WHERE 条件字段
JOIN 关联字段
ORDER BY 排序字段
高频查询字段
2.8 事务操作
-- 开启事务
BEGIN;
-- 执行修改
UPDATE users SET age = 31 WHERE username = 'zhangsan';
-- 提交事务
COMMIT;
如果发现操作有问题，可以回滚：
BEGIN;
DELETE FROM users WHERE username = 'lisi';
ROLLBACK;
| SQL | 作用 |
|---|---|
BEGIN | 开启事务 |
COMMIT | 提交事务 |
ROLLBACK | 回滚事务 |
3、数据库内部状态查询SQL语句
3.1 查看当前数据库版本
SELECT version();
PostgreSQL 16.14 (Ubuntu 16.14-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)
作用：查看当前 PostgreSQL 版本信息。
3.2 查看当前数据库和用户
SELECT current_database();
SELECT current_user;
SELECT inet_client_addr();
SELECT inet_server_addr();
| SQL | 作用 |
|---|---|
current_database() | 查看当前数据库 |
current_user | 查看当前登录用户 |
inet_client_addr() | 查看客户端 IP |
inet_server_addr() | 查看服务端 IP |
3.3 查看当前连接
SELECT
pid,
usename,
datname,
client_addr,
state,
backend_start,
query_start,
query
FROM pg_stat_activity
ORDER BY query_start DESC;
字段说明：
| 字段 | 说明 |
|---|---|
pid | PostgreSQL 后端进程 ID |
usename | 数据库用户 |
datname | 数据库名称 |
client_addr | 客户端 IP |
state | 当前连接状态 |
backend_start | 连接创建时间 |
query_start | SQL 开始执行时间 |
query | 当前 SQL |
3.4 查看正在执行的 SQL
SELECT
pid,
usename,
datname,
client_addr,
now() - query_start AS running_time,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY running_time DESC;
作用：查看当前正在执行的 SQL，并按执行时间排序。
3.5 查看空闲连接
SELECT
pid,
usename,
datname,
client_addr,
state,
now() - state_change AS idle_time
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_time DESC;
作用：查看空闲连接，排查连接池是否释放连接异常。
3.6 查看连接数统计
SELECT
datname,
COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY datname
ORDER BY connection_count DESC;
作用：按数据库统计当前连接数。
查看最大连接数配置：
SHOW max_connections;
3.7 查看锁等待
SELECT
pid,
usename,
datname,
wait_event_type,
wait_event,
state,
query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;
作用：查看当前是否存在等待事件，例如锁等待、IO 等待。
3.8 查看阻塞关系
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking
ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
作用：查看谁被阻塞、谁在阻塞别人。
3.9 查看数据库大小
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
作用：查看每个数据库占用空间。
3.10 查看表大小
SELECT
schemaname,
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
字段说明：
| 字段 | 说明 |
|---|---|
total_size | 表总大小，包括索引 |
table_size | 表数据大小 |
index_size | 索引大小 |
3.11 查看表行数估算
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
作用：快速查看业务表的大概数据量。
注意：
这里是估算值，不是精确 COUNT(*)。大表不要频繁执行全表 COUNT(*)。
3.12 查看数据库命中率
SELECT
datname,
blks_hit,
blks_read,
ROUND(
blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0),
2
) AS cache_hit_ratio
FROM pg_stat_database
WHERE blks_hit + blks_read > 0
ORDER BY cache_hit_ratio ASC;
作用：查看数据库缓存命中率。
一般来说，缓存命中率长期偏低，可能说明：
- 内存不足
- SQL 扫描数据过多
- 索引设计不合理
- 业务访问数据范围过大
3.13 查看事务提交和回滚情况
SELECT
datname,
xact_commit,
xact_rollback,
ROUND(
xact_rollback * 100.0 / NULLIF(xact_commit + xact_rollback, 0),
2
) AS rollback_ratio
FROM pg_stat_database
ORDER BY rollback_ratio DESC;
作用：查看事务提交和回滚比例。
如果回滚比例过高，可能说明：
- 业务异常较多
- SQL 执行失败较多
- 事务控制不合理
- 应用连接数据库存在错误
3.14 查看慢 SQL 配置
SHOW log_min_duration_statement;
作用：查看当前慢 SQL 记录阈值。
例如：
1000
表示记录执行时间超过 1000ms 的 SQL。
3.15 查看常用配置参数
SHOW listen_addresses;
SHOW port;
SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;
SHOW timezone;
SHOW log_timezone;
作用：查看当前 PostgreSQL 主要配置是否生效。
3.16 取消正在执行的 SQL
SELECT pg_cancel_backend(pid);
示例：
SELECT pg_cancel_backend(12345);
作用：取消指定 PID 正在执行的 SQL。
如果取消无效，可以终止连接：
SELECT pg_terminate_backend(12345);
区别：
| 命令 | 作用 |
|---|---|
pg_cancel_backend(pid) | 只取消当前 SQL，连接还在 |
pg_terminate_backend(pid) | 直接断开该数据库连接 |
PostgreSQL 运维调优
一、监控与告警
PostgreSQL 监控一般通过 postgres_exporter 采集数据库内部指标，再由 Prometheus 抓取，最后在 Nightingale / Grafana 中展示和配置告警。
本次设计不使用Prometheus作为存储，选择使用：
PostgreSQL
↓
postgres_exporter
↓
Categraf
↓
victoriametrics
↓
Nightingale
# 告警规则也采用夜莺平台进行告警
1、监控部署
1.1 创建 PostgreSQL 监控用户
监控用户不建议使用 postgres 超级用户，应该单独创建一个只读监控用户。
进入 PostgreSQL：
su - postgres
psql
执行 SQL：
CREATE USER postgres_exporter WITH PASSWORD '<监控用户密码>';
GRANT pg_monitor TO postgres_exporter;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
退出：
\q
参数说明：
| SQL | 作用 |
|---|---|
CREATE USER postgres_exporter | 创建 PostgreSQL 监控用户 |
WITH PASSWORD | 设置监控用户密码 |
GRANT pg_monitor | 授予 PostgreSQL 内置监控权限 |
GRANT CONNECT ON DATABASE postgres | 允许监控用户连接 postgres 数据库 |
其中 pg_monitor 是 PostgreSQL 内置监控角色，适合 exporter 采集数据库状态、连接数、事务、锁、表统计等信息。
1.2 验证监控用户连接
在 PostgreSQL 服务器本机执行：
psql -h 127.0.0.1 -p 5432 -U postgres_exporter -d postgres
输入密码后，如果可以正常进入，说明监控用户创建成功。
进入后可以执行：
SELECT current_user;
SELECT current_database();
\q
正常结果应显示当前用户为：
postgres_exporter
1.3 创建 postgres_exporter 配置文件
创建环境变量配置文件：
cat > /etc/default/postgres_exporter <<'EOF'
DATA_SOURCE_URI=127.0.0.1:5432/postgres?sslmode=disable
DATA_SOURCE_USER=postgres_exporter
DATA_SOURCE_PASS=<监控用户密码>
EOF
修改权限：
chmod 600 /etc/default/postgres_exporter
配置说明：
| 配置项 | 说明 |
|---|---|
DATA_SOURCE_URI | PostgreSQL 连接地址 |
127.0.0.1:5432 | 连接本机 PostgreSQL 5432 端口 |
/postgres | 连接的数据库名称 |
sslmode=disable | 本机连接不启用 SSL |
DATA_SOURCE_USER | exporter 使用的数据库用户 |
DATA_SOURCE_PASS | exporter 使用的数据库密码 |
注意：
文档中不要直接写真实密码，建议使用 <监控用户密码> 占位。
1.4 创建 postgres_exporter systemd 服务
假设 postgres_exporter 程序目录为：
/data/postgres_exporter
二进制文件路径为：
/data/postgres_exporter/postgres_exporter
创建 systemd 服务文件：
cat > /etc/systemd/system/postgres_exporter.service <<'EOF'
[Unit]
Description=PostgreSQL Exporter
After=network.target postgresql.service
[Service]
Type=simple
WorkingDirectory=/data/postgres_exporter
EnvironmentFile=/etc/default/postgres_exporter
ExecStart=/data/postgres_exporter/postgres_exporter --web.listen-address=:9187
Restart=always
RestartSec=5
[Install]
WantedBy=multi-user.target
EOF
配置说明：
| 配置项 | 作用 |
|---|---|
Description | 服务描述 |
After=network.target postgresql.service | 网络和 PostgreSQL 服务启动后再启动 exporter |
WorkingDirectory | exporter 工作目录 |
EnvironmentFile | 引用 exporter 数据库连接配置 |
ExecStart | exporter 启动命令 |
--web.listen-address=:9187 | exporter 监听 9187 端口 |
Restart=always | 异常退出后自动重启 |
RestartSec=5 | 退出 5 秒后重启 |
WantedBy=multi-user.target | 设置为系统多用户模式下启动 |
1.5 启动 postgres_exporter
重新加载 systemd：
systemctl daemon-reload
设置开机自启并立即启动：
systemctl enable --now postgres_exporter
查看服务状态：
systemctl status postgres_exporter
查看端口监听：
ss -lnpt | grep 9187
正常情况下可以看到：
LISTEN 0 4096 0.0.0.0:9187
1.6 验证 exporter 指标
本机访问 exporter 指标接口：
curl http://127.0.0.1:9187/metrics
如果正常，会看到类似指标：
pg_up 1
pg_database_size_bytes
pg_stat_database_xact_commit
pg_stat_database_xact_rollback
pg_stat_activity_count
1.7 接入Categraf
编辑 Categraf 的 Prometheus input 配置文件。
常见路径为：
vim /etc/categraf/conf/input.prometheus/prometheus.toml
添加 PostgreSQL exporter 采集配置：
# psql
[[instances]]
urls = ["http://127.0.0.1:9187/metrics"]
url_label_key = "instance"
url_label_value = "{{.Host}}"
interval_times = 2
labels = { job = "psql_exporter", service = "psql", cluster = "psql-prod", env = "prod" }
配置说明：
| 配置项 | 说明 |
|---|---|
urls | postgres_exporter 指标地址 |
url_label_key | 采集后附加的实例标签名称 |
url_label_value | 实例标签值，{{.Host}} 表示当前主机 |
interval_times | 采集间隔倍数，基于 Categraf 全局采集周期 |
job | 任务名称，方便在 PromQL 中筛选 |
service | 服务类型，这里标记为 psql |
cluster | PostgreSQL 集群名称，自定义即可 |
env | 环境标签，例如 prod、test、dev |
如果 Categraf 和 postgres_exporter 不在同一台机器，需要把：
urls = ["http://127.0.0.1:9187/metrics"]
改成 PostgreSQL 服务器 IP：
urls = ["http://PostgreSQL服务器IP:9187/metrics"]
1.8 配置 Categraf 写入 VictoriaMetrics
如果 Categraf 需要直接写入本机 VictoriaMetrics，可以在 Categraf 主配置文件中配置 writers。
常见配置文件路径：
vim /etc/categraf/conf/config.toml
添加或确认如下配置：
# victoria-metrics
[[writers]]
url = "http://127.0.0.1:8428/api/v1/write"
配置说明：
| 配置项 | 说明 |
|---|---|
[[writers]] | Categraf 指标写入目标 |
url | VictoriaMetrics remote write 地址 |
127.0.0.1:8428 | 本机 VictoriaMetrics 地址 |
/api/v1/write | VictoriaMetrics remote write 接口 |
如果 VictoriaMetrics 不在本机，需要改成实际地址：
[[writers]]
url = "http://VictoriaMetrics服务器IP:8428/api/v1/write"
1.9 配置 Categraf 心跳上报 Nightingale
如果 Categraf 需要向 Nightingale 上报主机心跳，需要配置 heartbeat。
[heartbeat]
enable = true
# report os version cpu.util mem.util metadata
url = "http://127.0.0.1:17000/v1/n9e/heartbeat"
配置说明：
| 配置项 | 说明 |
|---|---|
enable = true | 开启 Categraf 心跳上报 |
url | Nightingale heartbeat 地址 |
127.0.0.1:17000 | Nightingale server 地址 |
/v1/n9e/heartbeat | 夜莺心跳接口 |
如果 Nightingale 不在本机，需要修改为实际地址：
[heartbeat]
enable = true
url = "http://Nightingale服务器IP:17000/v1/n9e/heartbeat"
重启 Categraf，修改完成后，重启 Categraf：
systemctl restart categraf
查看服务状态：
systemctl status categraf
查看 Categraf 日志：
journalctl -u categraf -n 100 --no-pager
如果需要实时查看：
journalctl -u categraf -f
2、告警规则
2.1 P0 级别告警
| 告警名称 | PromQL | 持续时间 | 执行频率 | 重复通知 | 生效时间 | 聚合标签 |
|---|---|---|---|---|---|---|
| PostgreSQL 不可连接 | pg_up{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} != 1 | 1m | 30s | 10m | 全天 | cluster,env,instance |
| 连接使用率超过 90% | 100 * sum by(instance) (pg_stat_activity_count{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}) / max by(instance) (pg_settings_max_connections{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}) > 90 | 3m | 30s | 10m | 全天 | cluster,env,instance |
| 复制延迟超过 1 小时 | pg_replication_lag_seconds{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} > 3600 | 3m | 30s | 10m | 全天 | cluster,env,instance |
| 主从角色发生变化 | changes(pg_replication_is_replica{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}[5m]) > 0 | 1m | 30s | 10m | 全天 | cluster,env,instance |
2.2 P1 级别告警
| 告警名称 | PromQL | 持续时间 | 执行频率 | 重复通知 | 生效时间 | 聚合标签 | 处理方向 |
|---|---|---|---|---|---|---|---|
| Exporter 最近一次采集失败 | pg_exporter_last_scrape_error{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} > 0 | 5m | 60s | 30m | 全天 | cluster,env,instance | 查 exporter 日志、数据库权限、连接串 |
| Collector 采集失败 | pg_scrape_collector_success{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} == 0 | 5m | 60s | 30m | 全天 | cluster,env,instance,collector | 查具体 collector 权限或版本兼容 |
| 连接使用率超过 80% | 100 * sum by(instance) (pg_stat_activity_count{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}) / max by(instance) (pg_settings_max_connections{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}) > 80 | 10m | 60s | 30m | 全天 | cluster,env,instance | 查连接池、慢 SQL、长事务 |
| Rollback 率超过 10% | 100 * sum by(instance,datname) (rate(pg_stat_database_xact_rollback{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])) / clamp_min(sum by(instance,datname) (rate(pg_stat_database_xact_commit{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m]) + rate(pg_stat_database_xact_rollback{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])), 1) > 10 | 5m | 60s | 30m | 全天 | cluster,env,instance,datname | 查业务错误、SQL 异常、事务冲突 |
| Deadlock 发生 | increase(pg_stat_database_deadlocks{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m]) > 0 | 1m | 30s | 30m | 全天 | cluster,env,instance,datname | 查锁等待、事务顺序、业务并发 |
| 高风险锁存在 | sum by(instance,datname,mode) (pg_locks_count{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*",mode=~"accessexclusivelock | exclusivelock | sharelock | sharerowexclusivelock"}) > 0` | 3m | 60s | 30m |
| 最大事务持续时间超过 30 分钟 | max by(instance,datname,state) (pg_stat_activity_max_tx_duration{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}) > 1800 | 3m | 60s | 30m | 全天 | cluster,env,instance,datname,state | 查 long transaction、idle in transaction |
| Idle in transaction 连接过多 | sum by(instance,datname) (pg_stat_activity_count{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",state="idle in transaction"}) > 5 | 5m | 60s | 30m | 全天 | cluster,env,instance,datname | 查应用连接未提交事务 |
| 复制延迟超过 5 分钟 | pg_replication_lag_seconds{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} > 300 | 5m | 60s | 30m | 全天 | cluster,env,instance | 有主从时启用 |
| 复制槽未激活 | pg_replication_slots_active{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} == 0 | 30m | 60s | 30m | 全天 | cluster,env,instance,slot_name | 有 replication slot 时启用 |
| PostgreSQL 备份失败 | db_backup_success{db_type="postgres",env="prod"} == 0 | 1m | 60s | 30m | 全天 | env,db_type,job | 需要备份脚本输出指标后启用 |
| 超过 26 小时未成功备份 | time() - db_backup_last_timestamp{db_type="postgres",env="prod"} > 93600 | 5m | 60s | 30m | 全天 | env,db_type,job | 需要备份脚本输出指标后启用 |
2.3 P2 级别告警
| 告警名称 | PromQL | 持续时间 | 执行频率 | 重复通知 | 生效时间 | 聚合标签 | 处理方向 |
|---|---|---|---|---|---|---|---|
| Exporter 采集耗时过高 | pg_exporter_last_scrape_duration_seconds{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} > 10 | 5m | 60s | 2h | 全天 | cluster,env,instance | 查数据库响应、collector 查询耗时 |
| Cache Hit Ratio 低于 98% | 100 * sum by(instance,datname) (rate(pg_stat_database_blks_hit{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])) / clamp_min(sum by(instance,datname) (rate(pg_stat_database_blks_hit{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m]) + rate(pg_stat_database_blks_read{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])), 1) < 98 | 10m | 60s | 2h | 全天 | cluster,env,instance,datname | 查内存、索引、大查询、冷数据扫描 |
| 最大事务持续时间超过 5 分钟 | max by(instance,datname,state) (pg_stat_activity_max_tx_duration{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}) > 300 | 5m | 60s | 2h | 全天 | cluster,env,instance,datname,state | 长事务早期预警 |
| 临时文件写入速率过高 | sum by(instance,datname) (rate(pg_stat_database_temp_bytes{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])) > 104857600 | 10m | 60s | 2h | 全天 | cluster,env,instance,datname | 查大排序、大聚合、work_mem |
| 临时文件数量过高 | sum by(instance,datname) (rate(pg_stat_database_temp_files{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])) > 10 | 10m | 60s | 2h | 全天 | cluster,env,instance,datname | 查频繁落盘 SQL |
| Requested Checkpoint 占比过高 | rate(pg_stat_bgwriter_checkpoints_req_total{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}[5m]) / clamp_min(rate(pg_stat_bgwriter_checkpoints_req_total{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}[5m]) + rate(pg_stat_bgwriter_checkpoints_timed_total{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}[5m]), 1) > 0.5 | 10m | 60s | 2h | 全天 | cluster,env,instance | 查 WAL 产生速度、checkpoint 参数 |
| Backend Write 过高 | rate(pg_stat_bgwriter_buffers_backend_total{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}[5m]) > 1000 | 10m | 60s | 2h | 全天 | cluster,env,instance | 业务进程被迫写脏页 |
| Dead Tuples 数量过高 | pg_stat_user_tables_n_dead_tup{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} > 100000 | 30m | 60s | 2h | 全天 | cluster,env,instance,schemaname,relname | 查 autovacuum、长事务、频繁 update/delete |
| Dead Tuples 占比过高 | 100 * pg_stat_user_tables_n_dead_tup{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} / clamp_min(pg_stat_user_tables_n_live_tup{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} + pg_stat_user_tables_n_dead_tup{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}, 1) > 20 | 30m | 60s | 2h | 全天 | cluster,env,instance,schemaname,relname | 表膨胀风险 |
| Autovacuum 长时间未执行 | (time() - pg_stat_user_tables_last_autovacuum{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}) > 86400 and pg_stat_user_tables_n_dead_tup{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"} > 10000 | 30m | 60s | 2h | 全天 | cluster,env,instance,schemaname,relname | 查 autovacuum 是否被长事务阻塞 |
| 顺序扫描速率过高 | sum by(instance,schemaname,relname) (rate(pg_stat_user_tables_seq_scan{job="psql_exporter",service="psql",cluster="psql-prod",env="prod"}[5m])) > 10 | 30m | 60s | 2h | 全天 | cluster,env,instance,schemaname,relname | 查大表索引和执行计划 |
| TPS 异常过高 | sum by(instance,datname) (rate(pg_stat_database_xact_commit{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m]) + rate(pg_stat_database_xact_rollback{job="psql_exporter",service="psql",cluster="psql-prod",env="prod",datname!~"template.*"}[5m])) > 1000 | 10m | 60s | 2h | 全天 | cluster,env,instance,datname | 阈值需要按业务基线调整 |
| 备份文件过小 | db_backup_file_size_bytes{db_type="postgres",env="prod"} < 1048576 | 1m | 60s | 2h | 全天 | env,db_type,job | 需要备份脚本输出指标后启用 |
| 备份耗时过长 | db_backup_duration_seconds{db_type="postgres",env="prod"} > 7200 | 1m | 60s | 2h | 全天 | env,db_type,job | 需要备份脚本输出指标后启用 |
二、PostgreSQL 备份
PostgreSQL 常见备份方式主要分为两类：
| 备份类型 | 常用工具 | 备份内容 | 适合场景 |
|---|---|---|---|
| 逻辑备份 | pg_dump、pg_dumpall | 数据库对象和数据 | 单库备份、单表恢复、数据迁移 |
| 物理备份 | pg_basebackup、WAL 归档 | PostgreSQL 数据文件 | 整实例恢复、生产灾备、时间点恢复 |
简单理解：
逻辑备份：把数据库里的表、数据、函数、索引等内容导出来。
物理备份：把 PostgreSQL 的数据目录和 WAL 日志整体备份出来。
1、备份原理
1.1 逻辑备份
逻辑备份是从数据库层面导出数据，不直接复制 PostgreSQL 的数据文件。
常用工具：
pg_dump
pg_dump 主要用于备份单个数据库。如果需要备份用户、角色、权限等全局对象，需要使用：
pg_dumpall
| 场景 | 说明 |
|---|---|
| 单库备份 | 只备份某一个业务数据库 |
| 单表恢复 | 可以只恢复某一张表 |
| 数据迁移 | 适合数据库迁移到新服务器 |
| 跨版本升级 | 例如 PostgreSQL 14 迁移到 PostgreSQL 16 |
| 测试环境恢复 | 可以把生产库恢复到测试环境 |
备份命令：
pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -F c -f /data/backup/postgres/mydb.dump
参数说明：
| 参数 | 说明 |
|---|---|
-h | 数据库地址 |
-p | 数据库端口 |
-U | 连接用户 |
-d | 数据库名 |
-F c | custom 备份格式 |
-f | 备份文件路径 |
恢复命令：
pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydb /data/backup/postgres/mydb.dump
如果是 SQL 文件，则使用：
psql -U postgres -d mydb < /data/backup/postgres/mydb.sql
全局对象备份：
pg_dump 不会完整备份用户、角色等全局对象，生产环境建议额外执行：
pg_dumpall -g > /data/backup/postgres/global_$(date +%F).sql
否则恢复时可能出现：
role 不存在
owner 不存在
权限缺失
优缺点如下：
| 优点 | 缺点 |
|---|---|
| 使用简单 | 大库备份和恢复较慢 |
| 恢复灵活 | 不适合时间点恢复 |
| 适合迁移 | 全局对象需要单独备份 |
| 支持跨版本 | 恢复时需要重建索引和约束 |
1.2 物理备份
物理备份是直接备份 PostgreSQL 的数据目录，属于实例级别备份。
常用工具：
pg_basebackup
常见数据目录：
/var/lib/postgresql/16/main
或者：
/data/postgresql/data
适用场景：
| 场景 | 说明 |
|---|---|
| 大型数据库 | 数据量大时更适合整库备份 |
| 核心生产库 | 用于生产环境灾备 |
| 整实例恢复 | 可以恢复整个 PostgreSQL 实例 |
| 主从复制初始化 | 可以用来初始化从库 |
| 时间点恢复 | 配合 WAL 可恢复到指定时间点 |
备份命令：
pg_basebackup \
-h 127.0.0.1 \
-p 5432 \
-U repl \
-D /data/backup/postgres/basebackup_$(date +%F) \
-Fp \
-Xs \
-P
参数说明：
| 参数 | 说明 |
|---|---|
-U repl | 复制用户 |
-D | 备份目录 |
-Fp | plain 文件格式 |
-Xs | 同步备份 WAL 日志 |
-P | 显示进度 |
WAL 是 PostgreSQL 的预写日志，主要用于：
| 作用 | 说明 |
|---|---|
| 崩溃恢复 | 数据库异常宕机后恢复事务 |
| 主从复制 | 主库通过 WAL 同步数据到从库 |
| 时间点恢复 | 回放 WAL 恢复到指定时间 |
如果配置了：
pg_basebackup + WAL 归档
就可以做 PITR 时间点恢复。
例如：
10:30 误删数据
可以恢复到 10:29:59
优缺点：
| 优点 | 缺点 |
|---|---|
| 适合大库 | 恢复粒度较粗 |
| 可恢复整个实例 | 不适合单表恢复 |
| 支持时间点恢复 | 版本要求严格 |
| 适合生产灾备 | 配置复杂度更高 |
1.3 备份方式对比
| 对比项 | 逻辑备份 | 物理备份 |
|---|---|---|
| 工具 | pg_dump | pg_basebackup |
| 备份内容 | 表、数据、索引、函数等 | 数据目录和 WAL |
| 备份粒度 | 库、表、Schema | 整个实例 |
| 恢复粒度 | 单库、单表 | 整实例 |
| 是否适合迁移 | 适合 | 不适合 |
| 是否支持跨版本 | 支持较好 | 要求主版本一致 |
| 是否支持 PITR | 不支持 | 支持 |
| 使用复杂度 | 较低 | 较高 |
2、逻辑备份与恢复
| 测试项 | 说明 |
|---|---|
| 备份测试 | 使用 pg_dump 备份指定数据库 |
| 文件检查 | 检查备份文件是否正常生成 |
| 恢复测试 | 将备份恢复到新的测试数据库 |
| 数据校验 | 对比原库和恢复库的数据是否一致 |
| 流程验证 | 验证备份脚本是否具备实际恢复能力 |
2.1 准备工作
mkdir -p /data/backup/postgres-test
检查 PostgreSQL 工具版本：
psql --version
pg_dump --version
pg_restore --version
输出：
psql (PostgreSQL) 16.13
pg_dump (PostgreSQL) 16.13
pg_restore (PostgreSQL) 16.13
psql、pg_dump、pg_restore 版本与当前 PostgreSQL 服务端主版本保持一致。
2.2 创建用于测试的库表
在测试库中创建使用的库表与表结构数据：
-- 创建数据库表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入数据
INSERT INTO users (username, age, email)
VALUES ('zhangsan', 25, 'zhangsan@example.com');
INSERT INTO users (username, age, email)
VALUES
('lisi', 28, 'lisi@example.com'),
('wangwu', 30, 'wangwu@example.com');
-- 验证数据
SELECT * FROM users;
2.3 执行备份测试
使用 pg_dump 备份数据库：
pg_dump \
-h 127.0.0.1 \
-p 5432 \
-U postgres \
-d postgres \
-F c \
-f /data/backup/postgres-test/postgres_$(date +%F_%H%M%S).dump
参数说明：
| 参数 | 说明 |
|---|---|
-h | PostgreSQL 地址 |
-p | PostgreSQL 端口 |
-U | 连接用户 |
-d | 需要备份的数据库 |
-F c | custom 备份格式 |
-f | 备份文件保存路径 |
推荐使用 -F c 格式，恢复时更灵活，也便于后续使用 pg_restore 查看和恢复。
2.4 检查备份是否生成
ls -lh /data/backup/postgres-test/
total 4.0K
-rw-r--r-- 1 root root 3.1K May 15 14:00 postgres_2026-05-15_140000.dump
2.5 创建恢复测试库
psql -h 127.0.0.1 -p 5432 -U postgres -c "CREATE DATABASE postgres_restore_test;"
确认数据库已创建：
psql -h 127.0.0.1 -p 5432 -U postgres -l | grep postgres_restore_test
2.6 执行恢复测试
将备份恢复到测试库：
pg_restore \
-h 127.0.0.1 \
-p 5432 \
-U postgres \
-d postgres_restore_test \
/data/backup/postgres-test/postgres_2026-05-15_140000.dump
如果恢复时希望先清理已有对象，可以使用：
pg_restore \
-h 127.0.0.1 \
-p 5432 \
-U postgres \
-d postgres_restore_test \
--clean \
--if-exists \
/data/backup/postgres-test/postgres_2026-05-15_140000.dump
参数说明：
| 参数 | 说明 |
|---|---|
-d | 指定恢复到哪个数据库 |
--clean | 恢复前先删除已有对象 |
--if-exists | 删除对象时忽略不存在的对象 |
| 备份文件 | 指定 .dump 文件路径 |
2.6 验证恢复
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres_restore_test -c "\dt"
Password for user postgres:
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | users | table | postgres
(1 row)
postgres_restore_test=# SELECT * FROM users;
id | username | age | email | created_at
----+----------+-----+----------------------+----------------------------
1 | zhangsan | 25 | zhangsan@example.com | 2026-05-15 13:58:44.40627
2 | lisi | 28 | lisi@example.com | 2026-05-15 13:58:44.406932
3 | wangwu | 30 | wangwu@example.com | 2026-05-15 13:58:44.406932
(3 rows)
postgres_restore_test=#
恢复完成。
pg_dump只备份单个数据库，不完整备份用户、角色等全局对象。生产环境建议额外测试：
pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -g > /data/backup/postgres-test/global_$(date +%F_%H%M%S).sql检查文件：
ls -lh /data/backup/postgres-test/global_*.sql head /data/backup/postgres-test/global_*.sql # 清理备份环境 psql -h 127.0.0.1 -p 5432 -U postgres -c "DROP DATABASE postgres_restore_test;"
3、物理备份与恢复
| 测试项 | 说明 |
|---|---|
| 备份测试 | 使用 pg_basebackup 生成物理备份 |
| 文件检查 | 检查备份目录是否完整 |
| 备份校验 | 使用 pg_verifybackup 校验备份 |
| 恢复测试 | 使用备份目录启动一个新的测试实例 |
| 数据验证 | 查询恢复实例，确认数据可用 |
核心流程：
从原 PostgreSQL 实例上做了一份完整的物理备份，然后使用这份备份目录启动一个新的 PostgreSQL 测试实例，通过连接这个测试实例来验证备份是否可用、数据是否完整。
3.1 准备工作
mkdir -p /data/backup/postgres-physical
chown -R postgres:postgres /data/backup/postgres-physical
chmod 700 /data/backup/postgres-physical
检查工具版本：
psql --version
pg_basebackup --version
pg_verifybackup --version
示例：
psql (PostgreSQL) 16.13
pg_basebackup (PostgreSQL) 16.13
pg_verifybackup (PostgreSQL) 16.13
物理备份需要 PostgreSQL 支持复制连接。
查看关键参数：
psql -h 127.0.0.1 -p 5432 -U postgres -c "SHOW wal_level;"
psql -h 127.0.0.1 -p 5432 -U postgres -c "SHOW max_wal_senders;"
psql -h 127.0.0.1 -p 5432 -U postgres -c "SHOW archive_mode;"
# 输出：
wal_level
-----------
replica
(1 row)
Password for user postgres:
max_wal_senders
-----------------
10
(1 row)
Password for user postgres:
archive_mode
--------------
off
(1 row)
重点关注：
| 参数 | 建议值 | 说明 |
|---|---|---|
wal_level | replica | 支持物理复制和基础备份 |
max_wal_senders | 大于 0 | 允许复制连接 |
archive_mode | 可选 | 如果要做 PITR，需要开启 |
普通 pg_basebackup 测试不一定要求开启 WAL 归档。
如果后续要测试 PITR 时间点恢复，则需要配置：
archive_mode = on
archive_command = '...'
3.2 创建复制用户
执行方式：
psql -h 127.0.0.1 -p 5432 -U postgres -c "CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'repl_password';"
如果用户已经存在，可以跳过。
检查用户：
psql -h 127.0.0.1 -p 5432 -U postgres -c "\du repl"
3.2 配置 pg_hba.conf
允许复制用户连接。
编辑 pg_hba.conf：
vim /etc/postgresql/16/main/pg_hba.conf
增加配置：
host replication repl 127.0.0.1/32 scram-sha-256
如果是从其他机器备份，需要增加对应 IP：
host replication repl 备份服务器IP/32 scram-sha-256
重新加载配置：
systemctl reload postgresql
或者：
pg_ctlcluster 16 main reload
测试复制连接：
PGPASSWORD='repl_password' psql -h 127.0.0.1 -p 5432 -U repl -d postgres -c "SELECT 1;"
?column?
----------
1
(1 row)
3.3 执行物理备份
使用 pg_basebackup 执行物理备份：
BACKUP_DIR="/data/backup/postgres-physical/basebackup_$(date +%F_%H%M%S)"
sudo -u postgres env PGPASSWORD='repl_password' pg_basebackup \
-h 127.0.0.1 \
-p 5432 \
-U repl \
-D "$BACKUP_DIR" \
-Fp \
-Xs \
-P \
-v
参数说明：
| 参数 | 说明 |
|---|---|
-h | PostgreSQL 地址 |
-p | PostgreSQL 端口 |
-U | 复制用户 |
-D | 备份保存目录 |
-Fp | plain 文件格式 |
-Xs | 同步备份 WAL |
-P | 显示备份进度 |
-v | 输出详细日志 |
其中：
-Xs
表示在备份过程中同步获取 WAL，保证备份可以恢复到一致状态。
3.4 检查备份是否生成
查看备份目录：
ls -lh /data/backup/postgres-physical/
total 4.0K
drwx------ 19 postgres postgres 4.0K May 15 14:14 basebackup_2026-05-15_141414
查看备份大小：
du -sh "$BACKUP_DIR"
39M /data/backup/postgres-physical/basebackup_2026-05-15_141414
检查关键文件和目录：
ls -lh "$BACKUP_DIR"
total 216K
-rw------- 1 postgres postgres 3 May 15 14:14 PG_VERSION
-rw------- 1 postgres postgres 225 May 15 14:14 backup_label
-rw------- 1 postgres postgres 135K May 15 14:14 backup_manifest
drwx------ 5 postgres postgres 4.0K May 15 14:14 base
drwx------ 2 postgres postgres 4.0K May 15 14:14 global
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_commit_ts
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_dynshmem
drwx------ 4 postgres postgres 4.0K May 15 14:14 pg_logical
drwx------ 4 postgres postgres 4.0K May 15 14:14 pg_multixact
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_notify
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_replslot
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_serial
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_snapshots
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_stat
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_subtrans
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_tblspc
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_twophase
drwx------ 3 postgres postgres 4.0K May 15 14:14 pg_wal
drwx------ 2 postgres postgres 4.0K May 15 14:14 pg_xact
-rw------- 1 postgres postgres 88 May 15 14:14 postgresql.auto.conf
正常情况下应能看到类似内容：
PG_VERSION
backup_label
backup_manifest
base/
global/
pg_wal/
pg_tblspc/
postgresql.auto.conf
重点说明：
| 文件或目录 | 说明 |
|---|---|
PG_VERSION | PostgreSQL 主版本信息 |
base/ | 各数据库的数据文件 |
global/ | 全局系统表 |
pg_wal/ | WAL 日志目录 |
backup_manifest | 备份清单，用于校验 |
backup_label | 备份恢复所需元信息 |
3.5 校验物理备份
使用 pg_verifybackup 校验备份：
/usr/lib/postgresql/16/bin/pg_verifybackup "$BACKUP_DIR"
backup successfully verified
如果输出没有明显报错，说明备份文件基本完整。
3.6 准备恢复测试目录
创建恢复测试目录：
RESTORE_DIR="/data/postgresql/physical_restore_test"
rm -rf "$RESTORE_DIR"
mkdir -p /data/postgresql
cp -a "$BACKUP_DIR" "$RESTORE_DIR"
chown -R postgres:postgres "$RESTORE_DIR"
chmod 700 "$RESTORE_DIR"
| 操作 | 说明 |
|---|---|
cp -a | 保留文件权限和目录结构 |
chown | 确保 postgres 用户可以访问 |
chmod 700 | 保持 PostgreSQL 数据目录权限安全 |
RESTORE_DIR | 独立恢复测试目录，不影响原库 |
3.7 准备恢复测试实例配置
由于 Ubuntu/Debian 安装的 PostgreSQL，配置文件通常不在数据目录内，而是在：
/etc/postgresql/16/main/
所以通过 pg_basebackup 备份出来的数据目录中，可能没有完整的：
postgresql.conf
pg_hba.conf
因此需要给恢复测试实例单独准备配置文件。
创建 postgresql.conf：
cat > "$RESTORE_DIR/postgresql.conf" <<'EOF'
listen_addresses = '127.0.0.1'
port = 55432
unix_socket_directories = '/tmp'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-restore-test.log'
EOF
chown postgres:postgres "$RESTORE_DIR/postgresql.conf"
创建 pg_hba.conf：
cat > "$RESTORE_DIR/pg_hba.conf" <<'EOF'
local all all peer
host all all 127.0.0.1/32 scram-sha-256
EOF
chown postgres:postgres "$RESTORE_DIR/pg_hba.conf"
创建日志目录：
mkdir -p "$RESTORE_DIR/log"
chown -R postgres:postgres "$RESTORE_DIR/log"
本次恢复测试实例使用端口：
55432
检查端口是否被占用：
ss -lnpt | grep 55432
如果没有输出，说明端口没有被占用。
3.8 启动恢复测试实例
使用备份目录启动一个新的 PostgreSQL 测试实例：
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
-D "$RESTORE_DIR" \
-l "$RESTORE_DIR/restore_test.log" \
start
waiting for server to start.... done
server started
查看启动日志：
tail -n 100 "$RESTORE_DIR/restore_test.log"
2026-05-15 06:25:04.385 GMT [312383] LOG: redirecting log output to logging collector process
2026-05-15 06:25:04.385 GMT [312383] HINT: Future log output will appear in directory "log".
正常情况下，日志中应该能看到数据库完成恢复并启动成功的信息。
如果启动成功，可以查看端口：
ss -lnpt | grep 55432
正常输出类似：
LISTEN 0 244 127.0.0.1:55432 0.0.0.0:*
说明恢复测试实例已经启动。
3.9 连接恢复测试实例
使用本地 Unix Socket 连接恢复实例：
sudo -u postgres psql -h /tmp -p 55432 -d postgres -c "SELECT version();"
查看当前端口：
sudo -u postgres psql -h /tmp -p 55432 -d postgres -c "SHOW port;"
port
-------
55432
(1 row)
这个步骤很重要，用于确认当前连接的是恢复测试实例，而不是原来的生产实例。
查看数据库列表：
sudo -u postgres psql -h /tmp -p 55432 -d postgres -c "\l"
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
如果能正常看到数据库列表，说明物理备份已经可以启动并访问。
3.10 恢复后数据验证
物理备份恢复后，需要对比原实例和恢复实例中的数据。
查看原实例数据库列表：
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c "\l"
查看恢复实例数据库列表：
sudo -u postgres psql -h /tmp -p 55432 -d postgres -c "\l"
检查指定业务库的表数量。
原实例：
psql -h 127.0.0.1 -p 5432 -U postgres -d 业务库名 -c "
SELECT count(*)
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog','information_schema');
"
恢复实例：
sudo -u postgres psql -h /tmp -p 55432 -d 业务库名 -c "
SELECT count(*)
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog','information_schema');
"
也可以选择核心业务表做数据量验证。
原实例：
psql -h 127.0.0.1 -p 5432 -U postgres -d 业务库名 -c "SELECT count(*) FROM 表名;"
恢复实例：
sudo -u postgres psql -h /tmp -p 55432 -d 业务库名 -c "SELECT count(*) FROM 表名;"
如果两边数据库列表、表数量、核心表数据量基本一致，说明物理备份恢复测试通过。
3.11 停止恢复测试实例
测试完成后，需要停止恢复测试实例：
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
-D "$RESTORE_DIR" \
stop -m fast
确认端口已经释放：
ss -lnpt | grep 55432
如果没有输出，说明恢复测试实例已经停止。
3.12 清理测试环境
确认恢复测试实例已经停止后，再清理恢复测试目录：
rm -rf "$RESTORE_DIR"
如果确认物理备份文件也不需要保留，可以清理备份目录：
rm -rf /data/backup/postgres-physical/basebackup_*
3.13 测试结果确认
| 检查项 | 结果 |
|---|---|
pg_basebackup 是否执行成功 | 是 / 否 |
| 备份目录是否正常生成 | 是 / 否 |
| 备份目录大小是否正常 | 是 / 否 |
backup_manifest 是否存在 | 是 / 否 |
pg_verifybackup 是否校验通过 | 是 / 否 |
| 恢复测试目录是否创建成功 | 是 / 否 |
| 恢复测试实例是否启动成功 | 是 / 否 |
恢复实例端口是否为 55432 | 是 / 否 |
| 数据库列表是否正常 | 是 / 否 |
| 核心表数据是否可查询 | 是 / 否 |
| 测试实例是否已停止 | 是 / 否 |
3.14 常见问题
| 问题 | 原因 | 处理方式 |
|---|---|---|
pg_basebackup 连接失败 | pg_hba.conf 未放行复制连接 | 增加 replication 规则并 reload |
permission denied | 目录权限不正确 | 确保目录归属为 postgres:postgres |
| 备份目录已存在 | -D 指定目录必须为空 | 使用新的空目录 |
pg_verifybackup 校验失败 | 备份文件缺失或损坏 | 重新执行物理备份 |
| 恢复实例启动失败 | 缺少配置文件 | 单独创建 postgresql.conf 和 pg_hba.conf |
| 端口冲突 | 55432 已被占用 | 更换测试端口 |
| 连接到了原实例 | 连接参数错误 | 明确使用 -h /tmp -p 55432 |
| 版本不兼容 | PostgreSQL 主版本不一致 | 使用相同主版本恢复 |
4、Databasus 工具备份
4.1 Databasus 工具讲解
官方地址：https://databasus.com/
开源地址：https://github.com/databasus/databasus
官方文档：https://databasus.com/installation
Databasus 是一款免费、开源、可自托管的数据库备份管理工具。它最初专注于 PostgreSQL，现已扩展支持 MySQL、MariaDB 和 MongoDB，旨在通过一个精致且强大的 Web 界面，将分散的数据库备份工作统一起来，让备份真正变得“可管、可控、可协作”。
Databasus 支持市面上主流的关系型与非关系型数据库，且覆盖了广泛的版本。
现已扩展支持 MySQL、MariaDB 和 MongoDB，核心功能包括通过精致的 Web 界面实现多数据库类型的备份与恢复管理、灵活的计划备份（支持小时 / 日 / 周 / 月及自定义 cron 表达式）、健康检查与自动重试机制，以及将备份存储到 S3、Google Drive、FTP 等多种目标位置，同时可通过 Slack、Discord、Telegram 等平台发送自定义备份通知；
其核心优势在于部署配置快速（约 2 分钟）、界面友好易管理、完全开源免费且支持私有化部署保障数据安全，能为个人开发者、中小企业及 DevOps 团队提供可靠、低成本的数据库备份解决方案，解决分散数据库备份管理的痛点。
4.2 Databasus 环境搭建
不采用官方前后端部署的方式进行搭建了，采用Docker环境搭建，有效提高效率，高效部署使用。
Docker环境搭建：
# 使用Docker脚本进行安装。
./docker_install.sh install
docker version
Client:
Version: 28.5.2
API version: 1.51
Go version: go1.25.3
Git commit: ecc6942
Built: Wed Nov 5 14:42:42 2025
OS/Arch: linux/amd64
Context: default
Server: Docker Engine - Community
Engine:
Version: 28.5.2
API version: 1.51 (minimum version 1.24)
Go version: go1.24.9
Git commit: 89c5e8f
Built: Wed Nov 5 14:45:42 2025
OS/Arch: linux/amd64
Experimental: false
containerd:
Version: v1.7.28
GitCommit: b98a3aace656320842a23f4a392a33f46af97866
runc:
Version: 1.3.3
GitCommit: v1.3.3-0-gd842d77
docker-init:
Version: 0.19.0
GitCommit: de40ad0
拉取镜像：
docker pull databasus/databasus:latest
docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
databasus/databasus latest 5c4cb48cbc4e 24 hours ago 907MB
两种方式搭建：
# docker 语句
docker run -d \
--name databasus \
-p 4005:4005 \
-v ./databasus-data:/databasus-data \
--restart unless-stopped \
databasus/databasus:latest
# compose 文件
services:
databasus:
container_name: databasus
image: databasus/databasus:latest
ports:
- "4005:4005"
volumes:
- ./databasus-data:/databasus-data
restart: unless-stopped
我们选择使用 docker compose 搭建：
# 创建存储目录
mkdir /data/databasus
cd /data/databasus/
cat > docker-compose.yml <<EOF
services:
databasus:
container_name: databasus
image: databasus/databasus:latest
ports:
- "4005:4005"
volumes:
- ./databasus-data:/databasus-data
restart: unless-stopped
EOF
# 运行Databasus
docker compose up -d
[+] Running 2/2
✔ Network databasus_default Created 0.0s
✔ Container databasus Started 0.2s
docker compose ps
NAME IMAGE COMMAND SERVICE CREATED STATUS PORTS
databasus databasus/databasus:latest "/app/start.sh" databasus 5 seconds ago Up 4 seconds 0.0.0.0:4005->4005/tcp, [::]:4005->4005/tcp
浏览器访问 http://IP:4005 。
为备份测试创建数据库表：
# 创建user表 CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, age INT, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); # 插入测试信息 INSERT INTO users (username, age, email) VALUES ('zhangsan', 25, 'zhangsan@example.com'); INSERT INTO users (username, age, email) VALUES ('lisi', 28, 'lisi@example.com'), ('wangwu', 30, 'wangwu@example.com');
4.3 初始化 Databasus
浏览器访问 Databasus 平台地址 http://<IP>/ ，首次访问后，需设置 admin 管理员用户的密码，然后点击【Set password】：
设置完成后即可进入主界面，显示如下：
4.4 使用Databasus
4.4.1 创建工作空间
登录 Databasus 平台后，点击中间【Create workspace】，自定义配置【Workspace name】工作空间名，然后点击【Create workspace】创建：
4.4.2 添加飞书通知
4.4.2.1 安装python3环境
apt update
apt install -y python3 python3-pip
安装完成后，配置 pip.conf 文件，修改为国内镜像源：
mkdir -p /root/.pip
cat > /root/.pip/pip.conf <<'EOF'
[global]
timeout = 10
index-url = https://mirrors.tuna.tsinghua.edu.cn/pypi/web/simple/
[install]
trusted-host = mirrors.tuna.tsinghua.edu.cn
EOF
说明：
| 配置项 | 说明 |
|---|---|
timeout | pip 下载超时时间 |
index-url | pip 软件包下载源 |
trusted-host | 信任的镜像源地址 |
4.4.2.2 安装依赖
飞书告警脚本使用 Flask 提供 HTTP 接口，所以需要安装 Flask。
Ubuntu 22.04 可以直接使用 pip 安装：
pip3 install flask
也可以使用 apt 安装系统包：
apt install -y python3-flask
推荐使用：
apt install -y python3-flask
因为这是 Ubuntu 软件源中的 Python 包，后续系统维护更简单。
检查 Flask 是否可正常导入：
python3 -c "import flask; print(flask.__version__)"
4.4.2.3 编写通知脚本
进入脚本目录：
mkdir /data/databasus/scripts && cd /data/databasus/scripts
编写脚本文件 feishu_app.py ：
import time
import hmac
import base64
import hashlib
import urllib.request
import json
import re
from datetime import datetime, timezone, timedelta
from flask import Flask, request
app = Flask(__name__)
# 飞书自定义机器人 Webhook 地址
FEISHU_WEBHOOK_URL = "YOUR_FEISHU_WEBHOOK_URL"
# 如果飞书机器人开启了签名校验，填写机器人 Secret
# 如果未开启签名校验，保持为空即可
FEISHU_SECRET = ""
# 飞书机器人关键词
# 当前飞书机器人要求消息中包含 MySQL 或 PostgreSQL
FEISHU_KEYWORDS = "MySQL PostgreSQL"
def now_time():
"""获取当前北京时间"""
tz = timezone(timedelta(hours=8))
return datetime.now(tz).strftime("%Y-%m-%d %H:%M:%S")
def gen_sign(secret):
"""生成飞书机器人签名"""
timestamp = str(int(time.time()))
string_to_sign = f"{timestamp}\n{secret}"
hmac_code = hmac.new(
string_to_sign.encode("utf-8"),
digestmod=hashlib.sha256
).digest()
sign = base64.b64encode(hmac_code).decode("utf-8")
return timestamp, sign
def short_text(text, limit=1200):
"""限制消息长度，避免飞书卡片内容过长"""
if not text:
return ""
text = str(text).strip()
if len(text) <= limit:
return text
return text[-limit:]
def get_database_type(db_name, heading, message):
"""识别数据库类型"""
source = f"{db_name} {heading} {message}".lower()
if "postgres" in source or "pgsql" in source or "psql" in source:
return "PostgreSQL"
if "mysql" in source or "mariadb" in source:
return "MySQL"
return "Unknown"
def extract_db_name_from_heading(heading):
"""从 heading 中提取数据库名称"""
if not heading:
return "unknown"
# Backup completed for database "xxx" in workspace "xxx"
match = re.search(r'database\s+"([^"]+)"', heading)
if match:
return match.group(1).strip()
# DB is online [xxx]
match = re.search(r'\[([^\]]+)\]', heading)
if match:
return match.group(1).strip()
return "unknown"
def extract_workspace_from_heading(heading):
"""从 heading 中提取 workspace"""
if not heading:
return "unknown"
match = re.search(r'workspace\s+"([^"]+)"', heading)
if match:
return match.group(1).strip()
return "unknown"
def extract_duration(message):
"""从 message 中提取备份耗时"""
if not message:
return "未知"
# Backup completed successfully in 3 seconds.
match = re.search(r'completed successfully in\s+(.+?)(?:\.|$)', message)
if match:
return match.group(1).strip()
# 兼容其他可能格式
match = re.search(r'in\s+([0-9.]+\s*(seconds|second|minutes|minute|s|min))', message, re.I)
if match:
return match.group(1).strip()
return "未知"
def extract_backup_size(message):
"""从 message 中提取备份大小"""
if not message:
return "未知"
# Compressed backup size: 12.5 MB
match = re.search(r'Compressed backup size:\s*(.+?)(?:\n|$)', message)
if match:
return match.group(1).strip()
# 兼容 size: 12.5 MB
match = re.search(r'(backup size|size):\s*([0-9.]+\s*(B|KB|MB|GB|TB))', message, re.I)
if match:
return match.group(2).strip()
return "未知"
def build_field_lines(fields):
"""构造飞书 Markdown 字段"""
lines = []
for key, value in fields:
if value is None or value == "":
value = "未知"
lines.append(f"**{key}：** {value}")
return "\n".join(lines)
def build_card(title, template, fields, detail=None):
"""构造飞书卡片"""
content = build_field_lines(fields)
elements = [
{
"tag": "div",
"text": {
"tag": "lark_md",
"content": content
}
}
]
if detail:
elements.extend([
{
"tag": "hr"
},
{
"tag": "div",
"text": {
"tag": "lark_md",
"content": f"**详细信息：**\n{short_text(detail, 1200)}"
}
}
])
elements.extend([
{
"tag": "hr"
},
{
"tag": "div",
"text": {
"tag": "lark_md",
"content": (
f"**告警来源：** Databasus\n"
f"**通知类型：** 数据库备份与可用性监控\n"
f"**关键词：** {FEISHU_KEYWORDS}"
)
}
}
])
card = {
"config": {
"wide_screen_mode": True
},
"header": {
"template": template,
"title": {
"tag": "plain_text",
"content": f"{title} / {FEISHU_KEYWORDS}"
}
},
"elements": elements
}
return card
def parse_databasus_msg(heading, message):
"""解析 Databasus 通知内容并转换为飞书卡片"""
heading = heading or "Databasus Notification"
message = message or ""
db_name = extract_db_name_from_heading(heading)
workspace = extract_workspace_from_heading(heading)
db_type = get_database_type(db_name, heading, message)
notify_time = now_time()
if "✅ Backup completed" in heading:
duration = extract_duration(message)
backup_size = extract_backup_size(message)
fields = [
("数据库名称", db_name),
("数据库类型", db_type),
("所属工作区", workspace),
("当前状态", "<font color='green'>Backup: success</font>"),
("备份耗时", duration),
("备份大小", backup_size),
("通知时间", notify_time)
]
return build_card(
title=f"Databasus 备份成功 / {db_name}",
template="green",
fields=fields
)
if "❌ Backup failed" in heading:
fields = [
("数据库名称", db_name),
("数据库类型", db_type),
("所属工作区", workspace),
("当前状态", "<font color='red'>Backup: failed</font>"),
("备份耗时", "失败"),
("备份大小", "未生成"),
("通知时间", notify_time)
]
return build_card(
title=f"Databasus 备份失败 / {db_name}",
template="red",
fields=fields,
detail=message
)
if "DB is online" in heading:
fields = [
("数据库名称", db_name),
("数据库类型", db_type),
("所属工作区", workspace),
("当前状态", "<font color='green'>DB: online</font>"),
("问题详情", "数据库已恢复连接"),
("通知时间", notify_time)
]
return build_card(
title=f"Databasus 数据库恢复 / {db_name}",
template="green",
fields=fields
)
if "DB is unavailable" in heading:
fields = [
("数据库名称", db_name),
("数据库类型", db_type),
("所属工作区", workspace),
("当前状态", "<font color='red'>DB: unavailable</font>"),
("问题详情", "数据库连接异常"),
("通知时间", notify_time)
]
return build_card(
title=f"Databasus 数据库异常 / {db_name}",
template="red",
fields=fields,
detail=message
)
fields = [
("数据库名称", db_name),
("数据库类型", db_type),
("所属工作区", workspace),
("当前状态", "Unknown"),
("通知标题", heading),
("通知时间", notify_time)
]
return build_card(
title="Databasus 通知",
template="blue",
fields=fields,
detail=message
)
def send_to_feishu(card):
"""发送飞书卡片消息"""
payload = {
"msg_type": "interactive",
"card": card
}
if FEISHU_SECRET:
timestamp, sign = gen_sign(FEISHU_SECRET)
payload["timestamp"] = timestamp
payload["sign"] = sign
data = json.dumps(payload, ensure_ascii=False).encode("utf-8")
req = urllib.request.Request(
FEISHU_WEBHOOK_URL,
data=data,
headers={"Content-Type": "application/json"}
)
with urllib.request.urlopen(req, timeout=10) as response:
result = response.read().decode("utf-8")
return result
@app.route("/sendnotify", methods=["POST"])
def sendnotify():
try:
data = request.json
if not data:
return "no data", 400
if not isinstance(data, dict):
return "invalid data format", 400
heading = data.get("heading", "")
message = data.get("message", "")
card = parse_databasus_msg(heading, message)
result = send_to_feishu(card)
app.logger.info(f"飞书消息发送成功: {result}")
return "ok"
except Exception as e:
app.logger.error(f"处理 Databasus 通知失败: {str(e)}", exc_info=True)
return f"处理失败: {str(e)}", 500
if __name__ == "__main__":
app.run(host="0.0.0.0", port=9090)
4.4.2.4 启动通知服务
启动脚本服务，建议使用 systemd 管理脚本服务。
创建服务文件：
cat > /etc/systemd/system/databasus-feishu-notify.service <<'EOF'
[Unit]
Description=Databasus Feishu Notify Service
After=network.target
[Service]
Type=simple
WorkingDirectory=/data/databasus/scripts
ExecStart=/usr/bin/python3 /data/databasus/scripts/feishu_app.py
Restart=always
RestartSec=5
StandardOutput=append:/data/databasus/scripts/sendnotify.log
StandardError=append:/data/databasus/scripts/sendnotify.log
[Install]
WantedBy=multi-user.target
EOF
重新加载 systemd：
systemctl daemon-reload
启动服务：
systemctl status databasus-feishu-notify.service
● databasus-feishu-notify.service - Databasus Feishu Notify Service
Loaded: loaded (/etc/systemd/system/databasus-feishu-notify.service; disabled; vendor preset: enabled)
Active: active (running) since Fri 2026-05-15 15:33:02 CST; 5s ago
Main PID: 389381 (python3)
Tasks: 1 (limit: 4665)
Memory: 18.4M
CPU: 119ms
CGroup: /system.slice/databasus-feishu-notify.service
└─389381 /usr/bin/python3 /data/databasus/scripts/feishu_app.py
May 15 15:33:02 instance-wc5p3ngj systemd[1]: Started Databasus Feishu Notify Service.
4.4.2.5 添加通知设置
在 Databasus 平台上，点击左侧通知图标，然后点击【Add notifier】，配置以下示例信息：
-
【Name】：
feishu； -
【Type】：
Webhook； -
【Webhook URL】：
http://192.168.16.2:9090/sendnotify；注意，不能填写127.0.0.1，Databasus在容器里。 -
【Method】：
POST； -
【Body template】：
{ "heading": "{{heading}}", "message": "{{message}}" }
配置完成后，点击【Send test notification】，测试发送成功后，再点击【Save】：
保存即可。
4.4.3 添加存储空间
点击左侧存储图标，然后点击【Add storage】，配置以下示例信息：
- 【Name】：
my-storage； - 【Type】：
Local storage（有好几种存储类型，这里以本地存储为例）；
配置完成后，点击【Test connection】，测试连接成功后，再点击【Save】：
4.4.4 备份数据库
以备份 PostGreSQL 数据库为例，点击左侧数据库图标，然后点击【Add database】，配置以下示例信息：
- 【Name】：
local-psql； - 【Database type】：
PostGreSQL；
配置完成后，点击【Continue】：
在下一步中，继续配置以下示例信息：
- 【Backup type】：
Remote（默认，若使用Agent则还需安装数据库代理）； - 【Host】：
192.168.16.2； - 【Port】：
5432； - 【Username】：
postgres； - 【Password】：
YOUR_PASSWORD_HERE； - 【DB name】：
postgres； - 【Use HTTPS】：
禁用；
配置完成后，点击【Test connection】，测试连接成功后，点击【Continue】，然后在下一步中，选择是否为数据库创建一个只读用户，这里直接先选择【Skip】跳过，再选择【Yes, I accept risks】选项：
在下一步中，继续配置以下示例信息：
- 【Backup interval】：
Daily（表示备份间隔为每天）； - 【Backup time of day】：
00:00（表示每天的00:00分开始执行备份）； - 【Storage】：
my-storage（之前创建的本地存储类型）； - 【Encryption】：
Encrypt backup files（表示对备份文件进行加密）； - 【Retention policy】：
Time period (last N days)（表示将保留策略设置为过去 N 天）；1 week（表示备份文件最多保留 1 周）；
- 【Notifications】：勾选
Backup success、Backup failed（表示备份成功失败都通知）； - 【Retry backup if failed】：
启用； - 【Max failed tries count】：
2（表示备份失败时最多再尝试 2 次重新执行备份）；
配置完成后，点击【Continue】：
在下一步中，继续配置以下示例信息：
- 【Notifiers】：
feishu（之前创建的告警通知）；
配置完成后，点击【Complete】完成：
完成后，会自动执行第一次备份：
备份成功后，会发送通知到飞书机器人：
因为我的库表极小，所以耗时和备份大小可以忽略不计了。
4.4.5 健康检查
在创建完数据库后，都默认会每隔 1 分钟执行 1 次健康检查，来判断该数据库是否正常：
如果要修改健康检查设置，可进入【Config】栏，点击编辑【Healthcheck settings】，进行修改配置：
- 【Enable healthcheck】：
启用； - 【Notify when unavailable】：
启用（表示数据库不健康时发送告警通知）； - 【Check interval (minutes)】：
5（表示数据库每隔 5 分钟执行健康检查）； - 【Attempts before down】：
3（表示数据库被标记为宕机前的失败尝试次数）； - 【Store attempts (days)】：
7（表示健康检查尝试历史记录要保存的天数）；
配置完成后，点击【Save】：
4.4.6 还原数据库
如果要还原某个数据库的某个备份点，以 PostGreSQL 数据库为例，在【Backups】栏中，选择要还原的备份点，在右侧点击【Restore from backup】图标，然后点击【Select database to restore to】：
在下一步中，配置要还原的主机数据库，配置以下示例信息：
- 【Host】：
192.168.16.2； - 【Port】：
5432； - 【Username】：
postgres； - 【Password】：
YOUR_PASSWORD_HERE； - 【DB name】：
postgres； - 【Use HTTPS】：
禁用； - 【CPU count】：
2（表示用于备份和恢复操作的 CPU 核心数，更高的值可以加快操作速度，但会占用更多资源）；
配置完成后，点击【Test connection】，测试连接成功后，点击【Restore to this DB】：
等待还原成功：
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
7
0- 0
已为社区贡献1条内容
所有评论(0)