deploy docs

Appearance

pg 17 ​

sh
mkdir -p /opt/pg17
cd /opt/pg17
mkdir data backup
docker network create pg-17-net
sh
vim .env
env
POSTGRES_DB=admin_db
POSTGRES_USER=admin
POSTGRES_PASSWORD=strong_password_here
TZ=Asia/Shanghai
sh
vim docker-compose.yml
yml
services:
  postgres:
    image: postgres:17-alpine
    container_name: postgres
    restart: unless-stopped

    # environment:
    #   TZ: ${TZ}
    #   POSTGRES_DB: ${POSTGRES_DB}
    #   POSTGRES_USER: ${POSTGRES_USER}
    #   POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}

    env_file:
      - .env

    ports:
      - "127.0.0.1:5432:5432"

    volumes:
      - ./data:/var/lib/postgresql/data
      - ./backup:/backup

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER"]
      interval: 10s
      timeout: 5s
      retries: 5

    networks:
      - pg-17-net

networks:
  pg-17-net:
    external: true

创建业务账户 ​

docker exec -it pg17 psql -U admin -d admin_db

例如：

sql
CREATE USER wanx_user WITH PASSWORD 'wanx123123';

CREATE DATABASE wanx3 OWNER wanx_user;

\connect wanx3

-- 把 public schema 的 owner 改成 wanx_user
ALTER SCHEMA public OWNER TO wanx_user;
-- 业务表默认创建在 public schema 下，所以应用用户必须能在 public 里建表。
GRANT ALL ON SCHEMA public TO wanx_user;
sql
-- 删除
DROP USER wanx_user;

-- 查询目前所有 user
SELECT rolname AS 用户名, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles
WHERE rolcanlogin = true; -- 只查可登录的普通用户

init 脚本创建 ​

也可选择初始化脚本方案，但是初始化仅仅在 init 时生效。

/opt/pg17/init/001-init.sql
volumes:
  - ./init:/docker-entrypoint-initdb.d