Service Access
Split read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably.
Service is an abstraction: it represents the form in which database clusters expose their capabilities externally, encapsulating underlying cluster details.
Services are crucial for stable access in production environments, showing their value during automatic failover in high availability clusters. Personal users typically don’t need to worry about this concept.
Personal Users
The concept of “service” is for production environments. Personal users with single-node clusters can skip the complexity and directly use instance names or IP addresses to access the database.
For example, Pigsty’s default single-node pg-meta.meta database can be connected directly using three different users:
Service Overview
In real-world production environments, we use primary-replica database clusters based on replication. Within a cluster, one and only one instance serves as the leader (primary) that can accept writes. Other instances (replicas) continuously fetch change logs from the cluster leader to stay synchronized. Replicas can also handle read-only requests, significantly offloading the primary in read-heavy, write-light scenarios. Therefore, distinguishing write requests from read-only requests is a common practice.
Additionally, for production environments with high-frequency, short-lived connections, we pool requests through connection pool middleware (Pgbouncer) to reduce connection and backend process creation overhead. However, for scenarios like ETL and change execution, we need to bypass the connection pool and directly access the database. Meanwhile, high-availability clusters may undergo failover during failures, causing cluster leadership changes. Therefore, high-availability database solutions require write traffic to automatically adapt to cluster leadership changes. These varying access needs (read-write separation, pooled vs. direct connections, failover auto-adaptation) ultimately lead to the abstraction of the Service concept.
Typically, database clusters must provide this most basic service:
- Read-write service (primary): Can read from 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 served by the primary.
- Read-only service (replica): Read data: Can be served by replicas; falls back to primary when no replicas are available
Additionally, depending on specific business scenarios, there may be other services, such as:
- Default direct service (default): Allows (admin) users to bypass the connection pool and directly access the database
- Offline replica service (offline): Dedicated replica not serving online read traffic, used for ETL and analytical queries
- Sync replica service (standby): Read-only service with no replication delay, handled by synchronous standby/primary for read queries
- Delayed replica service (delayed): Access data from the same cluster as it was some time ago, handled by delayed replicas
Access Services
Pigsty’s service delivery boundary stops at the cluster’s HAProxy. Users can access these load balancers through various means.
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 service in different ways.
Host
| Type | Sample | Description |
|---|---|---|
| Cluster Domain Name | pg-test |
Resolved by dnsmasq on INFRA nodes; with pg_dns_target: auto, points to the VIP when enabled, otherwise to the primary IP |
| Cluster VIP Address | 10.10.10.3 |
When pg_vip_enabled is enabled, an L2 VIP managed by vip-manager and bound to the primary node |
| Instance Hostname | pg-test-1 |
Access via any instance hostname (resolved by dnsmasq @ infra nodes) |
| Instance IP Address | 10.10.10.11 |
Access any instance’s IP address |
Port
Pigsty uses different ports to distinguish pg services
| Port | Service | Type | Description |
|---|---|---|---|
| 5432 | postgres | Database | Direct access to postgres server |
| 6432 | pgbouncer | Middleware | Access postgres through connection pool middleware |
| 5433 | primary | Service | Access primary pgbouncer (or postgres) |
| 5434 | replica | Service | Access replica pgbouncer (or postgres) |
| 5436 | default | Service | Access primary postgres |
| 5438 | offline | Service | Access offline postgres |
Combinations