### Deploy PgCat using Docker Compose

Source: https://github.com/postgresml/pgcat/blob/main/README.md

This example demonstrates how to deploy PgCat using Docker Compose for a quick local setup. It includes commands to start the Docker Compose environment and then connect to the running PgCat instance using `psql`.

```bash
docker-compose up

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

--------------------------------

### Start Docker Development Environment

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Launches a Dockerized development environment, providing a terminal with an isolated environment similar to the test setup. This allows for easier debugging, compiling the pooler, and running tests within the container. Compiled objects and bundled gems are stored in dev/cache.

```bash
./dev/script/console
```

--------------------------------

### Setup PostgreSQL Databases for Sharding Tests

Source: https://github.com/postgresml/pgcat/blob/main/tests/sharding/README.md

This script sets up three PostgreSQL databases (shard0, shard1, shard2) and creates a partitioned table named 'data' in each. The table is partitioned by hash, with each database holding one partition based on a modulus of 3.

```bash
psql -f query_routing_setup.sql
```

--------------------------------

### Run pgbench for Protocol Testing

Source: https://github.com/postgresml/pgcat/blob/main/README.md

This command sequence uses pgbench to test the protocol implementation. It first initializes the database, then runs tests with simple and extended protocols, targeting a PostgreSQL instance on localhost port 6432. Ensure pgbench is installed and accessible in your PATH.

```bash
pgbench -i -h 127.0.0.1 -p 6432 && \
pgbench -t 1000 -p 6432 -h 127.0.0.1 --protocol simple && \
pgbench -t 1000 -p 6432 -h 127.0.0.1 --protocol extended
```

--------------------------------

### Build and Run PgCat Locally with Rust

Source: https://github.com/postgresml/pgcat/blob/main/README.md

This snippet outlines the steps to build and run PgCat locally for development. It requires Rust to be installed and involves compiling the project, optionally modifying the configuration, setting up a PostgreSQL database, and then running the application with logging enabled.

```bash
rustup default stable
cargo build --release
# Optional: Change config in pgcat.toml
psql -f tests/sharding/query_routing_setup.sql
RUST_LOG=info cargo run --release
```

--------------------------------

### Sharding Key Configuration in Ruby

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Demonstrates how to set the sharding key and perform database operations using ActiveRecord in Ruby. It shows examples for sharding by ID and geographical sharding, including user creation and retrieval.

```ruby
User.connection.execute "SET SHARDING KEY TO '1234'"
User.find_by_id(1234)

User.connection.execute "SET SERVER ROLE TO 'primary'"
User.connection.execute "SET SHARDING KEY TO '85'"
User.create(name: "test user", email: "test@example.com", zone_id: 85)

User.connection.execute "SET SERVER ROLE TO 'auto'"
User.find_by_email("test@example.com")
```

--------------------------------

### Run PgCat Sharding Tests with Insert and Select Queries

Source: https://github.com/postgresml/pgcat/blob/main/tests/sharding/README.md

This section details how to run the sharding tests after starting PgCat. It involves executing insert and select queries against the PgCat instance to verify the sharding logic. Successful execution should not produce errors related to partition bounds.

```bash
psql -h 127.0.0.1 -p 6432 -f query_routing_test_insert.sql
psql -h 127.0.0.1 -p 6432 -f query_routing_test_select.sql
```

--------------------------------

### ActiveRecord Integration for PgCat Sharding

Source: https://context7.com/postgresml/pgcat/llms.txt

Illustrates how to integrate PgCat's sharding capabilities with Ruby on Rails ActiveRecord. This includes methods for setting shards, sharding keys, and server roles, along with usage examples.

```ruby
# Rails application using PgCat sharding

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  def self.with_shard(shard_id)
    connection.execute("SET SHARD TO '#{shard_id}'")
    yield
  ensure
    connection.execute("SET SHARD TO '0'")  # Reset to default
  end

  def self.with_sharding_key(key)
    connection.execute("SET SHARDING KEY TO '#{key}'")
    yield
  end

  def self.on_primary
    connection.execute("SET SERVER ROLE TO 'primary'")
    yield
  ensure
    connection.execute("SET SERVER ROLE TO 'auto'")
  end
end

class User < ApplicationRecord
  def self.find_by_tenant(tenant_id, user_id)
    with_sharding_key(tenant_id) do
      find(user_id)
    end
  end
end

# Usage examples
User.with_shard(1) do
  User.where(active: true).limit(100)
end

User.on_primary do
  User.create!(name: "New User", email: "user@example.com")
end

# Using query annotations (comment-based routing)
User.annotate("/* shard_id: 2 */").where(tenant_id: 123).first

```

--------------------------------

### Run Ruby and Python Integration Tests with Docker Compose

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Starts a Docker Compose environment to run integration tests for Ruby and Python. This command also generates a coverage report in the ./cov/ directory. Navigate to the tests/docker/ directory before running this command.

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

--------------------------------

### Raw SQL Sharding Commands

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Provides examples of raw SQL commands for interacting with PgCat, including setting shards, sharding keys, and server roles for read/write operations. It covers selecting data, finding by ID, and inserting records.

```sql
-- Grab a bunch of users from shard 1
SET SHARD TO '1';
SELECT * FROM users LIMIT 10;

