pgcat and postgresql replication with docker compose

docker-compose.yaml

# Environment variables for PostgreSQL containers
x-default-pg-env: &default-pg-env
  # Set the timezone to Asia/Bangkok
  TZ: Asia/Bangkok
  # Username for the PostgreSQL administrative account
  POSTGRESQL_USERNAME: postgres
  # Name of the default PostgreSQL database
  POSTGRESQL_DATABASE: postgres
  # Password for the PostgreSQL administrative account
  POSTGRESQL_PASSWORD: mysecretpassword

# Define services for running PostgreSQL containers
services:
  # pgcat service
  pgcat:
    # Use the Docker image from the GitHub Container Registry
    image: ghcr.io/postgresml/pgcat
    # Set the container name
    container_name: pgcat
    # Restart the container unless explicitly stopped
    restart: unless-stopped
    # Set environment variables for the container
    environment:
      <<: *default-pg-env
    # Map container ports to host ports
    ports:
      - "6432:6432"
      - "9930:9930"
    # Define dependencies for the container
    depends_on:
      pg1:
        condition: service_healthy
      pg2:
        condition: service_started
      pg3:
        condition: service_started
    # Mount a volume for the container
    volumes:
      - ./pgcat.simple.toml:/etc/pgcat/pgcat.toml

  # pg1 service
  pg1:
    # Use the Docker image from the Bitnami repository
    image: bitnami/postgresql:16
    # Set the container name
    container_name: pg1
    # Restart the container unless explicitly stopped
    restart: unless-stopped
    # Set environment variables for the container
    environment:
      <<: *default-pg-env
      # Enable read/write auditing for PG
      POSTGRESQL_PGAUDIT_LOG: READ,WRITE
      # Log the hostname of the PostgreSQL server
      POSTGRESQL_LOG_HOSTNAME: true
      # Set the replication mode to master
      POSTGRESQL_REPLICATION_MODE: master
      # Set the replication username and password
      POSTGRESQL_REPLICATION_USER: repl_user
      POSTGRESQL_REPLICATION_PASSWORD: repl_password
    # Map container ports to host ports
    ports:
      - "127.0.0.1:5433:5432"
    # Mount a volume for the container
    volumes:
      - pg1data:/var/lib/postgresql/data
    # Define a healthcheck for the container
    healthcheck:
      test:
        [
          "CMD-SHELL",
          "pg_isready -U $$POSTGRESQL_USERNAME -d $$POSTGRESQL_DATABASE",
        ]
      interval: 10s
      timeout: 5s
      retries: 5

  # pg2 service
  pg2:
    # Use the Docker image from the Bitnami repository
    image: bitnami/postgresql:16
    # Set the container name
    container_name: pg2
    # Restart the container unless explicitly stopped
    restart: unless-stopped
    # Set environment variables for the container
    environment:
      <<: *default-pg-env
      # Enable read/write auditing for PG
      POSTGRESQL_PGAUDIT_LOG: READ,WRITE
      # Log the hostname of the PostgreSQL server
      POSTGRESQL_LOG_HOSTNAME: true
      # Set the replication mode to slave
      POSTGRESQL_REPLICATION_MODE: slave
      # Set the replication username and password
      POSTGRESQL_REPLICATION_USER: repl_user
      POSTGRESQL_REPLICATION_PASSWORD: repl_password
      # Set the master host and port
      POSTGRESQL_MASTER_HOST: pg1
      POSTGRESQL_MASTER_PORT_NUMBER: 5432
    # Map container ports to host ports
    ports:
      - "127.0.0.1:5434:5432"
    # Mount a volume for the container
    volumes:
      - pg2data:/var/lib/postgresql/data
    # Define a dependency for the container
    depends_on:
      pg1:
        condition: service_healthy
    # Define a healthcheck for the container
    healthcheck:
      test:
        [
          "CMD-SHELL",
          "pg_isready -U $$POSTGRESQL_USERNAME -d $$POSTGRESQL_DATABASE",
        ]
      interval: 10s
      timeout: 5s
      retries: 5

  # pg3 service
  pg3:
    # Use the Docker image from the Bitnami repository
    image: bitnami/postgresql:16
    # Set the container name
    container_name: pg3
    # Restart the container unless explicitly stopped
    restart: unless-stopped
    # Set environment variables for the container
    environment:
      <<: *default-pg-env
      # Enable read/write auditing for PG
      POSTGRESQL_PGAUDIT_LOG: READ,WRITE
      # Log the hostname of the PostgreSQL server
      POSTGRESQL_LOG_HOSTNAME: true
      # Set the replication mode to slave
      POSTGRESQL_REPLICATION_MODE: slave
      # Set the replication username and password
      POSTGRESQL_REPLICATION_USER: repl_user
      POSTGRESQL_REPLICATION_PASSWORD: repl_password
      # Set the master host and port
      POSTGRESQL_MASTER_HOST: pg1
      POSTGRESQL_MASTER_PORT_NUMBER: 5432
    # Map container ports to host ports
    ports:
      - "127.0.0.1:5435:5432"
    # Mount a volume for the container
    volumes:
      - pg3data:/var/lib/postgresql/data
    # Define a dependency for the container
    depends_on:
      pg1:
        condition: service_healthy
    # Define a healthcheck for the container
    healthcheck:
      test:
        [
          "CMD-SHELL",
          "pg_isready -U $$POSTGRESQL_USERNAME -d $$POSTGRESQL_DATABASE",
        ]
      interval: 10s
      timeout: 5s
      retries: 5

