Cross-Region Database Replication Setup

This document outlines the configuration for the cross-region replication of the ERP-Unlocked PostgreSQL database hosted on Neon. This setup enhances fault tolerance and provides a foundation for disaster recovery.

Overview

The primary goal of this configuration is to maintain a real-time, read-only replica of the production database in a separate geographical region. If the primary region becomes unavailable, this replica can be promoted to become the new primary, minimizing downtime.

• Primary Project: EVL (ID: small-voice-46022531)
• Primary Region: aws-us-east-2
• Replica Project: EVL-Replica (ID: autumn-surf-72174435)
• Replica Region: aws-us-west-2
• Replication Method: Neon Logical Replication

Configuration Steps

The replication was configured using Neon’s logical PUBLICATION and SUBSCRIPTION model.

1. Primary Database (us-east-2)

A PUBLICATION named erp_unlocked_replication was created on the primary database. This publication includes all of the application’s tables, making them available for replication.

The following SQL command was used to create the publication:

CREATE PUBLICATION erp_unlocked_replication FOR TABLE
    public.customer_aliases,
    public.erp_connection_access,
    public.erp_connections,
    public.erp_customers,
    public.erp_inventory_locations,
    public.erp_products,
    public.erp_shipping_addresses,
    public.extracted_order_items,
    public.extracted_orders,
    public.orders,
    public.part_number_mappings,
    public.parts,
    public.pdf_documents,
    public.shipping_address_mappings;

2. Replica Database (us-west-2)

A new Neon project was created in the us-west-2 region to serve as the replica.

a. Schema Synchronization

The schema of the replica database was synchronized with the primary using the drizzle-kit push command. This ensures the table structures are identical. The command was executed with the replica’s direct connection string:

DATABASE_URL="<replica_database_url>" pnpm db:push

b. Subscription Creation

A SUBSCRIPTION named erp_unlocked_subscription was created on the replica database. This subscription connects to the primary database’s publication and begins replicating data.

The following command was used:

CREATE SUBSCRIPTION erp_unlocked_subscription
CONNECTION 'postgres://<primary_user>:<primary_password>@<primary_host>/<dbname>?sslmode=require'
PUBLICATION erp_unlocked_replication;

Failover Strategy

This setup does not include automatic failover. In the event of an outage in the us-east-2 region, the following manual steps must be taken:

1. Promote the Replica: In the Neon console for the EVL-Replica project, promote the read replica to a primary, writable instance.
2. Update Application Configuration: Update the DATABASE_URL environment variable in the application’s hosting environment to point to the connection string of the newly promoted replica.
3. Restart Application: Restart the application services to use the new database connection.

Other Production Recommendations Implemented

• Connection Pooling: The application’s DATABASE_URL should be updated to use the pooled connection string for better performance.
• Query Performance Monitoring: The pg_stat_statements extension has been installed on the primary database to help identify performance bottlenecks.