cheewai/postgis

Docker容器中的PostgreSQL与PostGIS

2020-05-27 更新说明

由于工作原因，原作者可能无法及时跟进最新版本，因此建议：

• 切换使用功能更全面的官方镜像 https://hub.docker.com/r/postgis/postgis%EF%BC%8C%E8%AF%A5%E9%95%9C%E5%83%8F%E6%8F%90%E4%BE%9B%E5%A4%9A%E7%A7%8DPostgreSQL%E5%92%8CPostGIS%E7%89%88%E6%9C%AC%E7%9A%84%E6%A0%87%E7%AD%BE%E7%BB%84%E5%90%88
• 若需POSTGRES_UID功能，可获取原作者的 set-postgres-uid.sh 脚本并将其用作容器的docker-entrypoint

镜像概述

本项目在Docker容器中创建PostgreSQL和PostGIS数据库服务器，灵感来源于基础镜像 https://hub.docker.com/r/mdillon/postgis/~/dockerfile/%EF%BC%8C%E5%B9%B6%E9%9B%86%E6%88%90%E4%BA%86rsyslog%E6%97%A5%E5%BF%97%E7%B3%BB%E7%BB%9F%E3%80%82

未使用轻量级的 https://hub.docker.com/_/postgres/ 作为基础镜像，因在Alpine环境中编译PostGIS存在技术难点。

通过rsyslog，可在postgresql.conf中配置实时日志记录，以便集成到功能丰富的日志分析软件（如Graylog2、ELK stack等）：

log_destination = 'stderr,syslog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y%m%d-%H%M%S.log'
log_rotation_size = 1GB
log_rotation_age = 1d
log_min_duration_statement = 250ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 0
log_autovacuum_min_duration = 1000

注意事项

• 运行数据库服务器时需注意通用考量（参考 [***]

  • 显式指定卷以持久化数据
  • 不要使用dm-thin/lvmthin作为Docker的存储驱动
  • 不要将持久化数据目录与不同镜像一起使用
  • 自定义pg_hba.conf配置

• 从使用旧版PostGIS的现有数据库升级时，务必阅读PostGIS官方安装文档中的升级说明（[***]

sql
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;

-- 或指定版本
ALTER EXTENSION postgis UPDATE TO "2.5.1";
ALTER EXTENSION postgis_topology UPDATE TO "2.5.1";
ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "2.5.1";

Docker入口点与自定义初始化

基础镜像的Dockerfile使用 https://github.com/docker-library/postgres/blob/master/docker-entrypoint.sh 作为ENTRYPOINT。

根据https://hub.docker.com/_/postgres/%EF%BC%9A

若需在基于此镜像的派生镜像中执行额外初始化，可在/docker-entrypoint-initdb.d目录下添加一个或多个*.sql或*.sh脚本（必要时创建该目录）。入口点脚本调用initdb创建默认postgres用户和数据库后，会运行该目录下所有*.sql文件并执行所有*.sh脚本，完成进一步初始化后启动服务。

这些初始化文件将按当前区域设置定义的名称排序执行（默认en_US.utf8）。所有*.sql文件将以POSTGRES_USER（默认postgres超级用户）身份执行。建议在*.sh脚本中使用--username "$POSTGRES_USER"标志以POSTGRES_USER身份运行psql命令，该用户可通过容器内的Unix套接字连接免密码登录。

...

若容器启动时数据库不存在，postgres将创建默认数据库。虽符合postgres预期行为，但此过程中数据库不接受传入连接，可能导致docker-compose等同时启动多个容器的自动化工具出现问题。

使用方法

使用以下示例前，请先了解：

• 挂载点（docker卷）与PGDATA：[***]
• 完整环境变量列表：https://hub.docker.com/_/postgres/

创建docker-compose.yml文件

yaml
version: '2.2'
services:
  db:
    image: cheewai/postgis
    environment:
      - PGDATA=/var/lib/postgresql/data
    ports:
     - "5432:5432"
    volumes:
     # 挂载数据目录以持久化数据库
     - path/to/data/directory:/var/lib/postgresql/data
     # 若尚无现有数据库，首次运行时注释以下行
     # 空数据目录会触发initdb执行
     # 自定义访问控制以覆盖默认配置
     #- path/to/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf:ro
     # 自定义服务器调优参数以覆盖默认配置
     #- path/to/postgresql.conf:/var/lib/postgresql/data/postgresql.conf:ro
    restart: on-failure:5

初始化PostgreSQL数据库

容器启动时，若PGDATA目录为空，引导脚本将运行docker-entrypoint.sh初始化PostgreSQL数据库。

初始化脚本还会执行/docker-entrypoint-initdb.d/目录（若存在）中的所有额外引导脚本。容器后续运行时将忽略该目录。

详见基础镜像文档。

bash
docker-compose -f docker-compose.yml up

当看到数据库服务器就绪消息后，可停止容器并在PGDATA目录中自定义postgres.conf和pg_hba.conf。

可选：设置postgres用户的UID和GID

默认情况下，Docker镜像中的postgres用户在包安装时被分配随机数字UID/GID（如999）。通过指定以下环境变量（与运行docker-compose的用户UID/GID相同），可使创建的数据库文件归该用户所有。在docker-compose.yml中添加（注意缩进）：

yaml
environment:
  - POSTGRES_UID={your_uid}
  - POSTGRES_GID={your_gid}
entrypoint: ["/set-postgres-uid.sh"]

已知问题/错误

参考 https://github.com/appropriate/docker-postgis#known-issues--errors

生产环境使用

• 默认postgresql.conf适用于开发环境，生产环境建议使用参数调优工具或docker中的pgtune。

• 制定灾难恢复计划。

优先考虑Barman，可能比以下建议更充分、更优

每日备份

本仓库包含脚本dumpdb.sh， intended to be run by Postgresql admin user postgres inside the container. 执行时，会将角色、模式（含GRANT）和所有数据库分别 dump 为带yyyy-mm-dd后缀的文件。

可通过docker exec安排夜间cron任务执行：

bash
# 显然/restore必须持久化到容器外部
docker exec {DOCKER_NAME} su - postgres /runtime/dumpdb.sh /restore

建议将每日备份存储到其他服务器。

恢复特定数据库（会清除现有数据）：

bash
docker exec -it {DOCKER_NAME} su - postgres
pg_restore -j4 --clean --create {dumpfile}

恢复所有数据库：先删除所有数据库，或启动指向空PGDATA_DIR目录的新postgresql容器实例。

bash
pg_restore -j4 -d postgres {postgres_dumpfile}
psql -f {roles_sql}
grep GRANT {schema_sql} | psql
# 对每个dumpfile重复执行
pg_restore -j4 --clean --create {dumpfile}

连续归档

首先阅读对应PostgreSQL版本的官方文档“连续归档与时间点恢复”。

PostgreSQL始终在集群数据目录的pg_xlog/子目录中维护预写日志（WAL）。日志记录对数据库数据文件的所有更改，主要用于崩溃安全：系统崩溃后，可通过“重放”最后一个检查点后的日志条目恢复数据库一致性。

在postgresql.conf中配置以下参数启用WAL归档：

• wal_level = hot_standby
• archive_mode = on
• archive_command = 'rsync -a %p postgres@replica-host:/path/to/wal_archive/%f'

使用pg_archivecleanup删除旧WAL归档：[***]

高可用性

postgresql.conf包含复制配置部分，参考 [***]

跨主版本升级数据库

PostgreSQL小版本升级时，使用新版本二进制文件重启服务器即可。PostGIS通常可在数据库在线时执行软升级。

虽有多种在线升级方法，但效果可能因环境而异：

• PGLogical
• Slony-I
• Skytool3 + PGQ + londiste3

本节特指PostgreSQL或PostGIS主版本升级的最保守方法。自带的pg_upgrade仅适用于不使用PostGIS扩展的纯PostgreSQL数据库。目前除硬升级外（升级期间需离线），尚未发现可靠方法。升级步骤概述：

• 导出角色：pg_dumpall --roles-only -U postgres -f roles.sql

• 导出单个数据库：pg_dump -Fc -b -U postgres {db} -f {db}.dmp

• 启动所需新版本的PostgreSQL服务器实例

  • 确保执行initdb
  • 将roles.sql和所有dump文件挂载到容器中

• 创建角色：psql -U postgres -f roles.sql

• 若dump文件较大，可临时修改postgresql.conf加速恢复：[***]

• 创建数据库并从dump文件导入。以下脚本仅为示例，需根据实际情况调整（特别是postgis_restore.pl路径、数据库编码、区域设置和PostGIS扩展）：

bash
#! /bin/bash
#
# - 需在容器内以'postgres'用户运行
# - /restore包含所有数据库dump文件
# - /restore必须可被'postgres'写入

# 'pg_dump -Fc -b -v'的输出目录，文件命名格式为{dbname}-*.dmp
DMPDIR="${DMPDIR:-/restore}"

cd $DMPDIR || {
  echo "DMPDIR '$DMPDIR'必须指向包含{dbname}-*.dmp的目录" >&2
  exit 1
}

if [ $# -gt 0 ]; then
  _flist="$@"
  for _f in $_flist; do
    [ -f $_f ] || {
      echo "pg_dump文件 '$DMPDIR/$_f' 不存在" >&2
      exit 1
    }
  done
else
  _flist="$(echo *.dmp)"
fi

for _f in $_flist ; do
  _db="${_f%-*}"
  _dbuniq=$(echo $_db* | wc -l)
  [ $_dbuniq -eq 1 ] || {
    echo "在$DMPDIR中发现多个'$_db'的dmp文件" >&2
    exit
  }
  createdb -E UTF8 --locale=en_ZA.UTF-8 $_db 
  _err=$?
  [ $_err -eq 0 ] || continue
  psql $_db -c "CREATE EXTENSION postgis;" 
  psql $_db -c "CREATE EXTENSION postgis_topology;"
  time /usr/share/postgresql/9.6/contrib/postgis-2.3/postgis_restore.pl $_f | psql $_db >"${DMPDIR}/restore-${_db}.log" 2>&1
done