PostgreSQL auto-clustering | Stack Harbor Documentation

For PostgreSQL, Stack Harbor PaaS builds a primary-secondary cluster with asynchronous WAL-based replication. New nodes added later by horizontal scaling are joined automatically as secondaries. Adding a Pgpool-II load-balancing layer in front of the cluster enables automatic failover.

Two ways to create the cluster

From the Marketplace

The fastest path. Open Marketplace in the top-left of the dashboard and search for PostgreSQL Primary-Secondary Cluster. Click Install, pick the PostgreSQL version, optionally enable Pgpool-II load balancers, and apply. A few minutes later you get a notification with admin credentials (also delivered by email).

From the topology wizard

More control over the rest of the environment. Open the wizard, pick PostgreSQL, turn on Auto-Clustering, optionally enable Pgpool-II. Adjust node count, cloudlets, add other stacks, set regions and public IPs as needed. Apply.

How PostgreSQL replication works here

Replication uses Write-Ahead Logging (WAL) in asynchronous mode. The primary writes WAL segments to its storage and the standby reads them in recovery mode. The standby applies the segments as soon as they arrive — fast, but not synchronous.

Three settings on the primary’s postgresql.conf control how this works:

wal_level = hot_standby
max_wal_senders = 10
archive_mode = on
archive_command = 'cd .'

wal_level controls how much information is written to the WAL. Values: minimal (recovery only), replica (default — enough for WAL archiving and replication, supports read-only queries on standbys), logical (adds info for logical decoding). Older versions used archive and hot_standby; both are mapped to replica now.
max_wal_senders is the maximum number of simultaneous WAL transfer processes.
archive_mode enables WAL archiving alongside wal_level. Every value except minimal permits archiving.
archive_command is the shell command run for each completed WAL segment. The default 'cd .' is a no-op — archiving is effectively off. Set it to copy WAL segments somewhere durable if you want to keep them, e.g.:
```
archive_command = 'test ! -f /var/lib/pgsql/data/pg_wal/%f && cp %p /tmp/mydata/%f'
```

The standby’s connection is authorised from the primary’s pg_hba.conf:

host replication all {standby_IP_address}/32 trust

On the standby, three things mark the node as standby:

hot_standby = on in postgresql.conf.
The primary_conninfo parameter at the bottom of postgresql.conf — host (or address) and port of the sending server, plus the user; password is either in primary_conninfo itself or in ~/.pgpass.
A standby.signal file in the PostgreSQL data directory. The presence of this file is what tells PostgreSQL to start as a hot standby. When a standby is promoted, this file is deleted.

After changing replication-related options, restart PostgreSQL. From the dashboard, restart one or both nodes; from the Web SSH terminal: sudo service postgresql restart.

Cluster entry point

Without Pgpool-II — connect directly to the primary node.
With Pgpool-II — connect to any Pgpool-II node. Pgpool-II routes writes to the current primary and balances reads.

Admin panels

phpPgAdmin is built into each database node. Use the one on the primary for live writes; the secondaries’ panels are read-only.
pgAdmin4 is available as a separate node via a marketplace manifest if you want richer tooling.
pgpoolAdmin is built into each Pgpool-II node. It exposes load-balancing rules, connection pools, logging, replication, debugging, and failover / failback settings.

Verify replication works

In phpPgAdmin on the primary, create a database. Then open the panel on a standby and confirm the new database appears there. If it does, replication is working.

Failover

Automatic failover (with Pgpool-II)

When Pgpool-II is in front of the cluster, it monitors the primary, detects when it is down, and promotes one of the available secondaries. When the original primary comes back, Pgpool-II re-adds it as a secondary using pg_rewind to reconcile any divergence.

Manual failover (without Pgpool-II)

PostgreSQL itself does not perform automatic failover. To promote a standby to primary by hand, open Web SSH on the standby and run:

/usr/pgsql-12/bin/pg_ctl promote -D /var/lib/pgsql/data

Then point your application at the new primary’s address.

You can automate this with a simple cron job on the standby that probes the primary with pg_isready and promotes the standby when the primary stops responding. A minimal script:

#!/bin/bash
primary="172.25.2.22"
secondary="172.25.2.31"
status=$(/usr/pgsql-12/bin/pg_isready -d postgres -h $primary)
response="$primary:5432 - no response"
if [ "$status" == "$response" ]; then
    /usr/pgsql-12/bin/pg_ctl promote -D /var/lib/pgsql/data
    echo "Secondary promoted. Switch app connection to $secondary"
else
    echo "Primary is alive. Nothing to do."
fi

Cluster restoration after a promotion

If you want the original topology back after a failover — the former primary back as primary, the promoted secondary back as secondary — you can do that. There are two reasons to bother: avoiding a connection-string change at the application, and preserving the ability to scale out (only the original-shaped cluster scales cleanly).

Outline:

On the former primary, clear the data directory: rm -rf /var/lib/pgsql/data/*.
Add the former primary’s IP to the current primary’s pg_hba.conf, e.g. host replication all 172.25.2.22/32 trust, and reload.
From the former primary, take a base backup from the current primary using pg_basebackup with the recovery / standby options.
Start PostgreSQL on the former primary; it now runs as a standby of the current primary.
Promote the former primary back when ready, demote the current primary by the same procedure in reverse.

This is delicate work; treat it as a planned change, not a recovery shortcut.

What is auto-clustering and auto-clustering via topology wizard — the higher-level model.
Database auto-clustering — the equivalent for MariaDB / MySQL / Percona.
MongoDB auto-clustering — for NoSQL workloads.