pgdog

PgDog is an open source proxy for scaling PostgreSQL. It supports connection pooling, load balancing queries and sharding entire databases. Written in Rust, PgDog is fast, secure and can manage thousands of connections on commodity hardware. ## Documentation 📘 PgDog documentation can be **[found here](https://docs.pgdog.dev/)**. Any questions? Chat with us on **[Discord](https://discord.com/invite/CcBZkjSJdd)**. ##### Enterprise edition 🏢 Enterprise edition (EE) documentation is available **[here](https://docs.pgdog.dev/enterprise_edition/)**. Changelog is available **[here](CHANGELOG-ENTERPRISE.md)**. ## Quick start ### Kubernetes Helm chart is **[here](https://github.com/pgdogdev/helm)**. To install it, run: ```bash helm repo add pgdogdev https://helm.pgdog.dev helm install pgdog pgdogdev/pgdog ``` ### AWS If you're using AWS RDS, you can deploy PgDog using one of two supported methods: 1. [Helm chart](https://github.com/pgdogdev/helm) with [EKS](https://aws.amazon.com/eks/), or a self-hosted Kubernetes cluster 2. [Terraform module](https://github.com/pgdogdev/pgdog-ecs-terraform) to deploy PgDog on [ECS](https://aws.amazon.com/ecs/) ### Try in Docker You can try PgDog quickly using Docker. Install [Docker Compose](https://docs.docker.com/compose/) and run: ``` docker-compose up ``` Once started, you can connect to PgDog with psql or any other PostgreSQL client: ``` PGPASSWORD=postgres psql -h 127.0.0.1 -p 6432 -U postgres ``` The demo comes with 3 shards and 2 sharded tables: ```sql INSERT INTO users (id, email) VALUES (1, '[email protected]'); INSERT INTO payments (id, user_id, amount) VALUES (1, 1, 100.0); SELECT * FROM users WHERE id = 1; SELECT * FROM payments WHERE user_id = 1; ``` ## Features 📘 **[Configuration](https://docs.pgdog.dev/configuration/)** All PgDog features are configurable and can be turned on and off. PgDog requires 2 configuration files to operate: 1. `pgdog.toml`: hosts, sharding configuration, and other settings 2. `users.toml`: usernames and passwords ### Example Most options have reasonable defaults, so a basic configuration for a single user and database running on the same machine is pretty short: **`pgdog.toml`** ```toml [general] port = 6432 default_pool_size = 10 [[databases]] name = "pgdog" host = "127.0.0.1" ``` **`users.toml`** ```toml [[users]] name = "alice" database = "pgdog" password = "hunter2" ``` If a database in `pgdog.toml` doesn't have a user in `users.toml`, the connection pool for that database will not be created and users won't be able to connect. If you'd like to try it out locally, create the database and user like so: ```sql CREATE DATABASE pgdog; CREATE USER pgdog PASSWORD 'pgdog' LOGIN; ``` ### Transaction pooling 📘 **[Transactions](https://docs.pgdog.dev/features/transaction-mode)** Like PgBouncer, PgDog supports transaction (and session) pooling, allowing thousands of clients to use just a few PostgreSQL server connections. Unlike PgBouncer, PgDog can parse and handle `SET` statements and startup options, ensuring session state is set correctly when sharing server connections between clients with different parameters. PgDog also has more advanced connection recovery options, like automatic abandoned transaction rollbacks and connection re-synchronization to avoid churning server connections during an application crash. ### Load balancer 📘 **[Load balancer](https://docs.pgdog.dev/features/load-balancer/)** PgDog is an application layer (OSI Level 7) load balancer for PostgreSQL. It understands the Postgres protocol, can proxy multiple replicas (and primary) and distributes transactions evenly between databases. The load balancer supports 3 strategies: round robin, random and least active connections. **Example** The load balancer is enabled automatically when a database has more than one host: ```toml [[databases]] name = "prod" host = "10.0.0.1" role = "primary" [[databases]] name = "prod" host = "10.0.0.2" role = "replica" ``` #### Health checks 📘 **[Healthchecks](https://docs.pgdog.dev/features/load-balancer/healthchecks/)** PgDog maintains a real-time list of healthy hosts. When a database fails a health check, it's removed from the active rotation and queries are re-routed to other replicas. This works like an HTTP load balancer, except it's for your database. Health checks maximize database availability and protect against bad network connections, temporary hardware failures or misconfiguration. #### Single endpoint 📘 **[Single endpoint](https://docs.pgdog.dev/features/load-balancer/#single-endpoint)** PgDog uses [`pg_raw_parse`](https://github.com/pgdogdev/pg_raw_parse), which includes the PostgreSQL native parser. By parsing queries, PgDog can detect writes (e.g. `INSERT`, `UPDATE`, `CREATE TABLE`, etc.) and send them to the primary, leaving the replicas to serve reads (`SELECT`). This allows applications to connect to the same PgDog deployment for both reads and writes. ##### Transactions 📘 **[Load balancer & transactions](https://docs.pgdog.dev/features/load-balancer/transactions/)** Transactions can execute multiple statements, so in a primary & replica configuration, PgDog routes them to the primary. Clients can indicate a transaction is read-only, in which case PgDog will send it to a replica: ```sql BEGIN READ ONLY; -- This goes to a replica. SELECT * FROM users LIMIT 1; COMMIT; ``` #### Failover 📘 **[Failover](https://docs.pgdog.dev/features/load-balancer/replication-failover/)** PgDog monitors Postgres replication state and can automatically redirect writes to a different database if a replica is promoted. This doesn't replace tools like Patroni that actually orchestrate failovers. You can use PgDog alongside Patroni (or AWS RDS or other managed Postgres host), to gracefully failover live traffic. **Example** To enable failover, set all database `role` attributes to `auto` and enable replication monitoring (`lsn_check_delay` setting): ```toml [general] lsn_check_delay = 0 [[databases]] name = "prod" host = "10.0.0.1" role = "auto" [[databases]] name = "prod" host = "10.0.0.2" role = "auto" ``` ### Authentication 📘 **[Authentication](https://docs.pgdog.dev/features/authentication/)** PgDog supports five authentication methods: 1. Password-based 2. AWS RDS IAM 3. Azure Workload Identity 4. HashiCorp Vault dynamic credentials 5. HashiCorp Vault static role credentials #### Password-based authentication Password-based authentication allows for clients to authenticate to PgDog and for PgDog to authenticate to PostgreSQL. It currently supports the following password hashing algorithms: - SCRAM-SHA-256 - MD5 - Plain #### RDS IAM backend authentication PgDog can keep client-to-PgDog authentication unchanged while using AWS RDS IAM tokens for PgDog-to-PostgreSQL authentication on a per-user basis. **Example** ```toml [[users]] name = "alice" database = "pgdog" password = "client-password" server_auth = "rds_iam" # Optional; PgDog infers region from *.region.rds.amazonaws.com(.cn) hostnames when omitted. # server_iam_region = "us-east-1" ``` When any user has `server_auth = "rds_iam"`, the following settings must be configured as well: - `tls_verify` must **not** be `"disabled"`. - `passthrough_auth` must be `"disabled"`. #### Azure Workload Identity authentication PgDog can also use Azure Workload Identity for PgDog-to-PostgreSQL authentication, while keeping client-to-PgDog authentication unchanged. This is configured on a per-user basis, similarly to RDS IAM: **Example** ```toml [[users]] name = "alice" database = "pgdog" password = "client-password" server_auth = "azure_workload_identity" ``` When any user has `server_auth = "azure_workload_identity"`, the following settings must be configured as well: - `tls_verify` must **not** be `"disabled"`. - `passthrough_auth` must be `"disabled"`. #### HashiCorp Vault dynamic role authentication PgDog can fetch dynamic database credentials (username and password) from HashiCorp Vault's database secrets engine, while keeping client-to-PgDog authentication unchanged. Credentials are cached and rotated automatically after a configured percentage of the Vault lease has elapsed. **Example** In `users.toml`: ```toml [[users]] name = "alice" database = "pgdog" password = "client-password" server_auth = "vault_dynamic" server_vault_path = "database/creds/pgdog" # Refresh credentials after 80% of the lease has elapsed (default). # vault_refresh_percent = 80 ``` In `pgdog.toml`: ```toml [vault] url = "https://vault.internal:8200" auth_method = "kubernetes" # or "approle" kubernetes_role = "pgdog" ``` PgDog logs into Vault with Kubernetes auth (using the pod's service account JWT) or AppRole (`approle_role_id` plus `approle_secret_id_file` or the `VAULT_SECRET_ID` environment variable). When any user has `server_auth = "vault_dynamic"` or `"vault_static"`, the following settings must be configured as well: - `tls_verify` must **not** be `"disabled"`. - `passthrough_auth` must be `"disabled"`. #### HashiCorp Vault static role authentication Unlike dynamic credentials, a Vault static database role has a fixed username and only its password rotates, on a schedule Vault manages. PgDog supports two independent uses of a static role, they don't need to point at the same role, and each has its own username setting: - `vault_path`: verify the password a client sends to PgDog against Vault's current password for the role, instead of a statically configured password. - `server_auth = "vault_static"` with `server_vault_path`: use the role's Vault-managed password for PgDog-to-PostgreSQL connections. Unlike `vault_dynamic`, PgDog doesn't take the username from Vault, it connects as `server_user` or `name`, if `server_user` isn't set. **Example** In `users.toml`, for a client authenticating as `alice` (verified against a static role registered under that same name) while PgDog connects to Postgres as `pgdog_service` (a separate static role): ```toml [[users]] name = "alice" database = "pgdog" vault_path = "database/static-creds/alice" server_user = "pgdog_service" server_auth = "vault_static" server_vault_path = "database/static-creds/pgdog-service" ``` In `pgdog.toml`, the same `[vault]` section used for dynamic credentials applies. Both settings are optional and independent: set only `vault_path` to verify client passwords while keeping any other backend authentication method, or only `server_auth = "vault_static"` to use a static role for backend connections while clients authenticate with a regular password. ### Sharding 📘 **[Sharding](https://docs.pgdog.dev/features/sharding/)** PgDog is able to manage databases with multiple shards. By using the PostgreSQL parser, PgDog extracts sharding keys and determines the best routing strategy for each query. For cross-shard queries, PgDog assembles and transforms results in memory, sending all rows to the client as if they are coming from a single database. **Example** Configuring multiple hosts for the same database with different shard numbers (`shard` setting) enables sharding: ```toml [[databases]] name = "prod" host = "10.0.0.1" shard = 0 [[databases]] name = "prod" host = "10.0.0.2" shard = 1 ``` Note: read below for how to configure query routing. At least one sharded table is required for sharding to work as expected. #### Sharding functions 📘 **[Sharding functions](https://docs.pgdog.dev/features/sharding/sharding-functions/)** PgDog has two main sharding algorithms: 1. PostgreSQL partition functions (`HASH`, `LIST`, `RANGE`) 2. Using schemas #####