From 7e5376fb8003f358cd16b7ee9e787f35d5b34514 Mon Sep 17 00:00:00 2001 From: Peter Palfrader Date: Mon, 5 Feb 2018 10:43:26 +0100 Subject: [PATCH] pg backup update --- input/howto/postgres-backup.creole | 96 ++++++++++++++++++++---------- 1 file changed, 65 insertions(+), 31 deletions(-) diff --git a/input/howto/postgres-backup.creole b/input/howto/postgres-backup.creole index fee97c6..9c07596 100644 --- 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 -- 2.20.1