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
- 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 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.
- 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 220.127.116.11/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