DOCSEARCHbyAlgolia
Submit Docs

PgCat Documentation

repository·main·Indexed 26 days ago

https://github.com/postgresml/pgcat

PgCat is a high-performance PostgreSQL pooler and proxy designed as a modern alternative to PgBouncer. It provides advanced features including sharding, load balancing, failover, and mirroring, utilizing the Tokio asynchronous runtime for multicore efficiency. It supports both session and transaction pooling modes, SSL/TLS security, and provides administrative statistics via a Prometheus HTTP endpoint and SQL interface.

Tokens
17.5K
Snippets
33
Records
117
Agent score
87%

What's inside PgCat

  1. Overview of PgCat features and status

    main

    PgCat is a next-generation PostgreSQL pooler and proxy designed to provide sharding, load balancing, failover, and mirroring. It uses the Tokio asynchronous runtime to leverage multicore machines.

    Stable Features

    • Transaction pooling: Improved handling of bad clients and abandoned transactions.
    • Session pooling: Identical to PgBouncer.
    • Load balancing: Automatic load balancing between replicas and the primary.
    • Failover: Automatic rerouting around broken replicas via health checks.
    • Statistics: Admin statistics via pgbouncer and pgcat databases, plus Prometheus HTTP endpoint.
    • Security: SSL/TLS support for both client-to-pooler and pooler-to-Postgres connections. Supports MD5 and SCRAM-SHA-256 authentication.
    • Auth passthrough: MD5 authentication can use an auth_query to avoid storing cleartext passwords in config.
    • Live configuration reloading: Most settings can be reloaded dynamically (except host and port).

    Experimental Features

    • Sharding: Available via extended SQL syntax, comments parsing/Regex, or automatic detection.
    • Mirroring: Mirror queries between multiple databases for production traffic testing.

  2. Quickstart local deployment with Docker Compose

    main

    To run a quick local example, use the provided Docker Compose environment. After starting the containers, you can connect using psql to verify the connection.

    docker-compose up

# In a new terminal:
PGPASSWORD=postgres psql -h 127.0.0.1 -p 6432 -U postgres -c 'SELECT 1'

  3. Deploy PgCat using Docker

    main

    You can deploy PgCat using the official Docker image. For optimal performance, it is recommended to have 4 CPUs available, as the pooler is configured to spawn 4 workers by default (this can be adjusted in configuration).

    docker pull ghcr.io/postgresml/pgcat:latest

  4. Implement database sharding with PgCat

    main

    PgCat supports sharding using the PARTITION BY HASH function (matching Postgres declarative partitioning). You can route queries to specific shards or use a sharding key.

    Manual Routing via SQL

    Use these commands to control shard targeting:

    • SET SHARD TO 'shard_id';: Explicitly target a shard.
    • SET SHARDING KEY TO 'value';: Route based on a specific value.

    Routing via SQL Comments

    To reduce latency caused by issuing extra SET commands, you can embed sharding information directly in SQL comments. This is compatible with ORMs like ActiveRecord or SQLAlchemy:

    • /* shard_id: 5 */ SELECT ...
    • /* sharding_key: 1234 */ SELECT ...

    Automatic Sharding Key Extraction

    PgCat can automatically extract sharding keys from queries using the sqlparser crate. This is an experimental feature enabled via the automatic_sharding_key setting.

    -- To talk to a shard explicitly
SET SHARD TO '1';

-- To let the pooler choose based on a value
SET SHARDING KEY TO '1234';

/* shard_id: 5 */ SELECT * FROM foo WHERE id = 1234;

/* sharding_key: 1234 */ SELECT * FROM foo WHERE id = 1234;

  5. Use the Docker-based development console

    main

    For easier debugging of tests, you can spin up a Docker development environment. This provides a terminal environment similar to the test environment. Compiled objects and bundled gems are stored in dev/cache to prevent interference with your host machine.

    ./dev/script/console

  6. Run integration tests using Docker

    main

    To run Ruby and Python integration tests, use the Docker Compose setup located in the tests/docker/ directory. This will also generate a coverage report in ./cov/.

    cd tests/docker/
docker compose up --exit-code-from main

  7. Reload PgCat configuration live

    main

    Most settings (including sharding and replicas configurations) can be reloaded without restarting the pooler. The host and port settings cannot be reloaded live.

    To trigger a reload, use one of the following methods:

    1. Send a SIGHUP signal to the process: kill -s SIGHUP <pid>
    2. Query the RELOAD command against the admin database.

  8. Set up local development environment

    main

    To develop on PgCat locally, follow these steps:

    1. Install the latest stable version of Rust.
    2. Build the project in release mode: cargo build --release.
    3. (Optional) Modify pgcat.toml to match your local setup.
    4. Install Postgres and run the sharding setup script: psql -f tests/sharding/query_routing_setup.sql.
    5. Run the pooler: RUST_LOG=info cargo run --release.

  9. Configure TCP keepalives and user timeout

    main

    Fine-tune TCP connection behavior.

    • general.tcp_keepalives_idle: Seconds of idleness before sending a keepalive packet (default: 5).
    • general.tcp_keepalives_count: Number of unacknowledged keepalive packets allowed before closing the connection (default: 5).
    • general.tcp_keepalives_interval: Interval between keepalive packets (default: 5).
    • general.tcp_user_timeout: (Linux only) Time (ms) transmitted data may remain unacknowledged before TCP forcibly disconnects (default: 10000).

  11. Configure MD5 authentication via auth_query

    main

    Define how PgCat obtains the hash used for MD5 authentication from servers.

    • general.auth_query: The query sent to servers to obtain the hash (e.g., "SELECT $1"). This is inherited by all pools unless redefined.
    • general.auth_query_user: User used to connect to servers to run the auth_query (default: <UNSET>).
    • general.auth_query_password: Password used to connect to servers to run the auth_query (default: <UNSET>).

  12. Configure server failover and ban behavior

    main

    PgCat monitors server health using fast ; queries and by tracking every client query. If a server is unreachable, it is banned for a specified duration. The primary server can never be banned.

    • Ban duration: Controlled by the ban_time setting. The default is 60 seconds.
    • Safety mechanism: If all servers become banned, the ban list is automatically cleared to prevent total service loss due to false positives.