= 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