PgDog
1. Overview
PgDog is a high-performance, open-source clustering middleware (proxy tool) designed specifically for PostgreSQL and written in Rust. It integrates automatic sharding, connection pooling, and load balancing, enabling developers to achieve horizontal scaling and high-availability management of PostgreSQL databases without modifying any application code.
Note that PgDog uses PostgreSQL’s native pg_query module to parse statements, so it does not support running in Oracle compatibility mode.
Project URL: https://github.com/pgdogdev/pgdog
Version: v0.1.45
Open-source license: AGPL-3.0 License
2. Installation
| The source build was tested on Ubuntu 26.04. |
2.1. Dependencies
sudo apt update && \
sudo apt install -y cmake clang curl pkg-config \
libssl-dev git build-essential mold rustup \
docker| The instructions in this document require two IvorySQL database instances, which can be quickly set up using the docker-compose file provided in this document. To install docker-compose, refer to: https://docs.docker.com/compose/install/linux/ |
3. Configuration
This document configures two shards and uses PgDog’s automatic sharding feature as an example.
PgDog is configured through two files:
| File Name | Description |
|---|---|
pgdog.toml |
Contains basic configuration information such as PgDog’s port settings and the backend PostgreSQL service configuration |
users.toml |
The username and password for accessing PgDog are configured here |
Create pgdog.toml:
[general]
host = "0.0.0.0"
port = 6432
default_pool_size = 10
# ---- ivory_shard: shard across two IvorySQL backends ----
# host, port, and database_name need to be modified according to your actual setup if you are not using the environment built with the docker-compose provided in this document
[[databases]]
name = "ivory_shard"
host = "ivory-shard0"
port = 5432
database_name = "testdb"
user = "ivorysql" # if not provide, using `name` in `users.toml`
password = "ivorysql" # if not provide, using `password` in `users.toml`
shard = 0
[[databases]]
name = "ivory_shard"
host = "ivory-shard1"
port = 5432
database_name = "testdb"
shard = 1
# ---- Shard key declaration ----
# The configuration must match the actual table structure
[[sharded_tables]]
database = "ivory_shard"
name = "orders"
column = "customer_id"
data_type = "bigint"Create users.toml:
[admin]
name = "admin"
user = "admin"
password = "pgdog"
[[users]]
name = "ivorysql"
password = "ivorysql"
database = "ivory_shard"
pool_size = 10Create docker-compose.shard.yml:
Please modify the volumes field according to the actual location of your configuration files.
|
# Sharding test topology (standalone compose): 2 shard backends.
# ivory-shard0 IvorySQL 5.4 (pg) host:5443
# ivory-shard1 IvorySQL 5.4 (pg) host:5444
x-ivory: &ivory
image: registry.highgo.com/ivorysql/ivorysql:5.4-bookworm
environment: &ivoryenv
MODE: pg
IVORYSQL_USER: ivorysql
IVORYSQL_PASSWORD: ivorysql
IVORYSQL_DB: testdb
healthcheck:
test: ["CMD-SHELL", "pg_isready -U ivorysql -d testdb"]
interval: 5s
timeout: 3s
retries: 30
services:
ivory-shard0:
<<: *ivory
container_name: ivory-shard0
ports: ["5443:5432"]
ivory-shard1:
<<: *ivory
container_name: ivory-shard1
ports: ["5444:5432"]4. Usage
4.2. Administration Console
PgDog provides a built-in administration database. The username and password are configured via the field in users.toml.
psql "postgres://admin:pgdog@localhost:6433/admin"-- View client connections and real-time statistics
SHOW CLIENTS
-- View PostgreSQL connections initiated by PgDog
SHOW SERVERS
-- View connection pool information
SHOW POOLS
-- View the configuration currently loaded from pgdog.toml
SHOW CONFIG
-- View connection pool statistics
SHOW STATS
-- List of PgDog processes running on the same network. Requires service discovery to be enabled
SHOW PEERS
-- Reload the configuration from disk. For which options can be changed at runtime, refer to pgdog.toml and users.toml
RELOAD
-- Recreate all server connections using the existing configuration
RECONNECT
-- Pause all connection pools. Clients will wait for a connection until the pools resume. Useful for performing a graceful restart of the PostgreSQL server
PAUSE
-- Resume all connection pools. Clients can acquire connections again
RESUME
-- List the prepared statements currently in the cache
SHOW PREPARED
-- List the statements currently in the AST cache used for query routing
SHOW QUERY_CACHE
-- Pause all queries in order to synchronize configuration changes across multiple PgDog instances
MAINTENANCE
-- Show the PostgreSQL replication status for each database, including replica lag
SHOW REPLICATION4.4. Performing Operations
Make sure the shard backends do not have an orders table; PgDog will create it automatically.
|
-- Create the table
CREATE TABLE orders (
order_id bigint,
customer_id bigint,
amount numeric(10,2),
PRIMARY KEY (order_id, customer_id)
);
-- Insert data: these rows will be inserted into the two shard backends respectively
-- BUG: the `generate_series` function cannot be used here, because PgDog currently passes this function through transparently
INSERT INTO orders values(1, 1, 1);
INSERT INTO orders values(2, 2, 2);
INSERT INTO orders values(3, 3, 3);
INSERT INTO orders values(4, 4, 4);
INSERT INTO orders values(5, 5, 5);
INSERT INTO orders values(6, 6, 6);
INSERT INTO orders values(7, 7, 7);
-- Query the data
SELECT * FROM orders;4.5. Connecting to Each Shard Backend to Verify the Data
# Shard 1
psql "postgres://ivorysql:ivorysql@localhost:5443/testdb",
# Shard 2
psql "postgres://ivorysql:ivorysql@localhost:5444/testdb",Run the query on each shard backend to verify the data
SELECT * FROM orders;