PostgreSQL 日常维护全攻略：从基础操作到生产级运维

PostgreSQL（简称 PG）凭借开源免费、高度兼容 SQL 标准、支持复杂查询、扩展性强、稳定性优异等特性，已成为企业级开发、数据分析、中台存储的首选关系型数据库之一。无论是开发环境快速搭建，还是生产环境高可用运维，掌握一套完整的日常维护流程都是 DBA 与后端工程师的必备技能。

本文基于官方运维规范与实战经验，系统整理 PG 从登录、库表管理、模式操作、备份恢复、远程连接、密码重置等基础操作，再补充自动清理、性能监控、日志管理、索引维护、安全加固、自动化脚本等生产必备知识点，内容详实、可直接落地，适合发布 CSDN 博客、学习笔记、运维手册。

---

一、PostgreSQL 基础使用

1.1 登录与退出

PG 默认使用系统用户 postgres 登录，不建议直接用 root 操作数据库。

bash

# 切换到 postgres 用户
su - postgres
# 进入 psql 交互终端
psql

登录成功提示符：

sql

postgres=#

退出 psql：

sql

\q

1.2 数据库常用操作

1.2.1 查看所有数据库

sql

-- 元命令（最常用）
\l
-- 扩展显示（大小、表空间、描述）
\l+
-- SQL 查询系统表
SELECT datname FROM pg_database;

1.2.2 创建 / 删除 / 切换数据库

sql

-- 创建库
CREATE DATABASE mydb;
-- 删除库（谨慎！）
DROP DATABASE mydb;
-- 切换库
\c mydb

1.2.3 查看数据库大小

sql

-- 原始字节
SELECT pg_database_size('mydb');
-- 友好格式
SELECT pg_size_pretty(pg_database_size('mydb'));

1.3 数据表基础操作

1.3.1 查看表

sql

-- 当前 schema 下表
\dt
-- 表+视图+序列
\d
-- 所有表（含系统表）
\dt *.*
-- SQL 查询 public 模式下表
SELECT * FROM pg_tables WHERE schemaname = 'public';

1.3.2 创建 / 复制 / 删除表

sql

-- 创建表
CREATE TABLE test(id INT, name CHAR(10), age INT);
-- 复制表（结构+数据）
CREATE TABLE test2 AS TABLE test;
-- 删除表
DROP TABLE test2;

1.3.3 查看表结构

sql

\d test

1.4 模式（Schema）管理

Schema = 数据库内逻辑分组，类似文件夹，解决同名表冲突、权限隔离、多业务模块隔离。

1.4.1 常用命令

sql

-- 创建模式
CREATE SCHEMA hr;
-- 删除空模式
DROP SCHEMA hr;
-- 强制删除（含对象）
DROP SCHEMA hr CASCADE;
-- 查看所有模式
\dn
-- 查看当前模式
SELECT current_schema();
-- 切换搜索路径（优先级）
SET search_path TO hr, public;

1.4.2 Schema 隔离实战

sql

-- 同一库不同 Schema 建同名表
CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE TABLE schema1.users(id INT);
CREATE TABLE schema2.users(id INT);

-- 跨 Schema 查询
SELECT * FROM schema1.users;
SELECT * FROM schema2.users;

-- 切换默认 Schema
SET search_path TO schema1;
SELECT * FROM users; -- 自动访问 schema1.users

1.5 数据增删改查（DML）

sql

-- 插入
INSERT INTO test VALUES(1,'zhangsan',18);
-- 查询
SELECT * FROM test;
-- 更新
UPDATE test SET age=20 WHERE id=1;
-- 删除
DELETE FROM test WHERE id=1;

---

二、备份与恢复（数据安全生命线）

PG 提供三种备份方案：SQL 转储、文件系统备份、连续归档（WAL），日常优先使用 pg_dump / pg_dumpall。

2.1 SQL 转储（pg_dump）

适合单库备份、跨版本迁移、小 / 中型库。

bash

运行

# 基础备份
pg_dump mydb > mydb_$(date +%Y%m%d).sql
# 压缩备份（节省空间）
pg_dump mydb | gzip > mydb_$(date +%Y%m%d).sql.gz
# 指定主机/端口/用户
pg_dump -h 127.0.0.1 -p 5432 -U postgres mydb > mydb.sql

2.2 恢复 SQL 转储

bash

运行

# 先创建空库
createdb -T template0 mydb
# 恢复
psql mydb < mydb.sql
# 出错即终止（保证一致性）
psql --set ON_ERROR_STOP=on mydb < mydb.sql
# 单事务恢复（全成功或全回滚）
psql -1 mydb < mydb.sql

2.3 全集群备份（pg_dumpall）

备份所有库 + 角色 + 表空间，适合整机迁移、初始化备份。

bash

运行

pg_dumpall > all_$(date +%Y%m%d).sql
# 恢复
psql -f all.sql postgres

2.4 补充：生产备份最佳实践

1. 每日全量 + 定时 WAL 归档，支持时间点恢复（PITR）
2. 备份文件异地存储，避免单机故障
3. 定期恢复演练，确保备份可用
4. 大库使用并行备份：pg_dump -j 4 -Fd mydb -f backup_dir

---

三、远程连接配置（开发 / 生产必配）

3.1 修改监听地址

文件路径：

• yum/dnf 安装：/var/lib/pgsql/data/postgresql.conf
• 源码安装：/usr/local/pgsql/data/postgresql.conf

