input/howto/postgres-backup.creole

   1 = Postgres backup =
   2
   3 Backing up postgres consists of two main pieces: backing up the WALs (write
   4 ahead logs) and regular backups of the base.  See
   5 https://www.postgresql.org/docs/9.4/static/continuous-archiving.html
   6
   7 The WALs are copied from the postgres server to the backup hosts using ssh with
   8 the {{{pg-backup-file}}} script.  Base backups are pulled on the backup hosts
   9 using {{{postgres-make-base-backups}}}.  The former requires the postgres servers
  10 be able to ssh to the backup servers, and the latter requires the postgres server
  11 listen on the network, have ssl set up correctly, access is allowed in the firewall,
  12 a postgres user with replication privileges exists and is configured to allowed
  13 to connect in {{{pg_hba}}}.
  14
  15 == server config ==
  16
  17 (2018-02 - This documentation has not really been tested since it was written -- weasel)
  18
  19 There are two possible ways to configure the server.
  20
  21 === Single cluster on a host ===
  22
  23 If there is only one cluster on the host, we can use puppet's
  24 {{{postgresql::server}}} to configure the cluster and any databases on it, see
  25 {{{modules/salsa/manifests/database.pp}}} for an example.  In particular, you
  26 want to set {{{archive_command}}} and the ssl options in {{{pg.conf}}}, as well
  27 as set {{{listen_address}}} correctly.
  28
  29 Add a {{{postgres::backup_cluster}}} stanza to get it backed up.
  30
  31 === Multiple clusters/compatibility mode ===
  32
  33 If there is potentially more than one cluster, we cannot use the puppet
  34 {{{postgresql::server}}} class.  We also use this for clusters that were
  35 initially set up without puppet.
  36
  37 * Add the server to the postgresql_server role in puppet's
  38   hieradata/common.yaml.  This will cause some scripts to be installed on the
  39   host, as well as an ssh key to be created for the postgres user.
  40
  41 * Add these to {{{/etc/postgresql/9.6/main/postgresql.conf}}} or equivalent
  42 {{{
  43   track_counts = yes
  44   archive_mode = on
  45   wal_level = archive
  46   max_wal_senders = 3
  47   archive_timeout = 1h
  48   archive_command = '/usr/local/bin/pg-backup-file main WAL %p'
  49 }}}
  50
  51 * Run puppet on the postgresql server,
  52
  53 ==== ssh authkeys ====
  54 * If you need extra options in the {{{debbackup-ssh-wrap}}} call on the backup server
  55   (for instance of the host should be allowed to fetch files), manually copy
  56   {{{~postgres/.ssh/id_rsa.pub}}} to
  57   {{{puppet:modules/postgres/templates/backup_server/sshkeys-manual.erb}}}.
  58 * Otherwise, add the host to the postgres::backup_server::register_backup_clienthost line
  59   in {{{puppet:modules/postgres/manifests/backup_source.pp}}}.
  60
  61 ==== base backup config ====
  62
  63 * Register each cluster in puppet's
  64   {{{puppet:modules/postgres/manifests/backup_source.pp}}}.
  65   This takes care of adding the replication user to pgpass on the backup servers,
  66   and the firewall rule and adds the cluster to {{{make-base-backups}}}.
  67   (The module can also create the postgres role and modify the hba file, but we
  68   do not do this when we don't configure the entire cluster via puppet.)
  69 * Historically, we also have clusters hardcoded in
  70   {{{puppet:modules/postgres/templates/backup_server/postgres-make-base-backups.erb}}}.
  71 * Run puppet on the backup hosts (storace and backuphost as of 2018).
  72
  73 * On the db server, create a role.  Find the password to use on the backup host in {{{~debbackup/.pgpass}}}:\\
  74   {{{sudo -u postgres createuser -D -E -P -R -S debian-backup}}}
  75 * Give the role replication access:\\
  76   {{{sudo -u postgres psql -c 'ALTER ROLE "debian-backup" REPLICATION;'}}}
  77 * Add an entry to pg_hba to allow access:\\
  78   {{{hostssl replication     debian-backup   5.153.231.12/32                 md5 # backuphost
  79 hostssl replication     debian-backup   2001:41c8:1000:21::21:12/128    md5 # backuphost
  80 hostssl replication     debian-backup   93.94.130.161/32                md5 # storace
  81 hostssl replication     debian-backup   2a02:158:380:280::161/128       md5 # storace}}}
  82 * Ensure pg is listening on *.
  83 * Ensure the server is using ssl and a proper debian auto-ca cert.
  84 * Reload db server.
  85 * Test running "postgres-make-base-backups host:port".
  86 * You should see a tarball and WALs
  87
  88 = Nagios warnings =
  89
  90 == BASE-IS-OLD ==
  91
  92 (2018-02) Our nagios check warns us when a backup server has not successfully fetched
  93 a base backup recently.  The causes often are that either the postgres server or the
  94 backup host went down or was down during the time of the weekly cronjob.
  95
  96 To re-run a base backup for a specific cluster, log into the backup server
  97 (either storace or backuphost), cat /usr/local/bin/postgres-make-base-backups
  98 to see the port for the cluster, and run
  99 {{{
 100   sudo -u debbackup /usr/local/bin/postgres-make-base-backups <host>:<port>
 101 }}}
 102 probably best to do that in a screen as it might take a while.
 103
 104 == MISSING-BASE ==
 105
 106 e.g.:
 107 {{{
 108 sudo -u debbackup /usr/lib/nagios/plugins/dsa-check-backuppg  | grep BASE
 109 [fasolo, dak] MISSING-BASE: dak.BASE.backuphost.debian.org-20180211-012002-fasolo.debian.org-dak-9.6-backup.tar.gz
 110 }}}
 111
 112 This means that we started doing a base backup (as witnessed by a .backup file
 113 next to a WAL), but for some reason we don't have the corresponding base file.
 114 {{{
 115 root@backuphost:/srv/backups/pg/fasolo# ls -l *backup*
 116 -rw------- 1 debbackup debbackup 9201093916 Jan 14 06:18 dak.BASE.backuphost.debian.org-20180114-012001-fasolo.debian.org-dak-9.6-backup.tar.gz
 117 -rw------- 1 debbackup debbackup 9227651542 Jan 21 06:25 dak.BASE.backuphost.debian.org-20180121-012001-fasolo.debian.org-dak-9.6-backup.tar.gz
 118 -rw------- 1 debbackup debbackup 9266306750 Jan 28 07:59 dak.BASE.backuphost.debian.org-20180128-012001-fasolo.debian.org-dak-9.6-backup.tar.gz
 119 -rw------- 1 debbackup debbackup 9312602089 Feb  5 11:00 dak.BASE.backuphost.debian.org-20180204-012001-fasolo.debian.org-dak-9.6-backup.tar.gz
 120 -rw------- 1 debbackup debbackup 9346830509 Feb 12 10:25 dak.BASE.backuphost.debian.org-20180212-094930-fasolo.debian.org-dak-9.6-backup.tar.gz
 121 -rw------- 1 debbackup debbackup        353 Jan 14 06:18 dak.WAL.0000000100000033000000A6.00000028.backup
 122 -rw------- 1 debbackup debbackup        350 Jan 20 11:20 dak.WAL.00000001000000350000008C.00000028.backup
 123 -rw------- 1 debbackup debbackup        353 Jan 21 06:25 dak.WAL.000000010000003600000068.00000028.backup
 124 -rw------- 1 debbackup debbackup        353 Jan 28 07:59 dak.WAL.0000000100000038000000E3.00000028.backup
 125 -rw------- 1 debbackup debbackup        353 Feb  5 11:00 dak.WAL.000000010000003B00000090.00000028.backup
 126 -rw------- 1 debbackup debbackup        350 Feb  5 15:49 dak.WAL.000000010000003B0000009B.00000108.backup
 127 -rw------- 1 debbackup debbackup        353 Feb 11 10:09 dak.WAL.000000010000003D000000AC.00000028.backup
 128 -rw------- 1 debbackup debbackup        353 Feb 12 10:25 dak.WAL.000000010000003E00000027.00000178.backup
 129 }}}
 130
 131 {{{.backup}}} files are created on the postgres server and shipped to the
 132 backup hosts whenever a base backup is initiated.  We do some labelling, so
 133 we know which backup host the corresponding tarball should end up with.
 134
 135 e.g.:
 136 {{{
 137 root@backuphost:/srv/backups/pg/fasolo# cat dak.WAL.000000010000003B00000090.00000028.backup
 138 START WAL LOCATION: 3B/90000028 (file 000000010000003B00000090)
 139 STOP WAL LOCATION: 3B/97CF2138 (file 000000010000003B00000097)
 140 CHECKPOINT LOCATION: 3B/90000098
 141 BACKUP METHOD: streamed
 142 BACKUP FROM: master
 143 START TIME: 2018-02-05 10:25:28 UTC
 144 LABEL: backuphost.debian.org-20180204-012001-fasolo.debian.org-dak-9.6-backup
 145 STOP TIME: 2018-02-05 10:59:50 UTC
 146 }}}
 147
 148 To fix this, verify we have a later base tarball, or that we are fine for some other reason,
 149 and remove the corresponding .backup file.  In the case above, we would remove
 150 {{{dak.WAL.000000010000003D000000AC.00000028.backup}}}.