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 Since we often have more than one cluster, we cannot use the puppet
  34 {{{postgresql::server}}} class for most things.
  35
  36 * Add the server to the roles::postgresql::server class role in hiera.
  37   This will cause some scripts to be installed on the host, as well as an ssh
  38   key to be created for the postgres user.
  39
  40 * Add these to {{{/etc/postgresql/9.6/main/postgresql.conf}}} or equivalent
  41 {{{
  42   track_counts = yes
  43   archive_mode = on
  44   wal_level = archive
  45   max_wal_senders = 3
  46   archive_timeout = 1h
  47   archive_command = '/usr/local/bin/pg-backup-file mXXXXXX-CLUSTERNAMEHERE-XXXXain WAL %p'
  48 }}}
  49
  50 * Run puppet on the postgresql server,
  51
  52 * If the server is a replication receiver, it needs read access to the sender's WALs
  53   on the backup host (to recover from situations where the source might no longer
  54   have the WALs.)  This can be configured via hiera as well.  Example:
  55 {{{
  56 [git|master] weasel@orinoco:~/projects/debian/d-a/dsa-puppet$ cat data/nodes/snapshotdb-manda-01.debian.org.yaml
  57 classes:
  58   - roles::snapshot_db
  59   - roles::postgresql::server
  60
  61 postgres::backup_server::register_backup_clienthost::allow_read_hosts: ['sallinen']
  62 }}}
  63
  64 ==== base backup config ====
  65
  66 * Run puppet on the backup hosts (storace and backuphost as of 2019).
  67
  68 * On the db server, create a role.  Find the password to use on the backup host in {{{~debbackup/.pgpass}}}:\\
  69   {{{sudo -u postgres createuser -D -E -P -R -S debian-backup}}}
  70 * Give the role replication access:\\
  71   {{{sudo -u postgres psql -c 'ALTER ROLE "debian-backup" REPLICATION;'}}}
  72 * Add an entry to pg_hba to allow access:\\
  73   {{{hostssl replication     debian-backup   5.153.231.12/32                 md5 # backuphost
  74 hostssl replication     debian-backup   2001:41c8:1000:21::21:12/128    md5 # backuphost
  75 hostssl replication     debian-backup   93.94.130.161/32                md5 # storace
  76 hostssl replication     debian-backup   2a02:158:380:280::161/128       md5 # storace}}}
  77 * Ensure pg is listening on *.
  78 * Ensure the server is using ssl and a proper debian auto-ca cert.
  79 * Reload db server.
  80 * Test running "postgres-make-base-backups host:port".
  81 * You should see a tarball and WALs
  82
  83 = Nagios warnings =
  84
  85 == BASE-IS-OLD ==
  86
  87 (2018-02) Our nagios check warns us when a backup server has not successfully fetched
  88 a base backup recently.  The causes often are that either the postgres server or the
  89 backup host went down or was down during the time of the weekly cronjob.
  90
  91 To re-run a base backup for a specific cluster, log into the backup server
  92 (either storace or backuphost), cat /usr/local/bin/postgres-make-base-backups
  93 to see the port for the cluster, and run
  94 {{{
  95   sudo -u debbackup /usr/local/bin/postgres-make-base-backups <host>:<port>
  96 }}}
  97 probably best to do that in a screen as it might take a while.
  98
  99 == MISSING-BASE ==
 100
 101 e.g.:
 102 {{{
 103 sudo -u debbackup /usr/lib/nagios/plugins/dsa-check-backuppg  | grep BASE
 104 [fasolo, dak] MISSING-BASE: dak.BASE.backuphost.debian.org-20180211-012002-fasolo.debian.org-dak-9.6-backup.tar.gz
 105 }}}
 106
 107 This means that we started doing a base backup (as witnessed by a .backup file
 108 next to a WAL), but for some reason we don't have the corresponding base file.
 109 {{{
 110 root@backuphost:/srv/backups/pg/fasolo# ls -l *backup*
 111 -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
 112 -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
 113 -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
 114 -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
 115 -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
 116 -rw------- 1 debbackup debbackup        353 Jan 14 06:18 dak.WAL.0000000100000033000000A6.00000028.backup
 117 -rw------- 1 debbackup debbackup        350 Jan 20 11:20 dak.WAL.00000001000000350000008C.00000028.backup
 118 -rw------- 1 debbackup debbackup        353 Jan 21 06:25 dak.WAL.000000010000003600000068.00000028.backup
 119 -rw------- 1 debbackup debbackup        353 Jan 28 07:59 dak.WAL.0000000100000038000000E3.00000028.backup
 120 -rw------- 1 debbackup debbackup        353 Feb  5 11:00 dak.WAL.000000010000003B00000090.00000028.backup
 121 -rw------- 1 debbackup debbackup        350 Feb  5 15:49 dak.WAL.000000010000003B0000009B.00000108.backup
 122 -rw------- 1 debbackup debbackup        353 Feb 11 10:09 dak.WAL.000000010000003D000000AC.00000028.backup
 123 -rw------- 1 debbackup debbackup        353 Feb 12 10:25 dak.WAL.000000010000003E00000027.00000178.backup
 124 }}}
 125
 126 {{{.backup}}} files are created on the postgres server and shipped to the
 127 backup hosts whenever a base backup is initiated.  We do some labelling, so
 128 we know which backup host the corresponding tarball should end up with.
 129
 130 e.g.:
 131 {{{
 132 root@backuphost:/srv/backups/pg/fasolo# cat dak.WAL.000000010000003B00000090.00000028.backup
 133 START WAL LOCATION: 3B/90000028 (file 000000010000003B00000090)
 134 STOP WAL LOCATION: 3B/97CF2138 (file 000000010000003B00000097)
 135 CHECKPOINT LOCATION: 3B/90000098
 136 BACKUP METHOD: streamed
 137 BACKUP FROM: master
 138 START TIME: 2018-02-05 10:25:28 UTC
 139 LABEL: backuphost.debian.org-20180204-012001-fasolo.debian.org-dak-9.6-backup
 140 STOP TIME: 2018-02-05 10:59:50 UTC
 141 }}}
 142
 143 To fix this, verify we have a later base tarball, or that we are fine for some other reason,
 144 and remove the corresponding .backup file.  In the case above, we would remove
 145 {{{dak.WAL.000000010000003D000000AC.00000028.backup}}}.
 146
 147 == WAL-MISSING-AFTER ==
 148
 149 e.g.:
 150 {{{
 151 [bmdb1, main] WAL-MISSING-AFTER: bmdb1/main.WAL.0000000100001340000000DB
 152 }}
 153
 154 If it's just one WAL file missing, it can be recovered from the other backup host.  If more logs are missing, check the server's logs for archive errors.