Postgres backup

Backing up postgres consists of two main pieces: backing up the WALs (write ahead logs) and regular backups of the base. See https://www.postgresql.org/docs/9.4/static/continuous-archiving.html

WAL Shipping

  sudo -H -u postgres ssh-keygen -C "postgres@`hostname` (`date +%Y%m%d`)"
  cd /usr/local && git clone https://db.debian.org/git/dsa-misc.git &&
  cd bin && ln -s ../dsa-misc/scripts/pg-backup/pg* .
  track_counts = yes
  archive_mode = yes
  wal_level = archive
  max_wal_senders = 3
  archive_timeout = 1h
  archive_command = '/usr/local/bin/pg-backup-file main WAL %p'

base backups

  sudo -u postgres createuser -D -E -P -R -S debian-backup
  sudo -u postgres psql -c 'ALTER ROLE "debian-backup" REPLICATION;'
  hostssl replication     debian-backup         93.94.130.161/32                md5 # storace