Build it

We will turn a second server into a streaming replica, measure its lag, and route reads from NestJS with Prisma. The commands use the Ubuntu packages for PostgreSQL 16, with the primary at 10.0.1.10 and the new replica at 10.0.1.11. Try it on two test machines or two local clusters on different ports. Replication cannot run in a browser, but the two Practice exercises below, choosing where a read goes and measuring lag in bytes, can.

1. Prepare the primary

   The defaults since PG 10 already allow replication (wal_level = replica, 10 WAL senders, 10 slots), so you mostly add a limit for slots and a login for replicas.

   INICopy

   # /etc/postgresql/16/main/postgresql.conf on the PRIMARY
   listen_addresses       = '10.0.1.10'   # private address only
   wal_level              = replica       # default; 'logical' if you also need step 6
   max_wal_senders        = 10            # default; one per replica plus spare
   max_replication_slots  = 10            # default
   max_slot_wal_keep_size = '50GB'        # a dead replica's slot may not fill the disk

   SQLCopy

   -- On the primary: a role that may only replicate
   CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';

   OutputCopy

   # /etc/postgresql/16/main/pg_hba.conf on the PRIMARY
   # TYPE  DATABASE     USER        ADDRESS         METHOD
   host    replication  replicator  10.0.1.11/32    scram-sha-256

   Reload with sudo systemctl reload postgresql. A change to wal_level, max_wal_senders or max_replication_slots needs a restart instead.

2. Clone the primary with pg_basebackup -R

   On the new replica machine only. -R writes the file standby.signal and the connection settings, so the copy starts as a replica. -C -S replica1 creates a replication slot, so the primary keeps the WAL this replica still needs.

   TerminalCopy

   # On the NEW REPLICA machine (10.0.1.11)
   sudo systemctl stop postgresql
   sudo -u postgres mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.empty
   
   sudo -u postgres pg_basebackup \
     -h 10.0.1.10 -U replicator \
     -D /var/lib/postgresql/16/main \
     -R -X stream -C -S replica1 -P   # prompts for the replicator password
   
   # Give the replica a name so it shows up by name on the primary
   echo "cluster_name = 'replica1'" | sudo tee -a /etc/postgresql/16/main/postgresql.conf
   sudo systemctl start postgresql
   
   sudo -u postgres ls /var/lib/postgresql/16/main | grep -E 'signal|auto'

   OutputCopy

   postgresql.auto.conf     <- now holds primary_conninfo and primary_slot_name
   standby.signal           <- "start as a replica"

3. Check the replica from both sides

   On the primary, pg_stat_replication has one row per connected replica. The output below was captured on a test pair under a write benchmark, with the replica deliberately delayed by 3 seconds.

   SQLCopy

   -- On the PRIMARY
   SELECT application_name AS replica, state, sync_state,
          pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS bytes_behind,
          write_lag, flush_lag, replay_lag
   FROM pg_stat_replication;

   OutputCopy

    replica  |   state   | sync_state | bytes_behind |    write_lag    |    flush_lag    |   replay_lag
   ----------+-----------+------------+--------------+-----------------+-----------------+-----------------
    replica1 | streaming | async      | 2802 kB      | 00:00:00.000804 | 00:00:00.000804 | 00:00:02.998796

   Read it like this: the WAL reached the replica's disk in under a millisecond, but replay is 3 seconds behind. The *_lag columns describe recent WAL only, and go NULL a while after the primary goes idle.

   SQLCopy

   -- On the REPLICA
   SELECT pg_is_in_recovery() AS is_replica,
          CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
               THEN interval '0'            -- caught up: nothing left to replay
               ELSE now() - pg_last_xact_replay_timestamp()
          END AS replay_delay;

   Keep the CASE. Without it, the delay keeps growing whenever the primary has no new writes, say at 4 am, and pages you for lag that does not exist.

4. Watch the slots

   A slot whose replica is gone keeps WAL on the primary until the disk fills and the primary stops. Alert on this, and drop slots you no longer need.

   SQLCopy

   SELECT slot_name, slot_type, active, wal_status,
          pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_kept
   FROM pg_replication_slots;
   
   -- Only after you are sure nobody uses it:
   -- SELECT pg_drop_replication_slot('old_replica');

   Good alert starting points: replay_lag above 30 seconds for 5 minutes, any slot that is inactive for more than an hour, and wal_status other than reserved.

5. Route reads in NestJS with Prisma

   Install @prisma/extension-read-replicas. Version 0.4.1 targets Prisma 6, 0.3.x and 0.4.0 target Prisma 5, and 0.5 needs Prisma 7, which configures replicas differently. Reads outside a transaction go to the replica. Writes, $transaction and raw SQL go to the primary. $primary() forces a read to the primary.

   TypeScriptCopy

   // db.module.ts
   import { Global, Module } from '@nestjs/common';
   import { PrismaClient } from '@prisma/client';
   import { readReplicas } from '@prisma/extension-read-replicas';
   
   export const DB = 'DB';
   const createDb = () =>
     new PrismaClient().$extends(
       readReplicas({ url: process.env.DATABASE_REPLICA_URL! }), // an array also works
     );
   export type Db = ReturnType<typeof createDb>;
   
   @Global()
   @Module({ providers: [{ provide: DB, useFactory: createDb }], exports: [DB] })
   export class DbModule {}

   TypeScriptCopy

   // grn.service.ts
   @Injectable()
   export class GrnService {
     constructor(@Inject(DB) private readonly db: Db) {}
   
     // Report: a few seconds old is fine, so this goes to the replica
     salesBySite(from: Date, to: Date) {
       return this.db.sale.groupBy({
         by: ['siteId'],
         where: { saleDate: { gte: from, lt: to } },
         _sum: { total: true },
       });
     }
   
     // The list shown right after a save: must be fresh, so force the primary
     recentGrns(siteId: bigint) {
       return this.db.$primary().grn.findMany({
         where: { siteId },
         orderBy: { receivedAt: 'desc' },
         take: 20,
       });
     }
   }

   Raw SQL always goes to the primary, because the extension cannot tell a read from a write. For a heavy raw report, opt in to the replica with this.db.$replica().$queryRaw`...`.

6. Optional: feed a reporting database with logical replication

   This needs wal_level = logical on the primary (restart required). Create the same table on the reporting database first, because DDL is not copied.

   SQLCopy

   -- On the PRIMARY
   CREATE PUBLICATION reporting_pub FOR TABLE grn, sale
     WITH (publish_via_partition_root = true);   -- because sale is partitioned
   GRANT SELECT ON grn, sale TO replicator;       -- needed for the first copy
   
   -- On the REPORTING database (tables already created)
   CREATE SUBSCRIPTION reporting_sub
     CONNECTION 'host=10.0.1.10 dbname=mangrove user=replicator password=change-me'
     PUBLICATION reporting_pub;

   Without the GRANT, the first copy never arrives, and the subscriber's log repeats permission denied for table grn.

7. Practise a failover on the test pair

   Do this on test machines until the steps are boring. Then write them into the runbook.

   TerminalCopy

   # 1. Stop the old primary so it can never take writes again
   sudo systemctl stop postgresql            # on 10.0.1.10
   
   # 2. Promote the replica
   sudo -u postgres psql -c "SELECT pg_promote();"                 # on 10.0.1.11
   sudo -u postgres psql -Atc "SELECT pg_is_in_recovery();"        # f = it is a primary now
   
   # 3. Point DATABASE_URL at 10.0.1.11 (or move the DNS name) and restart the API