Service / Access

Separate read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably.

A service exposes database cluster capabilities to clients. It hides the details of the underlying cluster.

Services provide stable access in production, including after automatic failover in high-availability clusters. Single-node users usually do not need services.

Single-Node Users

Services are intended for production environments. Personal users and single-node clusters can connect directly through an instance name or IP address.

For example, Pigsty’s default single-node pg-meta.meta database can be connected directly using three different users:

psql postgres://dbuser_dba:[email protected]/meta     # Connect directly with DBA superuser
psql postgres://dbuser_meta:[email protected]/meta   # Connect with default business admin user
psql postgres://dbuser_view:DBUser.Viewer@pg-meta/meta     # Connect with default read-only user via instance domain name

Service Overview

Production environments use primary-replica clusters with replication. Exactly one instance, the primary, leads the cluster and accepts writes. Replicas continuously fetch change logs from the primary to keep their data consistent. Replicas also handle read-only requests, which substantially reduces primary load in workloads with many reads. Thus, separating write requests from read-only requests is common.

For frequent short connections, Pgbouncer pools requests to reduce the cost of connection and backend process creation. Operations such as ETL and database changes need direct connections that bypass the pool. Failures in high-availability clusters can trigger failover and change the primary. Write traffic must follow the new primary automatically. Services represent these access requirements: separate reads and writes, pooled or direct connections, and automatic routing after failover.

Typically, database clusters must provide this most basic service:

• Read-Write Service (primary): Can read and write to the database

For production database clusters, at least these two services should be provided:

• Read-Write Service (primary): Write data: can only be carried by the primary.
• Read-Only Service (replica): Read data: can be carried by replicas, or by the primary if there are no replicas

In addition, depending on specific business scenarios, there may be other services, such as:

• Default Direct Service (default): Allows (admin) users to access the database directly, bypassing the connection pool
• Offline Replica Service (offline): Dedicated replicas that do not handle online read-only traffic, used for ETL and analytical queries
• Standby Replica Service (standby): Read-only service without replication lag, handled by sync standby/primary for read-only queries
• Delayed Replica Service (delayed): Access old data from the same cluster at a previous point in time, handled by delayed replica

Default Services

By default, Pigsty provides four services for each PostgreSQL cluster:

Taking the default pg-meta cluster as an example, it provides four default services:

psql postgres://dbuser_meta:DBUser.Meta@pg-meta:5433/meta   # pg-meta-primary : production read-write via primary pgbouncer(6432)
psql postgres://dbuser_meta:DBUser.Meta@pg-meta:5434/meta   # pg-meta-replica : production read-only via replica pgbouncer(6432)
psql postgres://dbuser_dba:DBUser.DBA@pg-meta:5436/meta     # pg-meta-default : direct connection via primary postgres(5432)
psql postgres://dbuser_stats:DBUser.Stats@pg-meta:5438/meta # pg-meta-offline : direct connection via offline postgres(5432)

You can see how these four services work from the sample cluster architecture diagram:

pg_dns_target controls the DNS target of pg-meta. The default, auto, selects the L2 VIP when enabled. Otherwise, it selects the IP address of the inventory primary. VIP is disabled by default. See Accessing Services.

Service Implementation

Pigsty implements services with HAProxy on nodes. Different ports identify different services.

Pigsty enables HAProxy on each managed node, including database nodes. Database nodes have primary or replica roles, but each node exposes the same services. With the correct service port, you can access the primary read-write service through a replica node. Thus, access to any cluster instance gives access to all cluster services.

This design is similar to Kubernetes NodePort services. Each Pigsty service has two core elements:

1. Access endpoints exposed through NodePort (port number, where to access?)
2. Target instances selected through Selectors (instance list, who carries the load?)

Pigsty provides services up to the cluster HAProxy endpoint. Clients can reach these load balancers in several ways. See Accessing Services.

Declare all services in configuration files. For example, pg_default_services defines the default PostgreSQL services:

