1#!/bin/bash
2
3###############################################################################
4# PostgreSQL Primary-Standby Replication Setup Script
5#
6# This script automates the setup of PostgreSQL streaming replication with:
7# 1. Replication user creation on primary
8# 2. pg_hba.conf configuration for replication connections
9# 3. Base backup for standby initialization
10# 4. Standby configuration with streaming replication
11# 5. Replication verification and failover testing
12#
13# Prerequisites:
14# - PostgreSQL installed on both primary and standby servers
15# - SSH access between servers (for remote setup)
16# - Sufficient disk space for base backup
17###############################################################################
18
19set -e # Exit on error
20set -u # Exit on undefined variable
21
22# Configuration
23PRIMARY_HOST="${PRIMARY_HOST:-localhost}"
24PRIMARY_PORT="${PRIMARY_PORT:-5432}"
25STANDBY_HOST="${STANDBY_HOST:-localhost}"
26STANDBY_PORT="${STANDBY_PORT:-5433}"
27
28POSTGRES_USER="${POSTGRES_USER:-postgres}"
29REPLICATION_USER="${REPLICATION_USER:-replicator}"
30REPLICATION_PASSWORD="${REPLICATION_PASSWORD:-repl_password}"
31
32PRIMARY_DATA_DIR="${PRIMARY_DATA_DIR:-/var/lib/postgresql/data}"
33STANDBY_DATA_DIR="${STANDBY_DATA_DIR:-/var/lib/postgresql/standby}"
34WAL_ARCHIVE_DIR="${WAL_ARCHIVE_DIR:-/var/lib/postgresql/wal_archive}"
35
36# Colors for output
37RED='\033[0;31m'
38GREEN='\033[0;32m'
39YELLOW='\033[1;33m'
40NC='\033[0m' # No Color
41
42# Helper functions
43log_info() {
44 echo -e "${GREEN}[INFO]${NC} $1"
45}
46
47log_warn() {
48 echo -e "${YELLOW}[WARN]${NC} $1"
49}
50
51log_error() {
52 echo -e "${RED}[ERROR]${NC} $1"
53}
54
55###############################################################################
56# Step 1: Create replication user on primary
57###############################################################################
58create_replication_user() {
59 log_info "Creating replication user on primary..."
60
61 PGPASSWORD="${POSTGRES_PASSWORD}" psql -h "$PRIMARY_HOST" -p "$PRIMARY_PORT" \
62 -U "$POSTGRES_USER" -d postgres <<EOF
63-- Create replication user with replication privileges
64DO \$\$
65BEGIN
66 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '$REPLICATION_USER') THEN
67 CREATE ROLE $REPLICATION_USER WITH REPLICATION LOGIN PASSWORD '$REPLICATION_PASSWORD';
68 END IF;
69END
70\$\$;
71
72-- Grant necessary permissions
73GRANT CONNECT ON DATABASE postgres TO $REPLICATION_USER;
74
75-- Show replication slots
76SELECT * FROM pg_replication_slots;
77EOF
78
79 log_info "Replication user created successfully."
80}
81
82###############################################################################
83# Step 2: Configure pg_hba.conf for replication
84###############################################################################
85configure_pg_hba() {
86 log_info "Configuring pg_hba.conf for replication..."
87
88 # Add replication entry to pg_hba.conf
89 # In production, restrict to specific IP addresses
90 local HBA_ENTRY="host replication $REPLICATION_USER all md5"
91
92 # Check if entry already exists
93 if docker exec postgres-primary grep -q "^$HBA_ENTRY" /var/lib/postgresql/data/pg_hba.conf 2>/dev/null; then
94 log_warn "Replication entry already exists in pg_hba.conf"
95 else
96 docker exec postgres-primary bash -c "echo '$HBA_ENTRY' >> /var/lib/postgresql/data/pg_hba.conf"
97 log_info "Added replication entry to pg_hba.conf"
98
99 # Reload PostgreSQL configuration
100 docker exec postgres-primary psql -U postgres -c "SELECT pg_reload_conf();"
101 log_info "PostgreSQL configuration reloaded."
102 fi
103}
104
105###############################################################################
106# Step 3: Create base backup for standby
107###############################################################################
108create_base_backup() {
109 log_info "Creating base backup from primary to standby..."
110
111 # Stop standby if running
112 log_info "Stopping standby server..."
113 docker stop postgres-standby 2>/dev/null || true
114
115 # Remove old standby data
116 log_warn "Removing old standby data directory..."
117 docker exec postgres-standby rm -rf /var/lib/postgresql/data/* 2>/dev/null || true
118
119 # Create base backup using pg_basebackup
120 log_info "Running pg_basebackup..."
121 docker exec postgres-standby bash -c "PGPASSWORD='$REPLICATION_PASSWORD' pg_basebackup \
122 -h postgres-primary \
123 -p 5432 \
124 -U $REPLICATION_USER \
125 -D /var/lib/postgresql/data \
126 -Fp \
127 -Xs \
128 -P \
129 -R"
130
131 log_info "Base backup completed successfully."
132}
133
134###############################################################################
135# Step 4: Configure standby for streaming replication
136###############################################################################
137configure_standby() {
138 log_info "Configuring standby for streaming replication..."
139
140 # Create standby.signal file (PostgreSQL 12+)
141 docker exec postgres-standby touch /var/lib/postgresql/data/standby.signal
142
143 # Configure primary_conninfo in postgresql.auto.conf
144 local CONN_INFO="host=postgres-primary port=5432 user=$REPLICATION_USER password=$REPLICATION_PASSWORD"
145
146 docker exec postgres-standby bash -c "cat >> /var/lib/postgresql/data/postgresql.auto.conf <<EOF
147# Standby configuration
148primary_conninfo = '$CONN_INFO'
149restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
150archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/wal_archive %r'
151EOF"
152
153 log_info "Standby configuration completed."
154}
155
156###############################################################################
157# Step 5: Start standby and verify replication
158###############################################################################
159verify_replication() {
160 log_info "Starting standby server..."
161 docker start postgres-standby
162
163 # Wait for standby to start
164 sleep 5
165
166 log_info "Verifying replication status on primary..."
167 PGPASSWORD="${POSTGRES_PASSWORD}" psql -h "$PRIMARY_HOST" -p "$PRIMARY_PORT" \
168 -U "$POSTGRES_USER" -d postgres -c "SELECT * FROM pg_stat_replication;"
169
170 log_info "Checking replication lag..."
171 PGPASSWORD="${POSTGRES_PASSWORD}" psql -h "$PRIMARY_HOST" -p "$PRIMARY_PORT" \
172 -U "$POSTGRES_USER" -d postgres -c "
173 SELECT
174 client_addr,
175 state,
176 sent_lsn,
177 write_lsn,
178 flush_lsn,
179 replay_lsn,
180 sync_state
181 FROM pg_stat_replication;"
182
183 log_info "${GREEN}Replication setup completed successfully!${NC}"
184}
185
186###############################################################################
187# Step 6: Test replication
188###############################################################################
189test_replication() {
190 log_info "Testing replication..."
191
192 # Create test table on primary
193 log_info "Creating test table on primary..."
194 PGPASSWORD="${POSTGRES_PASSWORD}" psql -h "$PRIMARY_HOST" -p "$PRIMARY_PORT" \
195 -U "$POSTGRES_USER" -d postgres <<EOF
196CREATE TABLE IF NOT EXISTS replication_test (
197 id SERIAL PRIMARY KEY,
198 data TEXT,
199 created_at TIMESTAMP DEFAULT NOW()
200);
201
202INSERT INTO replication_test (data) VALUES ('Test replication data');
203EOF
204
205 # Wait for replication
206 sleep 2
207
208 # Verify data on standby
209 log_info "Verifying data on standby..."
210 PGPASSWORD="${POSTGRES_PASSWORD}" psql -h "$STANDBY_HOST" -p "$STANDBY_PORT" \
211 -U "$POSTGRES_USER" -d postgres -c "SELECT * FROM replication_test;"
212
213 log_info "${GREEN}Replication test successful!${NC}"
214}
215
216###############################################################################
217# Main execution
218###############################################################################
219main() {
220 log_info "Starting PostgreSQL Primary-Standby setup..."
221
222 # Ensure directories exist
223 mkdir -p "$WAL_ARCHIVE_DIR"
224
225 # Execute setup steps
226 create_replication_user
227 configure_pg_hba
228 create_base_backup
229 configure_standby
230 verify_replication
231 test_replication
232
233 log_info "${GREEN}All setup steps completed successfully!${NC}"
234 log_info "Primary: $PRIMARY_HOST:$PRIMARY_PORT"
235 log_info "Standby: $STANDBY_HOST:$STANDBY_PORT"
236}
237
238# Run main function
239main "$@"