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 https://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 -c http.sslCAInfo=/etc/ssl/ca-debian/ca-certificates.crt 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
58 (2018-02) Our nagios check warns us when a backup server has not successfully fetched
59 a base backup recently. The causes often are that either the postgres server or the
60 backup host went down or was down during the time of the weekly cronjob.
62 To re-run a base backup for a specific cluster, log into the backup server
63 (either storace or backuphost), cat /usr/local/bin/postgres-make-base-backups
64 to see the port for the cluster, and run
66 sudo -u debbackup /usr/local/bin/postgres-make-base-backups <host>:<port>
68 probably best to do that in a screen as it might take a while.