Auto-Clustering

PostgreSQL Auto-Clustering

PostgreSQL Auto-Clustering creates a primary-secondary PostgreSQL cluster with asynchronous WAL-based replication. New nodes added by horizontal scaling are joined automatically as secondary nodes, while an optional Pgpool-II layer can provide load balancing and automatic failover.

In this article

Create the PostgreSQL Cluster

Marketplace

  • Open Marketplace and search for the PostgreSQL Primary-Secondary Cluster package.
  • Select the PostgreSQL version.
  • Enable Pgpool-II where the option is available.
  • Install the package and wait for the database credentials and access information.

Topology Wizard

  • Open New Environment.
  • Select PostgreSQL.
  • Turn on Auto-Clustering.
  • Optionally enable Pgpool-II.
  • Configure node count, resources, region, and networking.
  • Apply the topology.
Primary nodeHandles read and write operations.
Secondary nodesReceive replicated WAL data and can serve read-only workloads.
Horizontal scalingNew PostgreSQL nodes are joined automatically as secondaries.
Pgpool-IIOptional load-balancing and failover layer in front of the database cluster.

How Replication Works

The cluster uses asynchronous Write-Ahead Log replication. The Primary writes WAL records and Secondary nodes receive and apply them in standby mode.

wal_level = hot_standby
max_wal_senders = 10
archive_mode = on
archive_command = 'cd .'
Asynchronous replication: the Primary does not wait for a Secondary to confirm every transaction before completing the commit. This keeps write latency low, but a very recent transaction may not yet be present on a Secondary if the Primary fails immediately afterward.

Cluster Entry Point

Without Pgpool-IIConnect the application directly to the Primary PostgreSQL node.
With Pgpool-IIConnect through Pgpool-II. Writes are routed to the current Primary and read-only requests can be balanced.

phpPgAdmin is available on PostgreSQL nodes. Use the Primary for write operations; Secondary nodes are read-only.

Verify Replication

  1. Open phpPgAdmin on the Primary.
  2. Create a new test database.
  3. Open phpPgAdmin on a Secondary.
  4. Confirm that the same database appears there.

If the test database appears on the Secondary, replication is working.

Failover and Recovery

With Pgpool-II, the cluster can detect a failed Primary and promote an available Secondary automatically.

After failover, the former Primary can be rebuilt as a Secondary by taking a fresh base backup from the current Primary and rejoining it to the cluster. A planned role reversal can later restore the original topology.

Recovery caution: rebuilding a PostgreSQL node is an advanced administrative operation. Back up critical data before clearing a data directory or re-seeding a node.

Expected Result

A PostgreSQL Primary-Secondary cluster is created with asynchronous replication. Horizontal scaling automatically adds Secondary nodes, and Pgpool-II can provide a stable connection layer, read balancing, and automatic failover.

Important Notes

  • PostgreSQL Auto-Clustering uses asynchronous Primary-Secondary replication.
  • New horizontally scaled nodes join as Secondaries automatically.
  • Without Pgpool-II, applications connect to the Primary directly.
  • With Pgpool-II, read-only requests can be balanced and automatic failover is available.
  • Verify replication by creating a database on the Primary and checking it on a Secondary.