优良网站_泰州网站制作费用_长沙百度快速优化排名_郑州企业网站seo

一、基础持久化部署

1.1 创建专用数据卷

# 创建持久化卷
docker volume create pg_data# 查看卷路径
docker volume inspect pg_data --format '{{.Mountpoint}}'
# 输出：/var/lib/docker/volumes/pg_data/_data

1.2 启动PostgreSQL容器

docker run -d \--name postgres-prod \-v pg_data:/var/lib/postgresql/data \-e POSTGRES_PASSWORD=mysecretpassword \-p 5432:5432 \postgres:15-alpine

---

二、生产级配置优化

2.1 自定义配置文件

# 创建配置目录
mkdir -p ~/postgres/{conf,data}# 生成核心配置文件
cat > ~/postgres/conf/postgresql.conf <<EOF
listen_addresses = '*'
max_connections = 200
shared_buffers = 256MB
work_mem = 16MB
EOFcat > ~/postgres/conf/pg_hba.conf <<EOF
host all all 0.0.0.0/0 scram-sha-256
EOF

2.2 启动优化容器

docker run -d \--name postgres-prod \-v pg_data:/var/lib/postgresql/data \-v ~/postgres/conf:/etc/postgresql/custom-config \-e POSTGRES_USER=prod_admin \-e POSTGRES_PASSWORD=StrongPass!2024 \-e POSTGRES_DB=app_db \-e PGDATA=/var/lib/postgresql/data/pgdata \--shm-size=1g \postgres:15-alpine \-c config_file=/etc/postgresql/custom-config/postgresql.conf \-c hba_file=/etc/postgresql/custom-config/pg_hba.conf

---

三、数据备份与恢复

3.1 物理备份方案

# 全量备份
docker exec postgres-prod pg_basebackup -D /backup -Ft -z -Xs -P -U replicator# 备份文件导出
docker cp postgres-prod:/backup/base.tar.gz ~/backups/# 定时备份（crontab示例）
0 2 * * * docker exec postgres-prod pg_dump -U prod_admin -Fc app_db > /backups/daily/app_db_$(date +\%Y\%m\%d).dump

3.2 逻辑恢复操作

# 创建恢复数据库
docker exec -i postgres-prod psql -U prod_admin -c "CREATE DATABASE restoredb;"# 执行恢复
docker exec -i postgres-prod pg_restore -U prod_admin -d restoredb < /backups/app_db_20240520.dump

---

四、监控与维护

4.1 监控配置

# 启动Prometheus监控容器
docker run -d \--name prometheus \-v ~/monitor/prometheus.yml:/etc/prometheus/prometheus.yml \-p 9090:9090 \prom/prometheus# 示例监控目标配置
scrape_configs:- job_name: 'postgres'static_configs:- targets: ['postgres-prod:9187']

4.2 维护任务自动化

-- 设置自动清理任务
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_max_workers = 3;
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;

---

五、高可用集群搭建

5.1 流复制配置

# 主库配置
docker exec postgres-prod sh -c "echo 'wal_level = replica' >> /etc/postgresql/custom-config/postgresql.conf"
docker exec postgres-prod sh -c "echo 'max_wal_senders = 10' >> /etc/postgresql/custom-config/postgresql.conf"# 创建复制用户
docker exec -i postgres-prod psql -U prod_admin -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'RepPass!2024';"

5.2 从库部署

docker run -d \--name postgres-replica \-v pg_replica:/var/lib/postgresql/data \--link postgres-prod:master \-e POSTGRES_PASSWORD=StandbyPass!2024 \postgres:15-alpine \-c primary_conninfo='host=master port=5432 user=replicator password=RepPass!2024'

---

六、安全加固措施

6.1 权限控制

-- 创建应用只读用户
CREATE ROLE app_readonly WITH LOGIN PASSWORD 'ReadOnly123' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '2025-12-31';-- 授予权限
GRANT CONNECT ON DATABASE app_db TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

6.2 加密存储

# 创建加密卷
docker volume create \--driver local \--opt type=tmpfs \--opt device=tmpfs \--opt o=size=100m,encryption=aes-xts-plain64 \pg_secure

---

七、Docker Compose全栈部署

7.1 docker-compose.yml

version: '3.8'services:postgres:image: postgres:15-alpinecontainer_name: pg-mainvolumes:- pg_data:/var/lib/postgresql/data- ./config:/etc/postgresql/custom-configenvironment:POSTGRES_USER: adminPOSTGRES_PASSWORD: SecurePass!2024POSTGRES_DB: app_prodports:- "5432:5432"networks:- db-netpgadmin:image: dpage/pgadmin4environment:PGADMIN_DEFAULT_EMAIL: admin@example.comPGADMIN_DEFAULT_PASSWORD: PgAdmin!2024ports:- "8080:80"networks:- db-netvolumes:pg_data:networks:db-net:driver: bridge

---

附：生产检查清单

✅ 每日验证备份可恢复性
✅ 监控磁盘空间使用率（阈值≤85%）
✅ 定期轮换数据库密码
✅ 每季度执行故障转移演练
✅ 启用SQL审计日志

架构师提示：将数据库视为有状态服务，持久化只是基础，需配合备份、监控、高可用三位一体才能构建可靠存储方案。推荐使用Patroni等工具实现自动故障转移。