docker-compose.yml

# see https://github.com/compose-spec/compose-spec/blob/master/spec.md
# see https://github.com/opencontainers/image-spec/blob/master/annotations.md
services:
  postgres:
    # see https://hub.docker.com/_/postgres
    # see https://github.com/docker-library/postgres/tree/master/16/bookwork
    image: postgres:16-bookworm
    environment:
      - POSTGRES_PASSWORD=password
    restart: on-failure
  init:
    depends_on:
      - postgres
    build: .
    # see https://www.postgresql.org/docs/16/libpq-envars.html
    environment:
      - PGHOST=postgres
      - PGPORT=5432
      - PGUSER=postgres
      - PGPASSWORD=password
    command:
      - |
        - hosts: localhost
          connection: local
          vars:
            database: example
            users:
              - name: alice
                password: password
                privs: all
          tasks:
            - name: Create database
              community.postgresql.postgresql_db:
                name: '{{ database }}'
            - name: Create user
              community.postgresql.postgresql_user:
                name: '{{ item.name }}'
                password: '{{ item.password }}'
              loop: '{{ users }}'
            - name: Grant user database permissions
              community.postgresql.postgresql_privs:
                type: database
                database: '{{ database }}'
                roles: '{{ item.name }}'
                privs: '{{ item.privs }}'
                grant_option: false
              loop: '{{ users }}'
  test:
    depends_on:
      - postgres
    build: .
    # see https://www.postgresql.org/docs/16/libpq-envars.html
    environment:
      - PGHOST=postgres
      - PGPORT=5432
      - PGDATABASE=example
      - PGUSER=alice
      - PGPASSWORD=password
    entrypoint: python3
    command:
      - -c
      - |
        import psycopg2
        import time
        def wait_for_postgres():
          for _ in range(5):
            try:
              with psycopg2.connect('') as connection:
                return
            except psycopg2.OperationalError as err:
              # e.g. could not connect to server: Connection refused
              error_message = str(err)
              if 'could not connect to server' in error_message:
                pass
              # e.g. connection to server at "postgres" (192.168.144.2), port 5432 failed: Connection refused\nIs the server running on that host and accepting TCP/IP connections?
              elif 'Connection refused' in error_message:
                pass
              # e.g. the database system is starting up
              elif 'the database system is starting up' in error_message:
                pass
              # e.g. password authentication failed for user "alice"
              elif 'password authentication failed' in error_message:
                # NB this exception seems to happen immediately after creating the user.
                pass
              else:
                print(type(err), err)
              time.sleep(1)
          raise Exception('timeout waiting for postgres')
        def sql_execute_scalar(sql):
          with psycopg2.connect('') as connection:
            with connection.cursor() as cursor:
              cursor.execute(sql)
              return cursor.fetchone()[0]
        wait_for_postgres()
        print(f"PostgreSQL Version: {sql_execute_scalar('select version()')}")
        print(f"PostgreSQL Database: {sql_execute_scalar('select current_catalog')}")
        print(f"PostgreSQL User: {sql_execute_scalar('select current_user')}")
        print(f"PostgreSQL User Address: {sql_execute_scalar('select inet_client_addr()')}")
        print(f"PostgreSQL Server Address: {sql_execute_scalar('select inet_server_addr()')}")