hetzner/db/postgis/postgis.yml

# https://hub.docker.com/r/crunchydata/crunchy-postgres-gis/tags
services:
  primary:
    hostname: 'primary'
    ports:
      - target: 5432
        published: 5432
        protocol: tcp
        mode: ingress
    image: crunchydata/crunchy-postgres-gis:centos7-12.4-3.0-4.4.1
    environment:
      - PGHOST=/tmp
      - MAX_CONNECTIONS=30
      - MAX_WAL_SENDERS=5
      - PG_MODE=primary
      - PG_PRIMARY_USER=primaryuser
      - PG_PRIMARY_PASSWORD=C9DNCIqtQ8x8f0aROyWnC9DNCIqtQ8x8f0aROyWn
      - PG_DATABASE=testdb
      - PG_USER=testuser
      - PG_PASSWORD=xPQ6Quhtk2hQefcIJobAr1Nm9icXjf
      - PG_ROOT_PASSWORD=R36aVTUKgOWgawMRvSVqZ4LfYWswZc
      - PG_PRIMARY_PORT=5432
    volumes:
      - pg-primary-vol:/pgdata
      - pg-primary-vol-pgconf:/pgconf
      - pg-primary-vol-pgwal:/pgwal
      - pg-primary-vol-recover:/recover
      - pg-primary-vol-sshd:/sshd
      - pg-primary-vol-backrestrepo:/backrestrepo
      - backups:/srv/backups
#    ports:
#    - "5432"
    networks:
      - db-network
      - db-external
    deploy:
      replicas: 1
      mode: replicated
      placement:
        constraints:
          - node.role == manager
          - node.hostname == hetzner

  replica:
    hostname: 'replica'
    image: crunchydata/crunchy-postgres-gis:centos7-12.4-3.0-4.4.1
    environment:
      - PGHOST=/tmp
      - MAX_CONNECTIONS=30
      - MAX_WAL_SENDERS=5
      - PG_MODE=replica
      - PG_PRIMARY_HOST=primary
      - PG_PRIMARY_PORT=5432
      - PG_PRIMARY_USER=primaryuser
      - PG_PRIMARY_PASSWORD=C9DNCIqtQ8x8f0aROyWnC9DNCIqtQ8x8f0aROyWn
      - PG_DATABASE=testdb
      - PG_USER=testuser
      - PG_PASSWORD=xPQ6Quhtk2hQefcIJobAr1Nm9icXjf
      - PG_ROOT_PASSWORD=R36aVTUKgOWgawMRvSVqZ4LfYWswZc
    volumes:
      - pg-replica-vol:/pgdata
      - pg-replica-vol-pgconf:/pgconf
      - pg-replica-vol-pgwal:/pgwal
      - pg-replica-vol-recover:/recover
      - pg-replica-vol-sshd:/sshd
      - pg-replica-vol-backrestrepo:/backrestrepo
      - backups:/srv/backups
    networks:
      - db-network
      - db-external
    deploy:
      replicas: 1
      mode: replicated
      placement:
        constraints:
          - node.role == manager
          - node.hostname == hetzner

  dbclient:
    image: alpine
    environment:
      - BACKUP_ENABLED=1
      - BACKUP_INTERVAL=86400
      - BACKUP_PATH=/srv/backups
      - BACKUP_FILENAME=swarm_db_backup_postgis
      - PG_PRIMARY_USER=primaryuser
      - PG_PRIMARY_PASSWORD=C9DNCIqtQ8x8f0aROyWnC9DNCIqtQ8x8f0aROyWn
      - PG_USER=testuser
      - PG_PASSWORD=xPQ6Quhtk2hQefcIJobAr1Nm9icXjf
      - PG_ROOT_PASSWORD=R36aVTUKgOWgawMRvSVqZ4LfYWswZc
    networks:
      - db-network
    entrypoint:
      - /bin/sh
      - -c
      - |
        apk add --no-cache postgresql-client

        # Ootame, et andmebaas oleks valmis
        echo "Waiting for database..."
        until PGPASSWORD=$${PG_ROOT_PASSWORD} pg_isready -h primary -U postgres; do
          echo "Database unavailable - sleeping"
          sleep 5
        done
        echo "Database is ready!"

        while true; do
          if [ $${BACKUP_ENABLED} -eq 1 ]; then
            echo "Starting backup at $$(date)"
            mkdir -p $${BACKUP_PATH}/$$(date +%F)

            TIMESTAMP=$$(date +%FT%H-%M)
            BACKUP_FILE="$${BACKUP_PATH}/$$(date +%F)/$${BACKUP_FILENAME}-$${TIMESTAMP}.sql.gz"

            echo "Making backup to: $${BACKUP_FILE}"

            export PGPASSWORD=$${PG_ROOT_PASSWORD}
            pg_dumpall -U postgres -h primary --exclude-database=osm_estonia --exclude-database=transport --exclude-database=postgres | gzip > $${BACKUP_FILE}

            if [ $$? -eq 0 ]; then
              echo "Backup completed successfully"
              ls -lh $${BACKUP_FILE}
            else
              echo "ERROR: Backup failed!"
            fi

            # Kustuta vanad varundused (vanemad kui 7 päeva)
            echo "Cleaning up old backups..."
            find $${BACKUP_PATH} -type f -name "*.sql.gz" -mtime +7 -delete

            # Arvuta järgmise varukoopia aeg
            NEXT_TIME=$$(( $$(date +%s) + $${BACKUP_INTERVAL} ))
            NEXT_BACKUP=$$(date -d @$${NEXT_TIME} '+%F %H:%M' 2>/dev/null || date -r $${NEXT_TIME} '+%F %H:%M')
            echo "Next backup scheduled at: $${NEXT_BACKUP}"

            sleep $${BACKUP_INTERVAL}
          else
            echo "Backup is disabled"
            sleep 3600
          fi
        done
    volumes:
      - backups:/srv/backups
      - /etc/localtime:/etc/localtime
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints:
          - node.role == manager
          - node.hostname == hetzner


