123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334 |
- #
- # PgCat config example.
- #
- #
- # 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 # milliseconds
- # How long an idle connection with a server is left open (ms).
- idle_timeout = 30000 # milliseconds
- # Max connection lifetime before it's closed, even if actively used.
- server_lifetime = 86400000 # 24 hours
- # How long a client is allowed to be idle while in a transaction (ms).
- idle_client_in_transaction_timeout = 0 # milliseconds
- # How much time to give the health check query to return with a result (ms).
- healthcheck_timeout = 1000 # milliseconds
- # How long to keep connection available for immediate re-use, without running a healthcheck query on it
- healthcheck_delay = 30000 # milliseconds
- # How much time to give clients during shutdown before forcibly killing client connections (ms).
- shutdown_timeout = 60000 # milliseconds
- # 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
- # When set to true, PgCat reloads configs if it detects a change in the config file.
- autoreload = 15000
- # Number of worker threads the Runtime will use (4 by default).
- worker_threads = 5
- # Number of seconds of connection idleness to wait before sending a keepalive packet to the server.
- tcp_keepalives_idle = 5
- # Number of unacknowledged keepalive packets allowed before giving up and closing the connection.
- tcp_keepalives_count = 5
- # Number of seconds between keepalive packets.
- tcp_keepalives_interval = 5
- # Path to TLS Certificate file to use for TLS connections
- # tls_certificate = ".circleci/server.cert"
- # Path to TLS private key file to use for TLS connections
- # tls_private_key = ".circleci/server.key"
- # Enable/disable server TLS
- server_tls = false
- # Verify server certificate is completely authentic.
- verify_server_certificate = false
- # User name to access the virtual administrative database (pgbouncer or pgcat)
- # Connecting to that database allows running commands like `SHOW POOLS`, `SHOW DATABASES`, etc..
- admin_username = "admin_user"
- # Password to access the virtual administrative database
- admin_password = "admin_pass"
- # Default plugins that are configured on all pools.
- [plugins]
- # Prewarmer plugin that runs queries on server startup, before giving the connection
- # to the client.
- [plugins.prewarmer]
- enabled = false
- queries = [
- "SELECT pg_prewarm('pgbench_accounts')",
- ]
- # Log all queries to stdout.
- [plugins.query_logger]
- enabled = false
- # Block access to tables that Postgres does not allow us to control.
- [plugins.table_access]
- enabled = false
- tables = [
- "pg_user",
- "pg_roles",
- "pg_database",
- ]
- # Intercept user queries and give a fake reply.
- [plugins.intercept]
- enabled = true
- [plugins.intercept.queries.0]
- query = "select current_database() as a, current_schemas(false) as b"
- schema = [
- ["a", "text"],
- ["b", "text"],
- ]
- result = [
- ["${DATABASE}", "{public}"],
- ]
- [plugins.intercept.queries.1]
- query = "select current_database(), current_schema(), current_user"
- schema = [
- ["current_database", "text"],
- ["current_schema", "text"],
- ["current_user", "text"],
- ]
- result = [
- ["${DATABASE}", "public", "${USER}"],
- ]
- # pool configs are structured as pool.<pool_name>
- # the pool_name is what clients use as database name when connecting.
- # For a pool named `sharded_db`, clients access that pool using connection string like
- # `postgres://sharding_user:sharding_user@pgcat_host:pgcat_port/sharded_db`
- [pools.sharded_db]
- # Pool mode (see PgBouncer docs for more).
- # `session` one server connection per connected client
- # `transaction` one server connection per client transaction
- pool_mode = "transaction"
- # Load balancing mode
- # `random` selects the server at random
- # `loc` selects the server with the least outstanding busy conncetions
- load_balancing_mode = "random"
- # If the client doesn't specify, PgCat routes 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"
- # If Query Parser is 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, 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 = true
- # Allow sharding commands to be passed as statement comments instead of
- # separate commands. If these are unset this functionality is disabled.
- # sharding_key_regex = '/\* sharding_key: (\d+) \*/'
- # shard_id_regex = '/\* shard_id: (\d+) \*/'
- # regex_search_limit = 1000 # only look at the first 1000 characters of SQL statements
- # 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"
- # Query to be sent to servers to obtain the hash used for md5 authentication. The connection will be
- # established using the database configured in the pool. This parameter is inherited by every pool
- # and can be redefined in pool configuration.
- # auth_query = "SELECT $1"
- # User to be used for connecting to servers to obtain the hash used for md5 authentication by sending the query
- # specified in `auth_query_user`. The connection will be established using the database configured in the pool.
- # This parameter is inherited by every pool and can be redefined in pool configuration.
- # auth_query_user = "sharding_user"
- # Password to be used for connecting to servers to obtain the hash used for md5 authentication by sending the query
- # specified in `auth_query_user`. The connection will be established using the database configured in the pool.
- # This parameter is inherited by every pool and can be redefined in pool configuration.
- # auth_query_password = "sharding_user"
- # Automatically parse this from queries and route queries to the right shard!
- # automatic_sharding_key = "data.id"
- # Idle timeout can be overwritten in the pool
- idle_timeout = 40000
- # Connect timeout can be overwritten in the pool
- connect_timeout = 3000
- # When enabled, ip resolutions for server connections specified using hostnames will be cached
- # and checked for changes every `dns_max_ttl` seconds. If a change in the host resolution is found
- # old ip connections are closed (gracefully) and new connections will start using new ip.
- # dns_cache_enabled = false
- # Specifies how often (in seconds) cached ip addresses for servers are rechecked (see `dns_cache_enabled`).
- # dns_max_ttl = 30
- # Plugins can be configured on a pool-per-pool basis. This overrides the global plugins setting,
- # so all plugins have to be configured here again.
- [pool.sharded_db.plugins]
- [pools.sharded_db.plugins.prewarmer]
- enabled = true
- queries = [
- "SELECT pg_prewarm('pgbench_accounts')",
- ]
- [pools.sharded_db.plugins.query_logger]
- enabled = false
- [pools.sharded_db.plugins.table_access]
- enabled = false
- tables = [
- "pg_user",
- "pg_roles",
- "pg_database",
- ]
- [pools.sharded_db.plugins.intercept]
- enabled = true
- [pools.sharded_db.plugins.intercept.queries.0]
- query = "select current_database() as a, current_schemas(false) as b"
- schema = [
- ["a", "text"],
- ["b", "text"],
- ]
- result = [
- ["${DATABASE}", "{public}"],
- ]
- [pools.sharded_db.plugins.intercept.queries.1]
- query = "select current_database(), current_schema(), current_user"
- schema = [
- ["current_database", "text"],
- ["current_schema", "text"],
- ["current_user", "text"],
- ]
- result = [
- ["${DATABASE}", "public", "${USER}"],
- ]
- # User configs are structured as pool.<pool_name>.users.<user_index>
- # This section holds the credentials for users that may connect to this cluster
- [pools.sharded_db.users.0]
- # PostgreSQL username used to authenticate the user and connect to the server
- # if `server_username` is not set.
- username = "sharding_user"
- # PostgreSQL password used to authenticate the user and connect to the server
- # if `server_password` is not set.
- password = "sharding_user"
- pool_mode = "session"
- # PostgreSQL username used to connect to the server.
- # server_username = "another_user"
- # PostgreSQL password used to connect to the server.
- # server_password = "another_password"
- # 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.
- # 0 means it is disabled.
- statement_timeout = 0
- [pools.sharded_db.users.1]
- username = "other_user"
- password = "other_user"
- pool_size = 21
- statement_timeout = 15000
- # Shard configs are structured as pool.<pool_name>.shards.<shard_id>
- # Each shard config contains a list of servers that make up the shard
- # and the database name to use.
- [pools.sharded_db.shards.0]
- # Array of servers in the shard, each server entry is an array of `[host, port, role]`
- servers = [["127.0.0.1", 5432, "primary"], ["localhost", 5432, "replica"]]
- # Array of mirrors for the shard, each mirror entry is an array of `[host, port, index of server in servers array]`
- # Traffic hitting the server identified by the index will be sent to the mirror.
- # mirrors = [["1.2.3.4", 5432, 0], ["1.2.3.4", 5432, 1]]
- # Database name (e.g. "postgres")
- database = "shard0"
- [pools.sharded_db.shards.1]
- servers = [["127.0.0.1", 5432, "primary"], ["localhost", 5432, "replica"]]
- database = "shard1"
- [pools.sharded_db.shards.2]
- servers = [["127.0.0.1", 5432, "primary" ], ["localhost", 5432, "replica" ]]
- database = "shard2"
- [pools.simple_db]
- pool_mode = "session"
- default_role = "primary"
- query_parser_enabled = true
- primary_reads_enabled = true
- sharding_function = "pg_bigint_hash"
- [pools.simple_db.users.0]
- username = "simple_user"
- password = "simple_user"
- pool_size = 5
- min_pool_size = 3
- server_lifetime = 60000
- statement_timeout = 0
- [pools.simple_db.shards.0]
- servers = [
- [ "127.0.0.1", 5432, "primary" ],
- [ "localhost", 5432, "replica" ]
- ]
- database = "some_db"
|