PostgreSQL Docker开发环境配置指南

本文档详细介绍如何使用Docker搭建PostgreSQL开发环境，包括环境配置、启动方式、维护方法等内容。

目录结构

1
2
3
4
5
6
7
8

PgSQL_Docker/
├── .env                  # 环境变量配置文件
├── README.md            # 项目说明文档
├── config/              # 配置文件目录
│   ├── pg_hba.conf     # 访问控制配置
│   └── postgresql.conf  # PostgreSQL主配置文件
├── data/               # 数据存储目录
└── logs/               # 日志文件目录

配置文件说明

1. docker-compose.yml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

version: '3.8'

services:
  postgres:
    container_name: postgres-server
    image: postgres:15
    environment:
      - POSTGRES_DB=${POSTGRES_DB}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
    ports:
      - "${POSTGRES_PORT}:5432"
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./logs:/var/log/postgresql
      - ./config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./config/pg_hba.conf:/etc/postgresql/pg_hba.conf
    command: postgres -c 'config_file=/etc/postgresql/postgresql.conf'
    networks:
      - postgres-network
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 10s

networks:
  postgres-network:
    driver: bridge

2. .env 环境变量配置

1
2
3
4
5

# PostgreSQL 配置环境变量
POSTGRES_DB=test_db           # 数据库名称
POSTGRES_USER=data           # 默认超级用户
POSTGRES_PASSWORD=your_password  # 用户密码
POSTGRES_PORT=5432          # 端口映射

3. postgresql.conf 主要配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 连接设置
listen_addresses = '*'
port = 5432
max_connections = 100

# 内存设置
shared_buffers = 128MB
work_mem = 4MB
maintenance_work_mem = 64MB
effective_cache_size = 512MB

# 写入设置
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB

# 日志设置
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB

# 区域设置
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.utf8'

4. pg_hba.conf 访问控制配置

1
2
3
4
5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all            all                                     trust
host    all            all             127.0.0.1/32           scram-sha-256
host    all            all             ::1/128                scram-sha-256
host    all            all             0.0.0.0/0              scram-sha-256

使用说明

1. 启动服务

1
2
3
4
5
6
7
8

# 启动服务
docker-compose up -d

# 查看服务状态
docker-compose ps

# 查看日志
docker-compose logs postgres

2. 连接数据库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 使用超级用户连接
docker-compose exec postgres psql -U data -d test_db

# 查看用户列表和权限
\du

# 查看数据库列表
\l

# 退出psql
\q

3. 停止服务

1
2

# 停止服务
docker-compose down

用户和权限管理

PostgreSQL使用角色（Role）概念来管理用户权限。在我们的配置中：

  1. 通过POSTGRES_USER环境变量创建的用户（本例中为"data"）是超级用户，具有所有权限：
  2. Superuser（超级用户权限）
  3. Create role（创建角色权限）
  4. Create DB（创建数据库权限）
  5. Replication（复制权限）

Bypass RLS（绕过行级安全性）

创建新用户示例：

1

CREATE ROLE username WITH LOGIN PASSWORD 'password';
  1. 授予权限示例：
1

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

数据备份和恢复

备份数据

1
2
3
4
5

# 备份整个数据库
docker-compose exec postgres pg_dump -U data test_db > backup.sql

# 备份特定表
docker-compose exec postgres pg_dump -U data -t table_name test_db > table_backup.sql

恢复数据

1
2

# 恢复数据
docker-compose exec -T postgres psql -U data test_db < backup.sql

性能优化建议

  1. 内存配置
  2. shared_buffers: 建议设置为系统内存的25%
  3. effective_cache_size: 建议设置为系统内存的50%

work_mem: 根据并发连接数调整

写入性能

  1. wal_buffers: 建议设置为16MB
  2. checkpoint_timeout: 可根据写入压力调整

max_wal_size: 根据磁盘空间调整

连接池

  1. 建议使用pgBouncer等连接池管理工具
  2. 避免频繁创建新连接

常见问题处理

  1. 容器无法启动
  2. 检查端口占用：lsof -i :5432
  3. 检查配置文件权限

查看错误日志：docker-compose logs postgres

连接失败

  1. 确认用户名和密码正确
  2. 检查pg_hba.conf配置

验证网络连接

性能问题

  1. 检查慢查询日志
  2. 使用EXPLAIN分析查询计划
  3. 优化索引和查询语句

安全建议

  1. 密码安全
  2. 使用强密码
  3. 定期更换密码

避免在命令行中明文输入密码

网络安全

  1. 限制访问IP
  2. 使用SSL连接

定期更新PostgreSQL版本

权限控制

  1. 遵循最小权限原则
  2. 使用角色管理权限
  3. 启用行级安全性（RLS）

维护建议

  1. 定期维护
  2. 执行VACUUM操作
  3. 更新统计信息

检查日志文件大小

监控

  1. 监控连接数
  2. 监控磁盘使用

监控查询性能

备份策略

  1. 定期备份
  2. 测试恢复流程