pg_default_services:
- { name: primary ,port: 5433 ,dest: default  ,check: /primary   ,selector: "[]" }
- { name: replica ,port: 5434 ,dest: default  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
- { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }
- { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"}

Define additional services in pg_services. Both pg_default_services and pg_services are arrays of service definition objects.

Defining Services

Pigsty allows you to define your own services:

• pg_default_services: Services uniformly exposed by all PostgreSQL clusters, four by default.
• pg_services: Additional PostgreSQL services, can be defined at global or cluster level as needed.
• haproxy_services: Directly customize HAProxy service content, can be used for accessing other components

For PostgreSQL clusters, you usually need only the first two parameters. Each service generates a configuration file on every related HAProxy instance: /etc/haproxy/conf.d/<pg_cluster>-<service>.cfg. The following custom standby service provides read-only access without replication lag. Add its definition to pg_services:

- name: standby                   # Required, service name, final svc name uses `pg_cluster` as prefix, e.g.: pg-meta-standby
  port: 5435                      # Required, exposed service port (as kubernetes service node port mode)
  ip: "*"                         # Optional, IP address the service binds to, all IP addresses by default
  selector: "[]"                  # Required, service member selector, uses JMESPath to filter configuration manifest
  backup: "[? pg_role == `primary`]"  # Optional, service member selector (backup), instances selected here only carry the service when all default selector instances are down
  dest: default                   # Optional, target port, default|postgres|pgbouncer|<port_number>, defaults to 'default', meaning pg_default_service_dest decides
  check: /sync                    # Optional, health check URL path, defaults to /, here uses Patroni API: /sync, only sync standby and primary return 200 healthy status code
  maxconn: 5000                   # Optional, maximum number of allowed frontend connections, defaults to 5000
  balance: roundrobin             # Optional, haproxy load balancing algorithm (defaults to roundrobin, other options: leastconn)
  options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

For the three-node pg-test example cluster, Pigsty generates /etc/haproxy/conf.d/pg-test-standby.cfg from this definition:

#---------------------------------------------------------------------
# service: pg-test-standby @ 10.10.10.11:5435
#---------------------------------------------------------------------
# service instances 10.10.10.11, 10.10.10.13, 10.10.10.12
# service backups   10.10.10.11
listen pg-test-standby
    bind *:5435            # <--- Binds port 5435 on all IP addresses
    mode tcp               # <--- Load balancer works on TCP protocol
    maxconn 5000           # <--- Maximum connections 5000, can be increased as needed
    balance roundrobin     # <--- Load balancing algorithm is rr round-robin, can also use leastconn
    option httpchk         # <--- Enable HTTP health check
    option http-keep-alive # <--- Keep HTTP connection
    http-check send meth OPTIONS uri /sync   # <---- Here uses /sync, Patroni health check API, only sync standby and primary return 200 healthy status code
    http-check expect status 200             # <---- Health check return code 200 means normal
    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    # servers: selector "[]" includes all three pg-test instances as pg-test-standby backends; /sync admits only the primary and synchronous standby.
    server pg-test-1 10.10.10.11:6432 check port 8008 weight 100 backup  # <----- Only primary satisfies condition pg_role == `primary`, selected by backup selector
    server pg-test-3 10.10.10.13:6432 check port 8008 weight 100         #        Therefore serves as service fallback instance: normally doesn't handle requests, only handles read-only requests when all other replicas fail, thus maximally avoiding read-write service being affected by read-only service
    server pg-test-2 10.10.10.12:6432 check port 8008 weight 100         #

The selector: "[]" expression includes all three pg-test instances in the pg-test-standby backend list. However, the Patroni /sync health check returns HTTP 200 only for the primary and synchronous standby. Only those instances can handle requests. The primary also matches pg_role == primary, so the backup selector marks it as a backup server. It handles requests only when no synchronous standby can do so.

Primary Service

The Primary service provides cluster read-write access on port 5433. It is a critical production service. Its definition is:

- { name: primary ,port: 5433 ,dest: default  ,check: /primary   ,selector: "[]" }

• selector: "[]" includes all cluster members in the Primary service.
• Only the primary passes check: /primary and receives traffic.
• dest: default makes pg_default_service_dest control the destination.
• Pigsty replaces the dest value default with pg_default_service_dest, whose default is pgbouncer.
• Thus, the default destination is the primary connection pool on pgbouncer_port, normally 6432.

Set pg_default_service_dest to postgres to bypass the connection pool. The Primary service then connects directly to pg_port, normally 5432. Use this setting when you do not need pooling.

listen pg-test-primary
    bind *:5433         # <--- primary service defaults to port 5433
    mode tcp
    maxconn 5000
    balance roundrobin
    option httpchk
    option http-keep-alive
    http-check send meth OPTIONS uri /primary # <--- primary service defaults to Patroni RestAPI /primary health check
    http-check expect status 200
    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    # servers
    server pg-test-1 10.10.10.11:6432 check port 8008 weight 100
    server pg-test-3 10.10.10.13:6432 check port 8008 weight 100
    server pg-test-2 10.10.10.12:6432 check port 8008 weight 100

Patroni high availability ensures that no more than one instance passes the /primary health check at a time. Thus, the Primary service always routes traffic to the primary.

The Primary service also protects traffic routing during a split-brain condition. For example, kill -9 can stop primary Patroni without a watchdog. HAProxy sends traffic only when Patroni is alive and reports primary status. This protection is unavailable with a direct database connection.

Replica Service

The Replica service provides read-only access on port 5434. It is the next most important production service after Primary. Its definition is:

- { name: replica ,port: 5434 ,dest: default  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }

• selector: "[]" includes all cluster members in the Replica service.
• All instances can pass check: /read-only and receive traffic.
• The backup selector [? pg_role == 'primary' || pg_role == 'offline' ] marks the primary and offline replicas as backups.
• The primary or offline replicas receive traffic only when all normal replicas are down.
• dest: default makes pg_default_service_dest control the destination.
• Pigsty replaces the dest value default with pg_default_service_dest, whose default is pgbouncer, as for the Primary service.
• Thus, the default destination is the replica connection pool on pgbouncer_port, normally 6432.

listen pg-test-replica
    bind *:5434
    mode tcp
    maxconn 5000
    balance roundrobin
    option httpchk
    option http-keep-alive
    http-check send meth OPTIONS uri /read-only
    http-check expect status 200
    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    # servers
    server pg-test-1 10.10.10.11:6432 check port 8008 weight 100 backup
    server pg-test-3 10.10.10.13:6432 check port 8008 weight 100
    server pg-test-2 10.10.10.12:6432 check port 8008 weight 100

The Replica service first sends read-only requests to available dedicated replicas. It sends them to the primary only when all replicas are down. For a two-node primary-replica cluster, this means it uses the replica while available and the primary when the replica fails.

The Replica service uses dedicated Offline instances only when all dedicated read-only instances are down. This separates fast online queries from slow offline queries to reduce interference.

Default Service

The Default service provides services on port 5436. It is a variant of the Primary service.

The Default service always bypasses the connection pool and connects directly to primary PostgreSQL. Use it for administration connections, ETL writes, and change data capture (CDC).

- { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }

If pg_default_service_dest is postgres, Default and Primary have the same behavior. Only their names and ports differ. In this case, you can remove Default from the default services.

listen pg-test-default
    bind *:5436         # <--- Except for listening port/target port and service name, other configurations are exactly the same as primary service
    mode tcp
    maxconn 5000
    balance roundrobin
    option httpchk
    option http-keep-alive
    http-check send meth OPTIONS uri /primary
    http-check expect status 200
    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    # servers
    server pg-test-1 10.10.10.11:5432 check port 8008 weight 100
    server pg-test-3 10.10.10.13:5432 check port 8008 weight 100
    server pg-test-2 10.10.10.12:5432 check port 8008 weight 100

Offline Service

The Offline service listens on port 5438 and connects directly to PostgreSQL without the pool. Use it for slow queries, analytical queries, ETL reads, and personal interactive queries:

- { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"}

Offline routes traffic directly to dedicated offline replicas. It can also use normal read-only instances with the pg_offline_query flag.

• The selector includes replicas with pg_role = offline and normal read-only instances with pg_offline_query = true.
• Dedicated offline replicas do not handle Replica service requests by default. This separates fast and slow requests. Flagged normal replicas do handle those requests.
• The backup selector includes normal replicas without the offline flag. They handle Offline traffic when dedicated or flagged offline instances fail.
• /replica returns HTTP 200 only for replicas. It rejects the primary, even when the primary is the only remaining cluster instance.
• Neither selector includes the primary. Thus, Offline traffic never reaches or affects the primary.

listen pg-test-offline
    bind *:5438
    mode tcp
    maxconn 5000
    balance roundrobin
    option httpchk
    option http-keep-alive
    http-check send meth OPTIONS uri /replica
    http-check expect status 200
    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    # servers
    server pg-test-3 10.10.10.13:5432 check port 8008 weight 100
    server pg-test-2 10.10.10.12:5432 check port 8008 weight 100 backup

Offline provides restricted read-only access for personal interactive queries, slow analytical or ETL queries, and long transactions.

The Offline service needs special handling after a switchover. The HAProxy /replica health check automatically rejects the new primary. However, selector uses static pg_role and pg_offline_query inventory labels. In a two-node primary-replica cluster, only the replica may serve Offline queries. A switchover can then leave no eligible backend temporarily. Reloading an unchanged inventory does not add the old primary to the backend list.

First update the inventory labels or pg_offline_query for the new plan, then reload services. Alternatively, switch the primary back.

For a simple business model, you can remove Default and Offline. Use Primary and Replica to connect directly to the database.

Reload Services

Reload services after changes to cluster membership, service definitions, static selector labels, or relative weights. Patroni health checks handle normal Primary and Replica switchovers automatically. These switchovers need no separate reload.

bin/pgsql-svc <cls> [ip...]         # Reload services for lb cluster or lb instance
# ./pgsql.yml -t pg_service         # Actual ansible task for reloading services

Accessing Services

Pigsty provides services up to the cluster HAProxy endpoint. Clients can reach these load balancers in several ways.

The typical approach is to use DNS or VIP access, binding them to all or any number of load balancers in the cluster.

You can use different host & port combinations, which provide PostgreSQL services in different ways.

Host

Port

Pigsty uses different ports to distinguish pg services

Combinations

# Access via cluster domain name (the examples below assume L2 VIP is enabled; without it, auto targets the inventory primary IP)
postgres://test@pg-test:5432/test # DNS -> L2 VIP -> Primary direct connection
postgres://test@pg-test:6432/test # DNS -> L2 VIP -> Primary connection pool -> Primary
postgres://test@pg-test:5433/test # DNS -> L2 VIP -> HAProxy -> Primary connection pool -> Primary
postgres://test@pg-test:5434/test # DNS -> L2 VIP -> HAProxy -> Replica connection pool -> Replica
postgres://dbuser_dba@pg-test:5436/test # DNS -> L2 VIP -> HAProxy -> Primary direct connection (for admin)
postgres://dbuser_stats@pg-test:5438/test # DNS -> L2 VIP -> HAProxy -> Offline direct connection (for ETL/personal queries)

# Direct access via cluster VIP
postgres://[email protected]:5432/test # L2 VIP -> Primary direct access
postgres://[email protected]:6432/test # L2 VIP -> Primary connection pool -> Primary
postgres://[email protected]:5433/test # L2 VIP -> HAProxy -> Primary connection pool -> Primary
postgres://[email protected]:5434/test # L2 VIP -> HAProxy -> Replica connection pool -> Replica
postgres://[email protected]:5436/test # L2 VIP -> HAProxy -> Primary direct connection (for admin)
postgres://[email protected]:5438/test # L2 VIP -> HAProxy -> Offline direct connection (for ETL/personal queries)

# Specify any cluster instance name directly
postgres://test@pg-test-1:5432/test # DNS -> Database instance direct connection (single instance access)
postgres://test@pg-test-1:6432/test # DNS -> Connection pool -> Database
postgres://test@pg-test-1:5433/test # DNS -> HAProxy -> Connection pool -> Database read/write
postgres://test@pg-test-1:5434/test # DNS -> HAProxy -> Connection pool -> Database read-only
postgres://dbuser_dba@pg-test-1:5436/test # DNS -> HAProxy -> Database direct connection
postgres://dbuser_stats@pg-test-1:5438/test # DNS -> HAProxy -> Database offline read/write

# Specify any cluster instance IP directly
postgres://[email protected]:5432/test # Database instance direct connection (direct instance specification, no automatic traffic distribution)
postgres://[email protected]:6432/test # Connection pool -> Database
postgres://[email protected]:5433/test # HAProxy -> Connection pool -> Database read/write
postgres://[email protected]:5434/test # HAProxy -> Connection pool -> Database read-only
postgres://[email protected]:5436/test # HAProxy -> Database direct connection
postgres://[email protected]:5438/test # HAProxy -> Database offline read/write

# Smart client: automatic read-write separation
postgres://[email protected]:6432,10.10.10.12:6432,10.10.10.13:6432/test?target_session_attrs=primary
postgres://[email protected]:6432,10.10.10.12:6432,10.10.10.13:6432/test?target_session_attrs=prefer-standby

Overriding Services

You can override service defaults in several ways. A common requirement is for Primary and Replica to bypass Pgbouncer and connect directly to PostgreSQL.

Set pg_default_service_dest to postgres. Services with svc.dest='default' then use postgres instead of the default pgbouncer destination.

If Primary already connects to PostgreSQL, Default is redundant. You can remove it.

If you do not need to distinguish personal interactive queries from slow analytical or ETL queries, you can remove Offline. Remove it from pg_default_services.

If replicas do not need to handle online read-only traffic, you can remove Replica from the default list.

Delegating Services

Pigsty exposes PostgreSQL services through HAProxy on nodes. All HAProxy instances in a cluster use the same service definitions.

You can instead delegate PostgreSQL services to a specific node group, such as a dedicated HAProxy load balancer cluster.

Override pg_default_services and set pg_service_provider to the proxy group name.

This example exposes the PostgreSQL Primary service on port 10013 through the proxy HAProxy node group.

pg_service_provider: proxy       # Use load balancer from `proxy` group on port 10013
pg_default_services:  [{ name: primary ,port: 10013 ,dest: postgres  ,check: /primary   ,selector: "[]" }]

Use a unique port for each delegated service in the proxy cluster.

An example of using a dedicated load balancer cluster is provided in the 20-node production environment simulation sandbox: conf/ha/simu.yml