pg backup update

diff --git a/input/howto/postgres-backup.creole b/input/howto/postgres-backup.creole
index fee97c6..9c07596 100644 (file)
--- a/input/howto/postgres-backup.creole
+++ b/input/howto/postgres-backup.creole
@@ -4,50 +4,84 @@ 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 ==
+The WALs are copied from the postgres server to the backup hosts using ssh with
+the {{{pg-backup-file}}} script.  Base backups are pulled on the backup hosts
+using {{{postgres-make-base-backups}}}.  The former requires the postgres servers
+be able to ssh to the backup servers, and the latter requires the postgres server
+listen on the network, have ssl set up correctly, access is allowed in the firewall,
+a postgres user with replication privileges exists and is configured to allowed
+to connect in {{{pg_hba}}}.
 
-* 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
+== server config ==
+
+(2018-02 - This documentation has not really been tested since it was written -- weasel)
+
+There are two possible ways to configure the server.
+
+=== Single cluster on a host ===
+
+If there is only one cluster on the host, we can use puppet's
+{{{postgresql::server}}} to configure the cluster and any databases on it, see
+{{{modules/salsa/manifests/database.pp}}} for an example.  In particular, you
+want to set {{{archive_command}}} and the ssl options in {{{pg.conf}}}, as well
+as set {{{listen_address}}} correctly.
+
+Add a {{{postgres::backup_cluster}}} stanza to get it backed up.
+
+=== Multiple clusters/compatibility mode ===
+
+If there is potentially more than one cluster, we cannot use the puppet
+{{{postgresql::server}}} class.  We also use this for clusters that were
+initially set up without puppet.
+
+* Add the server to the postgresql_server role in puppet's
+  hieradata/common.yaml.  This will cause some scripts to be installed on the
+  host, as well as an ssh key to be created for the postgres user.
+
+* Add these to {{{/etc/postgresql/9.6/main/postgresql.conf}}} or equivalent
 {{{
   track_counts = yes
-  archive_mode = yes
+  archive_mode = on
   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.
+* Run puppet on the postgresql server,
 
-== base backups ==
+==== ssh authkeys ====
+* If you need extra options in the {{{debbackup-ssh-wrap}}} call on the backup server
+  (for instance of the host should be allowed to fetch files), manually copy
+  {{{~postgres/.ssh/id_rsa.pub}}} to
+  {{{puppet:modules/postgres/templates/backup_server/sshkeys-manual.erb}}}.
+* Otherwise, add the host to the postgres::backup_server::register_backup_clienthost line
+  in {{{puppet:modules/postgres/manifests/backup_source.pp}}}.
 
-* 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.
+==== base backup config ====
 
-* Add host:port combination to postgres-make-base-backups in dsa-puppet.
+* Register each cluster in puppet's
+  {{{puppet:modules/postgres/manifests/backup_source.pp}}}.
+  This takes care of adding the replication user to pgpass on the backup servers,
+  and the firewall rule and adds the cluster to {{{make-base-backups}}}.
+  (The module can also create the postgres role and modify the hba file, but we
+  do not do this when we don't configure the entire cluster via puppet.)
+* Historically, we also have clusters hardcoded in
+  {{{puppet:modules/postgres/templates/backup_server/postgres-make-base-backups.erb}}}.
+* Run puppet on the backup hosts (storace and backuphost as of 2018).
 
+* On the db server, create a role.  Find the password to use on the backup host in {{{~debbackup/.pgpass}}}:\\
+  {{{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   5.153.231.12/32                 md5 # backuphost
+hostssl replication     debian-backup   2001:41c8:1000:21::21:12/128    md5 # backuphost
+hostssl replication     debian-backup   93.94.130.161/32                md5 # storace
+hostssl replication     debian-backup   2a02:158:380:280::161/128       md5 # storace}}}
+* Ensure pg is listening on *.
+* Ensure the server is using ssl and a proper debian auto-ca cert.
+* Reload db server.
 * Test running "postgres-make-base-backups host:port".
 * You should see a tarball and WALs