-- Find by id
SET SHARDING KEY TO '1234';
SELECT * FROM USERS WHERE id = 1234;

-- Writing in a primary/replicas configuration.
SET SHARD ROLE TO 'primary';
SET SHARDING KEY TO '85';
INSERT INTO users (name, email, zome_id) VALUES ('test user', 'test@example.com', 85);

SET SERVER ROLE TO 'auto'; -- let the query router figure out where the query should go
SELECT * FROM users WHERE email = 'test@example.com'; -- shard setting lasts until set again; we are reading from the primary
```

--------------------------------

### ActiveRecord/Rails Shard Interaction Example

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Demonstrates how to interact with different database shards using ActiveRecord in Rails. It shows establishing a connection and then explicitly setting the shard to '1' before fetching records from that specific shard.

```ruby
class User < ActiveRecord::Base
end

# Metadata will be fetched from shard 0
ActiveRecord::Base.establish_connection

# Grab a bunch of users from shard 1
User.connection.execute "SET SHARD TO '1'"
User.take(10)
```

--------------------------------

### Configure Shard Database Name

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Sets the database name for a specific shard. For example, 'postgres'.

```configuration
path: pools.<pool_name>.shards.<shard_index>.database
default: "shard0"
```

--------------------------------

### Get Application URL with Ingress Enabled (Helm Template)

Source: https://github.com/postgresml/pgcat/blob/main/charts/pgcat/templates/NOTES.txt

This snippet, written in Helm templating language, generates the application URL when Ingress is enabled. It iterates through hosts and paths defined in the Ingress configuration to construct the full URL. It supports both HTTP and HTTPS based on TLS settings.

```helm
{{- if .Values.ingress.enabled }}
{{- range $host := .Values.ingress.hosts }}
  {{- range .paths }}
  http{{ if $.Values.ingress.tls }}s{{ end }}://{{ $host.host }}{{ .path }}
  {{- end }}
{{- end }}
{{- end }}
```

--------------------------------

### Get Application URL with LoadBalancer Service (Bash)

Source: https://github.com/postgresml/pgcat/blob/main/charts/pgcat/templates/NOTES.txt

This bash script retrieves the application URL for a LoadBalancer service. It notes that it may take time for the LoadBalancer IP to become available and provides a command to monitor the service status. It then extracts the LoadBalancer IP and constructs the URL using the service port.

```bash
NOTE: It may take a few minutes for the LoadBalancer IP to be available.
      You can watch the status of by running 'kubectl get --namespace {{ .Release.Namespace }} svc -w {{ include "pgcat.fullname" . }}"'
export SERVICE_IP=$(kubectl get svc --namespace {{ .Release.Namespace }} {{ include "pgcat.fullname" . }} --template "{{ range (index .status.loadBalancer.ingress 0) }}{{.}}{{ end }}")
echo http://$SERVICE_IP:{{ .Values.service.port }}
```

--------------------------------

### Get Application URL with NodePort Service (Bash)

Source: https://github.com/postgresml/pgcat/blob/main/charts/pgcat/templates/NOTES.txt

This bash script retrieves the application URL for a NodePort service. It first obtains the NodePort assigned to the service and the IP address of a Kubernetes node. It then echoes the constructed URL. This is useful for accessing services exposed via NodePort.

```bash
export NODE_PORT=$(kubectl get --namespace {{ .Release.Namespace }} -o jsonpath="{.spec.ports[0].nodePort}" services {{ include "pgcat.fullname" . }})
export NODE_IP=$(kubectl get nodes --namespace {{ .Release.Namespace }} -o jsonpath="{.items[0].status.addresses[0].address}")
echo http://$NODE_IP:$NODE_PORT
```

--------------------------------

### Run PgCat with Docker and Local Development Commands

Source: https://context7.com/postgresml/pgcat/llms.txt

Provides commands to run PgCat as a Docker container and for quick local development using Docker Compose. Includes instructions for connecting to the running PgCat instance.

```bash
# Run with Docker
docker run -d \
    --name pgcat \
    -p 6432:6432 \
    -p 9930:9930 \
    -v $(pwd)/pgcat.toml:/etc/pgcat/pgcat.toml:ro \
    ghcr.io/postgresml/pgcat:latest \
    pgcat /etc/pgcat/pgcat.toml

# Quick local development
docker-compose up -d
PGPASSWORD=postgres psql -h 127.0.0.1 -p 6432 -U postgres -c 'SELECT 1'

```

--------------------------------

### Connect to PgCat with TLS and Generate Certificates

Source: https://context7.com/postgresml/pgcat/llms.txt

Demonstrates how to connect to PgCat using TLS-enabled PostgreSQL clients and how to generate self-signed certificates for testing purposes using OpenSSL.

```bash
# Connect with TLS
psql "host=pgcat-host port=6432 dbname=myapp user=app_user sslmode=require"

# Generate self-signed certificate for testing
openssl req -x509 -nodes -days 365 -newkey rsa:2048 \
    -keyout server.key -out server.crt \
    -subj "/CN=pgcat-host"

```

--------------------------------

### Control Server Role Selection with SQL Commands

Source: https://context7.com/postgresml/pgcat/llms.txt

Illustrates how to explicitly control which database server (primary, replica, auto, any, default) handles queries using the `SET SERVER ROLE` SQL command. This enables fine-grained routing for read/write operations.

```sql
-- Route all subsequent queries to primary (for writes)
SET SERVER ROLE TO 'primary';
INSERT INTO orders (customer_id, total) VALUES (123, 99.99);

