How to Configure ProxySQL for PostgreSQL for the first time

ProxySQL supports the PostgreSQL protocol, enabling you to leverage its powerful features like connection pooling, query routing, and monitoring for your PostgreSQL deployments.

This guide provides a complete step-by-step walkthrough for configuring ProxySQL to work with PostgreSQL from scratch.

Prerequisites

• ProxySQL installed and running. See Step-by-Step Instructions.
• A PostgreSQL client installed on your system.
• Your PostgreSQL servers are running and accessible from the ProxySQL server.

Overview

ProxySQL uses a multi-layer configuration system. All configuration is performed through the Admin interface and follows this workflow:

1. Make changes in the in-memory configuration tables.
2. Load to runtime to apply changes to the running proxy.
3. Save to disk to persist changes across restarts.

For PostgreSQL, ProxySQL provides dedicated tables and variables prefixed with pgsql_ (e.g., pgsql_servers, pgsql_users, pgsql_query_rules). The corresponding admin commands use LOAD PGSQL ... TO RUNTIME and SAVE PGSQL ... TO DISK.

The configuration process involves:

1. Adding PostgreSQL Backends: Define your PostgreSQL servers in the pgsql_servers table.
2. Configuring Monitoring: Set up monitoring credentials in PostgreSQL and configure them within ProxySQL so it can track the health of your backend servers.
3. Creating PostgreSQL Users: Add users to ProxySQL’s pgsql_users table to manage access control to your PostgreSQL databases.
4. Defining Query Rules (Optional): Implement advanced routing logic using pgsql_query_rules to direct specific queries to particular PostgreSQL instances.

Connecting to the Admin Interface

ProxySQL exposes a PostgreSQL-compatible admin interface on port 6132. Connect using psql:

psql -h 127.0.0.1 -p6132 -U admin -d admin

All configuration commands below are run from this admin interface.

Step-by-Step Configuration

Let’s assume we have three PostgreSQL backend servers: 10.0.0.1, 10.0.0.2, and 10.0.0.3, all running on the default PostgreSQL port (5432).

Adding PostgreSQL Backends

Add your PostgreSQL servers to the pgsql_servers table, specifying a hostgroup, hostname, and port:

INSERT INTO pgsql_servers (hostgroup_id, hostname, port) VALUES (1, '10.0.0.1', 5432);
INSERT INTO pgsql_servers (hostgroup_id, hostname, port) VALUES (1, '10.0.0.2', 5432);
INSERT INTO pgsql_servers (hostgroup_id, hostname, port) VALUES (1, '10.0.0.3', 5432);
SELECT * FROM pgsql_servers;
LOAD PGSQL SERVERS TO RUNTIME;
SAVE PGSQL SERVERS TO DISK;

Configuring Monitoring

Create a dedicated monitoring user in your primary PostgreSQL server:

CREATE USER monitor WITH PASSWORD 'monitor';
GRANT CONNECT ON DATABASE postgres TO monitor;

Then configure the monitoring credentials and reporting intervals in ProxySQL Admin Interface:

UPDATE global_variables SET variable_value = 'monitor'
  WHERE variable_name = 'pgsql-monitor_username';
UPDATE global_variables SET variable_value = 'monitor'
  WHERE variable_name = 'pgsql-monitor_password';
UPDATE global_variables SET variable_value = '2000'
  WHERE variable_name IN (
    'pgsql-monitor_connect_interval',
    'pgsql-monitor_ping_interval'
  );
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;

You can monitor the backend health of your databases through the monitor schema:

SELECT * FROM monitor.pgsql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT * FROM monitor.pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;

Adding PostgreSQL Users

Create any application users in your primary PostgreSQL server:

CREATE USER appuser WITH PASSWORD 'appuser';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO appuser;

Then add the user to ProxySQL’s pgsql_users table. The default_hostgroup controls where traffic from this user is routed by default:

INSERT INTO pgsql_users (username, password, default_hostgroup) VALUES ('appuser', 'appuser', 1);
SELECT * FROM pgsql_users;
LOAD PGSQL USERS TO RUNTIME;
SAVE PGSQL USERS TO DISK;

Connect and Test

You can now connect to your PostgreSQL servers through ProxySQL on port the default port 6133:

psql -h 127.0.0.1 -p6133 -U appuser -d mydatabase

Query Rules

You can route queries to specific hostgroups using regular expressions that match against the query digest or full query text. To redirect a specific query pattern to a different hostgroup:

INSERT INTO pgsql_query_rules
  (rule_id, active, username, match_digest, destination_hostgroup, apply)
  VALUES (10, 1, 'appuser', '^SELECT .* FROM read_table', 2, 1);
SELECT * FROM pgsql_query_rules;
LOAD PGSQL QUERY RULES TO RUNTIME;
SAVE PGSQL QUERY RULES TO DISK;

Rules are evaluated in ascending rule_id order. Only active=1 rules are considered. Setting apply=1 stops further rule evaluation for that query.

Query Statistics

ProxySQL collects statistics in the stats schema. Key tables for PostgreSQL:

Query traffic by hostgroup:

SELECT hostgroup hg, SUM(sum_time), SUM(count_star)
  FROM stats_pgsql_query_digest
  GROUP BY hostgroup;

Benefits

• Connection Pooling: Efficiently manages connections, reducing overhead on your PostgreSQL servers.
• Load Balancing: Distributes read traffic across multiple PostgreSQL instances.
• High Availability: Provides redundancy and failover capabilities.
• Query Routing: Allows for advanced traffic management based on various criteria.
• Monitoring and Statistics: Offers real-time insights into the performance and health of your PostgreSQL infrastructure.

Best Practices

• Use a dedicated monitoring user — avoid using application users for monitoring.
• Regularly back up your ProxySQL configuration with SAVE PGSQL ... TO DISK.
• Monitor ProxySQL’s performance by tracking metrics like connection usage, query latency, and error rates.
• Start with a simple configuration and add features gradually as needed.
• Test thoroughly in a staging environment before deploying to production.
• Use ProxySQL Clustering to avoid single points of failure.

Next Steps

• PostgreSQL Monitoring
• PostgreSQL Users Management
• PostgreSQL Variables