PostgreSQL Replication and High Availability

By Kokil Thapa | Last reviewed: August 2026

PostgreSQL replication and high availability is the difference between a minor outage and a catastrophic data loss event when your primary database server fails. For Laravel applications serving business-critical workflows—like legal-tech portals or eCommerce platforms handling payments—a single point of failure at the database layer is unacceptable. This guide covers practical streaming replication setup, automated failover with Patroni, and the monitoring required to keep production systems reliable in 2026.

How do you configure PostgreSQL streaming replication for Laravel applications?

Streaming replication is the foundation of any database-driven website development in Nepal that requires read scaling or disaster recovery. Unlike logical replication, streaming replication copies the exact binary WAL (Write-Ahead Log) records from primary to replica, ensuring byte-level consistency. For Laravel applications using Eloquent ORM, this means your replicas will have identical schema and data structures without application-level changes.

Primary server configuration

On PostgreSQL 17 (current stable as of 2026), edit postgresql.conf on the primary node. These settings enable replication slots and archive commands necessary for reliable streaming:

# postgresql.conf - Primary Node
listen_addresses = '*'
max_connections = 200
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'
synchronous_commit = on
synchronous_standby_names = 'ANY 1 (replica1, replica2)'
checkpoint_completion_target = 0.9

The synchronous_standby_names setting above uses "ANY 1" mode, meaning at least one replica must acknowledge each commit before the transaction completes. This prevents data loss during failover while avoiding the latency penalty of waiting for all replicas. On client projects where payment processing was involved, I've found this balance critical—you cannot afford lost transactions, but you also cannot tolerate 500ms added latency on every insert.

Replica bootstrap and authentication

Create a dedicated replication user on the primary:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password_here';
GRANT pg_read_all_data TO replicator;

On each replica server, use pg_basebackup to initialize from the primary. This command streams a consistent snapshot while the primary continues serving traffic:

pg_basebackup -h primary-db.example.com -U replicator \
  -D /var/lib/postgresql/17/main \
  -Fp -Xs -P -R --checkpoint=fast

The -R flag automatically creates standby.signal and configures primary_conninfo in postgresql.auto.conf. After starting PostgreSQL on the replica, verify replication status from the primary:

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       sync_state, application_name
FROM pg_stat_replication;

Laravel database configuration for read-write splitting

In your Laravel config/database.php, define separate connections for writes and reads. Laravel 12 supports sticky connections natively, ensuring reads within a request hit the primary after a write:

'pgsql' => [
    'write' => [
        'host' => env('DB_PRIMARY_HOST', 'primary-db.example.com'),
    ],
    'read' => [
        'host' => [
            env('DB_REPLICA_1_HOST', 'replica1.example.com'),
            env('DB_REPLICA_2_HOST', 'replica2.example.com'),
        ],
    ],
    'sticky' => true,
    // ... other shared config
],

A common mistake I've seen on production Laravel applications is forgetting the sticky option. Without it, a user might create a record and immediately see stale data on the next page load because the read query hits a replica that hasn't replayed the latest WAL yet. For legal-tech portals where document uploads trigger immediate confirmation screens, this causes support tickets.

What is the best automatic failover solution for PostgreSQL in 2026?

Manual failover works until your primary dies at 3 AM. Automatic failover tools detect primary failure and promote a replica within seconds. The three production-grade options for PostgreSQL 17 in 2026 are Patroni, repmgr, and PGAutoFailover. Each has distinct trade-offs.

For most Laravel projects I work on—whether legal-tech portals or eCommerce systems running on Ubuntu VPS infrastructure—Patroni with etcd is the default recommendation. Yes, it adds operational complexity with the distributed consensus store, but it eliminates split-brain scenarios that can corrupt data. When building custom ERP systems for manufacturing companies where inventory accuracy is non-negotiable, the extra setup cost pays for itself during the first unplanned failover.

Patroni minimal configuration

Install Patroni and etcd on each database node. A minimal patroni.yml for the primary candidate:

scope: laravel-production-cluster
name: pg-node-1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.10:8008
etcd3:
  hosts: 10.0.1.10:2379,10.0.1.11:2379,10.0.1.12:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    synchronous_node_count: 1
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.10:5432
  data_dir: /var/lib/postgresql/17/patroni
  authentication:
    replication:
      username: replicator
      password: secure_password_here
    superuser:
      username: postgres
      password: superuser_password
  parameters:
    max_connections: 200
    shared_buffers: 4GB
    effective_cache_size: 12GB
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10

The synchronous_mode: true setting ensures Patroni maintains at least one synchronous replica before allowing writes. During failover, Patroni promotes only a replica confirmed to have received all committed transactions. This is what makes PostgreSQL replication and high availability actually safe for financial or legal data.

How do you monitor PostgreSQL replication lag and prevent silent data loss?

Replication lag is the silent killer. Your replicas can be minutes behind while reporting "streaming" status, and you won't know until failover exposes missing data. Monitoring must be proactive, not reactive.

Essential monitoring queries

Run these on the primary via a monitoring agent (Prometheus postgres_exporter, Datadog, or custom Laravel scheduled command):

-- Replication lag in bytes and seconds
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_bytes,
       EXTRACT(EPOCH FROM (now() - last_msg_receipt_time))::int AS lag_seconds
FROM pg_replication_slots
JOIN pg_stat_replication USING (pid);

-- Alert if any replica lags more than 30 seconds
SELECT COUNT(*) AS critical_replicas
FROM pg_stat_replication
WHERE EXTRACT(EPOCH FROM (now() - replay_lag)) > 30;

On replicas, check replay progress directly:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay,
       pg_is_in_recovery() AS is_replica,
       pg_last_wal_receive_lsn() AS received_lsn,
       pg_last_wal_replay_lsn() AS replayed_lsn;

I set alerts at 10 seconds warning and 30 seconds critical for most Laravel production systems. For real-time inventory management systems, those thresholds drop to 2 and 5 seconds respectively—stale stock data causes overselling that costs real money.

Prometheus alerting rules

If you're using Prometheus with postgres_exporter, these rules catch problems before users notice:

groups:
- name: postgresql_replication
  rules:
  - alert: PostgreSQLReplicationLagHigh
    expr: pg_replication_lag > 30
    for: 2m
    labels:
      severity: critical
    annotations:
      summary: "PostgreSQL replica {{ $labels.instance }} lagging {{ $value }}s"
  - alert: PostgreSQLReplicationSlotInactive
    expr: pg_replication_slot_active == 0
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "Replication slot {{ $labels.slot_name }} inactive"

Inactive replication slots are particularly dangerous—they prevent WAL cleanup and can fill your disk within hours. Always alert on slot activity, not just lag.

When should you use synchronous versus asynchronous replication for production databases?

This decision determines whether you prioritize zero data loss or maximum write performance. There is no universally correct answer—it depends entirely on your application's tolerance for lost transactions versus added latency.

Synchronous replication: when data integrity is non-negotiable

Synchronous replication guarantees that every committed transaction exists on at least one replica before the client receives confirmation. The trade-off is write latency equal to network round-trip time to the nearest replica. For legal-tech portals handling marriage registrations, divorce filings, or notary attestations, losing even one submitted form is unacceptable. I configure synchronous mode for these systems despite the 5-15ms added latency per write.

Use synchronous_standby_names = 'ANY 1 (replica1, replica2)' rather than listing all replicas. This allows writes to proceed if any single replica acknowledges, protecting against individual replica failures without blocking the entire cluster.

Asynchronous replication: when throughput matters more

Asynchronous replication returns commits to the client immediately, streaming WAL records to replicas in the background. If the primary fails before a replica receives recent writes, those transactions are lost. For analytics dashboards, content management systems, or product catalogs where occasional data loss is recoverable, asynchronous replication avoids the latency penalty entirely.

Hybrid approach for Laravel applications

Many production Laravel systems benefit from per-transaction control. Use synchronous_commit at the session level for critical operations:

// In a Laravel migration or critical service
DB::statement('SET LOCAL synchronous_commit = on');
// Payment processing, legal document submission
DB::transaction(function () {
    Order::create([...]);
    PaymentRecord::create([...]);
});

// Non-critical operations can opt out
DB::statement('SET LOCAL synchronous_commit = off');
ActivityLog::create([...]); // Acceptable to lose on crash

This hybrid pattern gives you PostgreSQL replication and high availability that matches your actual business requirements rather than applying a blanket policy. On eCommerce projects, I typically make payment and order creation synchronous while keeping cart updates, session tracking, and analytics asynchronous.

How do you test PostgreSQL failover without breaking production?

Untested failover is imaginary failover. You must verify your PostgreSQL replication and high availability setup actually works under realistic conditions. Schedule quarterly failover drills during maintenance windows.

1. Pre-flight checks: Verify all replicas are caught up (replay_lag < 1s), Patroni health endpoints return 200, and etcd cluster is healthy (etcdctl endpoint health).
2. Simulate primary failure: Stop PostgreSQL on the primary (systemctl stop postgresql) or use patronictl switchover for controlled tests. Never test by killing processes or pulling network cables in production—use graceful shutdown first.
3. Verify promotion: Confirm the new primary accepts writes, old primary rejoins as replica, and application connections redirect within expected timeframe. Check Laravel logs for connection errors exceeding your SLA threshold.
4. Validate data integrity: Compare row counts and checksums on critical tables between old and new primary. Run application-level smoke tests that exercise read and write paths.
5. Document results: Record actual failover duration, any manual intervention required, and gaps in monitoring or runbooks. Update procedures before the next drill.

A common mistake is testing failover only during quiet periods. Schedule at least one drill during moderate load to observe how connection pooling (PgBouncer), Laravel queue workers, and scheduled tasks behave during the transition. I've seen queue workers hold stale connections for minutes after failover because they weren't configured with connection timeouts—something you only discover under load.

Implementing PostgreSQL Replication and High Availability Safely

PostgreSQL replication and high availability protects your Laravel applications from single-point-of-failure outages, but only if configured and tested correctly. Start with streaming replication and Patroni for automated failover, implement comprehensive lag monitoring with actionable alerts, choose synchronous or asynchronous modes based on actual business requirements, and conduct regular failover drills. For teams managing Laravel development in Nepal or globally, investing in proper database HA prevents far more expensive incidents down the line.

If you need help designing or auditing your PostgreSQL high availability architecture for a production Laravel system, get in touch to discuss your specific requirements.