# Define volumes for the containers
volumes:
  pg1data:
  pg2data:
  pg3data:
  

pgcat.sharded.toml

#
# PgCat config example.
# https://postgresml.org/docs/product/pgcat/features#sharding
#

#
# General pooler settings
[general]
# What IP to run on, 0.0.0.0 means accessible from everywhere.
host = "0.0.0.0"

# Port to run on, same as PgBouncer used in this example.
port = 6432

# Whether to enable prometheus exporter or not.
enable_prometheus_exporter = true

# Port at which prometheus exporter listens on.
prometheus_exporter_port = 9930

# How long to wait before aborting a server connection (ms).
connect_timeout = 5000

# How much time to give `SELECT 1` health check query to return with a result (ms).
healthcheck_timeout = 1000

# How long to keep connection available for immediate re-use, without running a healthcheck query on it
healthcheck_delay = 30000

# How much time to give clients during shutdown before forcibly killing client connections (ms).
shutdown_timeout = 60000

# For how long to ban a server if it fails a health check (seconds).
ban_time = 60 # seconds

# If we should log client connections
log_client_connections = false

# If we should log client disconnections
log_client_disconnections = false

# TLS
# tls_certificate = "server.cert"
# tls_private_key = "server.key"

# Credentials to access the virtual administrative database (pgbouncer or pgcat)
# Connecting to that database allows running commands like `SHOW POOLS`, `SHOW DATABASES`, etc..
admin_username = "pgcat"
admin_password = "mysecretpassword"

# pool
# configs are structured as pool.<pool_name>
# the pool_name is what clients use as database name when connecting
# For the example below a client can connect using "postgres://sharding_user:sharding_user@pgcat_host:pgcat_port/sharded"
[pools.postgres]
# Pool mode (see PgBouncer docs for more).
# session: one server connection per connected client
# transaction: one server connection per client transaction
pool_mode = "transaction"

# If the client doesn't specify, route traffic to
# this role by default.
#
# any: round-robin between primary and replicas,
# replica: round-robin between replicas only without touching the primary,
# primary: all queries go to the primary unless otherwise specified.
default_role = "any"

# Query parser. If enabled, we'll attempt to parse
# every incoming query to determine if it's a read or a write.
# If it's a read query, we'll direct it to a replica. Otherwise, if it's a write,
# we'll direct it to the primary.
query_parser_enabled = true

# If the query parser is enabled and this setting is enabled, we'll attempt to
# infer the role from the query itself.
query_parser_read_write_splitting = true

# If the query parser is enabled and this setting is enabled, the primary will be part of the pool of databases used for
# load balancing of read queries. Otherwise, the primary will only be used for write
# queries. The primary can always be explicitly selected with our custom protocol.
primary_reads_enabled = false

# So what if you wanted to implement a different hashing function,
# or you've already built one and you want this pooler to use it?
#
# Current options:
#
# pg_bigint_hash: PARTITION BY HASH (Postgres hashing function)
# sha1: A hashing function based on SHA1
#
sharding_function = "pg_bigint_hash"

# Credentials for users that may connect to this cluster
[pools.postgres.users.0]
username = "postgres"
password = "mysecretpassword"
# Maximum number of server connections that can be established for this user
# The maximum number of connection from a single Pgcat process to any database in the cluster
# is the sum of pool_size across all users.
pool_size = 9

# Maximum query duration. Dangerous, but protects against DBs that died in a non-obvious way.
statement_timeout = 0

