跳到主要内容

PostgreSQL

介绍​

PostgreSQL是世界上最先进的开源关系型数据库管理系统。经过30多年的积极开发，PostgreSQL已经赢得了业界的良好声誉，拥有可靠性、功能稳健性和性能方面的卓越表现。

作为一个功能强大的企业级数据库，PostgreSQL具有许多优势：

  • 全面的SQL合规性：支持大部分SQL标准特性
  • 强大的扩展性：可通过扩展系统添加新功能和数据类型
  • 多版本并发控制(MVCC)：提供高并发性能
  • 丰富的数据类型：除了标准类型外，还支持JSON、XML、地理信息等
  • 强大的索引技术：支持多种索引类型，如B-tree、Hash、GiST、SP-GiST等
  • 企业级安全特性：行级安全、强认证机制、细粒度访问控制
  • 出色的生态系统：丰富的工具、扩展和社区支持

无论是构建简单的应用程序还是管理TB级数据仓库，PostgreSQL都是一个值得信赖的选择。

官方网站​

访问PostgreSQL官方网站可获取最新版本、文档和社区资源。

Docker方式安装​

使用Docker安装PostgreSQL是目前最简便、可移植性最高的方法之一。它消除了不同操作系统间的安装差异，并提供了简单的环境变量配置方式。

目录结构​

首先创建一个项目目录并设置以下结构：

/home/username/                 # 用户家目录
├── compose/                    # 存放所有compose配置文件
│   ├── postgres/               # PostgreSQL的配置目录
│   │   ├── compose.yml         # Docker Compose配置文件
│   │   └── .env                # 环境变量配置文件
│   └── ... 其他服务
│
└── docker/                     # 存放所有容器的持久化数据
    ├── postgres/               # PostgreSQL数据目录
    │   └── data/               # 数据文件
    └── ... 其他服务数据

compose.yml文件​

创建compose.yml文件

networks:
  default:
    external: true
    name: ${DOCKER_MY_NETWORK}
services:
  postgres:
    image: postgres:15
    container_name: postgres
    restart: unless-stopped
    ports:
      - 5432:5432
    volumes:
      - ${DOCKER_HOME}/postgres/data:/var/lib/postgresql/data
      - /etc/localtime:/etc/localtime:ro
      - /etc/timezone:/etc/timezone:ro
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: tree
    networks:
      default:
        ipv4_address: 172.20.0.19
    deploy:
      resources:
        limits:
          cpus: "0.5"
          memory: 500M

.env文件​

创建.env环境变量配置文件：

POSTGRES_PASSWORD=your_secure_password
DOCKER_MY_NETWORK=my_network
DOCKER_HOME=/home/user/docker

请修改POSTGRES_PASSWORD为一个安全的密码。

启动服务​

# 启动服务
docker compose up -d

# 查看日志
docker compose logs -f

# 停止服务
docker compose down

连接到PostgreSQL​

服务启动后，你可以使用任何PostgreSQL客户端工具连接到数据库：

# 使用psql命令行客户端(从容器内部)
docker exec -it postgres psql -U admin -d mydatabase

# 或者使用本地安装的psql
psql -h localhost -p 5432 -U admin -d mydatabase

常见配置调优​

PostgreSQL默认配置适用于大多数情况，但在生产环境中，你可能需要根据硬件资源和工作负载特性进行调优。以下是一些重要的配置参数：

内存配置​

添加自定义配置文件可以通过卷挂载实现。首先创建postgres/custom.conf文件：

# 内存参数
shared_buffers = 1GB                  # 建议为系统内存的25%
work_mem = 32MB                       # 复杂查询的工作内存
maintenance_work_mem = 256MB          # 维护操作的内存
effective_cache_size = 3GB            # 系统可用内存估计值(通常为系统内存的75%)

# 检查点配置
checkpoint_timeout = 15min            # 检查点间隔时间
max_wal_size = 2GB                    # WAL最大大小

然后在compose.yml中添加此配置文件的挂载：

volumes:
  - ./data:/var/lib/postgresql/data
  - ./custom.conf:/etc/postgresql/postgresql.conf

并在环境变量中添加指令使用此配置文件：

environment:
  # ...现有环境变量...
  - POSTGRES_INITDB_ARGS=--data-checksums
  - POSTGRES_HOST_AUTH_METHOD=md5
  - POSTGRES_INITDB_WALDIR=/var/lib/postgresql/wal
  - POSTGRES_CONFIG_FILE=/etc/postgresql/postgresql.conf

备份与恢复​

创建备份​

# 创建SQL备份
docker exec -t postgres pg_dump -U admin mydatabase > backup_$(date +%Y-%m-%d_%H-%M-%S).sql

# 创建压缩备份
docker exec -t postgres pg_dump -U admin mydatabase | gzip > backup_$(date +%Y-%m-%d_%H-%M-%S).sql.gz

恢复备份​

# 恢复SQL备份
cat backup.sql | docker exec -i postgres psql -U admin -d mydatabase

# 恢复压缩备份
gunzip < backup.sql.gz | docker exec -i postgres psql -U admin -d mydatabase

安全最佳实践​

  1. 使用非默认端口：修改compose.yml中的端口映射，避免使用默认的5432端口
  2. 设置强密码：使用随机生成的强密码
  3. 限制网络访问：仅允许需要的容器或网络访问PostgreSQL
  4. 定期更新：保持PostgreSQL镜像更新到最新安全版本
  5. 启用SSL：在生产环境中配置SSL连接

常见问题排解​

  1. 连接失败：检查端口映射和网络配置
  2. 权限问题：确保data目录具有正确的权限（通常为700或750）
  3. 性能问题：检查资源使用情况并调整配置参数
  4. 数据一致性：启用数据校验和（--data-checksums）以早期检测存储问题

结语​

PostgreSQL结合Docker提供了一种灵活、可移植且易于管理的数据库解决方案。通过本指南中的配置，你可以快速部署一个安全、高性能的PostgreSQL实例，为你的应用程序提供强大的数据存储能力。

随着业务需求的增长，PostgreSQL还提供了丰富的扩展选项，如主从复制、连接池和分区表等高级特性，可满足各种规模的需求。

最后于 更新