-- Route all subsequent queries to replica (for reads)
SET SERVER ROLE TO 'replica';
SELECT * FROM orders WHERE customer_id = 123;

-- Let PgCat automatically route based on query type
SET SERVER ROLE TO 'auto';
SELECT COUNT(*) FROM orders;  -- Routes to replica
UPDATE orders SET status = 'shipped' WHERE id = 456;  -- Routes to primary

-- Round-robin between all servers
SET SERVER ROLE TO 'any';

-- Reset to pool's default configuration
SET SERVER ROLE TO 'default';

-- Check current server role setting
SHOW SERVER ROLE;

```

--------------------------------

### Configure Sharding with SQL and TOML

Source: https://context7.com/postgresml/pgcat/llms.txt

Details how to configure automatic query routing to specific database shards using explicit shard selection or sharding key-based routing. This includes SQL commands for setting shards and TOML configuration for defining shards and sharding functions.

```sql
-- Explicitly select a shard for subsequent queries
SET SHARD TO '1';
SELECT * FROM users LIMIT 10;  -- Queries shard 1

-- Route based on sharding key (uses PARTITION BY HASH)
SET SHARDING KEY TO '12345';
SELECT * FROM users WHERE id = 12345;  -- Routes to computed shard

-- Check current shard selection
SHOW SHARD;

-- Select random shard
SET SHARD TO 'ANY';

```

```toml
# pgcat.toml - Sharding configuration
[pools.sharded_app]
pool_mode = "transaction"
sharding_function = "pg_bigint_hash"  # or "sha1"
automatic_sharding_key = "users.id"   # Auto-parse from WHERE clause

# Define multiple shards
[pools.sharded_app.shards.0]
servers = [["shard0-primary.db", 5432, "primary"]]
database = "shard0"

[pools.sharded_app.shards.1]
servers = [["shard1-primary.db", 5432, "primary"]]
database = "shard1"

[pools.sharded_app.shards.2]
servers = [["shard2-primary.db", 5432, "primary"]]
database = "shard2"

```

--------------------------------

### Plugin Configuration

Source: https://context7.com/postgresml/pgcat/llms.txt

Configure built-in plugins for query interception, logging, table access control, and connection prewarming.

```APIDOC
## Plugin Configuration

Configure built-in plugins for query interception, logging, table access control, and connection prewarming.

### Configuration (`pgcat.toml`)

```toml
[plugins]

# Prewarm connections with specific queries
[plugins.prewarmer]
enabled = true
queries = [
    "SELECT pg_prewarm('frequently_accessed_table')",
    "SET work_mem = '256MB'"
]

# Log all queries to stdout
[plugins.query_logger]
enabled = false

# Block access to sensitive system tables
[plugins.table_access]
enabled = true
tables = ["pg_user", "pg_roles", "pg_shadow", "pg_authid"]

# Intercept and return fake results for specific queries
[plugins.intercept]
enabled = true

[plugins.intercept.queries.0]
query = "select current_database() as a, current_schemas(false) as b"
schema = [["a", "text"], ["b", "text"]]
result = [["${DATABASE}", "{public}"]]

[plugins.intercept.queries.1]
query = "select version()"
schema = [["version", "text"]]
result = [["PostgreSQL 14.0 (PgCat)"]]
```
```

--------------------------------

### Run Rust Unit Tests

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Executes all unit tests written in Rust for the project. This command is typically run from the project's root directory. It utilizes the Cargo build system to compile and run the tests.

```bash
cargo test
```

--------------------------------

### Configure PgCat Plugins

Source: https://context7.com/postgresml/pgcat/llms.txt

Configure various built-in PgCat plugins in `pgcat.toml` for functionalities like prewarming connections, logging queries, controlling table access, and intercepting queries to return fake results.

```toml
# pgcat.toml - Plugin configuration

[plugins]
# Prewarm connections with specific queries
[plugins.prewarmer]
enabled = true
queries = [
    "SELECT pg_prewarm('frequently_accessed_table')",
    "SET work_mem = '256MB'"
]

# Log all queries to stdout
[plugins.query_logger]
enabled = false

# Block access to sensitive system tables
[plugins.table_access]
enabled = true
tables = ["pg_user", "pg_roles", "pg_shadow", "pg_authid"]

# Intercept and return fake results for specific queries
[plugins.intercept]
enabled = true

[plugins.intercept.queries.0]
query = "select current_database() as a, current_schemas(false) as b"
schema = [["a", "text"], ["b", "text"]]
result = [["${DATABASE}", "{public}"]]

[plugins.intercept.queries.1]
query = "select version()"
schema = [["version", "text"]]
result = [["PostgreSQL 14.0 (PgCat)"]]
```

--------------------------------

### TLS Configuration

Source: https://context7.com/postgresml/pgcat/llms.txt

Enable TLS for secure client connections and server connections to PostgreSQL backends.

```APIDOC
## TLS Configuration

