3 Backing up postgres consists of two main pieces: backing up the WALs (write
4 ahead logs) and regular backups of the base. See
5 http://www.postgresql.org/docs/9.4/static/continuous-archiving.html
9 * On the db server, create an ssh key for postgres:
11 sudo -H -u postgres ssh-keygen -C "postgres@`hostname` (`date +%Y%m%d`)"
15 cd /usr/local && git clone https://db.debian.org/git/dsa-misc.git &&
16 cd bin && ln -s ../dsa-misc/scripts/pg-backup/pg* .
18 * Add these to /etc/postgresql/9.1/main/postgresql.conf
25 archive_command = '/usr/local/bin/pg-backup-file main WAL %p'
28 * On the backup server (storace as of 2015), add the ssh public key to /etc/ssh/userkeys/debbackup.
29 * Add the new server and cluster name to /etc/nagios/dsa-check-backuppg.conf.
33 * On the db server, create a role. Give a nice, long password.
35 sudo -u postgres createuser -D -E -P -R -S debian-backup
37 * Give the role replication access:
39 sudo -u postgres psql -c 'ALTER ROLE "debian-backup" REPLICATION;'
41 * Add an entry to pg_hba to allow access:
43 hostssl replication debian-backup 93.94.130.161/32 md5 # storace
45 * Ensure pg is listening on * and that connections from storace are allowed through the firewall.
46 * Ensure the server is using a proper debian auto-ca cert.
49 * Add host:port combination to postgres-make-base-backups in dsa-puppet.
51 * Test running "postgres-make-base-backups host:port".
52 * You should see a tarball and WALs