max / makenotwork

2.7 KB · 76 lines History Blame Raw

1	#!/bin/bash
2	# Setup WAL continuous archiving for PostgreSQL.
3	# Run once on the production server, then restart PostgreSQL.
4	#
5	# This enables point-in-time recovery (PITR): restore to any moment,
6	# not just the last daily pg_dump. Caps data loss at ~5 minutes.
7	#
8	# Usage:
9	# scp deploy/setup-wal-archiving.sh root@100.120.174.96:/opt/makenotwork/
10	# ssh root@100.120.174.96 "bash /opt/makenotwork/setup-wal-archiving.sh"
11
12	set -euo pipefail
13
14	WAL_ARCHIVE_DIR="/opt/makenotwork/wal-archive"
15	PG_CONF_DIR="/etc/postgresql/16/main"
16	PG_CONF="${PG_CONF_DIR}/postgresql.conf"
17	CONF_OVERRIDE="${PG_CONF_DIR}/conf.d/wal_archiving.conf"
18
19	echo "[$(date -Iseconds)] Setting up WAL continuous archiving..."
20
21	# Verify PostgreSQL config exists
22	if [ ! -f "$PG_CONF" ]; then
23	# Try version 15 (Debian 11)
24	PG_CONF_DIR="/etc/postgresql/15/main"
25	PG_CONF="${PG_CONF_DIR}/postgresql.conf"
26	CONF_OVERRIDE="${PG_CONF_DIR}/conf.d/wal_archiving.conf"
27	if [ ! -f "$PG_CONF" ]; then
28	echo "ERROR: PostgreSQL config not found at /etc/postgresql/{15,16}/main/postgresql.conf"
29	exit 1
30	fi
31	fi
32
33	# Ensure conf.d directory exists and is included
34	mkdir -p "${PG_CONF_DIR}/conf.d"
35	if ! grep -q "include_dir = 'conf.d'" "$PG_CONF" 2>/dev/null; then
36	echo "include_dir = 'conf.d'" >> "$PG_CONF"
37	echo "[$(date -Iseconds)] Added conf.d include directive to postgresql.conf"
38	fi
39
40	# Create WAL archive directory
41	mkdir -p "$WAL_ARCHIVE_DIR"
42	chown postgres:postgres "$WAL_ARCHIVE_DIR"
43	chmod 700 "$WAL_ARCHIVE_DIR"
44	echo "[$(date -Iseconds)] Created WAL archive directory: $WAL_ARCHIVE_DIR"
45
46	# Write WAL archiving configuration
47	cat > "$CONF_OVERRIDE" <<'PGCONF'
48	# WAL continuous archiving for PITR
49	# Caps data loss at ~5 minutes (archive_timeout).
50	# Applied: setup-wal-archiving.sh
51
52	wal_level = replica
53	archive_mode = on
54	archive_command = 'test ! -f /opt/makenotwork/wal-archive/%f && cp %p /opt/makenotwork/wal-archive/%f'
55	archive_timeout = 300
56	PGCONF
57
58	chown postgres:postgres "$CONF_OVERRIDE"
59	echo "[$(date -Iseconds)] Wrote WAL config to $CONF_OVERRIDE"
60
61	echo ""
62	echo "WAL archiving configured. To activate:"
63	echo ""
64	echo " sudo systemctl restart postgresql"
65	echo ""
66	echo "Verify after restart:"
67	echo " sudo -u postgres psql -c \"SHOW wal_level;\" -- expect: replica"
68	echo " sudo -u postgres psql -c \"SHOW archive_mode;\" -- expect: on"
69	echo " sudo -u postgres psql -c \"SHOW archive_timeout;\" -- expect: 300"
70	echo ""
71	echo "Then add cron jobs for WAL maintenance and offsite sync:"
72	echo " # Prune WAL segments older than 7 days (hourly):"
73	echo " 0 * * * * find /opt/makenotwork/wal-archive -name '0*' -mtime +7 -delete"
74	echo " # Offsite sync (every 10 minutes):"
75	echo " /10 * * * /opt/makenotwork/sync-wal-offsite.sh >> /opt/makenotwork/wal-archive/sync.log 2>&1"
76