pgsharding/spqr-router repository overview

# Create a network
docker network create spqr-net

# Start PostgreSQL shards
docker run -d --name shard1 --network spqr-net \
  -e POSTGRES_PASSWORD=password -e POSTGRES_USER=user1 -e POSTGRES_DB=db1 \
  postgres:15

docker run -d --name shard2 --network spqr-net \
  -e POSTGRES_PASSWORD=password -e POSTGRES_USER=user1 -e POSTGRES_DB=db1 \
  postgres:15

# Start SPQR router
docker run -d --name spqr-router --network spqr-net \
  -p 6432:6432 -p 7432:7432 \
  -v $(pwd)/router.yaml:/etc/spqr/router.yaml:ro \
  pgsharding/spqr-router:latest

version: '3.8'

services:
  shard1:
    image: postgres:15
    environment:
      POSTGRES_USER: user1
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db1

  shard2:
    image: postgres:15
    environment:
      POSTGRES_USER: user1
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db1

  spqr-router:
    image: pgsharding/spqr-router:latest
    ports:
      - "6432:6432"  # Router port
      - "7432:7432"  # Admin console
    volumes:
      - ./router.yaml:/etc/spqr/router.yaml:ro
    depends_on:
      - shard1
      - shard2

host: '0.0.0.0'
router_port: '6432'
admin_console_port: '7432'

router_mode: PROXY
log_level: info

frontend_rules:
  - usr: user1
    db: db1
    pool_mode: TRANSACTION
    auth_rule:
      auth_method: ok

shards:
  shard1:
    db: db1
    usr: user1
    pwd: password
    type: DATA
    tls:
      sslmode: disable
    hosts:
      - 'shard1:5432'
  shard2:
    db: db1
    usr: user1
    pwd: password
    type: DATA
    tls:
      sslmode: disable
    hosts:
      - 'shard2:5432'

backend_rules:
  - usr: user1
    db: db1
    connection_limit: 100

docker run -d --name spqr-router \
  -v $(pwd)/router.yaml:/etc/spqr/router.yaml:ro \
  pgsharding/spqr-router:latest

psql "host=localhost port=7432 user=user1 dbname=db1 sslmode=disable"

-- Create a distribution for integer-based sharding
CREATE DISTRIBUTION ds1 COLUMN TYPES integer;

-- Attach tables to the distribution
ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY id;

-- Define key ranges (1-999 → shard1, 1000+ → shard2)
CREATE KEY RANGE kr1 FROM 1 ROUTE TO shard1 FOR DISTRIBUTION ds1;
CREATE KEY RANGE kr2 FROM 1000 ROUTE TO shard2 FOR DISTRIBUTION ds1;

psql "host=localhost port=6432 user=user1 dbname=db1 sslmode=disable"

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total DECIMAL);

INSERT INTO orders VALUES (100, 'Alice', 150.00);   -- Routed to shard1
INSERT INTO orders VALUES (1500, 'Bob', 200.00);    -- Routed to shard2

SELECT * FROM orders WHERE id = 100;  -- Queries shard1
SELECT * FROM orders;                  -- Queries both shards