Enable TLS for secure client connections and server connections to PostgreSQL backends.
```

--------------------------------

### Reloading PgCat Configuration

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Demonstrates how to reload PgCat's configuration without restarting the process. This can be achieved by sending a `SIGHUP` signal to the process or by executing a `RELOAD` command on the admin database. Most settings, including sharding and replicas, can be reloaded dynamically.

```bash
# Send SIGHUP signal
kill -s SIGHUP <PID>

# Query admin database
psql -h 127.0.0.1 -p 6432 -d pgcate -c 'RELOAD'
```

--------------------------------

### Configure Server Selection and TLS in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Defines how PgCat selects backend servers and manages TLS connections. Options include round-robin server selection and TLS settings for server connections, including certificate verification.

```TOML
path: general.server_round_robin
default: false

path: general.server_tls
default: false

path: general.verify_server_certificate
default: false
```

--------------------------------

### Deploy PgCat using Docker Compose

Source: https://context7.com/postgresml/pgcat/llms.txt

Defines a docker-compose.yml file to deploy PgCat along with a PostgreSQL instance. It configures port mappings, volume mounts for the configuration file, environment variables, and a health check.

```yaml
# docker-compose.yml
version: '3.8'

services:
  pgcat:
    image: ghcr.io/postgresml/pgcat:latest
    ports:
      - "6432:6432"
      - "9930:9930"
    volumes:
      - ./pgcat.toml:/etc/pgcat/pgcat.toml:ro
    environment:
      - RUST_LOG=info
    command: ["pgcat", "/etc/pgcat/pgcat.toml"]
    healthcheck:
      test: ["CMD", "psql", "-h", "localhost", "-p", "6432", "-U", "admin_user", "-d", "pgbouncer", "-c", "SHOW VERSION"]
      interval: 10s
      timeout: 5s
      retries: 5

  postgres-primary:
    image: postgres:14
    environment:
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: secure_password
      POSTGRES_DB: production_db
    ports:
      - "5432:5432"

```

--------------------------------

### Configure Authentication Query

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Sets the SQL query executed on servers to obtain the MD5 hash for authentication. This query runs against the database configured in the pool and can be overridden at the pool level.

```configuration
path: pools.<pool_name>.auth_query
default: <UNSET>
example: "SELECT $1"
```

--------------------------------

### Configure PgCat Connection Pools (TOML)

Source: https://context7.com/postgresml/pgcat/llms.txt

Defines the configuration for connection pools in PgCat using the `pgcat.toml` file. It specifies general settings, pool modes, load balancing, user credentials, and sharding server details.

```toml
# pgcat.toml - Pool configuration example

[general]
host = "0.0.0.0"
port = 6432
enable_prometheus_exporter = true
prometheus_exporter_port = 9930
connect_timeout = 5000
idle_timeout = 30000
healthcheck_timeout = 1000
ban_time = 60
worker_threads = 5
admin_username = "admin_user"
admin_password = "admin_pass"

[pools.myapp]
pool_mode = "transaction"
load_balancing_mode = "random"
default_role = "any"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true

[pools.myapp.users.0]
username = "app_user"
password = "secure_password"
pool_size = 20
min_pool_size = 5
statement_timeout = 30000

[pools.myapp.shards.0]
servers = [
    ["primary.db.example.com", 5432, "primary"],
    ["replica1.db.example.com", 5432, "replica"],
    ["replica2.db.example.com", 5432, "replica"]
]
database = "production_db"

```

--------------------------------

### Configure Shard Servers

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

An array defining the servers within a shard. Each server is represented as an array containing host, port, and role (e.g., 'primary', 'replica').

```configuration
path: pools.<pool_name>.shards.<shard_index>.servers
default: [["127.0.0.1", 5432, "primary"], ["localhost", 5432, "replica"]]
```

--------------------------------

### Querying PgBouncer Statistics

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Shows how to query the admin database 'pgcat' (or 'pgbouncer' for compatibility) to retrieve statistics, similar to what PgBouncer reports. This command uses `psql` to connect and execute a `SHOW DATABASES` command.

```bash
psql -h 127.0.0.1 -p 6432 -d pgbouncer -c 'SHOW DATABASES'
```

--------------------------------

### Configure Shard Mirrors

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

An array specifying mirror servers for a shard. Each mirror entry includes host, port, and the index of the corresponding server in the `servers` array. Traffic to a server will be redirected to its mirror.

```configuration
path: pools.<pool_name>.shards.<shard_index>.mirrors
default: <UNSET>
example: [["1.2.3.4", 5432, 0], ["1.2.3.4", 5432, 1]]
```

--------------------------------

### Admin Database Commands

Source: https://context7.com/postgresml/pgcat/llms.txt

Connect to the `pgbouncer` or `pgcat` admin database to monitor pool statistics, manage connections, and perform administrative operations using `SHOW` commands.

```APIDOC
## Admin Database Commands

Connect to the `pgbouncer` or `pgcat` admin database to monitor pool statistics, manage connections, and perform administrative operations.

### Connecting to Admin Database

```bash
PGPASSWORD=admin_pass psql -h 127.0.0.1 -p 6432 -U admin_user -d pgbouncer
```

### Available Commands

- **`SHOW HELP;`**: Shows available commands.
- **`SHOW POOLS;`**: Views all connection pools and their status.
  *Returns*: `database`, `user`, `cl_active`, `cl_waiting`, `sv_active`, `sv_idle`, `sv_used`, etc.