networks:
#  crunchynet:
  db-network:
    external: true
  db-external:
    external: true


volumes:
#  dbclient:
  backups:
  pg-primary-vol:
#    name: '{{.Service.Name}}-{{.Task.Slot}}-pgdata'
    driver: local
  pg-primary-vol-pgconf:
    name: '{{.Service.Name}}-{{.Task.Slot}}-pgconf'
    driver: local
  pg-primary-vol-pgwal:
    name: '{{.Service.Name}}-{{.Task.Slot}}-pgwal'
    driver: local
  pg-primary-vol-recover:
    name: '{{.Service.Name}}-{{.Task.Slot}}-recover'
    driver: local
  pg-primary-vol-sshd:
    name: '{{.Service.Name}}-{{.Task.Slot}}-sshd'
    driver: local
  pg-primary-vol-backrestrepo:
    name: '{{.Service.Name}}-{{.Task.Slot}}-backrestrepo'
    driver: local

  pg-replica-vol:
#    name: '{{.Service.Name}}-{{.Task.Slot}}-pgdata'
    driver: local
  pg-replica-vol-pgconf:
    name: '{{.Service.Name}}-{{.Task.Slot}}-pgconf'
    driver: local
  pg-replica-vol-pgwal:
    name: '{{.Service.Name}}-{{.Task.Slot}}-pgwal'
    driver: local
  pg-replica-vol-recover:
    name: '{{.Service.Name}}-{{.Task.Slot}}-recover'
    driver: local
  pg-replica-vol-sshd:
    name: '{{.Service.Name}}-{{.Task.Slot}}-sshd'
    driver: local
  pg-replica-vol-backrestrepo:
    name: '{{.Service.Name}}-{{.Task.Slot}}-backrestrepo'
    driver: local