PostgreSQL

概念

目录类型	绝对路径	说明
配置文件	/etc/postgresql/15/main/	包含 postgresql.conf, pg_hba.conf, pg_ident.conf
数据目录	/var/lib/postgresql/15/main/	存放所有数据库表、索引及 WAL 日志等数据文件
二进制文件	/usr/lib/postgresql/15/bin/	包含 postgres, initdb, pg_ctl 等核心可执行程序
日志目录	/var/log/postgresql/	默认日志文件
Unix Socket	/ PID /var/run/postgresql/	存放 socket 连接文件 .s.PGSQL.5432 和 pid 文件
扩展与共享资源	/usr/share/postgresql/15/	包含 sql 扩展脚本、语言包及 extension 目录
动态链接库	/usr/lib/postgresql/15/lib/	存放 PG 扩展的 .so 文件

安装

apt 包安装

sudo apt update
sudo apt install -y curl ca-certificates gnupg

# 添加 PostgreSQL APT Key
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# 添加仓库
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

# 拉仓库元数据
sudo apt update
sudo apt install -y postgresql-15

# 验证
psql --version

访问策略

创建账号

管理

集群管理

1. 规划检查

# 查看已安装并运行的所有 PG 集群状态
pg_lsclusters

# 查看系统当前安装的 PostgreSQL 软件包版本
dpkg -l | grep postgresql

2. 创建实例

# 自定义实例
sudo pg_createcluster 15 prod_db \
  --port 5433 \
  --locale en_US.UTF-8 \
  --datadir /data/postgresql/15/prod_db \
  --start

3. 配置管理

# 查看某实例的指定配置项如 listen_addresses
pg_conftool 15 prod_db show listen_addresses

# 命令行修改配置项
sudo pg_conftool 15 prod_db set listen_addresses '*'

# 命令行修改最大连接数
sudo pg_conftool 15 prod_db set max_connections 200

# 手动编辑配置文件
sudo vi /etc/postgresql/15/prod_db/pg_hba.conf

# 重载配置使配置生效（无需重启服务）
sudo pg_ctlcluster 15 prod_db reload

4. 日常维护

sudo pg_ctlcluster 15 prod_db start

sudo pg_ctlcluster 15 prod_db restart

# 优雅停止
sudo pg_ctlcluster 15 prod_db stop

# -m fast 强制中断现有连接并停止
sudo pg_ctlcluster 15 prod_db stop -m fast

# 查看运行状态
sudo pg_ctlcluster 15 prod_db status

# 实时查看运行日志
sudo tail -f /var/log/postgresql/postgresql-15-prod_db.log

# 连入该实例交互终端, -d 指定数据库名称
sudo -u postgres psql -p 5433 -d postgres

# 通过 systemd 运维
sudo systemctl status postgresql@15-prod_db

5. 无缝升级

# 自动创建 15 的新集群并把 14 的数据同步过去
pg_upgradecluster 14 main -v 15

# 使用 hardlink 硬链接升级模式（速度极快，无需双倍磁盘空间）
sudo pg_upgradecluster -m link 14 main 15

6. 下线销毁

# 第一步：必须先停止要下线的集群
sudo pg_ctlcluster 15 dev_db stop

# 第二步：彻底销毁集群 (将删除 /etc 和 /var/lib 下该集群的所有配置文件与数据文件)
sudo pg_dropcluster 15 dev_db

# 第三步：清理剩余的日志文件
sudo rm -f /var/log/postgresql/postgresql-15-dev_db.log*

psql 交互管理

-- 常用元命令（Meta-commands）
\l              -- 列出所有数据库
\c dbname       -- 切换当前数据库到 dbname
\dt             -- 列出当前数据库的所有数据表
\du             -- 列出所有用户及其权限
\conninfo       -- 查看当前连接状态（用户、端口、Socket路径等）
\q              -- 退出 psql

-- 常用查询 SQL
SHOW data_directory;            -- 查看当前数据文件路径
SHOW config_file;               -- 查看主配置文件路径
SHOW hba_file;                  -- 查看客户端认证配置文件路径
SELECT version();               -- 查看详细版本信息

-- 修改 postgres 用户密码
ALTER USER postgres WITH PASSWORD 'NewPassword123!';

