一、基本操作

1. 连接数据库

使用 psql 命令行工具连接：

psql -U username -d dbname -h host -p port

• -U：指定用户名
• -d：指定数据库名
• -h：指定主机（默认本地）
• -p：指定端口（默认 5432）

2. 用户与权限管理

• 创建用户：

  CREATE USER myuser WITH PASSWORD 'mypassword';
  

• 授权：

  -- 授予数据库所有权限
  GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
  -- 授予表的查询、插入权限
  GRANT SELECT, INSERT ON mytable TO myuser;
  

3. 数据库操作

• 创建数据库：

  CREATE DATABASE mydb;
  

• 删除数据库：

  DROP DATABASE mydb;
  

• 切换数据库（psql 中）：

  \c mydb
  

4. 表操作

• 创建表：

  CREATE TABLE mytable (
      id SERIAL PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      age INT
  );
  

• 查看表结构：

  \d mytable
  

• 插入数据：

  INSERT INTO mytable (name, age) VALUES ('Alice', 30);
  

• 查询数据：

  SELECT * FROM mytable;
  

• 更新数据：

  UPDATE mytable SET age = 31 WHERE name = 'Alice';
  

• 删除数据：

  DELETE FROM mytable WHERE name = 'Alice';
  

5. 常用 psql 元命令

• \l：列出所有数据库
• \dt：列出当前数据库的所有表
• \d table_name：查看表结构
• \q：退出 psql
• \h：查看 SQL 命令帮助
• \?：查看 psql 元命令帮助

二、日常维护

1. 日志管理与监控

在 postgresql.conf 中配置日志：

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 1000  # 记录慢查询（单位：毫秒）
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

日常巡检：每日检查日志，重点关注慢查询、死锁、连接失败等。

2. 数据清理（VACUUM）与事务 ID 管理

PostgreSQL 的 MVCC 机制会产生 “死元组”，需通过 VACUUM 回收空间并防止事务 ID 回卷。

• 自动清理（默认开启）：

  autovacuum = on
  autovacuum_vacuum_scale_factor = 0.2  # 表更新20%时触发
  

• 手动清理：

  VACUUM;  -- 全库常规清理（不锁表）
  VACUUM FULL mytable;  -- 单表深度清理（锁表，大表慎用）
  

• 监控事务 ID 年龄：

  SELECT datname, age(datfrozenxid) FROM pg_database;
  

3. 统计信息更新（ANALYZE）

ANALYZE 更新表和索引的统计信息，供查询优化器使用：

ANALYZE;  -- 全库更新
ANALYZE mytable;  -- 单表更新

4. 备份与恢复

• 逻辑备份（适合小数据量）：

  pg_dump -U username -d mydb -f backup.sql  # 备份
  psql -U username -d mydb -f backup.sql     # 恢复
  

• 物理备份（适合大数据量，支持 PITR）：

  pg_basebackup -U username -D /path/to/backup -Ft -z -P
  

  配合 WAL 归档可实现基于时间点恢复（PITR）。
• 备份验证：定期在测试环境恢复备份，确保数据可用。

5. 性能监控与优化

• 查看活跃连接：

  SELECT * FROM pg_stat_activity;
  

• 查看慢查询（需启用 pg_stat_statements 扩展）：

  SELECT query, calls, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;
  

• 查看缓存命中率（应 > 99%）：

  SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio FROM pg_statio_user_tables;
  

6. 索引维护

• 查找未使用的索引：

  SELECT schemaname, relname, indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;
  

• 重建膨胀索引（生产环境推荐 CONCURRENTLY 避免锁表）：

  REINDEX INDEX CONCURRENTLY myindex;
  

7. 配置参数检查

定期调整 postgresql.conf 关键参数：

• shared_buffers：系统内存的 25%（不超过 8GB）
• work_mem：单个查询操作内存，根据并发数调整
• maintenance_work_mem：维护操作内存，可设较大
• effective_cache_size：系统内存的 50%-75%

通过以上基本操作和日常维护，可保障 PostgreSQL 数据库的稳定、高效运行。建议将关键任务（如备份、日志巡检）自动化，并建立监控告警机制。