修改：

ini

listen_addresses = '*'

3.2 配置客户端认证（pg_hba.conf）

ini

# 允许所有 IP 密码认证（生产推荐）
host all all 0.0.0.0/0 scram-sha-256
# 本地信任（用于免密本地登录）
host all all 127.0.0.1/32 trust

认证方式说明：

• trust：免密（仅内网测试）
• scram-sha-256：强加密（生产首选）
• md5：兼容旧版

3.3 重启生效

bash

运行

systemctl restart postgresql
# 验证端口监听
ss -tnl | grep 5432

3.4 远程连接测试

bash

运行

psql -h 192.168.1.100 -p 5432 -U postgres -d mydb

---

四、密码重置（忘记密码急救）

1. 备份 pg_hba.conf
2. 本地认证改为 trust

   ini

   host all all 127.0.0.1/32 trust
   

3. 重启服务
4. 登录修改密码

   sql

   ALTER USER postgres WITH PASSWORD '新密码';
   

5. 恢复 pg_hba.conf 配置，重启

---

五、生产环境必补：高级维护知识点（博客加分项）

5.1 自动清理（Autovacuum）—— 防膨胀、保性能

PG 采用 MVCC 多版本机制，更新 / 删除会产生死元组（dead tuple），不清理会导致：

• 表膨胀、磁盘暴涨
• 查询变慢、索引失效
• 事务 ID 回绕风险（数据丢失）

5.1.1 核心命令

sql

-- 轻量清理（不锁表，日常用）
VACUUM;
-- 清理+更新统计信息
VACUUM ANALYZE;
-- 全量清理（锁表、回收磁盘空间，低峰执行）
VACUUM FULL;

5.1.2 自动清理配置（postgresql.conf）

ini

autovacuum = on                # 开启自动清理
autovacuum_naptime = 60s       # 检查间隔
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

生产建议：高频更新大表单独配置存储参数，避免自动清理不及时。

5.2 索引维护（REINDEX）

索引频繁更新会出现碎片化、空间浪费、效率下降。

sql

-- 重建单表索引
REINDEX TABLE test;
-- 重建单个索引
REINDEX INDEX idx_test_id;
-- 并发重建（不锁表，生产推荐）
CREATE INDEX CONCURRENTLY idx_test_id_new ON test(id);
DROP INDEX idx_test_id;
ALTER INDEX idx_test_id_new RENAME TO idx_test_id;

5.3 性能监控与慢查询定位

5.3.1 查看活动会话

sql

SELECT pid, usename, datname, query, state FROM pg_stat_activity;
-- 杀掉慢查询
SELECT pg_terminate_backend(pid);

5.3.2 开启慢查询日志

ini

logging_collector = on
log_min_duration_statement = 1000  # 记录超过1s的SQL
log_statement = all

5.3.3 pg_stat_statements（最强 SQL 分析工具）

sql

-- 启用扩展
shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

-- 查最耗时间 SQL
SELECT queryid, query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

5.4 日志管理（排错必备）

ini

log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d    # 每日切割
log_rotation_size = 100MB

配合 logrotate 自动归档 + 压缩，保留 30 天。

5.5 WAL 日志与空间管理

WAL（预写日志）保证崩溃安全，但会持续占用磁盘。

ini

max_wal_size = 8GB        # 自动清理阈值
min_wal_size = 2GB

手动清理归档 WAL：

bash

运行

pg_archivecleanup /archive 000000010000000000000010

5.6 常用系统视图（运维神器）

视图	用途
pg_stat_activity	活跃连接 / 执行中 SQL
pg_stat_user_tables	表增删改查统计、死元组
pg_stat_user_indexes	索引使用次数
pg_statio_user_tables	缓存命中率
pg_database_size	数据库大小

---

六、安全加固（生产环境严禁裸奔）

1. 禁用超级用户远程连接，使用业务普通用户
2. 密码策略：长度 ≥12 位，字母 + 数字 + 符号
3. 最小权限原则：按库 / 表授权

   sql

   CREATE USER appuser WITH PASSWORD 'xxx';
   GRANT CONNECT ON DATABASE mydb TO appuser;
   GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public TO appuser;
   

4. SSL 远程连接，防止明文传输
5. 定期修改密码，审计登录日志

---

七、自动化运维脚本（直接放服务器用）

7.1 每日备份脚本

bash

运行

#!/bin/bash
DATE=$(date +%Y%m%d)
BACK_DIR=/backup/pg
mkdir -p $BACK_DIR
pg_dump -U postgres mydb | gzip > $BACK_DIR/mydb_$DATE.sql.gz
find $BACK_DIR -name "*.sql.gz" -mtime +7 -delete

7.2 定期清理与统计更新

bash

运行

#!/bin/bash
psql -U postgres -d mydb -c "VACUUM ANALYZE;"

7.3 加入 crontab

bash

运行

# 每日2点备份
0 2 * * * /backup/pg_backup.sh
# 每日4点清理优化
0 4 * * * /backup/pg_vacuum.sh

---

八、总结

PostgreSQL 日常维护的核心就是：稳连接、控权限、勤备份、常清理、监控到位。本文覆盖：

• 基础操作：登录、库表、Schema、DML
• 运维核心：备份恢复、远程连接、密码重置
• 生产增强：Autovacuum、索引、监控、日志、安全、自动化脚本