# Shard 0
[pools.postgres.shards.0]
# [ host, port, role ]
servers = [
    [ "pg1", 5432, "primary" ],
    [ "pg2", 5432, "replica" ],
    [ "pg3", 5432, "replica" ]
]
# Database name (e.g. "postgres")
database = "postgres"

[pools.postgres.shards.1]
servers = [
    [ "pg1", 5432, "primary" ],
    [ "pg2", 5432, "replica" ],
    [ "pg3", 5432, "replica" ]
]
database = "postgres"

[pools.postgres.shards.2]
servers = [
    [ "pg1", 5432, "primary" ],
    [ "pg2", 5432, "replica" ],
    [ "pg3", 5432, "replica" ]
]
database = "postgres"

pgcat.simple.toml

#
# PgCat config example.
# https://postgresml.org/docs/product/pgcat/features#read/write-query-separation
#

#
# General pooler settings
[general]
# What IP to run on, 0.0.0.0 means accessible from everywhere.
host = "0.0.0.0"

# Port to run on, same as PgBouncer used in this example.
port = 6432

# Whether to enable prometheus exporter or not.
enable_prometheus_exporter = true

# Port at which prometheus exporter listens on.
prometheus_exporter_port = 9930

# How long to wait before aborting a server connection (ms).
connect_timeout = 5000

# How much time to give `SELECT 1` health check query to return with a result (ms).
healthcheck_timeout = 1000

# How long to keep connection available for immediate re-use, without running a healthcheck query on it
healthcheck_delay = 30000

# How much time to give clients during shutdown before forcibly killing client connections (ms).
shutdown_timeout = 60000

# For how long to ban a server if it fails a health check (seconds).
ban_time = 60 # seconds

# If we should log client connections
log_client_connections = false

# If we should log client disconnections
log_client_disconnections = false

# TLS
# tls_certificate = "server.cert"
# tls_private_key = "server.key"

# Credentials to access the virtual administrative database (pgbouncer or pgcat)
# Connecting to that database allows running commands like `SHOW POOLS`, `SHOW DATABASES`, etc..
admin_username = "pgcat"
admin_password = "mysecretpassword"

# pool
# configs are structured as pool.<pool_name>
# the pool_name is what clients use as database name when connecting
# For the example below a client can connect using "postgres://sharding_user:sharding_user@pgcat_host:pgcat_port/sharded"
[pools.postgres]
# Pool mode (see PgBouncer docs for more).
# session: one server connection per connected client
# transaction: one server connection per client transaction
pool_mode = "transaction"

# If the client doesn't specify, route traffic to
# this role by default.
#
# any: round-robin between primary and replicas,
# replica: round-robin between replicas only without touching the primary,
# primary: all queries go to the primary unless otherwise specified.
default_role = "any"

# Query parser. If enabled, we'll attempt to parse
# every incoming query to determine if it's a read or a write.
# If it's a read query, we'll direct it to a replica. Otherwise, if it's a write,
# we'll direct it to the primary.
query_parser_enabled = true

# If the query parser is enabled and this setting is enabled, we'll attempt to
# infer the role from the query itself.
query_parser_read_write_splitting = true

# If the query parser is enabled and this setting is enabled, the primary will be part of the pool of databases used for
# load balancing of read queries. Otherwise, the primary will only be used for write
# queries. The primary can always be explicitly selected with our custom protocol.
primary_reads_enabled = false

# So what if you wanted to implement a different hashing function,
# or you've already built one and you want this pooler to use it?
#
# Current options:
#
# pg_bigint_hash: PARTITION BY HASH (Postgres hashing function)
# sha1: A hashing function based on SHA1
#
sharding_function = "pg_bigint_hash"

# Credentials for users that may connect to this cluster
[pools.postgres.users.0]
username = "postgres"
password = "mysecretpassword"
# Maximum number of server connections that can be established for this user
# The maximum number of connection from a single Pgcat process to any database in the cluster
# is the sum of pool_size across all users.
min_pool_size = 1
pool_size = 10

# Maximum query duration. Dangerous, but protects against DBs that died in a non-obvious way.
statement_timeout = 0

# Shard 0
[pools.postgres.shards.0]
# [ host, port, role ]
servers = [
    [ "pg1", 5432, "primary" ],
    [ "pg2", 5432, "replica" ],
    [ "pg3", 5432, "replica" ]
]
# Database name (e.g. "postgres")
database = "postgres"