From 1c33e078e83271d90c0877a0f7351871b3c49d4f Mon Sep 17 00:00:00 2001
From: Peter Palfrader <peter@palfrader.org>
Date: Sat, 21 Mar 2015 09:55:30 +0100
Subject: [PATCH] pg backup howto

---
 input/howto/postgres-backup.creole | 52 ++++++++++++++++++++++++++++++
 input/index.mdwn                   |  1 +
 2 files changed, 53 insertions(+)
 create mode 100644 input/howto/postgres-backup.creole

diff --git a/input/howto/postgres-backup.creole b/input/howto/postgres-backup.creole
new file mode 100644
index 0000000..d6957aa
--- /dev/null
+++ b/input/howto/postgres-backup.creole
@@ -0,0 +1,52 @@
+= Postgres backup =
+
+Backing up postgres consists of two main pieces: backing up the WALs (write
+ahead logs) and regular backups of the base.  See
+http://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 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
diff --git a/input/index.mdwn b/input/index.mdwn
index 24044c8..f29f378 100644
--- a/input/index.mdwn
+++ b/input/index.mdwn
@@ -50,6 +50,7 @@ ticket.
 * [[howto/export-iscsi]]: how to export new iscsi LUNs
 * [[howto/install-kvm]]: how to setup a new kvm domain without going through d-i etc.
 * [[howto/postgres]]: Random postgres stuff
+* [[howto/postgres-backup]]: Less random postgres stuff
 * [[howto/add-guest]]: how to add a guest to ud-ldap
 * [[howto/add-account]]: how to add an account to ud-ldap (also: how to upgrade a guest)
 * [[howto/lock-account]]: how to lock an account in ud-ldap
-- 
2.20.1