PostgreSQL Access

Access is about traffic routing, how to connect to the right service

Access is designed to address high concurrency, HA, and high performance in production envs. Individual users can choose to ignore the access method and access the database directly via IP.

Access default database via postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta (replace IP & password)

In Pigsty’s default config, a fully functional LB(HAProxy) is deployed on each database instance/node, so that any instance can serve as an access point for the entire cluster. The delivery boundary of a Pigsty cluster stops at the access layer LB(HAProxy); it is up to you to decide Access Policy: how to distribute business traffic to one, multiple, or all load balancing instances.

Pigsty provides a rich set of access methods. The Pigsty sandbox uses an L2 VIP bound to the primary and a domain name attached to that VIP as a sample. The application accesses the load-balancing instance on the primary through the L2 VIP via the domain name. When this node becomes unavailable, the VIP is transferred with the primary, and the traffic is carried by the LB on the new primary, as shown in the following figure.

Another classic policy is to use DNS polling to resolve DNS domain names to all instances, and several common access patterns will be given in this article.

User Interface

The interface that Pigsty ultimately delivers to the user is a database connection string.

The formal difference between the different access methods is the difference between host and port in the connection string.

Port

Pigsty uses different ports to distinguish between database services, which provide Postgres equivalent services, as follows

Host

Depending on the contents of the host section and the available port, multiple connection strings can be combined.

Available Combinations

The following connection strings are available for the test database on the cluster pg-test in a singleton sandbox.

Available Combinations

1. # Access via cluster domain
2. postgres://test@pg-test:5432/test # DNS -> L2 VIP -> primary direct connection
3. postgres://test@pg-test:6432/test # DNS -> L2 VIP -> primary connection pool -> primary
4. postgres://test@pg-test:5433/test # DNS -> L2 VIP -> HAProxy -> Primary Connection Pool -> Primary
5. postgres://test@pg-test:5434/test # DNS -> L2 VIP -> HAProxy -> Replica Connection Pool -> Replica
6. postgres://dbuser_dba@pg-test:5436/test # DNS -> L2 VIP -> HAProxy -> Primary direct connection (for Admin)
7. postgres://dbuser_stats@pg-test:5438/test # DNS -> L2 VIP -> HAProxy -> offline direct connection (for ETL/personal queries)

9. # Direct access via cluster VIP
10. postgres://test@10.10.10.3:5432/test # L2 VIP -> Primary direct access
11. postgres://test@10.10.10.3:6432/test # L2 VIP -> Primary Connection Pool -> Primary
12. postgres://test@10.10.10.3:5433/test # L2 VIP -> HAProxy -> Primary Connection Pool -> Primary
13. postgres://test@10.10.10.3:5434/test # L2 VIP -> HAProxy -> Repilca Connection Pool -> Replica
14. postgres://dbuser_dba@10.10.10.3:5436/test # L2 VIP -> HAProxy -> Primary direct connection (for Admin)
15. postgres://dbuser_stats@10.10.10.3::5438/test # L2 VIP -> HAProxy -> offline direct connect (for ETL/personal queries)

17. # Specify any cluster instance name directly
18. postgres://test@pg-test-1:5432/test # DNS -> Database Instance Direct Connect (singleton access)
19. postgres://test@pg-test-1:6432/test # DNS -> connection pool -> database
20. postgres://test@pg-test-1:5433/test # DNS -> HAProxy -> connection pool -> database read/write
21. postgres://test@pg-test-1:5434/test # DNS -> HAProxy -> connection pool -> database read-only
22. postgres://dbuser_dba@pg-test-1:5436/test # DNS -> HAProxy -> database direct connect
23. postgres://dbuser_stats@pg-test-1:5438/test # DNS -> HAProxy -> database offline read/write

25. # Directly specify any cluster instance IP access
26. postgres://test@10.10.10.11:5432/test # Database instance direct connection (directly specify instance, no automatic traffic distribution)
27. postgres://test@10.10.10.11:6432/test # Connection Pool -> Database
28. postgres://test@10.10.10.11:5433/test # HAProxy -> connection pool -> database read/write
29. postgres://test@10.10.10.11:5434/test # HAProxy -> connection pool -> database read-only
30. postgres://dbuser_dba@10.10.10.11:5436/test # HAProxy -> Database Direct Connections
31. postgres://dbuser_stats@10.10.10.11:5438/test # HAProxy -> database offline read-write

33. # Directly specify any cluster instance IP access
34. postgres://test@10.10.10.11:5432/test # Database instance direct connection (directly specify instance, no automatic traffic distribution)
35. postgres://test@10.10.10.11:6432/test # Connection pool -> database
36. postgres://test@10.10.10.11:5433/test # HAProxy -> connection pool -> database read/write
37. postgres://test@10.10.10.11:5434/test # HAProxy -> connection pool -> database read-only
38. postgres://dbuser_dba@10.10.10.11:5436/test # HAProxy -> Database Direct Connections
39. postgres://dbuser_stats@10.10.10.11:5438/test # HAProxy -> database offline read-write

41. # Smart client automatic read/write separation (connection pooling)
42. postgres://test@10.10.10.11:6432,10.10.10.12:6432,10.10.10.13:6432/test?target_session_attrs=primary
43. postgres://test@10.10.10.11:6432,10.10.10.12:6432,10.10.10.13:6432/test?target_session_attrs=prefer-standby

45. # Intelligent client automatic read/write separation (database)
46. postgres://test@10.10.10.11:5432,10.10.10.12:5432,10.10.10.13:5432/test?target_session_attrs=primary
47. postgres://test@10.10.10.11:5432,10.10.10.12:5432,10.10.10.13:5432/test?target_session_attrs=prefer-standby

