codesnips
sql 141 lines · 2 tabs

Database replication and high availability strategies

Maria Garcia Feb 2026
2 tabs 
-- Primary server configuration (postgresql.conf)
-- wal_level = replica
-- max_wal_senders = 10
-- wal_keep_size = 64MB
-- hot_standby = on

-- Create replication user on primary
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'password';

-- pg_hba.conf on primary: Allow replication connections
-- host replication replicator replica_ip/32 md5

-- On replica: Create base backup
-- pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P -R

-- Check replication status on primary
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  sync_state,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

-- Check replication lag in seconds
SELECT
  EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
  AS replication_lag_seconds;

-- Promote replica to primary (failover)
-- pg_ctl promote -D /var/lib/postgresql/data

-- Or use SQL (PostgreSQL 12+)
SELECT pg_promote();

-- Logical replication setup
-- On primary: Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Or publish all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- On subscriber: Create subscription
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=primary_host dbname=mydb user=replicator password=pass'
  PUBLICATION my_publication;

-- Check subscription status
SELECT * FROM pg_stat_subscription;

-- Monitoring replication slots
SELECT
  slot_name,
  slot_type,
  database,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

-- Synchronous replication configuration
-- postgresql.conf:
-- synchronous_commit = on
-- synchronous_standby_names = 'replica1,replica2'

-- This ensures commits wait for replica confirmation
2 files · sql Explain with highlit

Replication copies data across multiple servers for redundancy and scalability. Master-slave replication has one writable primary, multiple read-only replicas. I use read replicas to scale read-heavy workloads. Master-master allows writes to multiple nodes but risks conflicts. Streaming replication in PostgreSQL continuously ships WAL. Logical replication replicates specific tables or databases. Synchronous replication ensures replicas confirm writes—stronger consistency, higher latency. Asynchronous replication is faster but may lose recent commits on failure. Connection pooling via pgBouncer or ProxySQL distributes load. Automatic failover with Patroni or repmgr promotes replicas on primary failure. Understanding replication lag is critical for read consistency. High availability requires monitoring, failover automation, and proper network architecture.

Related snips

sql 
-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions
by Maria Garcia 2 tabs
ruby 
# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]

sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).first

SQL injection prevention with unsafe and safe query patterns

sql-injection owasp database
by Kai Nakamura 3 tabs
sql 
-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

Advanced query optimization techniques

database optimization query-performance
by Maria Garcia 2 tabs
python 
from django.db.models import Count, Avg, Sum, Q, F
from django.views.generic import TemplateView
from products.models import Product, Order, OrderItem


class DashboardView(TemplateView):

Django aggregation with annotate for statistics

django python database
by Priya Sharma 1 tab
ruby 
class ReportQuery
  SQL = <<~SQL.freeze
    SELECT date_trunc('day', events.created_at) AS day,
           count(*) AS total,
           count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
    FROM events

Safe Raw SQL with exec_query + Binds

rails activerecord sql
by codesnips 2 tabs
php 
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Laravel database migrations for schema management

laravel migrations database
by Carlos Mendez 3 tabs

Share this code

Here's the card — post it anywhere.

Database replication and high availability strategies — share card
Link copied