- **`SHOW DATABASES;`**: Views configured databases and servers.
  *Returns*: `name`, `host`, `port`, `database`, `pool_size`, `current_connections`, `paused`, `disabled`
- **`SHOW CLIENTS;`**: Views connected clients.
  *Returns*: `client_id`, `database`, `user`, `state`, `transaction_count`, `query_count`
- **`SHOW SERVERS;`**: Views server connections.
  *Returns*: `server_id`, `database_name`, `user`, `state`, `transaction_count`, `bytes_sent/received`
- **`SHOW STATS;`**: Views aggregate statistics.
  *Returns*: `total_xact_count`, `total_query_count`, `total_received`, `total_sent`, `avg times`
- **`SHOW CONFIG;`**: Views current configuration.
- **`SHOW VERSION;`**: Views PgCat version.
- **`SHOW BANS;`**: Views banned servers.
- **`SHOW LISTS;`**: Views summary counts.
```

--------------------------------

### Configure Authentication Queries in PgCat

Source: https://context7.com/postgresml/pgcat/llms.txt

Set up authentication queries in pgcat.toml to validate user credentials against the database without storing passwords directly in the configuration. This can be configured globally or per-pool.

```toml
# pgcat.toml - Auth query configuration

[general]
auth_query = "SELECT usename, passwd FROM pg_shadow WHERE usename = $1"
auth_query_user = "auth_user"
auth_query_password = "auth_password"

# Or configure per-pool
[pools.myapp]
auth_query = "SELECT username, password_hash FROM app_users WHERE username = $1"
auth_query_user = "auth_service"
auth_query_password = "service_password"

```

--------------------------------

### Configure Comment-Based Routing in pgcat.toml

Source: https://context7.com/postgresml/pgcat/llms.txt

Enable and configure comment-based routing in PgCat by defining regular expressions for extracting shard IDs and sharding keys from SQL comments within the `pgcat.toml` configuration file.

```toml
# pgcat.toml - Enable comment-based routing
[pools.myapp]
sharding_key_regex = '/\* sharding_key: (\\d+) \*/'
shard_id_regex = '/\* shard_id: (\\d+) \*/'
regex_search_limit = 1000  # Only scan first 1000 chars
```

--------------------------------

### Connect to PgCat using psql and Python (psycopg2)

Source: https://context7.com/postgresml/pgcat/llms.txt

Demonstrates how to connect to PgCat using standard PostgreSQL client tools like `psql` and programming languages like Python with the `psycopg2` library. The pool name is used as the database name in the connection string.

```bash
# Connect using psql
PGPASSWORD=secure_password psql -h 127.0.0.1 -p 6432 -U app_user -d myapp

# Connection string format
postgres://app_user:secure_password@pgcat-host:6432/myapp

```

```python
# Python example using psycopg2
import psycopg2