At the cluster level, users can access the four default services provided by the cluster via cluster domain + service port. Users can also bypass the domain name and access the database cluster directly using the cluster’s VIP (L2 or L4).

At the instance level, users can connect directly to Postgres via the node IP/domain name + port 5432 or port 6432 to access the database via Pgbouncer. Services provided by the cluster to which the instance belongs can also be accessed via Haproxy via 5433~543x.

Access Method

Pigsty recommends using a Haproxy-based access solution (1/2) or, in production envs with infra support, an L4VIP (or equivalent load balancing service) based access solution (3).

L2 VIP + Haproxy

Solution Description

The standard access method for Pigsty sandboxes uses a single domain name bound to a single L2 VIP, which points to the HAProxy.

The Haproxy uses Node Port to expose service in a unified way. Each Haproxy is an idempotent instance, providing complete load balancing and service distribution. Haproxy is deployed on each database node so that each member of the entire cluster is idempotent.

The availability of Haproxy is achieved through idempotent replicas. Each Haproxy can be used as an access portal, and users can use one, two, or more Haproxy instances, each providing exactly functionality.

Each cluster is assigned one L2 VIP, which is fixedly bound to the primary. When a switchover of the primary occurs, that L2 VIP is moved to the new primary with it. This is achieved through vip-manager: vip-manager will query Consul to get the current primary information, and then listen to the VIP address on the primary.

The L2 VIP of the cluster has a domain name corresponding to it. The domain name is fixed to resolve to that L2 VIP and remains unchanged during the lifecycle.

Solution Superiority

• No single point, HA
• VIP fixed binding to the primary, can be flexible access.

Solution limitations

• One more hop
• A client’s IP is lost, and some HBA policies cannot take effect normally.

• All candidate primary must be located in the same Layer 2 network.

• As an alternative, users can also bypass this restriction by using L4 VIP, but there will be one extra hop compared to L2 VIP.

• As an alternative, users can also choose not to use L2 VIP and use DNS to point directly to HAProxy, but may be affected by client DNS caching.

Schematic of the solution

DNS + Haproxy

Solution Description

Standard HA access method with no single point. A good balance of flexibility, applicability, and performance is achieved.

Haproxy in a cluster uses Node Port to expose service in a unified way. Each Haproxy is idempotent, providing complete load balancing and service distribution. Haproxy is deployed on each database node so that each member of the entire cluster is idempotent.

The availability of Haproxy is achieved through idempotent replicas. Each Haproxy can be used as an access portal, and users can use one, two, or multiple Haproxy instances, each providing precisely the same functionality.

The user needs to ensure that the application can access any healthy Haproxy instances. Users can resolve the DNS domain name of the database cluster to several Haproxy instances and enable DNS polling responses. And the client can choose not to cache DNS or use long connections and implement a mechanism to retry after a failed connection is established. Or refer to Method 2 and ensure HA of Haproxy with other L2/L4 VIPs on the architecture side.

Solution Superiority

• No single point, HA
• VIP fixed binding to the primary can be flexible access

Solution limitations

• One more hop
• A client’s IP is lost, and some HBA policies can not take effect properly.
• Haproxy is HA through idempotent replica, DNS polling, and client reconnection.

DNS should have a polling mechanism, clients should use long connections, and a failure retry mechanism should be in place. So that Haproxy failures can be transferred to other Haproxy instances in the cluster.

Schematic of the solution

L4 VIP + Haproxy

Four-layer load balancing + HAProxy access

Solution overview

Another variant of access method 1/2, ensuring HA of Haproxy via L4 VIP.

Solution advantages

• No single point, HA.
• Can use all Haproxy instances simultaneously to carry traffic evenly
• All candidate primary does not need to be located in the same Layer 2 network.
• Can operate a single VIP to complete traffic switching.

Solution limitations

• You can use Method 4: L4 VIP direct access for two more hops, which is more wasteful.
• The client’s IP is lost; part of the HBA policy can not correctly take effect.

L4 VIP

Four-layer load-balanced access

Program Description

Large-scale high-performance production env recommended using L4 VIP access (FullNAT, DPVS).

Solution Superiority

• Good performance and high throughput
• The correct client IP can be obtained through the toa module, and HBA can be fully effective.

Solution limitation

• Still one more article.
• We need to rely on external infra, which is complicated to deploy.
• Still lose client IP when the toa kernel module is not enabled.
• No Haproxy to mask primary-replica differences, and each node is no longer “idempotent”.

Consul DNS

Consul DNS access

Solution Description

The L2 VIP method is unavailable when all candidate primary must be located on the same Layer 2 network. In this case, DNS resolution can be used instead of L2 VIP

Solution Superiority

• One less hop

Solution Limitations

• Reliance on Consul DNS
• User needs to configure DNS caching policy properly

Static DNS

Static DNS Access

Solution Introduction

Traditional static DNS access method

Advantages of the solution

• One less hop
• Simple implementation

Solution Limitations

• No flexibility
• Prone to traffic loss during primary-replica switching

IP

IP Direct Access

Solution Introduction

Direct database IP access using innovative clients

Solution advantages

• Direct connection to database/connection pool, one less
• No reliance on additional components for primary-replica differentiation, reducing system complexity.

Solution limitations

• Too inflexible, cumbersome to expand and reduce cluster capacity.

Last modified 2022-06-04: fii en docs batch 2 (61bf601)