WARN: I’m not a database expert, and I’ve never seriously run a database in production.

Running a PostgreSQL cluster is surprisingly confusing given how popular postgres is. Searching for “postgres replication”, “postgres cluster”, and so on turns up a huge number of solutions, and most of them are either overly complex or possibly unmaintained.

For my personal apps, I needed a postgres cluster setup that met this requirements:

• May run on free-tier VMs around 1c1g, and can mix architectures like amd64/arm64
• HA + with real-time replication
• Minimal dependency on extra operational components for replication/clustering
• Easy to add or change nodes
• Works over a tailnet (tailscale)
• Simple: k8s was not an option

In the end I built it on top of AWS’s pgactive, and I’m quite happy with the result.

• Runs on nixOS + containers
• Active/Active, async replication (with DDL), mesh-like topology
• No operational components such as a controller (pgactive runs as a postgres extension)
• Applications point at a localhost:5432 for convenience and performance

postgres setup

First, set up postgres. All of my servers run nixos, so I run postgres on top of it.

Nixos provides the services.postgresql module. But once you depend on the system’s nixpkgs, the cache doesn’t behave correctly when updates happen, and pinning versions becomes a headache especially if you use pg extensions. Running multiple postgres instances isn’t possible either, so I concluded that using containers is the better approach.

The image is built from a nix flake with the required extensions (pgvector, pg_rational, timescaledb, and pgactive) and the necessary configuration added.

In the end, all you have to do is set up the following oci-containers on each node.

virtualisation.oci-containers = {
  containers.pg = {
    image = "ghcr.io/aca/containers/postgres-18:latest-arm64";
    extraOptions = [ "--network=host" ];
    volumes = [ "/var/lib/pg:/var/lib/postgresql/data" ];
  };
};

pgactive

With a postgres container running on each of the oci-xnzm-a1 and oci-impx-a1 nodes,

set up the node that will act as the seed (oci-xnzm-a1).

ssh root@oci-xnzm-a1 'podman exec -i pg psql -v ON_ERROR_STOP=1 -U postgres -d postgres' <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgactive;

CREATE SERVER "oci-xnzm-a1" FOREIGN DATA WRAPPER pgactive_fdw
  OPTIONS (host 'oci-xnzm-a1', port '5432', dbname 'postgres');
CREATE USER MAPPING FOR postgres SERVER "oci-xnzm-a1" OPTIONS (user 'postgres');

CREATE SERVER "oci-impx-a1" FOREIGN DATA WRAPPER pgactive_fdw
  OPTIONS (host 'oci-impx-a1', port '5432', dbname 'postgres');
CREATE USER MAPPING FOR postgres SERVER "oci-impx-a1" OPTIONS (user 'postgres');

SELECT pgactive.pgactive_create_group(
  node_name := 'pg-oci-xnzm-a1',
  node_dsn  := 'user_mapping=postgres pgactive_foreign_server=oci-xnzm-a1'
);
SELECT pgactive.pgactive_wait_for_node_ready();
SELECT node_name, node_status FROM pgactive.pgactive_nodes ORDER BY node_name;
SQL

On oci-impx-a1, connect to the seed (oci-xnzm-a1) with join_using_dsn to pull the entire DB (existing data at join time gets replicated).

ssh root@oci-impx-a1 'podman exec -i pg psql -v ON_ERROR_STOP=1 -U postgres -d postgres' <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgactive;

CREATE SERVER "oci-xnzm-a1" FOREIGN DATA WRAPPER pgactive_fdw
  OPTIONS (host 'oci-xnzm-a1', port '5432', dbname 'postgres');
CREATE USER MAPPING FOR postgres SERVER "oci-xnzm-a1" OPTIONS (user 'postgres');

CREATE SERVER "oci-impx-a1" FOREIGN DATA WRAPPER pgactive_fdw
  OPTIONS (host 'oci-impx-a1', port '5432', dbname 'postgres');
CREATE USER MAPPING FOR postgres SERVER "oci-impx-a1" OPTIONS (user 'postgres');

SELECT pgactive.pgactive_join_group(
  node_name      := 'pg-oci-impx-a1',
  node_dsn       := 'user_mapping=postgres pgactive_foreign_server=oci-impx-a1',
  join_using_dsn := 'user_mapping=postgres pgactive_foreign_server=oci-xnzm-a1'
);
SELECT pgactive.pgactive_wait_for_node_ready();
SELECT node_name, node_status FROM pgactive.pgactive_nodes ORDER BY node_name;
SQL

checking replication

psql "postgres://postgres@oci-impx-a1:5432/postgres" -c "CREATE TABLE xxx (id SERIAL PRIMARY KEY, name TEXT); INSERT INTO xxx (name) VALUES ('alice'); SELECT * FROM xxx;"
CREATE TABLE
INSERT 0 1
 id | name
----+-------
  1 | alice
(1 row)

$ psql "postgres://postgres@oci-xnzm-a1:5432/postgres" -c 'select * from xxx'
 id | name
----+-------
  1 | alice
(1 row)

$ psql "postgres://postgres@oci-xnzm-a1:5432/postgres" -c "INSERT INTO xxx (name) VALUES ('john');"
INSERT 0 1

$ psql "postgres://postgres@oci-impx-a1:5432/postgres" -c 'select * from xxx'
 id | name
----+-------
  1 | alice
  2 | john

While I haven’t exhausted every possible configuration, it has proven reliable for my purposes. If your needs align with mine, I’d consider this setup is worth a try.