PostgreSQL 安装、加固与自动备份

前置条件

开始之前，先确认这些都具备

• 一台至少 2 GB 内存的服务器与 sudo 权限
• 已配置防火墙

操作步骤

共 8 步

1. 步骤 01 / 08

   安装并确认集群状态 #

   Debian 系用 pg_lsclusters 查看集群：它会告诉你版本号、端口和数据目录 —— 后面所有配置文件的路径都带着这个版本号，先看清楚能省很多困惑。

   sudo apt update && sudo apt install -y postgresql postgresql-contrib
   
   pg_lsclusters
   systemctl status postgresql --no-pager
   psql --version

2. 步骤 02 / 08

   建库、建角色，并处理 PG15 的 schema 权限 #

   从 PostgreSQL 15 开始，普通角色不再自动拥有 public schema 的 CREATE 权限。只做 GRANT ALL ON DATABASE 是不够的，应用建表时会报权限错误 —— 这是从旧版本迁移过来的人最常撞的一堵墙。

   -- sudo -u postgres psql
   CREATE DATABASE appdb;
   CREATE USER appuser WITH ENCRYPTED PASSWORD '换成一个强密码';
   GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
   
   -- PostgreSQL 15 及以后必须补这一步
   \c appdb
   GRANT ALL ON SCHEMA public TO appuser;
   ALTER DATABASE appdb OWNER TO appuser;

3. 步骤 03 / 08

   确认只监听本地，并使用 scram-sha-256 #

   Debian 的默认值本来就是只监听本地，这个默认值是对的，别改。认证方式确认为 scram-sha-256（比旧的 md5 强），本地连接用 peer 即可。

   # 路径里的 15 换成你的实际版本号
   grep -E "^listen_addresses|^port" /etc/postgresql/15/main/postgresql.conf
   grep -vE "^\s*#|^$" /etc/postgresql/15/main/pg_hba.conf
   
   # 期望看到类似：
   #   local   all   postgres            peer
   #   host    all   all   127.0.0.1/32  scram-sha-256

4. 步骤 04 / 08

   按内存量调几个关键参数 #

   默认配置非常保守，是为了能在任何机器上启动，而不是为了跑得快。经验起点：shared_buffers 取内存的四分之一，effective_cache_size 取四分之三，work_mem 从 8–16 MB 起步。work_mem 是每个排序操作各占一份，并发高时不要给大。

   # /etc/postgresql/15/main/conf.d/tuning.conf
   # 下面这组数值对应 4 GB 内存的机器，请按自己的配置换算
   shared_buffers = 1GB
   effective_cache_size = 3GB
   work_mem = 16MB
   maintenance_work_mem = 256MB
   random_page_cost = 1.1          # NVMe 随机读代价接近顺序读
   log_min_duration_statement = 500ms

5. 步骤 05 / 08

   重启并验证参数生效 #

   shared_buffers 这类参数需要重启而不是 reload。改完用 SHOW 确认，别假设写进文件就生效了 —— 语法错误会导致服务起不来，先确认服务状态。

   sudo systemctl restart postgresql
   systemctl status postgresql --no-pager
   
   sudo -u postgres psql -c "SHOW shared_buffers;"
   sudo -u postgres psql -c "SHOW work_mem;"

6. 步骤 06 / 08

   远程管理用 SSH 端口转发，不要开放 5432 #

   需要用本地的图形客户端连库时，用 SSH 把远端的 5432 映射到本地，数据库端口始终不暴露在公网。命令跑在你自己的电脑上，之后客户端连 127.0.0.1:5432 即可。

   ssh -N -L 5432:127.0.0.1:5432 -p 22022 [email protected]
   
   # 另开一个终端验证
   psql -h 127.0.0.1 -U appuser -d appdb

7. 步骤 07 / 08

   每日自动导出 #

   pg_dump 的自定义格式（-Fc）体积小、支持并行恢复、可以只恢复某几张表。导出文件交给 restic 之类的工具再传到异地，这样才构成完整的备份链条。

   sudo mkdir -p /var/backups/pg && sudo chown postgres:postgres /var/backups/pg
   
   sudo -u postgres pg_dump -Fc appdb -f /var/backups/pg/appdb-$(date +%F).dump
   ls -lh /var/backups/pg
   
   # 恢复演练（务必恢复到一个新库，不要覆盖生产库）
   sudo -u postgres createdb appdb_restore_test
   sudo -u postgres pg_restore -d appdb_restore_test /var/backups/pg/appdb-$(date +%F).dump
   sudo -u postgres psql -d appdb_restore_test -c "\dt"

8. 步骤 08 / 08

   看慢查询 #

   前面已经把 log_min_duration_statement 设成 500 毫秒，超过这个时间的语句都会进日志。数据库「变慢」十有八九是某一条没走索引的查询，而不是机器不够快。

   sudo tail -f /var/log/postgresql/postgresql-15-main.log
   
   # 在 psql 里看单条语句的执行计划
   # EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

相关教程

接下来通常会看这几篇