From 2775eeb0feb2b443cbd05f9a6c79800685441fe0 Mon Sep 17 00:00:00 2001 From: Quentin Dufour Date: Thu, 27 Jan 2022 18:26:02 +0100 Subject: WIP manual backup --- op_guide/stolon/README.md | 3 ++ op_guide/stolon/create_database.md | 26 ++++++++++++++ op_guide/stolon/install.md | 72 ++++++++++++++++++++++++++++++++++++++ op_guide/stolon/manual_backup.md | 51 +++++++++++++++++++++++++++ 4 files changed, 152 insertions(+) create mode 100644 op_guide/stolon/README.md create mode 100644 op_guide/stolon/create_database.md create mode 100644 op_guide/stolon/install.md create mode 100644 op_guide/stolon/manual_backup.md (limited to 'op_guide/stolon') diff --git a/op_guide/stolon/README.md b/op_guide/stolon/README.md new file mode 100644 index 0000000..9e76b0e --- /dev/null +++ b/op_guide/stolon/README.md @@ -0,0 +1,3 @@ + - [Initialize the cluster](install.md) + - [Create a database](create_database.md) + - [Manually backup all the databases](manual_backup.md) diff --git a/op_guide/stolon/create_database.md b/op_guide/stolon/create_database.md new file mode 100644 index 0000000..fb3bdd9 --- /dev/null +++ b/op_guide/stolon/create_database.md @@ -0,0 +1,26 @@ +## 1. Create a LDAP user and assign a password for your service + +Go to guichet.deuxfleurs.fr + + 1. Everything takes place in `ou=services,ou=users,dc=deuxfleurs,dc=fr` + 2. Create a new user, like `johny` + 3. Generate a random password with `openssl rand -base64 32` + 4. Hash it with `slappasswd` + 5. Add a `userpassword` entry with the hash + +This step can also be done using the automated tool `secretmgr.py` in the app folder. + +## 2. Connect to postgres with the admin users + +```bash +# 1. Launch ssh tunnel given in the README +# 2. Make sure you have postregsql client installed locally +psql -h localhost -U postgres -W postgres +``` + +## 3. Create the binded users with LDAP in postgres + the database + +```sql +CREATE USER johny; +CREATE DATABASE amazingapp OWNER johny; +``` diff --git a/op_guide/stolon/install.md b/op_guide/stolon/install.md new file mode 100644 index 0000000..b511f59 --- /dev/null +++ b/op_guide/stolon/install.md @@ -0,0 +1,72 @@ +Spawn container: + +```bash +docker run -t -i superboum/arm32v7_postgres:v6 +# OR +docker run -t -i superboum/amd64_postgres:v1 +``` + + +Init with: + +``` +stolonctl \ + --cluster-name pissenlit \ + --store-backend=consul \ + --store-endpoints http://consul.service.2.cluster.deuxfleurs.fr:8500 \ + init \ + '{ "initMode": "new", "pgHBA": [ "host all postgres all md5", "host replication replicator all md5", "host all all all ldap ldapserver=bottin.service.2.cluster.deuxfleurs.fr ldapbasedn=\"ou=users,dc=deuxfleurs, dc=fr\" ldapbinddn=\"\" ldapbindpasswd=\"\" ldapsearchattribute=\"cn\"" ] }' + +``` + +Then set appropriate permission on host: + +``` +chown -R 102:102 /mnt/storage/postgres/ +``` + +(102 is the id of the postgres user used in Docker) +It might be improved by staying with root, then chmoding in an entrypoint and finally switching to user 102 before executing user's command. +Moreover it would enable the usage of the user namespace that shift the UIDs. + + + +## Upgrading the cluster + +To retreive the current stolon config: + +``` +stolonctl spec --cluster-name pissenlit --store-backend consul --store-endpoints http://consul.service.2.cluster.deuxfleurs.fr:8500 +``` + +The important part for the LDAP: + +``` +{ + "pgHBA": [ + "host all postgres all md5", + "host replication replicator all md5", + "host all all all ldap ldapserver=bottin.service.2.cluster.deuxfleurs.fr ldapbasedn=\"ou=users,dc=deuxfleurs,dc=fr\" ldapbinddn=\"cn=admin,dc=deuxfleurs,dc=fr\" ldapbindpasswd=\"\" ldapsearchattribute=\"cn\"" + ] +} +``` + +Once a patch is writen: + +``` +stolonctl --cluster-name pissenlit --store-backend consul --store-endpoints http://consul.service.2.cluster.deuxfleurs.fr:8500 update --patch -f /tmp/patch.json +``` + +## Log + +- 2020-12-18 Activate pg\_rewind in stolon + +``` +stolonctl --cluster-name pissenlit --store-backend consul --store-endpoints http://consul.service.2.cluster.deuxfleurs.fr:8500 update --patch '{ "usePgrewind" : true }' +``` + +- 2021-03-14 Increase proxy timeout to cope with consul latency spikes + +``` +stolonctl --cluster-name pissenlit --store-backend consul --store-endpoints http://consul.service.2.cluster.deuxfleurs.fr:8500 update --patch '{ "proxyTimeout" : "120s" }' +``` diff --git a/op_guide/stolon/manual_backup.md b/op_guide/stolon/manual_backup.md new file mode 100644 index 0000000..b952174 --- /dev/null +++ b/op_guide/stolon/manual_backup.md @@ -0,0 +1,51 @@ +## Disclaimer + +Do **NOT** use the following backup methods on the Stolon Cluster: + 1. copying the data directory + 2. `pg_dump` + 3. `pg_dumpall` + +The first one will lead to corrupted/inconsistent files. +The second and third ones put too much pressure on the cluster. +Basically, you will destroy it, in the following ways: + - Load will increase, requests will timeout + - RAM will increase, the daemon will be OOM (Out Of Memory) killed by Linux + - Potentially, the WAL log will grow a lot + + +## A binary backup with `pg_basebackup` + +The only acceptable solution is `pg_basebackup` with **some throttling configured**. +Later, if you want a SQL dump, you can inject this binary backup on an ephemeral database you spawned solely for this purpose on a non-production machine. + +First, start by fetching from Consul the identifiers of the replication account. +Do not use the root account setup in Stolon, it will not work. + +First setup a SSH tunnel on your machine that bind postgresql, eg: + +```bash +ssh -L 5432:psql-proxy.service.2.cluster.deuxfleurs.fr:5432 ... +``` + +Then export your password in `PGPASSWORD` and launch the backup: + +```bash +export PGPASSWORD=xxx + +pg_basebackup \ + --host=127.0.0.1 \ + --username=replicator \ + --pgdata=/tmp/sql \ + --format=tar \ + --wal-method=none \ + --gzip \ + --compress=6 \ + --progress \ + --max-rate=2M +``` + +*Take a cup of coffe, it will take some times...* + +## Importing the backup + +## Dump SQL -- cgit v1.2.3