-- 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
-- MySQL Master-Slave Replication Setup
-- On master: Enable binary logging (my.cnf)
-- server-id = 1
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_do_db = mydb
-- Create replication user on master
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
-- Get master status
SHOW MASTER STATUS;
-- Note: File and Position values
-- On slave: Configure replication (my.cnf)
-- server-id = 2
-- relay-log = /var/log/mysql/relay-bin
-- read_only = 1
-- Set up replication on slave
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;
-- Start replication
START SLAVE;
-- Check slave status
SHOW SLAVE STATUSG
-- Important fields:
-- Slave_IO_Running: Should be 'Yes'
-- Slave_SQL_Running: Should be 'Yes'
-- Seconds_Behind_Master: Replication lag
-- Last_Error: Any replication errors
-- Stop replication
STOP SLAVE;
-- Reset slave
RESET SLAVE;
-- GTID-based replication (MySQL 5.6+)
-- master my.cnf:
-- gtid_mode = ON
-- enforce_gtid_consistency = ON
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION = 1;
-- Multi-source replication (replicate from multiple masters)
CHANGE MASTER TO
MASTER_HOST='master1',
MASTER_USER='repl_user',
MASTER_PASSWORD='password'
FOR CHANNEL 'master1';
CHANGE MASTER TO
MASTER_HOST='master2',
MASTER_USER='repl_user',
MASTER_PASSWORD='password'
FOR CHANNEL 'master2';
-- Check specific channel
SHOW SLAVE STATUS FOR CHANNEL 'master1'G
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.
