= 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 ==

* On the db server, create an ssh key for postgres:
{{{
  sudo -H -u postgres ssh-keygen -C "postgres@`hostname` (`date +%Y%m%d`)"
}}}
* clone dsa-misc:
{{{
  cd /usr/local && git -c http.sslCAInfo=/etc/ssl/ca-debian/ca-certificates.crt clone https://db.debian.org/git/dsa-misc.git &&
  cd bin && ln -s ../dsa-misc/scripts/pg-backup/pg* .
}}}
* Add these to /etc/postgresql/9.1/main/postgresql.conf
{{{
  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'
}}}

* On the backup server (storace as of 2015), add the ssh public key to /etc/ssh/userkeys/debbackup.
* Add the new server and cluster name to /etc/nagios/dsa-check-backuppg.conf.

== base backups ==

* On the db server, create a role.  Give a nice, long password.
{{{
  sudo -u postgres createuser -D -E -P -R -S debian-backup
}}}
* Give the role replication access:
{{{
  sudo -u postgres psql -c 'ALTER ROLE "debian-backup" REPLICATION;'
}}}
* Add an entry to pg_hba to allow access:
{{{
  hostssl replication     debian-backup         93.94.130.161/32                md5 # storace
}}}
* Ensure pg is listening on * and that connections from storace are allowed through the firewall.
* Ensure the server is using a proper debian auto-ca cert.
* Reload db server.

* Add host:port combination to postgres-make-base-backups in dsa-puppet.

* Test running "postgres-make-base-backups host:port".
* You should see a tarball and WALs

= Nagios warnings =

== BASE-IS-OLD ==

(2018-02) Our nagios check warns us when a backup server has not successfully fetched
a base backup recently.  The causes often are that either the postgres server or the
backup host went down or was down during the time of the weekly cronjob.

To re-run a base backup for a specific cluster, log into the backup server
(either storace or backuphost), cat /usr/local/bin/postgres-make-base-backups
to see the port for the cluster, and run
{{{
  sudo -u debbackup /usr/local/bin/postgres-make-base-backups <host>:<port>
}}}
probably best to do that in a screen as it might take a while.