postgresql

用途

该技能用于管理、优化和保护开发与生产环境中的 PostgreSQL 数据库。适合以下场景：需要可靠且符合 ACID 的关系型数据库；应用需要 JSONB、全文检索或 CTE 等高级特性；需搭建流复制或时间点恢复（PITR）；需对现有 PostgreSQL 部署进行吞吐量调优。

前提条件

Linux 服务器（Debian/Ubuntu 或基于 RHEL）或 Docker。
安装软件包需要 root 或 sudo 权限。
熟悉 SQL 基础知识。

安装与初始化

Debian/Ubuntu 使用 sudo apt install -y postgresql postgresql-contrib；RHEL/Amazon Linux 使用 sudo dnf install -y postgresql15-server postgresql15-contrib 并执行 postgresql-setup --initdb，随后启用服务。可通过 psql --version 与 systemctl status postgresql 验证。

初始化阶段可切换至 postgres 系统用户进入 psql，创建应用用户与数据库、授予连接权限，并通过 ALTER DEFAULT PRIVILEGES 设置默认权限。

常用操作与配置调优

资料列出了常用 psql 命令，如 \l、\dt、\d+、\du、\x、\timing、\i、\copy。配置调优集中在 postgresql.conf，包含连接设置（listen_addresses、max_connections）、内存（shared_buffers 约为总内存 25%、effective_cache_size、work_mem、maintenance_work_mem）、WAL 与写入性能、查询规划器参数以及日志设置。部分参数可通过 SELECT pg_reload_conf() 热加载，shared_buffers、max_connections 等需要重启。客户端认证通过 pg_hba.conf 配置，示例中本地 postgres 使用 peer，应用连接使用 scram-sha-256，并默认拒绝其他来源。

备份与恢复

逻辑备份使用 pg_dump，支持纯 SQL 输出与自定义压缩格式（-Fc，推荐），也可备份单表；恢复使用 pg_restore（自定义格式，支持 --clean --if-exists）或 psql（纯 SQL）。物理备份使用 pg_basebackup，支持 --wal-method=stream、--checkpoint=fast、--progress、--verbose，可用 pg_verifybackup 校验。

流复制

主库需创建具有 REPLICATION 权限的用户，配置 wal_level=replica、max_wal_senders、wal_keep_size，并在 pg_hba.conf 中允许复制连接。从库需停止服务、清理数据目录，通过 pg_basebackup 从主库拉取基础备份，创建 standby.signal，配置 primary_conninfo 与 hot_standby=on 后启动。可通过主库的 pg_stat_replication 和从库的 pg_is_in_recovery()、pg_last_wal_receive_lsn()、pg_last_wal_replay_lsn() 验证。

监控与维护

资料提供多种监控查询：按状态统计活动连接、长时间运行查询（超过 30 秒）、表膨胀与死元组、索引使用统计、缓存命中率（建议 > 99%）以及数据库大小。维护任务包括手动 VACUUM ANALYZE、使用 REINDEX INDEX CONCURRENTLY 重建膨胀索引、查询未使用索引。

容器化部署

提供 docker-compose.yml 示例，包含 postgres:16-alpine 服务（含数据卷、初始化脚本、命令行参数与健康检查）以及 pgbouncer 连接池服务（事务池模式、最大客户端连接与默认池大小配置）。

故障排查

资料以表格形式列出常见症状、可能原因与修复方式，涵盖连接数过多、大表查询缓慢、autovacuum 高 CPU、复制延迟增加、数据目录访问失败以及密码认证失败等情况。

技能介绍

用途