1# PostgreSQL Examples
2
3This directory contains practical PostgreSQL examples demonstrating replication, high availability, and production configurations.
4
5## Files
6
7### 08_primary_standby_compose.yml
8
9Docker Compose configuration for PostgreSQL streaming replication setup.
10
11**Features:**
12- Primary-Standby architecture with streaming replication
13- WAL archiving for Point-In-Time Recovery (PITR)
14- Health checks for both instances
15- Shared volume for WAL archive
16- Network isolation
17
18**Usage:**
19```bash
20# Start the cluster
21docker-compose -f 08_primary_standby_compose.yml up -d
22
23# Check logs
24docker-compose -f 08_primary_standby_compose.yml logs -f
25
26# Connect to primary
27docker exec -it postgres-primary psql -U postgres
28
29# Connect to standby
30docker exec -it postgres-standby psql -U postgres
31
32# Stop the cluster
33docker-compose -f 08_primary_standby_compose.yml down
34```
35
36**Ports:**
37- Primary: 5432
38- Standby: 5433
39
40### 09_primary_standby_setup.sh
41
42Automated setup script for PostgreSQL Primary-Standby replication.
43
44**What it does:**
451. Creates replication user on primary
462. Configures pg_hba.conf for replication access
473. Creates base backup using pg_basebackup
484. Configures standby with streaming replication
495. Verifies replication status
506. Tests replication with sample data
51
52**Usage:**
53```bash
54# Make script executable first
55chmod +x 09_primary_standby_setup.sh
56
57# With Docker Compose
58./09_primary_standby_setup.sh
59
60# Custom configuration
61PRIMARY_HOST=db1.example.com \
62STANDBY_HOST=db2.example.com \
63REPLICATION_USER=repl_user \
64./09_primary_standby_setup.sh
65```
66
67**Configuration Variables:**
68- `PRIMARY_HOST`: Primary server hostname (default: localhost)
69- `PRIMARY_PORT`: Primary server port (default: 5432)
70- `STANDBY_HOST`: Standby server hostname (default: localhost)
71- `STANDBY_PORT`: Standby server port (default: 5433)
72- `REPLICATION_USER`: Replication user name (default: replicator)
73- `REPLICATION_PASSWORD`: Replication user password
74
75## Replication Concepts
76
77### Streaming Replication
78
79PostgreSQL streaming replication continuously streams WAL (Write-Ahead Log) records from the primary to standby servers.
80
81**Advantages:**
82- Near real-time replication
83- Low latency
84- Automatic synchronization
85- Read replicas for load distribution
86
87### WAL Archiving
88
89WAL archiving provides a backup mechanism for point-in-time recovery.
90
91**Key Settings:**
92- `wal_level=replica`: Enable WAL logging for replication
93- `archive_mode=on`: Enable WAL archiving
94- `archive_command`: Command to archive WAL files
95- `max_wal_senders`: Maximum number of concurrent replication connections
96
97### Monitoring Replication
98
99Check replication status on primary:
100```sql
101SELECT * FROM pg_stat_replication;
102```
103
104Check replication lag:
105```sql
106SELECT
107 client_addr,
108 state,
109 pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
110FROM pg_stat_replication;
111```
112
113Check recovery status on standby:
114```sql
115SELECT pg_is_in_recovery();
116SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
117```
118
119## Failover Scenarios
120
121### Manual Failover
122
1231. **Stop primary** (simulated failure):
124 ```bash
125 docker stop postgres-primary
126 ```
127
1282. **Promote standby to primary**:
129 ```bash
130 docker exec postgres-standby pg_ctl promote -D /var/lib/postgresql/data
131 ```
132
1333. **Verify standby is now primary**:
134 ```bash
135 docker exec postgres-standby psql -U postgres -c "SELECT pg_is_in_recovery();"
136 ```
137 Should return `f` (false) indicating it's now a primary.
138
139### Automatic Failover
140
141For production environments, consider using:
142- **Patroni**: HA solution with automatic failover
143- **repmgr**: Replication manager for PostgreSQL
144- **Pacemaker + Corosync**: Cluster resource manager
145
146## Best Practices
147
1481. **Network Security**
149 - Use SSL/TLS for replication connections
150 - Restrict `pg_hba.conf` entries to specific IP addresses
151 - Use strong passwords for replication users
152
1532. **Monitoring**
154 - Monitor replication lag regularly
155 - Set up alerts for replication failures
156 - Track WAL archive size
157
1583. **Backup Strategy**
159 - Maintain both WAL archives and base backups
160 - Test restore procedures regularly
161 - Use multiple backup retention policies
162
1634. **Performance**
164 - Adjust `max_wal_senders` based on number of replicas
165 - Configure `hot_standby_feedback` to prevent query conflicts
166 - Monitor disk I/O on standby servers
167
168## Troubleshooting
169
170### Replication Not Starting
171
172Check primary logs:
173```bash
174docker logs postgres-primary
175```
176
177Verify replication user permissions:
178```sql
179SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'replicator';
180```
181
182### Replication Lag Increasing
183
184Check network connectivity:
185```bash
186docker exec postgres-standby ping postgres-primary
187```
188
189Monitor replication state:
190```sql
191SELECT * FROM pg_stat_replication;
192```
193
194### Standby Cannot Connect
195
196Verify pg_hba.conf entry:
197```bash
198docker exec postgres-primary cat /var/lib/postgresql/data/pg_hba.conf | grep replication
199```
200
201Test connection from standby:
202```bash
203docker exec postgres-standby psql -h postgres-primary -U replicator -d postgres -c "SELECT 1;"
204```
205
206## References
207
208- [PostgreSQL Replication Documentation](https://www.postgresql.org/docs/current/high-availability.html)
209- [pg_basebackup](https://www.postgresql.org/docs/current/app-pgbasebackup.html)
210- [Streaming Replication](https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION)