Skip to content

Instantly share code, notes, and snippets.

@bouroo

bouroo/docker-compose.yaml

Last active August 7, 2024 03:13
Show Gist options
  • Save bouroo/587a77cd1d6b7d77d60177b46f591266 to your computer and use it in GitHub Desktop.
Save bouroo/587a77cd1d6b7d77d60177b46f591266 to your computer and use it in GitHub Desktop.
Download ZIP
pgcat and postgresql replication with docker compose
Raw
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:
Raw
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"
Raw
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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment