Skip to content

Instantly share code, notes, and snippets.

@capocasa
Last active January 1, 2024 12:36
Show Gist options
  • Save capocasa/b891407652fce9073f9ccd6f0bf97339 to your computer and use it in GitHub Desktop.
Save capocasa/b891407652fce9073f9ccd6f0bf97339 to your computer and use it in GitHub Desktop.
Triple-Redundant Hetzner Debian postgres setup
##### This is how to set up a triple redundant postgres.
##### Use a main box on Hetzner, a secondary small box on Hetzner in a
##### different datacenter for query replication, and a Hetzner Storage-Box
##### for file replication.
##### There will always be two copies of the data in different datacenters by the time
##### the transaction completes.
##### Here, Wireguard is used to have an internal network between the two hosts,
##### but Hetzner networks could also be used. SSH access between the two boxes
##### is *not* needed.
#### basic variables
PRIMARY_IP=10.0.0.1
SECONDARY_IP=10.0.0.2
STORAGEBOX_PASSWORD=mySecretPassword
STORAGEBOX_HOST=mySubdomain.your-storagebox.de
STORAGEBOX_DIR=myBackupDirectory
#### RUN ON MAIN SERVER
### install postgres
apt -y install postgresql
### add basic configuration
echo "
listen_addresses = '$PRIMARY_IP'
# configure wal settings for "hot standby" *and* "continuous archiving"
max_wal_senders = 10
wal_level = replica
wal_log_hints = on
max_replication_slots = 10
# return from query when written on both
synchronous_commit = on
synchronous_standby_names = '*'
" > /etc/postgresql/13/main/conf.d/replication.conf
### replication configuration
echo "host replication all $SECONDARY_IP/32 trust" >> /etc/postgresql/13/main/pg_hba.conf
systemctl restart postgresql
#### set up storage box
# scp storage box access for postgres user
< /dev/zero sudo -Hu postgres ssh-keygen -q -N ""
curl -k sftp://$STORAGEBOX_HOST/.ssh/authorized_keys --user $STORAGEBOX_USER:$STORAGEBOX_PASSWORD -T /var/lib/postgres/.ssh/id_rsa.pub --ftp-create-dirs
curl -k sftp://$STORAGEBOX_HOST --user $STORAGEBOX_USER:$STORAGEBOX_PASSWORD -Q "CHMOD 700 .ssh"
curl -k sftp://$STORAGEBOX_HOST --user $STORAGEBOX_USER:$STORAGEBOX_PASSWORD -Q "CHMOD 600 .ssh/authorized_keys"
echo "
mkdir $STORAGEBOX_DIR
mkdir $STORAGEBOX_DIR/wal
mkdir $STORAGEBOX_DIR/fs
mkdir $STORAGEBOX_DIR/basebackup
" | sudo -Hu postgres sftp -P 23 $STORAGEBOX_USER@$STORAGEBOX_HOST
echo "
wal_level = replica
archive_mode = on
archive_command = 'rsync -aessh\\ -p23 --info=name --ignore-existing %p $STORAGEBOX_USER@$STORAGEBOX_HOST:$STORAGEBOX_DIR/wal/%f | grep . '
" > /etc/postgresql/13/main/conf.d/archive.conf
systemctl restart postgresql
#### RUN ON STANDBY
apt -y install postgresql
systemctl stop postgresql
echo "
listen_addresses = '$SECONDARY_IP'
max_wal_senders = 10
wal_level = replica
wal_log_hints = on
max_replication_slots = 10
hot_standby = on
primary_conninfo = 'postgres://postgres@$PRIMARY_IP'
primary_slot_name = 'a1'
synchronous_commit = on
synchronous_standby_names = '*'
" > /etc/postgresql/13/main/conf.d/replication.conf
rm -rf /var/lib/postgresql/13/main
pg_basebackup -h 10.0.0.1 -D /var/lib/postgresql/13/main -U postgres -v -P -R -X stream -c fast
touch /var/lib/postgresql/13/main/standby.signal
chown -R postgres.postgres /var/lib/postgresql/13/main
systemctl start postgresql
### Weekly full backup
echo "$!/bin/bash
set -euxo pipefail
FILE=basebackup-$(date --rfc-3339=seconds).tar.zstd
pg_basebackup -D - -U postgres -Ft -v -P -R -X fetch -c fast -l l1t | zstd > /tmp/$FILE
scp -P23 /tmp/$FILE $STORAGEBOX_USER@$STORAGEBOX_HOST:$STORAGEBOX_DIR/basebackup
rm /tmp/$FILE
" > /etc/backup-database.sh
chmod +x /etc/backup-database.sh
echo "33 3 * * * postgres /etc/backup-database.sh" > /etc/cron.d/backup-database
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment