tracktor/postgres

PostgreSQL with PostGIS and pg_cron 镜像文档

1. 镜像概述

本镜像基于官方PostgreSQL数据库构建，预集成PostGIS地理空间扩展和pg_cron定时任务调度工具。旨在提供开箱即用的数据库解决方案，满足地理空间数据存储与分析、定时任务自动化执行等场景需求，简化包含地理信息和定时任务的应用部署流程。

2. 核心功能与特性

2.1 PostgreSQL数据库核心功能

• 支持PostgreSQL官方稳定版本的全部功能，包括标准SQL语法、事务管理、ACID特性、多种索引类型（B-tree、Hash、GiST等）及高级特性（JSON支持、全文搜索、存储过程）。
• 兼容官方PostgreSQL镜像的接口规范，支持数据持久化、用户权限管理、备份恢复等标准操作。

2.2 PostGIS地理空间扩展

• 提供完整的地理空间数据处理能力，支持OGC（开放地理空间联盟）标准。
• 支持地理数据类型：Point（点）、LineString（线）、Polygon（面）、MultiPoint（多点）等。
• 内置空间索引（GiST）及地理计算函数：距离计算（ST_Distance）、面积计算（ST_Area）、空间关系判断（ST_Contains、ST_Intersects）等。

2.3 pg_cron定时任务调度

• 支持标准cron语法的定时任务配置，可直接在数据库内创建、管理定时执行的SQL任务。
• 任务管理功能：支持任务创建（cron.schedule）、删除（cron.unschedule）、状态查询（cron.job）。
• 支持事务安全的任务执行，确保定时操作的数据一致性。

2.4 易用性与兼容性

• 预编译并启用PostGIS和pg_cron扩展，无需手动编译安装依赖。
• 与官方PostgreSQL镜像版本同步，确保兼容性和安全性更新。

3. 使用场景

3.1 地理信息系统（GIS）应用

• 地图服务后端（如位置标记、路径规划）
• 位置追踪系统（物流、共享单车轨迹存储）
• 地理编码服务（地址与坐标转换）

3.2 定时数据库任务系统

• 周期性数据维护（日志清理、过期数据归档）
• 定时报表生成（每日/周数据统计）
• 自动化数据同步（跨库数据迁移、ETL流程）
• 定时备份任务（数据库全量/增量备份）

3.3 地理数据分析平台

• 空间模式识别（区域热点分析、密度计算）
• 地理数据聚合统计（区域销售额、用户分布）

4. 使用方法

4.1 基础启动（Docker Run）

bash
docker run -d \
  --name postgres-gis-cron \
  -e POSTGRES_PASSWORD=SecurePass123 \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_DB=gisdb \
  -e CRON_DATABASE_NAME=gisdb \
  -v postgres-data:/var/lib/postgresql/data \
  -p 5432:5432 \
  [镜像名称]:[版本标签]

4.2 Docker Compose配置

yaml
version: '3.8'
services:
  postgres:
    image: [镜像名称]:[版本标签]
    container_name: postgres-gis-cron
    environment:
      POSTGRES_PASSWORD: SecurePass123
      POSTGRES_USER: appuser
      POSTGRES_DB: gisdb
      CRON_DATABASE_NAME: gisdb  # 指定启用pg_cron的数据库
    volumes:
      - postgres-data:/var/lib/postgresql/data  # 数据持久化
      - ./init-scripts:/docker-entrypoint-initdb.d  # 初始化脚本挂载（可选）
    ports:
      - "5432:5432"
    restart: unless-stopped

volumes:
  postgres-data:

启动服务：

bash
docker-compose up -d

5. 配置说明

5.1 环境变量参数

5.2 扩展启用与验证

镜像默认已安装PostGIS和pg_cron扩展，首次连接数据库后需手动启用：

1. 连接数据库：

bash
docker exec -it postgres-gis-cron psql -U appuser -d gisdb

2. 启用扩展：

sql
-- 启用PostGIS核心扩展
CREATE EXTENSION postgis;
-- 启用PostGIS拓扑扩展（可选）
CREATE EXTENSION postgis_topology;
-- 启用pg_cron扩展（需与CRON_DATABASE_NAME匹配）
CREATE EXTENSION pg_cron;

3. 验证安装：

sql
-- 验证PostGIS版本
SELECT postgis_version();
-- 验证pg_cron状态
SELECT cron.schedule('test-job', '* * * * *', 'SELECT 1;');  -- 创建测试任务
SELECT jobid, schedule, command FROM cron.job;  -- 查看任务列表
SELECT cron.unschedule('test-job');  -- 删除测试任务

5.3 pg_cron任务示例

创建定时数据清理任务

sql
-- 每天凌晨2点删除30天前的日志数据
CREATE TABLE IF NOT EXISTS app_logs (id SERIAL, log_time TIMESTAMP, message TEXT);

SELECT cron.schedule(
  'clean-old-logs',  -- 任务名称
  '0 2 * * *',       -- 每天2点执行（cron语法：分 时 日 月 周）
  'DELETE FROM app_logs WHERE log_time < NOW() - INTERVAL ''30 days'';'
);

创建定时报表任务

sql
-- 每周一凌晨生成上周销售报表
CREATE TABLE IF NOT EXISTS weekly_sales_report (
  report_date DATE PRIMARY KEY,
  total_sales NUMERIC,
  generated_at TIMESTAMP DEFAULT NOW()
);

SELECT cron.schedule(
  'generate-sales-report',
  '0 0 * * 1',  -- 每周一凌晨0点执行
  $INSERT INTO weekly_sales_report (report_date, total_sales)
    SELECT DATE_TRUNC('week', NOW() - INTERVAL '1 week')::DATE, SUM(amount)
    FROM sales
    WHERE sale_date >= DATE_TRUNC('week', NOW() - INTERVAL '1 week')
      AND sale_date < DATE_TRUNC('week', NOW());$
);

5.4 数据持久化配置

通过Docker卷挂载/var/lib/postgresql/data目录确保数据持久化，避免容器重启后数据丢失：

bash
-v postgres-data:/var/lib/postgresql/data

（postgres-data为本地卷名称，自动创建并映射到容器数据目录）

6. 注意事项

• 安全性：生产环境需通过网络策略限制数据库访问来源，避免直接暴露公网；使用强密码并定期轮换。
• 性能优化：PostGIS查询需为地理字段创建GiST索引（如CREATE INDEX idx_geo ON table USING GIST (geom_column);）；pg_cron任务避免设置过高频率（如每秒执行），防止资源占用过高。
• 权限管理：非超级用户需通过GRANT cron授权才能管理定时任务，遵循最小权限原则。
• 版本兼容性：选择与应用依赖匹配的镜像标签（如14-postgis3.3-pgcron1.4表示PostgreSQL 14 + PostGIS 3.3 + pg_cron 1.4）。