由于業(yè)務(wù)需要，需要使用到postgres數(shù)據(jù)庫的postgis胞四、timescaledb以及pg_cron插件恬汁，又因線上環(huán)境數(shù)據(jù)庫使用docker部署，故需要部署手動構(gòu)建鏡像辜伟。
- 編寫Dockerfile
# 使用 PostgreSQL 16.2 作為基礎(chǔ)鏡像
FROM postgres:16.2
# 安裝必要的依賴
RUN apt-get update && \
apt-get install -y gnupg postgresql-common apt-transport-https lsb-release wget && \
rm -rf /var/lib/apt/lists/*
# 使用清華大學(xué)的 Debian 鏡像源
RUN echo "deb https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm main non-free contrib" > /etc/apt/sources.list && \
echo "deb https://mirrors.tuna.tsinghua.edu.cn/debian-security/ bookworm-security main" >> /etc/apt/sources.list && \
echo "deb https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-updates main" >> /etc/apt/sources.list
# 添加 TimescaleDB 倉庫
RUN sh -c "echo 'deb [signed-by=/usr/share/keyrings/timescale.keyring] https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list" && \
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | gpg --dearmor -o /usr/share/keyrings/timescale.keyring
# 添加 pg_cron 倉庫
RUN sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main' > /etc/apt/sources.list.d/pgdg.list" && \
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
# 導(dǎo)入缺失的公鑰
RUN apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 7FCC7D46ACCC4CF8
# 更新包列表并安裝插件
RUN apt-get update && \
apt-get install -y timescaledb-2-postgresql-16 postgresql-16-postgis-3 postgresql-16-cron && \
rm -rf /var/lib/apt/lists/*
# 修改 PostgreSQL 配置以加載 TimescaleDB 和 pg_cron
RUN echo "shared_preload_libraries = 'timescaledb,pg_cron'" >> /usr/share/postgresql/postgresql.conf.sample
# 設(shè)置數(shù)據(jù)庫初始化腳本
COPY init-db.sh /docker-entrypoint-initdb.d/
# 設(shè)定容器啟動時的默認(rèn)命令
CMD ["postgres"]
- 編寫啟動腳本
#!/bin/bash
set -e
# 創(chuàng)建數(shù)據(jù)庫擴(kuò)展
echo "Creating extensions..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS pg_cron;
EOSQL
echo "Extensions created successfully."
- 將上面兩個文件放在同一個目錄下氓侧，并在該目錄下構(gòu)建鏡像
docker build -t wesafer-postgres:v2 .
- 啟動容器
docker run -d \
--name postgres_db \
-v /data/postgres_data:/var/lib/postgresql/data \
-v /etc/localtime:/etc/localtime:ro \
-v /etc/timezone:/etc/timezone:ro \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=your_password\
-e POSTGRES_DB=postgres \
-p 5432:5432 \
wesafer-postgres:v2
- 驗(yàn)證安裝
SELECT * FROM pg_extension;
image.png
- 導(dǎo)出及導(dǎo)入鏡像
# 導(dǎo)出鏡像導(dǎo)出為tar文件
docker save -o wesafer-postgres.tar wesafer-postgres:v2
# 在需要部署的環(huán)境導(dǎo)入該鏡像tar文件
docker load -i wesafer-postgres.tar
注意，pg_cron插件需要指定數(shù)據(jù)庫安裝导狡，默認(rèn)只能在postgres數(shù)據(jù)庫安裝约巷，如需變更需要修改postgresql.conf：
cron.database_name = 'your_database_name' -- 這里需要指定一個數(shù)據(jù)庫名