conn = psycopg2.connect(
    host="127.0.0.1",
    port=6432,
    database="myapp",
    user="app_user",
    password="secure_password"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = %s", (123,))
results = cursor.fetchall()
conn.close()

```

--------------------------------

### Configure TLS for PgCat

Source: https://context7.com/postgresml/pgcat/llms.txt

Configure TLS settings for both client connections to PgCat and PgCat's connections to PostgreSQL servers. This involves specifying certificate and key paths for server-side TLS and enabling server certificate verification.

```toml
# pgcat.toml - TLS configuration

[general]
# Client TLS (clients connecting to PgCat)
tls_certificate = "/etc/pgcat/server.crt"
tls_private_key = "/etc/pgcat/server.key"

# Server TLS (PgCat connecting to PostgreSQL)
server_tls = true
verify_server_certificate = true

```

--------------------------------

### Access Application with ClusterIP Service (Bash)

Source: https://github.com/postgresml/pgcat/blob/main/charts/pgcat/templates/NOTES.txt

This bash script facilitates access to an application running on a ClusterIP service by setting up port forwarding. It identifies the pod name and container port, then forwards local port 8080 to the container's port. It instructs the user to visit http://127.0.0.1:8080.

```bash
export POD_NAME=$(kubectl get pods --namespace {{ .Release.Namespace }} -l "app.kubernetes.io/name={{ include "pgcat.name" . }},app.kubernetes.io/instance={{ .Release.Name }}" -o jsonpath="{.items[0].metadata.name}")
export CONTAINER_PORT=$(kubectl get pod --namespace {{ .Release.Namespace }} $POD_NAME -o jsonpath="{.spec.containers[0].ports[0].containerPort}")
echo "Visit http://127.0.0.1:8080 to use your application"
kubectl --namespace {{ .Release.Namespace }} port-forward $POD_NAME 8080:$CONTAINER_PORT
```

--------------------------------

### Configure General Network Settings in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Defines the IP address and port for PgCat to run on. '0.0.0.0' allows access from any network interface. The port defaults to 6432, matching PgBouncer.

```TOML
path: general.host
default: "0.0.0.0"

path: general.port
default: 6432
```

--------------------------------

### Prometheus Scrape Configuration for PgCat

Source: https://context7.com/postgresml/pgcat/llms.txt

Configure Prometheus to scrape metrics from PgCat by defining a scrape job in `prometheus.yml`. This enables monitoring of PgCat's performance and connection pool statistics.

```yaml
# prometheus.yml
scrape_configs:
  - job_name: 'pgcat'
    static_configs:
      - targets: ['pgcat-host:9930']
    scrape_interval: 15s
```

--------------------------------

### Comment-Based Shard Routing

Source: https://context7.com/postgresml/pgcat/llms.txt

Route queries by embedding sharding information directly within SQL comments. This method allows for dynamic routing without separate commands and integrates seamlessly with ORMs that support query annotations.

```APIDOC
## Comment-Based Shard Routing

Include sharding information directly in SQL comments for routing without separate commands. This approach minimizes round trips and integrates well with ORMs that support query annotations.

### Routing Examples

- **Route by explicit shard ID:**
  ```sql
  /* shard_id: 2 */ SELECT * FROM orders WHERE customer_id = 789;
  ```

- **Route by sharding key (computes shard via hash):**
  ```sql
  /* sharding_key: 12345 */ SELECT * FROM users WHERE id = 12345;
  ```

- **Works with any query type (INSERT, UPDATE):**
  ```sql
  /* shard_id: 1 */ INSERT INTO events (user_id, action) VALUES (100, 'login');
  /* sharding_key: 100 */ UPDATE users SET last_login = NOW() WHERE id = 100;
  ```

### Configuration (`pgcat.toml`)

```toml
[pools.myapp]
sharding_key_regex = '/\* sharding_key: (\\d+) \*/'
shard_id_regex = '/\* shard_id: (\\d+) \*/'
regex_search_limit = 1000  # Only scan first 1000 chars
```
```

--------------------------------

### Primary Reads Configuration

Source: https://context7.com/postgresml/pgcat/llms.txt

Control whether the primary server participates in read query load balancing alongside replicas.

```APIDOC
## Primary Reads Configuration

Control whether the primary server participates in read query load balancing alongside replicas.

### Commands

- **`SET PRIMARY READS TO 'on';`**: Includes the primary server in read load balancing.
  *Example*: `SELECT * FROM products;` (May route to primary or replica)

- **`SET PRIMARY READS TO 'off';`**: Excludes the primary server from reads (routes only to replicas).
  *Example*: `SELECT * FROM products;` (Routes only to replicas)

- **`SET PRIMARY READS TO 'default';`**: Resets to the pool's default setting.

### Checking Current Setting

- **`SHOW PRIMARY READS;`**: Displays the current setting for primary reads.
```

--------------------------------

### Pool Management Commands

Source: https://context7.com/postgresml/pgcat/llms.txt

Control pool operations with pause, resume, ban, and reload commands for graceful maintenance and failover scenarios.

```APIDOC
## Pool Management Commands

Control pool operation with pause, resume, ban, and reload commands for graceful maintenance and failover scenarios.

### Commands

- **`PAUSE;`**: Pauses all pools (no new queries allowed).
- **`PAUSE myapp, app_user;`**: Pauses specific pools.
- **`RESUME;`**: Resumes all pools.
- **`RESUME myapp, app_user;`**: Resumes specific pools.
- **`BAN replica1.db.example.com 60;`**: Bans a server from receiving traffic for 60 seconds.
- **`UNBAN replica1.db.example.com;`**: Removes a ban from a server.
- **`RELOAD;`**: Reloads configuration without restarting.
- **`SHUTDOWN;`**: Initiates a graceful shutdown.

### Reloading Configuration via Signal

```bash
kill -SIGHUP $(pgrep pgcat)
```
```

--------------------------------

### Configure TLS Certificate and Key Paths in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Specifies the file paths for the TLS certificate and private key used for secure connections. These are essential for enabling TLS encryption.

```TOML
path: general.tls_certificate
default: <UNSET>
example: "server.cert"

path: general.tls_private_key
default: <UNSET>
example: "server.key"
```

--------------------------------

### SQL Comment-Based Shard Routing

Source: https://context7.com/postgresml/pgcat/llms.txt

Route queries to specific shards by embedding routing information directly within SQL comments. This method supports explicit shard IDs or sharding keys, allowing for efficient routing without separate commands and integrating well with ORMs.

```sql
-- Route by explicit shard ID in comment
/* shard_id: 2 */ SELECT * FROM orders WHERE customer_id = 789;

-- Route by sharding key in comment (computes shard via hash)
/* sharding_key: 12345 */ SELECT * FROM users WHERE id = 12345;

-- Works with any query type
/* shard_id: 1 */ INSERT INTO events (user_id, action) VALUES (100, 'login');
/* sharding_key: 100 */ UPDATE users SET last_login = NOW() WHERE id = 100;
```

--------------------------------

### Fetch PgCat Metrics Manually

Source: https://context7.com/postgresml/pgcat/llms.txt

Manually fetch Prometheus metrics exposed by PgCat via HTTP using `curl`. This is useful for quick checks and debugging the metrics endpoint.

```bash
# Fetch metrics manually
curl http://localhost:9930/metrics

# Example metrics output:
# pgcat_stats_total_query_count{pool="myapp",user="app_user",shard="0"} 1523456
# pgcat_stats_total_xact_count{pool="myapp",user="app_user",shard="0"} 892341
# pgcat_pools_cl_active{pool="myapp",user="app_user"} 15
# pgcat_pools_sv_idle{pool="myapp",user="app_user"} 8
# pgcat_pools_cl_waiting{pool="myapp",user="app_user"} 0
```

--------------------------------

### Configure User Connect Timeout

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Specifies how long a client waits to obtain a server connection before aborting, in milliseconds. If unset, it inherits the global `connect_timeout`.

```configuration
path: pools.<pool_name>.users.<user_index>.connect_timeout
default: <UNSET> # milliseconds
```

--------------------------------

### Configure Authentication Query in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Defines the SQL query used to obtain the hash for MD5 authentication. This query is sent to servers to authenticate clients, and it inherits settings from the pool configuration.

```TOML
path: general.auth_query
default: <UNSET>
example: "SELECT $1"
```

--------------------------------

### Configure Connect Timeout

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Specifies the maximum time (in milliseconds) a client will wait to establish a server connection before aborting. This is similar to PgBouncer's `query_wait_timeout` and can be overridden globally or at the pool level.

```configuration
path: pools.<pool_name>.connect_timeout
default: 3000
```

--------------------------------

### PgCat Admin Database Commands

Source: https://context7.com/postgresml/pgcat/llms.txt

Execute various administrative commands within the PgCat admin database to inspect pool statistics, view configured databases and servers, monitor client and server connections, and check system status.

```sql
-- Show available commands
SHOW HELP;

-- View all connection pools and their status
SHOW POOLS;
-- Returns: database, user, cl_active, cl_waiting, sv_active, sv_idle, sv_used, etc.

-- View configured databases and servers
SHOW DATABASES;
-- Returns: name, host, port, database, pool_size, current_connections, paused, disabled

-- View connected clients
SHOW CLIENTS;
-- Returns: client_id, database, user, state, transaction_count, query_count

-- View server connections
SHOW SERVERS;
-- Returns: server_id, database_name, user, state, transaction_count, bytes_sent/received

-- View aggregate statistics
SHOW STATS;
-- Returns: total_xact_count, total_query_count, total_received, total_sent, avg times

-- View current configuration
SHOW CONFIG;

-- View PgCat version
SHOW VERSION;

-- View banned servers
SHOW BANS;

-- View summary counts
SHOW LISTS;
```

--------------------------------

### Reload PgCat Configuration via SIGHUP Signal

Source: https://context7.com/postgresml/pgcat/llms.txt

Reload the PgCat configuration dynamically by sending a SIGHUP signal to the running PgCat process. This allows applying configuration changes without interrupting service.

```bash
# Reload config via signal
kill -SIGHUP $(pgrep pgcat)
```

--------------------------------

### Prometheus Metrics

Source: https://context7.com/postgresml/pgcat/llms.txt

PgCat exposes metrics via HTTP for Prometheus scraping. Configure your Prometheus to scrape the metrics endpoint for monitoring dashboards.

```APIDOC
## Prometheus Metrics

PgCat exposes metrics via HTTP for Prometheus scraping. Configure your Prometheus to scrape the metrics endpoint for monitoring dashboards.

### Prometheus Configuration (`prometheus.yml`)

```yaml
scrape_configs:
  - job_name: 'pgcat'
    static_configs:
      - targets: ['pgcat-host:9930']
    scrape_interval: 15s
```

### Fetching Metrics Manually

```bash
curl http://localhost:9930/metrics
```

### Example Metrics Output

```
# pgcat_stats_total_query_count{pool="myapp",user="app_user",shard="0"} 1523456
# pgcat_stats_total_xact_count{pool="myapp",user="app_user",shard="0"} 892341
# pgcat_pools_cl_active{pool="myapp",user="app_user"} 15
# pgcat_pools_sv_idle{pool="myapp",user="app_user"} 8
# pgcat_pools_cl_waiting{pool="myapp",user="app_user"} 0
```
```

--------------------------------

### Configure Primary Read Load Balancing

Source: https://context7.com/postgresml/pgcat/llms.txt

Control whether the primary PostgreSQL server participates in read query load balancing alongside replicas using the `SET PRIMARY READS` command. This allows directing read traffic to replicas only or including the primary.

```sql
-- Include primary in read load balancing
SET PRIMARY READS TO 'on';
SELECT * FROM products;  -- May route to primary or replica

-- Exclude primary from reads (replicas only)
SET PRIMARY READS TO 'off';
SELECT * FROM products;  -- Routes only to replicas

-- Reset to pool default
SET PRIMARY READS TO 'default';

-- Check current setting
SHOW PRIMARY READS;
```

--------------------------------

### Connect to PgCat Admin Database

Source: https://context7.com/postgresml/pgcat/llms.txt

Connect to the PgCat admin database using `psql` to access administrative commands for monitoring and managing connection pools. Requires setting the `PGPASSWORD` environment variable.

```sql
-- Connect to admin database
PGPASSWORD=admin_pass psql -h 127.0.0.1 -p 6432 -U admin_user -d pgbouncer
```

--------------------------------

### Sharding Information in SQL Comments

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Illustrates how to embed sharding information directly within SQL comments. This method can reduce latency by bypassing the pooler for sharding directives, compatible with ORMs like ActiveRecord and SQLAlchemy.

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

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

--------------------------------

### Control Server Role for Query Routing

Source: https://github.com/postgresml/pgcat/blob/main/README.md

Custom SQL syntax to explicitly control which server role (primary, replica, auto, any, default) subsequent queries should be directed to for the duration of a transaction or until changed. This is useful for directing specific operations to primary or replica databases.

```sql
-- To talk to the primary for the duration of the next transaction:
SET SERVER ROLE TO 'primary';

-- To talk to the replica for the duration of the next transaction:
SET SERVER ROLE TO 'replica';

-- Let the query parser decide
SET SERVER ROLE TO 'auto';

-- Pick any server at random
SET SERVER ROLE TO 'any';

-- Reset to default configured settings
SET SERVER ROLE TO 'default';
```

--------------------------------

### Configure Automatic Sharding Key

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Enables automatic parsing of sharding keys directly from queries. This allows Pgcat to route queries to the correct shard without explicit sharding commands.

```configuration
path: pools.<pool_name>.automatic_sharding_key
default: <UNSET>
example: "data.id"
```

--------------------------------

### Configure Server Ban Time and Logging in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Specifies the duration for banning unhealthy servers and enables logging for client connections and disconnections. The ban time is in seconds.

```TOML
path: general.ban_time
default: 60 # seconds

path: general.log_client_connections
default: false

path: general.log_client_disconnections
default: false
```

--------------------------------

### Configure Authentication Query User

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Defines the username used when executing the `auth_query`. This user connects to the configured database to retrieve the MD5 hash. It's inherited by all pools but can be redefined.

```configuration
path: pools.<pool_name>.auth_query_user
default: <UNSET>
example: "sharding_user"
```

--------------------------------

### Configure Administrative Database Access in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Sets the username and password for accessing the virtual administrative database. This database allows running administrative commands like showing pools and databases.

```TOML
path: general.admin_username
default: "admin_user"

path: general.admin_password
default: "admin_pass"
```

--------------------------------

### PgCat Pool Management Commands

Source: https://context7.com/postgresml/pgcat/llms.txt

Manage connection pool operations using commands like PAUSE, RESUME, BAN, UNBAN, RELOAD, and SHUTDOWN for graceful maintenance, failover scenarios, and configuration updates without restarting the service.

```sql
-- Pause all pools (no new queries allowed)
PAUSE;

-- Pause specific pool
PAUSE myapp, app_user;

-- Resume all pools
RESUME;

-- Resume specific pool
RESUME myapp, app_user;

-- Ban a server from receiving traffic (60 seconds)
BAN replica1.db.example.com 60;

-- Remove ban from server
UNBAN replica1.db.example.com;

-- Reload configuration without restart
RELOAD;

-- Graceful shutdown
SHUTDOWN;
```

--------------------------------

### Enable TLS for PgCat Connections

Source: https://context7.com/postgresml/pgcat/llms.txt

Configure PgCat to enable TLS for secure client connections and server connections to PostgreSQL backends. This enhances security by encrypting data in transit.

```toml
# TLS configuration would typically go here in pgcat.toml
# Example structure (specific keys may vary based on PgCat version):
# [tls]
# cert_file = "/path/to/your/cert.pem"
# key_file = "/path/to/your/key.pem"
# ca_file = "/path/to/your/ca.pem"
```

--------------------------------

### Configure Connection Timeouts in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Sets various timeout durations for client and server connections. This includes connect timeout, idle timeout, server lifetime, and idle client timeouts, all specified in milliseconds.

```TOML
path: general.connect_timeout
default: 1000 # milliseconds

path: general.idle_timeout
default: 30000 # milliseconds

path: general.server_lifetime
default: 86400000 # 24 hours

path: general.idle_client_in_transaction_timeout
default: 0 # milliseconds
```

--------------------------------

### Configure Statement Timeout

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Sets the maximum duration for a query to execute. A value of 0 disables this feature. It can protect against unresponsive databases.

```configuration
path: pools.<pool_name>.users.<user_index>.statement_timeout
default: 0
```

--------------------------------

### Configure Prometheus Exporter in PgCat

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Enables and configures the Prometheus metrics exporter. This includes a boolean flag to enable the exporter and a specific port for it to listen on.

```TOML
path: general.enable_prometheus_exporter
default: true

path: general.prometheus_exporter_port
default: 9930
```

--------------------------------

### Configure Sharding Key Regex

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Defines a regular expression to extract sharding keys from SQL comments. If unset, this feature is disabled, preventing sharding commands from being passed as comments.

```configuration
path: pools.<pool_name>.sharding_key_regex
default: <UNSET>
example: '/\* sharding_key: (\d+) \*/'
```

--------------------------------

### Configure Server Username

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Specifies the PostgreSQL username used to connect to the server. If set, this overrides the `username` parameter for server connections.

```configuration
path: pools.<pool_name>.users.<user_index>.server_username
default: <UNSET>
example: "another_user"
```

--------------------------------

### Configure Password for User Authentication

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Sets the PostgreSQL password used for authenticating users and connecting to the server when `server_password` is not specified. Defaults to 'sharding_user'.

```configuration
path: pools.<pool_name>.users.<user_index>.password
default: "sharding_user"
```

--------------------------------

### Configure Username for User Authentication

Source: https://github.com/postgresml/pgcat/blob/main/CONFIG.md

Defines the PostgreSQL username used for authenticating users and connecting to the server when `server_username` is not specified. Defaults to 'sharding_user'.

```configuration
path: pools.<pool_name>.users.<user_index>.username
default: "sharding_user"
```