aboutsummaryrefslogtreecommitdiff
path: root/app/postgres
diff options
context:
space:
mode:
authorQuentin <quentin@dufour.io>2021-01-18 08:18:21 +0100
committerQuentin <quentin@dufour.io>2021-01-18 08:18:21 +0100
commitad6017eea058f7cb6fdf078783f992a4f45a3e15 (patch)
tree6620bcc9e1ea61a5689b763b9ad8280275e35e76 /app/postgres
parent79b7273ff2a487d6721d393682c8ad3927467a75 (diff)
parentc642370def01f09d966b3b9c643cfe416ea115cf (diff)
downloadinfrastructure-ad6017eea058f7cb6fdf078783f992a4f45a3e15.tar.gz
infrastructure-ad6017eea058f7cb6fdf078783f992a4f45a3e15.zip
Merge pull request 'Reorganize app/ and add script for secret management' (#29) from test_reorganize into master
Reviewed-on: https://git.deuxfleurs.fr/Deuxfleurs/infrastructure/pulls/29
Diffstat (limited to 'app/postgres')
-rw-r--r--app/postgres/build/postgres/Dockerfile19
-rw-r--r--app/postgres/build/postgres/README.md4
-rw-r--r--app/postgres/build/postgres/postgresql.conf25
-rwxr-xr-xapp/postgres/build/postgres/start.sh22
-rw-r--r--app/postgres/config/keeper/env.tpl3
-rw-r--r--app/postgres/deploy/postgres.hcl134
-rw-r--r--app/postgres/secrets/postgres/keeper/pg_repl_pwd1
-rw-r--r--app/postgres/secrets/postgres/keeper/pg_repl_username1
-rw-r--r--app/postgres/secrets/postgres/keeper/pg_su_pwd1
9 files changed, 210 insertions, 0 deletions
diff --git a/app/postgres/build/postgres/Dockerfile b/app/postgres/build/postgres/Dockerfile
new file mode 100644
index 0000000..bb018b8
--- /dev/null
+++ b/app/postgres/build/postgres/Dockerfile
@@ -0,0 +1,19 @@
+FROM amd64/debian:stretch
+
+RUN echo "deb http://deb.debian.org/debian stretch-backports main contrib non-free # available after stretch release" > /etc/apt/sources.list.d/stretch-backports.list && \
+ apt-get update && \
+ apt-get -qq -y full-upgrade && \
+ apt-get install -y postgresql-all golang-1.11 git && \
+ export GOPATH=/usr/local/go && \
+ mkdir -p /usr/local/go/src/github.com/sorintlab && \
+ cd /usr/local/go/src/github.com/sorintlab && \
+ git clone --depth=1 https://github.com/sorintlab/stolon && \
+ ln -s /usr/lib/go-1.11/bin/go /usr/bin/go && \
+ ln -s /usr/lib/go-1.11/bin/gofmt /usr/bin/gofmt && \
+ cd ./stolon && \
+ ./build && \
+ mv /usr/local/go/src/github.com/sorintlab/stolon/bin/* /usr/local/bin/ && \
+ rm -rf /usr/local/go
+
+USER postgres
+
diff --git a/app/postgres/build/postgres/README.md b/app/postgres/build/postgres/README.md
new file mode 100644
index 0000000..d2f7a12
--- /dev/null
+++ b/app/postgres/build/postgres/README.md
@@ -0,0 +1,4 @@
+```
+docker build -t superboum/arm32v7_postgres .
+docker build -t superboum/amd64_postgres:v2 .
+```
diff --git a/app/postgres/build/postgres/postgresql.conf b/app/postgres/build/postgres/postgresql.conf
new file mode 100644
index 0000000..8e0af2b
--- /dev/null
+++ b/app/postgres/build/postgres/postgresql.conf
@@ -0,0 +1,25 @@
+data_directory = '/var/lib/postgresql/9.6/main' # use data in another directory
+hba_file = '/etc/postgresql/9.6/main/pg_hba.conf' # host-based authentication file
+ident_file = '/etc/postgresql/9.6/main/pg_ident.conf' # ident configuration file
+external_pid_file = '/var/run/postgresql/9.6-main.pid' # write an extra PID file
+listen_addresses = '*' #listen on every ip / interfaces
+port = 5432 # (change requires restart)
+max_connections = 100 # (change requires restart)
+unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
+ssl = true # (change requires restart)
+ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' # (change requires restart)
+ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires restart)
+shared_buffers = 128MB # min 128kB
+dynamic_shared_memory_type = posix # the default is the first option
+log_line_prefix = '%m [%p] %q%u@%d ' # special values:
+log_timezone = 'UTC'
+cluster_name = '9.6/main' # added to process titles if nonempty
+stats_temp_directory = '/var/run/postgresql/9.6-main.pg_stat_tmp'
+datestyle = 'iso, mdy'
+timezone = 'UTC'
+lc_messages = 'C.UTF-8' # locale for system error message
+lc_monetary = 'C.UTF-8' # locale for monetary formatting
+lc_numeric = 'C.UTF-8' # locale for number formatting
+lc_time = 'C.UTF-8' # locale for time formatting
+default_text_search_config = 'pg_catalog.english'
+
diff --git a/app/postgres/build/postgres/start.sh b/app/postgres/build/postgres/start.sh
new file mode 100755
index 0000000..f1d493f
--- /dev/null
+++ b/app/postgres/build/postgres/start.sh
@@ -0,0 +1,22 @@
+#!/bin/bash
+
+if [ -f /local/pg_hba.conf ]; then
+ echo "Copying Nomad configuration..."
+ cp /local/pg_hba.conf /etc/postgresql/9.6/main/
+ echo "Done"
+fi
+
+
+if [ -z "$(ls -A /var/lib/postgresql/9.6/main)" ]; then
+ echo "Copying base"
+ cp -r /var/lib/postgresql/9.6/base/* /var/lib/postgresql/9.6/main
+ echo "Done"
+fi
+
+chmod -R 700 /var/lib/postgresql/9.6/main
+chown -R postgres /var/lib/postgresql/9.6/main
+
+echo "Starting postgres..."
+. /usr/share/postgresql-common/init.d-functions
+start 9.6
+tail -f /var/log/postgresql/postgresql-9.6-main.log
diff --git a/app/postgres/config/keeper/env.tpl b/app/postgres/config/keeper/env.tpl
new file mode 100644
index 0000000..7831aad
--- /dev/null
+++ b/app/postgres/config/keeper/env.tpl
@@ -0,0 +1,3 @@
+PG_SU_PWD={{ key "secrets/postgres/keeper/pg_su_pwd" | trimSpace }}
+PG_REPL_USER={{ key "secrets/postgres/keeper/pg_repl_username" | trimSpace }}
+PG_REPL_PWD={{ key "secrets/postgres/keeper/pg_repl_pwd" | trimSpace }}
diff --git a/app/postgres/deploy/postgres.hcl b/app/postgres/deploy/postgres.hcl
new file mode 100644
index 0000000..f5eec51
--- /dev/null
+++ b/app/postgres/deploy/postgres.hcl
@@ -0,0 +1,134 @@
+job "postgres" {
+ datacenters = ["dc1"]
+ type = "system"
+ priority = 90
+
+ update {
+ max_parallel = 1
+ stagger = "2m"
+ }
+
+ group "postgres" {
+ network {
+ port "psql_proxy_port" { static = 5432 }
+ port "psql_port" { static = 5433 }
+ }
+
+ task "sentinel" {
+ driver = "docker"
+
+ config {
+ image = "superboum/amd64_postgres:v3"
+ network_mode = "host"
+ readonly_rootfs = false
+ command = "/usr/local/bin/stolon-sentinel"
+ args = [
+ "--cluster-name", "pissenlit",
+ "--store-backend", "consul",
+ "--store-endpoints", "http://consul.service.2.cluster.deuxfleurs.fr:8500",
+ ]
+ }
+ resources {
+ memory = 100
+ }
+ }
+
+ task "proxy" {
+ driver = "docker"
+
+ config {
+ image = "superboum/amd64_postgres:v3"
+ network_mode = "host"
+ readonly_rootfs = false
+ command = "/usr/local/bin/stolon-proxy"
+ args = [
+ "--cluster-name", "pissenlit",
+ "--store-backend", "consul",
+ "--store-endpoints", "http://consul.service.2.cluster.deuxfleurs.fr:8500",
+ "--port", "${NOMAD_PORT_psql_proxy_port}",
+ "--listen-address", "0.0.0.0"
+ ]
+ ports = [ "psql_proxy_port" ]
+ }
+
+ resources {
+ memory = 100
+ }
+
+ service {
+ tags = ["sql"]
+ port = "psql_proxy_port"
+ address_mode = "host"
+ name = "psql-proxy"
+ check {
+ type = "tcp"
+ port = "psql_proxy_port"
+ interval = "60s"
+ timeout = "5s"
+ check_restart {
+ limit = 3
+ grace = "10m"
+ ignore_warnings = false
+ }
+ }
+ }
+ }
+
+ task "keeper" {
+ driver = "docker"
+
+ config {
+ image = "superboum/amd64_postgres:v3"
+ network_mode = "host"
+ readonly_rootfs = false
+ command = "/usr/local/bin/stolon-keeper"
+ args = [
+ "--cluster-name", "pissenlit",
+ "--store-backend", "consul",
+ "--store-endpoints", "http://consul.service.2.cluster.deuxfleurs.fr:8500",
+ "--data-dir", "/mnt/persist",
+ "--pg-su-password", "${PG_SU_PWD}",
+ "--pg-repl-username", "${PG_REPL_USER}",
+ "--pg-repl-password", "${PG_REPL_PWD}",
+ "--pg-listen-address", "${attr.unique.network.ip-address}",
+ "--pg-port", "${NOMAD_PORT_psql_port}",
+ "--pg-bin-path", "/usr/lib/postgresql/9.6/bin/"
+ ]
+ ports = [ "psql_port" ]
+ volumes = [
+ "/mnt/ssd/postgres:/mnt/persist"
+ ]
+ }
+
+ template {
+ data = file("../config/keeper/env.tpl")
+ destination = "secrets/env"
+ env = true
+ }
+
+ resources {
+ memory = 500
+ }
+
+ service {
+ tags = ["sql"]
+ port = "psql_port"
+ address_mode = "host"
+ name = "keeper"
+ check {
+ type = "tcp"
+ port = "psql_port"
+ interval = "60s"
+ timeout = "5s"
+
+ check_restart {
+ limit = 3
+ grace = "60m"
+ ignore_warnings = false
+ }
+ }
+ }
+ }
+ }
+}
+
diff --git a/app/postgres/secrets/postgres/keeper/pg_repl_pwd b/app/postgres/secrets/postgres/keeper/pg_repl_pwd
new file mode 100644
index 0000000..ae0c229
--- /dev/null
+++ b/app/postgres/secrets/postgres/keeper/pg_repl_pwd
@@ -0,0 +1 @@
+SERVICE_PASSWORD replicator
diff --git a/app/postgres/secrets/postgres/keeper/pg_repl_username b/app/postgres/secrets/postgres/keeper/pg_repl_username
new file mode 100644
index 0000000..58e6e46
--- /dev/null
+++ b/app/postgres/secrets/postgres/keeper/pg_repl_username
@@ -0,0 +1 @@
+CONST replicator
diff --git a/app/postgres/secrets/postgres/keeper/pg_su_pwd b/app/postgres/secrets/postgres/keeper/pg_su_pwd
new file mode 100644
index 0000000..907e2b8
--- /dev/null
+++ b/app/postgres/secrets/postgres/keeper/pg_su_pwd
@@ -0,0 +1 @@
+USER postgres superuser password