Docker 部署 PostgreSQL

快速启动

# 基本运行
docker run -d --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=your_password postgres:16-alpine

# 带数据持久化
docker run -d \
  --name postgres \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=mydb \
  -v postgres_data:/var/lib/postgresql/data \
  postgres:16-alpine

Docker Compose 部署

基础配置
生产配置
多数据库

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: your_password
      POSTGRES_USER: postgres
      POSTGRES_DB: mydb
      TZ: Asia/Shanghai
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_DB: ${POSTGRES_DB:-mydb}
      POSTGRES_INITDB_ARGS: "--encoding=UTF8 --locale=C"
      TZ: Asia/Shanghai
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-postgres}"]
      interval: 10s
      timeout: 5s
      retries: 5
    shm_size: 256mb

volumes:
  postgres_data:

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: your_password
      POSTGRES_USER: postgres
      # 通过初始化脚本创建多个数据库
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init-multiple-databases.sh:/docker-entrypoint-initdb.d/init-multiple-databases.sh

volumes:
  postgres_data:

init-multiple-databases.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE DATABASE db1;
    CREATE DATABASE db2;
    CREATE DATABASE db3;

    CREATE USER user1 WITH PASSWORD 'password1';
    CREATE USER user2 WITH PASSWORD 'password2';

    GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;
    GRANT ALL PRIVILEGES ON DATABASE db2 TO user2;
EOSQL

自定义配置

postgresql.conf

# 连接设置
listen_addresses = '*'
max_connections = 200
superuser_reserved_connections = 3

# 内存设置
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
work_mem = 4MB

# WAL 设置
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
wal_buffers = 16MB

# 查询优化
random_page_cost = 1.1
effective_io_concurrency = 200

# 日志设置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# 慢查询日志
log_statement = 'all'
log_duration = off

# 自动清理
autovacuum = on
autovacuum_max_workers = 3

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             all                                     trust

# IPv4 connections
host    all             all             0.0.0.0/0               scram-sha-256

# IPv6 connections
host    all             all             ::0/0                   scram-sha-256

# Replication connections
host    replication     all             0.0.0.0/0               scram-sha-256

常用命令

# 连接数据库
docker exec -it postgres psql -U postgres -d mydb

# 执行 SQL 文件
docker exec -i postgres psql -U postgres -d mydb < backup.sql

# 查看数据库列表
docker exec postgres psql -U postgres -c '\l'

# 查看数据库大小
docker exec postgres psql -U postgres -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database;"

# 查看连接数
docker exec postgres psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# 查看当前活动查询
docker exec postgres psql -U postgres -c "SELECT pid, usename, application_name, client_addr, query, state FROM pg_stat_activity WHERE state != 'idle';"

# 终止慢查询
docker exec postgres psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '5 minutes';"

备份与恢复

备份

逻辑备份
自动备份脚本

# 备份单个数据库
docker exec postgres pg_dump -U postgres mydb > backup_$(date +%Y%m%d_%H%M%S).sql

# 备份单个数据库（压缩）
docker exec postgres pg_dump -U postgres mydb | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

# 备份所有数据库
docker exec postgres pg_dumpall -U postgres > all_databases_$(date +%Y%m%d_%H%M%S).sql

# 备份指定表
docker exec postgres pg_dump -U postgres -t table_name mydb > table_backup.sql

# 自定义格式备份（支持并行恢复）
docker exec postgres pg_dump -U postgres -Fc mydb > backup.dump

#!/bin/bash
# backup-postgres.sh

BACKUP_DIR="/backup/postgres"
POSTGRES_USER="postgres"
POSTGRES_DB="mydb"
CONTAINER_NAME="postgres"
RETENTION_DAYS=7

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

# 生成备份文件名
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/backup_$TIMESTAMP.sql.gz"

# 执行备份
docker exec $CONTAINER_NAME pg_dump -U $POSTGRES_USER $POSTGRES_DB | gzip > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date): Backup successful - $BACKUP_FILE"

    # 删除旧备份
    find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "$(date): Old backups removed (older than $RETENTION_DAYS days)"
else
    echo "$(date): Backup failed!"
    exit 1
fi

添加到 crontab:

# 每天凌晨 2 点自动备份
0 2 * * * /path/to/backup-postgres.sh >> /var/log/postgres-backup.log 2>&1

恢复

# 从 SQL 文件恢复
docker exec -i postgres psql -U postgres -d mydb < backup.sql

# 从压缩文件恢复
gunzip -c backup.sql.gz | docker exec -i postgres psql -U postgres -d mydb

# 从自定义格式恢复
docker exec -i postgres pg_restore -U postgres -d mydb < backup.dump

# 恢复时创建新数据库
docker exec postgres createdb -U postgres newdb
docker exec -i postgres psql -U postgres -d newdb < backup.sql

# 恢复所有数据库
docker exec -i postgres psql -U postgres < all_databases.sql

主从复制

version: '3.8'

