PostgreSQL VPS托管：数据库服务器部署指南

在Ubuntu/Debian VPS上安装最新版PostgreSQL：

apt update
apt install -y postgresql postgresql-contrib

# 检查服务状态
systemctl status postgresql
systemctl enable postgresql

# 切换到postgres系统用户
sudo -u postgres psql

创建数据库和应用用户：

-- 在psql交互界面中执行
CREATE DATABASE production_db;
CREATE USER app_user WITH ENCRYPTED PASSWORD 'very_strong_password';
GRANT ALL PRIVILEGES ON DATABASE production_db TO app_user;

-- 限制连接权限（最小权限原则）
REVOKE ALL ON DATABASE production_db FROM PUBLIC;
GRANT CONNECT ON DATABASE production_db TO app_user;
q

查看PostgreSQL版本和数据目录：

postgres --version
sudo -u postgres psql -c "SHOW data_directory;"

默认PostgreSQL安装需要额外加固才适合生产环境。编辑pg_hba.conf配置认证方式：

nano /etc/postgresql/16/main/pg_hba.conf

推荐的生产安全配置（只允许本地socket连接和特定IP）：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    production_db   app_user        127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               reject

编辑postgresql.conf调整安全参数：

listen_addresses = 'localhost'    # 只监听本地（如需远程访问则改为具体IP）
ssl = on                           # 启用SSL
log_connections = on              # 记录连接日志
log_disconnections = on           # 记录断开日志
log_failed_authentications = on  # 记录失败认证

systemctl reload postgresql

根据Anubiz Host VPS的配置调整PostgreSQL性能参数（以4GB RAM VPS为例）：

nano /etc/postgresql/16/main/postgresql.conf

关键性能参数：

# 内存配置（根据RAM按比例调整）
shared_buffers = 1GB           # RAM的25%
effective_cache_size = 3GB     # RAM的75%
work_mem = 64MB                # 每个查询排序操作的内存
maintenance_work_mem = 256MB   # VACUUM和CREATE INDEX使用

# WAL和检查点
wal_buffers = 16MB
checkpoint_completion_target = 0.9
wal_level = replica

# 查询优化器
random_page_cost = 1.1         # NVMe SSD的随机读取接近顺序读取
effective_io_concurrency = 200  # NVMe的并发I/O能力

# 连接数
max_connections = 200

重启PostgreSQL使配置生效：

systemctl restart postgresql

使用pg_stat_statements扩展分析慢查询：

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

在VPS上实施健全的PostgreSQL备份策略是保护数据的关键。以下是完整的备份方案： 逻辑备份（pg_dump）：适合中小型数据库，可以跨版本恢复：

# 备份单个数据库
sudo -u postgres pg_dump production_db | gzip > /var/backups/pg_$(date +%Y%m%d_%H%M%S).sql.gz

# 备份所有数据库（包含角色和权限）
sudo -u postgres pg_dumpall | gzip > /var/backups/pg_all_$(date +%Y%m%d).sql.gz

# 恢复数据库
gunzip < backup.sql.gz | sudo -u postgres psql production_db

配置每日自动备份crontab：

0 3 * * * sudo -u postgres pg_dump production_db | gzip > /var/backups/pg_$(date +%Y%m%d).sql.gz && find /var/backups -name "pg_*.sql.gz" -mtime +30 -delete

对于大型数据库，考虑使用pg_basebackup进行物理备份，并配置WAL归档实现时间点恢复（PITR）。将备份文件定期同步到异地存储（如另一台VPS或加密对象存储），是完整灾备策略的必要组成部分。