Router with Init SQL

Simple SPQR deployment using init_sql for static sharding configuration

Overview

The bare router deployment is the simplest way to run SPQR. It’s ideal for testing, development, and scenarios where your sharding rules don’t need to be updated dynamically.

In this deployment mode:

• You run spqr-router with an init_sql file
• The router reads the sharding configuration from this local file on startup
• No external coordinator or etcd cluster is required
• You can run multiple router instances simultaneously
• Each router instance operates independently

Setup

1. Create Router Configuration File

Create a YAML configuration file (e.g., router.yaml):

router.yaml

host: 'localhost'
router_port: '6432'
admin_console_port: '7432'
grpc_api_port: '7001'

# Enable init SQL mode
use_init_sql: true
init_sql: "/path/to/init.sql"
exit_on_init_sql: true  # Exit if init SQL parsing fails

router_mode: PROXY
log_level: info

frontend_rules:
  - db: mydb
    usr: myuser
    pool_mode: TRANSACTION
    auth_rule:
      auth_method: ok

backend_rules:
  - db: mydb
    usr: myuser
    connection_limit: 100
    pool_discard: false
    pool_rollback: true

shards:
  shard1:
    db: mydb
    usr: myuser
    pwd: password
    type: DATA
    hosts:
      - 'shard1-host:5432'
  shard2:
    db: mydb
    usr: myuser
    pwd: password
    type: DATA
    hosts:
      - 'shard2-host:5432'

2. Create Init SQL File

Create an SQL file with your sharding configuration (e.g., init.sql):

init.sql

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

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

-- Define key ranges
CREATE KEY RANGE krid1 FROM 0 ROUTE TO shard1 FOR DISTRIBUTION ds1;
CREATE KEY RANGE krid2 FROM 10000 ROUTE TO shard2 FOR DISTRIBUTION ds1;

3. Start the Router

spqr-router run --config router.yaml

Connect to the admin console to verify the configuration:

psql "host=localhost port=6432 dbname=spqr-console user=spqr-console sslmode=disable"

Check the configured shards and key ranges:

-- List all shards
SHOW shards;

-- List distributions
SHOW distributions;

-- List key ranges
SHOW key_ranges;

4. Connect

Clients can now connect to the router:

psql "host=localhost port=6432 dbname=mydb user=myuser sslmode=disable"

The router will automatically route queries to the appropriate shard based on the sharding key.

Considerations

• Configuration changes require updating the init.sql file and restarting the router
• No dynamic updates are possible while the router is running
• For persistence you may set memqdb_backup_path. This feature is deprecated and may be removed in a future release — use at your own risk.

For configuration reference, see the Router Configuration reference.