nqdev/postgres-pgagent repository overview

# START: docker-compose up -d --build --force-recreate --remove-orphans
# STOP: docker-compose down -v
docker-compose up -d --build --force-recreate --remove-orphans

# Build với PostgreSQL version mặc định (17.5)
docker build -t nqdev/postgres-pgagent:latest .

# Build với version khác
docker build --build-arg VERSION=16.4 -t nqdev/postgres-pgagent:16.4 .

# # # # # PostgreSQL + pgAgent, pgsql-http extension
# START: docker-compose up -d --build --force-recreate --remove-orphans
# STOP: docker-compose down -v
# # # # #

services:
  postgres-pgagent:
    container_name: postgres-pgagent-custom
    image: postgres:17.5-custom
    build:
      context: ./
      dockerfile: ./Dockerfile
    ports:
      - "5432:5432"
    environment:
      TZ: Asia/Ho_Chi_Minh
      POSTGRES_USER: ${POSTGRES_USER:-superuser}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-superuser}
      POSTGRES_DB: ${POSTGRES_DB:-postgresdb}
      POSTGRES_HOST_AUTH_METHOD: trust
      POSTGRES_PORT: ${POSTGRES_PORT:-5432}
      POSTGRES_INITDB_ARGS: ${POSTGRES_INITDB_ARGS:-"--data-checksums"}
    env_file:
      - .env
    volumes:
      - ./data:/var/lib/postgresql/data:rw
    extra_hosts:
      - "host.docker.internal:host-gateway"
    dns:
      - 1.1.1.1
      - 1.0.0.1
      - 8.8.8.8
      - 8.8.4.4
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
        max-file: "5"
    deploy:
      resources:
        limits:
          cpus: "0.80"
          memory: "3.2G"
        reservations:
          memory: "256M"

docker run -d \
  --name postgres-pgagent-custom \
  -e POSTGRES_PASSWORD=superuser \
  -e POSTGRES_USER=superuser \
  -e POSTGRES_DB=postgresdb \
  -e TZ=Asia/Ho_Chi_Minh \
  -v ./data:/var/lib/postgresql/data:rw \
  -p 5432:5432 \
  nqdev/postgres-pgagent:latest

/docker-entrypoint-initdb.d/          # Auto-initialization scripts
├── 10-init-http.sql                  # HTTP extension setup
└── 11-init-pgagent.sql               # pgAgent extension setup

/nqdev/postgres/                       # Custom NQDEV structure
├── data/                              # Database data (volume mount)
├── logs/                              # Application logs (volume mount)
└── scripts/                           # Shell utilities
    ├── 00-init-custom.sh              # Custom SQL initialization
    ├── 01-docker-entrypoint.sh        # Main entrypoint script
    ├── 02-docker-ensure-initdb.sh     # Init verification
    └── 10-wait-for-it.sh              # Connection utility

-- Kích hoạt extension
CREATE EXTENSION IF NOT EXISTS http;

-- Thực hiện GET request
SELECT status, content::json
FROM http_get('https://api.github.com/repos/octocat/Hello-World');

-- POST request với data
SELECT status, content
FROM http_post('https://httpbin.org/post',
               '{"key": "value"}',
               'application/json');

-- Kích hoạt extension
CREATE EXTENSION IF NOT EXISTS pgagent;

-- Tạo job đơn giản
DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
    -- Tạo job
    INSERT INTO pgagent.pga_job (jobjclid, jobname)
    VALUES (1, 'Daily Maintenance') RETURNING jobid INTO jid;

    -- Tạo step
    INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstkind, jstcode)
    VALUES (jid, 'Analyze Tables', 's', 'ANALYZE;');
END $$;

# Đợi PostgreSQL sẵn sàng
./scripts/10-wait-for-it.sh localhost:5432 --timeout=30 -- echo "PostgreSQL is ready!"

# Kết nối database
docker exec -it postgres-pgagent-custom psql -U superuser -d postgresdb

# Kiểm tra extensions
docker exec -it postgres-pgagent-custom psql -U superuser -d postgresdb -c "\dx"

# Xem logs
docker logs postgres-pgagent-custom

-- Kiểm tra connection stats
SELECT * FROM pg_stat_activity;

-- Monitor job execution (pgAgent)
SELECT * FROM pgagent.pga_joblog ORDER BY jlgstart DESC LIMIT 10;

# Persistent data storage
-v ./data:/var/lib/postgresql/data:rw

# Log access (optional)
-v ./logs:/nqdev/postgres/logs:rw

# Custom config (optional)
-v ./custom-config:/nqdev/postgres/config:ro

# Database dump
docker exec postgres-pgagent-custom pg_dump -U superuser postgresdb > backup.sql

# Full data directory backup (với container dừng)
docker-compose down
tar -czf postgres-backup-$(date +%Y%m%d).tar.gz ./data
docker-compose up -d

# docker-compose.prod.yml
services:
  postgres-pgagent:
    environment:
      POSTGRES_HOST_AUTH_METHOD: scram-sha-256
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - postgres-logs:/nqdev/postgres/logs
    networks:
      - postgres-network

volumes:
  postgres-data:
    driver: local
  postgres-logs:
    driver: local

networks:
  postgres-network:
    driver: bridge