X-Git-Url: https://git.adam-barratt.org.uk/?p=mirror%2Fdsa-wiki.git;a=blobdiff_plain;f=input%2Fhowto%2Fpostgres-backup.creole;fp=input%2Fhowto%2Fpostgres-backup.creole;h=d6957aa50e09d607fd64f1f587595a2353286009;hp=0000000000000000000000000000000000000000;hb=1c33e078e83271d90c0877a0f7351871b3c49d4f;hpb=f4785ef44c93c6e0cc59c1f3857d076ed0743d44

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