环境：

• PostgreSQL 16
• 单机
• 多应用
• PgBouncer 已规划
• 2C4G，稳定优先

需求：

• 一键执行
• 可放 cron
• 幂等 / 可重复
• 不依赖人工操作

一、应用创建脚本（用户 + 数据库 + 权限）

1.1 脚本用途

一条命令完成：

• 创建应用用户
• 创建数据库
• 初始化 schema 权限
• 设置默认权限
• 设置资源限制

1.2 脚本：create_app.sh

#!/bin/bash
# 用法: ./create_app.sh app_name app_password conn_limit

APP_NAME=$1
APP_PASS=$2
CONN_LIMIT=${3:-20}

if [ -z "$APP_NAME" ] || [ -z "$APP_PASS" ]; then
  echo "Usage: $0 app_name app_password [conn_limit]"
  exit 1
fi

APP_USER="${APP_NAME}_user"
APP_DB="${APP_NAME}_db"

sudo -iu postgres psql <<EOF
DO \$\$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='${APP_USER}') THEN
    CREATE ROLE ${APP_USER}
      LOGIN
      PASSWORD '${APP_PASS}'
      NOSUPERUSER
      NOCREATEDB
      NOCREATEROLE
      NOREPLICATION;
  END IF;
END
\$\$;

DO \$\$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname='${APP_DB}') THEN
    CREATE DATABASE ${APP_DB}
      OWNER ${APP_USER}
      ENCODING 'UTF8'
      TEMPLATE template0;
  END IF;
END
\$\$;
EOF

sudo -iu postgres psql -d ${APP_DB} <<EOF
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO ${APP_USER};

ALTER DEFAULT PRIVILEGES
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${APP_USER};

ALTER DEFAULT PRIVILEGES
GRANT USAGE, SELECT ON SEQUENCES TO ${APP_USER};

ALTER ROLE ${APP_USER} CONNECTION LIMIT ${CONN_LIMIT};
ALTER ROLE ${APP_USER} SET statement_timeout = '30s';
EOF

echo "✔ Application ${APP_NAME} initialized successfully."

使用示例

chmod +x create_app.sh
./create_app.sh order_service 'StrongPass123!' 20

二、自动备份脚本（逻辑备份，cron 用）

2.1 设计原则

• 每个数据库单独备份
• 自动清理过期文件
• 失败直接退出（方便监控）

2.2 脚本：pg_backup.sh

#!/bin/bash

BACKUP_DIR="/data/pg_backup"
RETENTION_DAYS=7
DATE=$(date +%F)

mkdir -p ${BACKUP_DIR}

DB_LIST=$(sudo -iu postgres psql -Atc \
"SELECT datname FROM pg_database WHERE datistemplate = false;")

for DB in $DB_LIST; do
  FILE="${BACKUP_DIR}/${DB}_${DATE}.dump"
  echo "Backing up ${DB} ..."
  sudo -iu postgres pg_dump -Fc ${DB} > ${FILE}
  if [ $? -ne 0 ]; then
    echo "Backup failed for ${DB}"
    exit 1
  fi
done

# 清理过期备份
find ${BACKUP_DIR} -type f -mtime +${RETENTION_DAYS} -delete

echo "✔ Backup completed at $(date)"

手动测试

chmod +x pg_backup.sh
./pg_backup.sh

2.3 cron 计划任务（每天凌晨 2 点）

crontab -e

0 2 * * * /opt/pg/pg_backup.sh >> /var/log/pg_backup.log 2>&1

三、数据库巡检脚本（健康检查）

3.1 巡检目标

• 连接数
• 慢 SQL
• 死锁
• 表膨胀风险
• autovacuum 状态

3.2 pg_stat_statements

pg_stat_statements 解决的是 生产环境最核心的 3 个问题：

1️⃣ 哪些 SQL 真正在吃 CPU 2️⃣ 哪些 SQL 调用次数异常 3️⃣ 性能退化是“新 SQL”还是“老 SQL 变慢”

👉 没有它，所有性能问题都只能靠猜。

pg_stat_statements 是扩展，不是 PostgreSQL 默认自带表。永久开启的“正确姿势”（按顺序来）

✅ 第 1 步：shared_preload_libraries（必须）

这是唯一必须重启数据库的一步。

sudo -iu postgres psql postgres

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

重启：

systemctl restart postgresql-16

确认：

sudo -iu postgres psql -c "SHOW shared_preload_libraries;"

必须看到：

pg_stat_statements

✅ 第 2 步：设置推荐参数（生产基线）

这一步决定 开得稳不稳。

ALTER SYSTEM SET pg_stat_statements.max = 5000;
ALTER SYSTEM SET pg_stat_statements.track = 'top';
ALTER SYSTEM SET pg_stat_statements.track_utility = off;
ALTER SYSTEM SET pg_stat_statements.save = on;

解释（很关键）：

✅ 第 3 步：reload 即可（不用重启）

SELECT pg_reload_conf();

✅ 第 4 步：创建扩展（一次）

建议在 postgres 库：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

验证：

SELECT * FROM pg_stat_statements LIMIT 1;

3.3 巡检 SQL：pg_health.sql

-- 连接数
SELECT count(*) AS connections FROM pg_stat_activity;

-- 长事务
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND now() - xact_start > interval '5 minutes';

-- 慢 SQL TOP 10
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 死锁
SELECT deadlocks FROM pg_stat_database;

-- autovacuum 停止表
SELECT relname, last_autovacuum
FROM pg_stat_user_tables
WHERE last_autovacuum IS NULL;

3.4 巡检执行脚本：pg_health_check.sh

#!/bin/bash

REPORT_DIR="/data/pg_health"
DATE=$(date +%F_%H%M)

mkdir -p ${REPORT_DIR}

sudo -iu postgres psql <<EOF > ${REPORT_DIR}/health_${DATE}.txt
\i /opt/pg/pg_health.sql
EOF

echo "✔ Health check done: ${REPORT_DIR}/health_${DATE}.txt"

3.5 cron（每小时一次）

0 * * * * /opt/pg/pg_health_check.sh

四、推荐目录结构（规范）

/opt/pg/
├── create_app.sh
├── pg_backup.sh
├── pg_health.sql
├── pg_health_check.sh

本文由 best 创作，采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用，但需署名作者且注明文章出处。