WIP manual backup

4 files changed, 152 insertions, 0 deletions

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=\"<bind_dn>\" ldapbindpasswd=\"<bind_pwd>\" 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=\"<REDACTED>\" 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