-- 查看当前活跃的数据库连接
SELECT pid, usename, datname, client_addr, state, query 
FROM pg_stat_activity 
WHERE state != 'idle';

备份

方法一：LVM 快照

# 1. 主库通知 PG 刷盘并加备份锁
psql -U postgres -c "SELECT pg_backup_start(label => 'lvm_snap_$(date +%F)', fast => true);"

# 2. 瞬间创建 LVM 快照, -L 为快照预分配空间, -s 标识为快照卷, -n 快照卷名称, 最后是源数据卷
lvcreate -L 10G -s -n pg_snap /dev/vg_data/lv_postgres
# 或调用阿里云 CLI / OpenAPI 创建云盘快照 (秒级创建)
# aliyun ecs CreateSnapshot --DiskId d-bp1xxxxxxxxx --SnapshotName pg_backup_$(date +%F)

# 3. 立即释放 PG 备份锁（业务写入恢复正常）
psql -U postgres -c "SELECT pg_backup_stop();"

# 4. 只读挂载快照卷, XFS 文件系统需加 nouuid
mkdir -p /mnt/snap_pg
mount -o ro,nouuid /dev/vg_data/pg_snap /mnt/snap_pg

# 5. 后台从快照慢慢打包/同步到远端（不影响主库 IO）
tar -czf /backup/pg_data_$(date +%F).tar.gz -C /mnt/snap_pg .

# 6. 备份完成后，卸载并销毁快照卷（释放存储池空间）
umount /mnt/snap_pg
lvremove -y /dev/vg_data/pg_snap

快照底层是 写时复制 （Copy on Write, COW）机制

                    你发起的拷贝指令 (tar / rsync)
                                 │
                                 ▼
                     读取挂载点 /mnt/snap_pg 中的文件
                                 │
                 ┌───────────────┴───────────────┐
                 ▼                               ▼
          【该数据块未被修改】              【该数据块已被业务修改】
                 │                               │
                 ▼                               ▼
       从【源卷】中直接透明读取            从【快照空间】中读取
        (无需占用额外快照空间)            (读取修改前被保护的历史旧块)
                 │                               │
                 └───────────────┬───────────────┘
                                 ▼
                最终合成：打快照那一瞬间的 100% 完整数据！

恢复步骤:

1. 停止服务并清空损坏的数据目录

systemctl stop postgresql

# 清空或移走旧的数据目录
mv /var/lib/postgresql/data /var/lib/postgresql/data_bak_$(date +%F)
mkdir -p /var/lib/postgresql/data

2. 解压全量备份文件

# 从快照里打包出来的文件解压到数据目录
tar -xzvf /backup/pg_data_2026-08-27.tar.gz -C /var/lib/postgresql/data/
# 删除 `postmaster.pid`, 若有残留 PG 会误以为已有实例运行

3. 修正目录权限

# 属主必须是 postgres，权限必须是 700
chown -R postgres:postgres /var/lib/postgresql/data
chmod 0700 /var/lib/postgresql/data

4. 如果备份是一天前打的, 但有持续的 WAL 归档日志, 基于时间点恢复到事故发生前一秒

• 创建恢复信号文件（PostgreSQL 12 及以上版本）：

  touch /var/lib/postgresql/data/recovery.signal
  chown postgres:postgres /var/lib/postgresql/data/recovery.signal

• 修改 /var/lib/postgresql/data/postgresql.conf，在末尾追加 WAL 获取路径与目标时间：

  # 指定从哪里拉取历史归档日志
  restore_command = 'cp /backup/archived_wal/%f %p'
  
  # 恢复的目标时间点（例如停在误删库之前的时刻）
  recovery_target_time = '2026-08-27 17:30:00'
  
  # 到达目标点后自动提升为主库正常对外服务
  recovery_target_action = 'promote'

• 启动数据库并观察恢复进度

# 启动服务
systemctl start postgresql

# 实时查看日志确认恢复完成
tail -f /var/log/postgresql/postgresql-*.log

在日志中看到如下关键输出，说明数据库成功恢复：

LOG:  redo starts at 0/15000028
LOG:  consistent recovery state reached at 0/15000100
LOG:  database system is ready to accept connections