services:
  postgres-master:
    image: postgres:16-alpine
    container_name: postgres-master
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: your_password
      POSTGRES_DB: mydb
    volumes:
      - postgres_master_data:/var/lib/postgresql/data
      - ./master-init.sh:/docker-entrypoint-initdb.d/master-init.sh
    command: |
      postgres
      -c wal_level=replica
      -c max_wal_senders=3
      -c max_replication_slots=3
      -c hot_standby=on

  postgres-slave:
    image: postgres:16-alpine
    container_name: postgres-slave
    restart: always
    ports:
      - "5433:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: your_password
    volumes:
      - postgres_slave_data:/var/lib/postgresql/data
    depends_on:
      - postgres-master
    command: |
      bash -c "
      if [ ! -f /var/lib/postgresql/data/PG_VERSION ]; then
        pg_basebackup -h postgres-master -D /var/lib/postgresql/data -U postgres -v -P -W
        echo 'standby_mode = on' > /var/lib/postgresql/data/recovery.conf
        echo 'primary_conninfo = ''host=postgres-master port=5432 user=postgres password=your_password''' >> /var/lib/postgresql/data/recovery.conf
        echo 'trigger_file = ''/tmp/postgresql.trigger''' >> /var/lib/postgresql/data/recovery.conf
      fi
      postgres
      "

volumes:
  postgres_master_data:
  postgres_slave_data:

master-init.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator_password';
EOSQL

echo "host replication replicator 0.0.0.0/0 md5" >> /var/lib/postgresql/data/pg_hba.conf

性能监控

-- 查看数据库大小
SELECT
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查看表大小
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- 查看索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

-- 查看慢查询
SELECT
    pid,
    now() - query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

-- 查看缓存命中率
SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- 查看连接数统计
SELECT
    state,
    count(*)
FROM pg_stat_activity
GROUP BY state;

-- 查看锁等待
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

性能优化

1. 连接池配置

使用 PgBouncer 作为连接池：

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    # ... postgres 配置

  pgbouncer:
    image: edoburu/pgbouncer:latest
    container_name: pgbouncer
    restart: always
    ports:
      - "6432:5432"
    environment:
      DATABASE_URL: postgres://postgres:your_password@postgres:5432/mydb
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 20
      RESERVE_POOL_SIZE: 5
    depends_on:
      - postgres

2. 索引优化

-- 查找缺失索引的外键
SELECT
    c.conrelid::regclass AS table_name,
    string_agg(a.attname, ', ') AS columns,
    pg_size_pretty(pg_relation_size(c.conrelid)) AS table_size
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
      SELECT 1 FROM pg_index i
      WHERE i.indrelid = c.conrelid
        AND (i.indkey::int[])[0:cardinality(c.conkey)-1] @> c.conkey
  )
GROUP BY c.conrelid, c.conname
ORDER BY pg_relation_size(c.conrelid) DESC;

-- 查找未使用的索引
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

3. VACUUM 和 ANALYZE

-- 手动 VACUUM
VACUUM VERBOSE ANALYZE table_name;

-- 全库 VACUUM
VACUUM VERBOSE ANALYZE;

-- 查看需要 VACUUM 的表
SELECT
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

常见问题

1. 修改密码

ALTER USER postgres WITH PASSWORD 'new_password';

2. 创建只读用户

-- 创建只读用户
CREATE USER readonly WITH PASSWORD 'readonly_password';

-- 授予连接权限
GRANT CONNECT ON DATABASE mydb TO readonly;

-- 授予使用 schema 权限
GRANT USAGE ON SCHEMA public TO readonly;

-- 授予查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 为新表自动授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

3. 终止所有连接

-- 终止指定数据库的所有连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid != pg_backend_pid();

最佳实践

安全

✅ 使用强密码并定期更换
✅ 限制远程连接（配置 pg_hba.conf）
✅ 使用 SSL 连接
✅ 定期更新 PostgreSQL 版本
✅ 最小权限原则（不使用 superuser）

性能

✅ 合理配置内存参数（shared_buffers, work_mem）
✅ 使用连接池（PgBouncer）
✅ 定期 VACUUM 和 ANALYZE
✅ 创建合适的索引
✅ 监控慢查询日志

可靠性

✅ 启用数据持久化（Docker volumes）
✅ 配置主从复制
✅ 定期自动备份
✅ 测试备份恢复流程
✅ 配置健康检查

快速启动​

Docker Compose 部署​

自定义配置​

postgresql.conf​

pg_hba.conf​

常用命令​

备份与恢复​

备份​

恢复​

主从复制​

性能监控​

性能优化​

1. 连接池配置​

2. 索引优化​

3. VACUUM 和 ANALYZE​

常见问题​

1. 修改密码​

2. 创建只读用户​

3. 终止所有连接​

最佳实践​

安全​

性能​

可靠性​

相关资源​

快速启动

Docker Compose 部署

自定义配置

postgresql.conf

pg_hba.conf

常用命令

备份与恢复

备份

恢复

主从复制

性能监控

性能优化

1. 连接池配置

2. 索引优化

3. VACUUM 和 ANALYZE

常见问题

1. 修改密码

2. 创建只读用户

3. 终止所有连接

最佳实践

安全

性能

可靠性

相关资源