PostgreSQL 数据库环境搭建与基础管理完全指南

# 导入 GPG 密钥
sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg

# 添加 PostgreSQL 官方源（以 Ubuntu 22.04 Jammy 为例）
echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 更新软件包列表
sudo apt update

# 安装 PostgreSQL 17 服务器和客户端工具
sudo apt install -y postgresql-17 postgresql-client-17

# 可选：安装 contrib 扩展包（包含 pg_stat_statements 等常用扩展）
sudo apt install -y postgresql-contrib-17

# 检查服务状态
sudo systemctl status postgresql

# 检查 PostgreSQL 版本
psql --version

# 以 postgres 用户登录测试
sudo -u postgres psql -c "SELECT version();"

sudo -u postgres psql -c "SHOW config_file;"

sudo systemctl restart postgresql

# 方法一：使用 createuser 命令行工具
sudo -u postgres createuser --interactive
# 按提示输入：用户名、是否超级用户、是否允许创建数据库、是否允许创建角色

# 方法二：使用 psql
sudo -u postgres psql

-- 创建登录用户
CREATE USER myuser WITH PASSWORD 'StrongPass123!';

-- 创建超级用户
CREATE USER adminuser WITH SUPERUSER PASSWORD 'AdminPass456!';

-- 创建只读用户
CREATE USER readonly WITH PASSWORD 'ReadOnly789!';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# 备份单个数据库
pg_dump -U myuser -h localhost -d mydb -F c -f /backup/mydb_20260612.dump

# 备份为 SQL 纯文本格式
pg_dump -U myuser -h localhost -d mydb -f /backup/mydb_20260612.sql

# 备份指定表
pg_dump -U myuser -h localhost -d mydb -t mytable -f /backup/mytable.sql

# 压缩备份
pg_dump -U myuser -h localhost -d mydb -F c -Z 9 -f /backup/mydb_20260612.dump

# 并行备份（PostgreSQL 17 支持）
pg_dump -U myuser -h localhost -d mydb -j 4 -F d -f /backup/mydb_parallel/

# 恢复自定义格式备份
pg_restore -U myuser -h localhost -d mydb -v /backup/mydb_20260612.dump

# 若数据库不存在，先创建空数据库
createdb -U myuser -h localhost mydb_new
pg_restore -U myuser -h localhost -d mydb_new /backup/mydb_20260612.dump

# 备份所有数据库（包含用户信息和表空间）
sudo -u postgres pg_dumpall -f /backup/full_backup_20260612.sql

# 注意：pg_dumpall 只能输出 SQL 文本格式

#!/bin/bash
# /usr/local/bin/pg_backup.sh

BACKUP_DIR="/backup/postgresql"
DB_NAME="mydb"
DB_USER="myuser"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
echo "[$(date)] 开始备份 $DB_NAME" >> $LOG_FILE
pg_dump -U $DB_USER -h localhost -d $DB_NAME -F c -Z 6 -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump 2>> $LOG_FILE

# 校验备份文件
if [ $? -eq 0 ]; then
    echo "[$(date)] 备份成功: ${DB_NAME}_${DATE}.dump (大小: $(du -h $BACKUP_DIR/${DB_NAME}_${DATE}.dump | cut -f1))" >> $LOG_FILE
else
    echo "[$(date)] 备份失败!" >> $LOG_FILE
    exit 1
fi

# 删除过期备份
find $BACKUP_DIR -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] 清理完毕" >> $LOG_FILE

# 每天凌晨 3 点执行备份
sudo crontab -e
# 添加以下行：
0 3 * * * /usr/local/bin/pg_backup.sh

sudo apt install -y postgresql-17-postgis-3

# 查看慢查询日志
sudo tail -f /var/log/postgresql/postgresql-17-main.log | grep "duration:"

# 使用 pgBadger 生成可视化报告
sudo apt install -y pgbadger
sudo pgbadger /var/log/postgresql/postgresql-17-main.log -o /tmp/report.html

# 配置 UFW 防火墙，仅允许可信 IP 访问
sudo ufw allow from 192.168.1.0/24 to any port 5432
# 不建议直接对外网暴露 5432 端口

# 生成自签名证书
sudo -u postgres mkdir -p /etc/postgresql/17/main/certs
sudo -u postgres openssl req -new -text -subj "/CN=server" \
  -out /etc/postgresql/17/main/certs/server.req
openssl rsa -in /etc/postgresql/17/main/certs/privkey.pem \
  -out /etc/postgresql/17/main/certs/server.key
rm -f /etc/postgresql/17/main/certs/privkey.pem
openssl req -x509 -in /etc/postgresql/17/main/certs/server.req \
  -text -key /etc/postgresql/17/main/certs/server.key \
  -out /etc/postgresql/17/main/certs/server.crt
chmod 600 /etc/postgresql/17/main/certs/server.key
chown postgres:postgres /etc/postgresql/17/main/certs/server.key