Triple-Redundant Hetzner Debian postgres setup

postgres-database-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