wumvi/postgresql

PostgreSQL 数据库镜像

镜像概述

本镜像基于PostgreSQL构建，预安装并启用了两个实用扩展：pg_cron v1.0.2（定时任务调度工具）和pg_stat_statements（SQL性能监控工具），适用于需要定时执行数据库操作或监控SQL执行性能的场景。

核心功能与特性

• pg_cron v1.0.2：提供类Cron的定时任务调度功能，支持在数据库内部直接创建、管理定时任务
• pg_stat_statements：记录SQL语句的执行统计信息，帮助分析和优化数据库性能
• 基于标准PostgreSQL，保持原生数据库功能完整性

使用场景

• 需要定时执行数据库维护任务（如数据备份、数据清理、统计数据更新）
• 需要监控SQL语句执行性能，优化慢查询
• 开发环境和生产环境的PostgreSQL数据库部署

使用方法

构建镜像

手动构建镜像命令：

bash
docker build -t wumvi/postgresql:v10 -f Dockerfile .

运行容器

生产环境部署

1. 首先创建网络和卷：

bash
DOCKER_NODE_TYPE=prod
docker network create db.$DOCKER_NODE_TYPE
docker volume create db.$DOCKER_NODE_TYPE 

2. 启动容器：

bash
docker run \
    --restart=always \
    --rm \
    -d \
    -v db.$DOCKER_NODE_TYPE:/data \
    -v /tmp/db/:/tmp/ \
    --name=db.$DOCKER_NODE_TYPE \
    --hostname=db.$DOCKER_NODE_TYPE.master \
    --net=net.$DOCKER_NODE_TYPE \
    wumvi/postgresql:v10

开发环境部署

1. 首先创建网络和卷：

bash
DOCKER_NODE_TYPE=dev
docker network create db.$DOCKER_NODE_TYPE
docker volume create db.$DOCKER_NODE_TYPE 

2. 启动容器（暴露5432端口）：

bash
docker run \
    --restart=always \
    --rm \
    -d \
    -p 5432:5432 \
    -v db.$DOCKER_NODE_TYPE:/data \
    -v /tmp/db/:/tmp/ \
    --name=db.$DOCKER_NODE_TYPE \
    --hostname=db.$DOCKER_NODE_TYPE.master \
    --net=net.$DOCKER_NODE_TYPE \
    wumvi/postgresql:v10

端口说明

• 5432：PostgreSQL数据库默认连接端口

环境变量

常用操作

修改postgres用户密码

将postgres用户密码修改为'pwd'：

bash
docker exec \
    -ti \
    $DOCKER_CONTAINER_NAME \
    sudo \
    -u postgres \
    psql \
    --command "ALTER USER postgres with password 'pwd';"

创建新用户

创建名为'user_name'、密码为'pwd'的新用户：

bash
docker exec \
    -ti \
    $DOCKER_CONTAINER_NAME \
    sudo \
    -u postgres \
    psql \
    --command "CREATE USER user_name WITH PASSWORD 'pwd';"

数据库备份

创建数据库备份并压缩保存到/tmp目录：

bash
docker exec \
    -ti \
    $DOCKER_CONTAINER_NAME \
    sudo \
    -u postgres \
    pg_dump \
    $DOCKER_DB_NAME | gzip > /tmp/$DOCKER_DB_NAME.sql.gz

解压备份文件

解压.gz格式的备份文件：

bash
docker exec \
    -ti $DOCKER_CONTAINER_NAME \
    gunzip \
    /tmp/$DOCKER_DB_NAME.sql.gz

恢复数据库备份

恢复已解压的备份文件：

bash
# 删除现有数据库
docker exec \
    -ti \
    $DOCKER_CONTAINER_NAME \
    sudo \
    -u postgres \
    psql --command "DROP DATABASE $DOCKER_DB_NAME;"

# 创建新数据库
docker exec \
    -ti \
    $DOCKER_CONTAINER_NAME \
    sudo \
    -u postgres \
    psql --command "CREATE DATABASE $DOCKER_DB_NAME;"

# 恢复数据
docker exec \
    -ti \
    $DOCKER_CONTAINER_NAME \
    sudo \
    -u postgres \
    psql \
    $DOCKER_DB_NAME \
    --set ON_ERROR_STOP=on \
    -f /tmp/$DOCKER_DB_NAME.sql

监控与管理SQL

查看数据库大小

sql
SELECT pg_database.datname, pg_database_size(pg_database.datname) as size FROM pg_database;

创建定时任务

使用pg_cron创建每分钟执行一次的任务：

sql
SELECT cron.schedule('* * * * *', $EXEC some_function()$);

启用扩展（如需手动启用）

sql
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_cron;