Merge ~cjwatson/launchpad-mojo-specs:lp-db into launchpad-mojo-specs:master

[Colin Watson]

lp-db: New spec

I agreed with William that we should keep our PostgreSQL databases in a separate environment even once we start deploying them using our own Mojo specs, which means we need a standalone spec for them. So far this only handles staging and qastaging, but it can be expanded to cover production as well in the future.

The current staging and qastaging databases are sort of deployed using this, but we really need to upgrade them to PostgreSQL 12 before `mojo run` will work properly.

[Simone Pelosi (pelpsi)]

LGTM!

review: Approve

[Jürgen Gmach (jugmac00)]

review: Needs Fixing

[Jürgen Gmach (jugmac00)]

review: Approve

[Simone Pelosi (pelpsi)]

Please, merge this only when Postgresql is upgraded to version 12.

[Colin Watson (cjwatson)]

That might not be a good plan. At least as I'd planned it in November, the path to upgrading PostgreSQL to version 12 required first deploying a new production DB environment using this spec, temporarily manually installing PostgreSQL 10 in it so that it can be replicated from the current primary, switching the primary to this one, and then upgrading. (Otherwise you'd have to get PostgreSQL 12 wedged into the current cluster, which IIRC is on bionic, and that seemed worse.)

But I suppose it would be possible to just deploy the new production environment from this branch. I don't think it makes a whole lot of difference either way.

Unmerged commits

e8f2d09... by Colin Watson on 2023-11-28

lp-db: New spec

I agreed with William that we should keep our PostgreSQL databases in a
separate environment even once we start deploying them using our own
Mojo specs, which means we need a standalone spec for them. So far this
only handles staging and qastaging, but it can be expanded to cover
production as well in the future.

lint:0 (build)

1 → 1 of 1 result

First • Previous • Next • Last

diff --git a/lp-db/README.md b/lp-db/README.md
new file mode 100644
index 0000000..54a263a
--- /dev/null
+++ b/lp-db/README.md
@@ -0,0 +1,26 @@
+# Launchpad standalone database deployment
+
+This spec deploys the Launchpad PostgreSQL database.  While the `lp` spec
+also supports deploying PostgreSQL directly to keep local test deployments
+simple, for non-test deployments we prefer to deploy PostgreSQL separately
+from the rest of Launchpad to reduce the risk of ordinary code deployments
+causing database havoc due to Juju bugs.
+
+You can run this spec locally using Juju's LXD support and Mojo.  First,
+configure your environment:
+
+    export MOJO_ROOT="$HOME/.local/share/mojo"
+    export MOJO_PROJECT=mojo-lp-db
+    export MOJO_WORKSPACE=devel
+    export MOJO_SERIES=focal
+    export MOJO_SPEC=git+https://git.launchpad.net/launchpad-mojo-specs
+    export MOJO_STAGE=lp-db/devel
+
+Then run the spec using Mojo:
+
+    mojo project-new -c containerless
+    mojo workspace-new
+    mojo run
+
+See the "Initial database setup" section of `lp/README.md` for how to set up
+the contents of the database.
diff --git a/lp-db/bundle.yaml b/lp-db/bundle.yaml
new file mode 100644
index 0000000..7382b8c
--- /dev/null
+++ b/lp-db/bundle.yaml
@@ -0,0 +1,154 @@
+{#- Defaults. #}
+{%- set cron_mailto = "[e-mail]" %}
+{#- Address of corresponding launchpad-db-update unit. #}
+{%- set db_update_ip = "" %}
+{%- set devel = False %}
+{#- Network where this bundle is deployed. #}
+{%- set local_network = "" %}
+
+{%- if stage_name == "production" %}
+{%-   set dbname_launchpad = "launchpad_prod" %}
+{%-   set dbname_session = "session_prod" %}
+{%-   set nagios_context = "prod-launchpad" %}
+{%-   set nagios_hostgroups = "prodstack-lp" %}
+{%-   set nagios_master = "nagios.ps5.internal" %}
+{%-   set postgresql_constraints = "cores=16 mem=256G root-disk=3000G root-disk-source=local" %}
+{%-   set postgresql_num_units = 3 %}
+{%- elif stage_name == "staging" %}
+{%-   set db_update_ip = "10.132.54.153" %}
+{%-   set dbname_launchpad = "lpmain_staging" %}
+{%-   set dbname_session = "session_staging" %}
+{%-   set local_network = "10.132.64.0/24" %}
+{%-   set nagios_context = "staging-launchpad" %}
+{%-   set nagios_hostgroups = "stagingstack-lp" %}
+{%-   set nagios_master = "devops-nagios.ps5.internal" %}
+{%-   set postgresql_constraints = "cores=8 mem=16G root-disk=3000G root-disk-source=local" %}
+{%-   set postgresql_num_units = 2 %}
+{%- elif stage_name == "qastaging" %}
+{%-   set db_update_ip = "10.132.54.103" %}
+{%-   set dbname_launchpad = "launchpad_qastaging" %}
+{%-   set dbname_session = "session_qastaging" %}
+{%-   set local_network = "10.132.223.0/24" %}
+{%-   set nagios_context = "qastaging-launchpad" %}
+{%-   set nagios_hostgroups = "stagingstack-lp" %}
+{%-   set nagios_master = "devops-nagios.ps5.internal" %}
+{%-   set postgresql_constraints = "cores=8 mem=16G root-disk=1500G root-disk-source=local" %}
+{%-   set postgresql_num_units = 2 %}
+{%- else %}
+{%-   set cron_mailto = "[e-mail]" %}
+{%-   set dbname_launchpad = "launchpad_dev" %}
+{%-   set dbname_session = "session_dev" %}
+{%-   set devel = True %}
+{%-   set nagios_context = "devel-launchpad" %}
+{%-   set nagios_hostgroups = "devel-lp" %}
+{#-   The configured nagios_master doesn't have to be real, but it does have
+      to resolve. #}
+{%-   set nagios_master = "localhost" %}
+{%-   set postgresql_num_units = 1 %}
+{%- endif -%}
+
+series: focal
+applications:
+  postgresql:
+    charm: ch:postgresql
+    channel: latest/stable
+    revision: 345
+    constraints: "{{ postgresql_constraints }}"
+    num_units: {{ postgresql_num_units }}
+    options:
+      admin_addresses: "127.0.0.1"
+{%- if stage_name == "staging" %}
+{#- Backups of staging take a long time, and they interfere with the weekly
+    restore of staging from production.  They aren't very useful due to that
+    weekly restore, so just disable them. #}
+      backup_schedule: ""
+{%- endif %}
+      extra_pg_auth: |-
+{%- if stage_name == "production" %}
+        # Slony, running in the sso-db Juju environment.
+        host launchpad_prod slony 162.213.33.34/32 md5
+        host launchpad_prod slony 10.131.151.0/24 md5
+{%- endif %}
+        # pgbouncer connections, from localhost and from other trusted
+        # machines.
+        local {{ dbname_launchpad }} all trust
+        local {{ dbname_session }} all trust
+{%- if local_network %}
+        host {{ dbname_launchpad }} all {{ local_network }} trust
+        host {{ dbname_session }} all {{ local_network }} trust
+{%- endif %}
+{%- if stage_name in ("staging", "qastaging") %}
+        host replication stgreplication 10.22.112.54/32 trust  # pamola.internal
+        host replication stgreplication 10.22.112.55/32 trust  # pherkad.internal
+{%- endif %}
+{%- if db_update_ip %}
+        # Allow direct access from launchpad-db-update unit.
+        host all all {{ db_update_ip }}/32 md5
+{%- endif %}
+      extra_pg_conf: "include-file://{{ spec_dir }}/{{ stage }}/extra_pg.conf"
+{%- if not devel %}
+      manual_replication: true
+{%- endif %}
+      package_status: "hold"
+      version: "12"
+  launchpad-postgresql-extras:
+    charm: ch:launchpad-postgresql-extras
+    channel: stable
+    revision: 2
+    options:
+      cron_mailto: "{{ cron_mailto }}"
+{%- if not devel %}
+      install_keys: |
+        - null
+      install_sources: |
+        - ppa:canonical-is-sa/ubuntu/launchpad
+{%- endif %}
+{%- if stage_name in ("qastaging", "staging") %}
+      pgbouncer_db_config: "include-file://{{ spec_dir }}/{{ stage }}/pgbouncer_databases.ini"
+      pgbouncer_extra_config: "include-file://{{ spec_dir }}/{{ stage }}/pgbouncer_extra_config.txt"
+      pgbouncer_userlist: "include-file://{{ local_dir }}/userlist.txt"
+{%- endif %}
+{%- if stage_name == "production" %}
+  postfix-relay:
+    charm: ch:postfix-relay
+    channel: stable
+    revision: 9
+    options:
+{#- postfix-relay/smtp_auth_username and postfix-relay/smtp_auth_password
+    must be set in secrets. #}
+      relayhost: "[launchpad.smtp.canonical.com]:25"
+{%- endif %}
+  nrpe:
+    charm: ch:nrpe
+    channel: stable
+    revision: 106
+    options:
+      export_nagios_definitions: true
+      hostgroups: "{{ nagios_hostgroups }}"
+      nagios_host_context: "{{ nagios_context }}"
+      nagios_hostname_type: host
+      nagios_master: "{{ nagios_master }}"
+      space_check: |
+        check: auto
+        auto_params: "-w 15% -c 10% -K 5%"
+      swap: ""
+      swap_activity: ""
+{%- if not devel %}
+  telegraf:
+    charm: ch:telegraf
+    channel: stable
+    revision: 73
+    expose: true
+    options:
+      install_method: snap
+{%- endif %}
+relations:
+  - ["postgresql", "launchpad-postgresql-extras"]
+{%- if stage_name == "production" %}
+  - ["postfix-relay", "postgresql"]
+{%- endif %}
+  - ["nrpe:nrpe-external-master", "postgresql:nrpe-external-master"]
+{%- if not devel %}
+  - ["telegraf:juju-info", "postgresql:juju-info"]
+  - ["telegraf:postgresql", "postgresql:db-admin"]
+{%- endif %}
diff --git a/lp-db/configs/custom-secgroups-qastaging.yaml b/lp-db/configs/custom-secgroups-qastaging.yaml
new file mode 100644
index 0000000..6b8a8a4
--- /dev/null
+++ b/lp-db/configs/custom-secgroups-qastaging.yaml
@@ -0,0 +1,46 @@
+applications:
+    nrpe:
+        type: neutron
+        rules:
+            - nagios-monitored
+    postgresql:
+        type: neutron
+        rules:
+            - legacy-postgresql
+            - postgresql
+            - pgbouncer
+    telegraf:
+        type: neutron
+        rules:
+            - metrics
+rules:
+    legacy-postgresql:
+        # pherkad
+        - {"protocol": "tcp", "family": "IPv4", "port": 5432, "cidr": "10.22.112.55/32"}
+        # pamola
+        - {"protocol": "tcp", "family": "IPv4", "port": 5432, "cidr": "10.22.112.54/32"}
+    metrics:
+        # Allow IS Prometheus to scrape telegraf.
+        # 3FP
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "91.189.94.59/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "91.189.94.60/32"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2001:67c:1561:8008::13/128"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2001:67c:1561:8008::14/128"}
+        # IL3
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "185.125.190.67/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "185.125.190.68/32"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2620:2d:4000:1::67/128"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2620:2d:4000:1::68/128"}
+    nagios-monitored:
+        # Allow monitoring from devops-nagios.ps5.internal.
+        - {"protocol": "tcp", "family": "IPv4", "port": 22, "cidr": "10.131.26.77/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 873, "cidr": "10.131.26.77/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 5666, "cidr": "10.131.26.77/32"}
+        - {"protocol": "icmp", "family": "IPv4", "cidr": "10.131.26.77/32"}
+    pgbouncer:
+        # stg-launchpad@launchpad-bastion-ps5 (firewall rules apply finer-grained ACLs)
+        - {"protocol": "tcp", "family": "IPv4", "port": 5433, "cidr": "10.132.54.0/24"}
+    postgresql:
+        # stg-launchpad@launchpad-bastion-ps5 (firewall rules apply
+        # finer-grained ACLs; this is just for launchpad-db-update)
+        - {"protocol": "tcp", "family": "IPv4", "port": 5432, "cidr": "10.132.54.0/24"}
diff --git a/lp-db/configs/custom-secgroups-staging.yaml b/lp-db/configs/custom-secgroups-staging.yaml
new file mode 100644
index 0000000..e690c9a
--- /dev/null
+++ b/lp-db/configs/custom-secgroups-staging.yaml
@@ -0,0 +1,55 @@
+applications:
+    nrpe:
+        type: neutron
+        rules:
+            - nagios-monitored
+    postgresql:
+        type: neutron
+        rules:
+            - legacy-postgresql
+            - pgbouncer
+            - postgresql
+            - push-backups
+    telegraf:
+        type: neutron
+        rules:
+            - metrics
+rules:
+    legacy-postgresql:
+        # pamola
+        - {"protocol": "tcp", "family": "IPv4", "port": 5432, "cidr": "10.22.112.54/32"}
+        # pherkad
+        - {"protocol": "tcp", "family": "IPv4", "port": 5432, "cidr": "10.22.112.55/32"}
+    metrics:
+        # Allow IS Prometheus to scrape telegraf.
+        # 3FP
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "91.189.94.59/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "91.189.94.60/32"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2001:67c:1561:8008::13/128"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2001:67c:1561:8008::14/128"}
+        # IL3
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "185.125.190.67/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 9103, "cidr": "185.125.190.68/32"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2620:2d:4000:1::67/128"}
+        - {"protocol": "tcp", "family": "IPv6", "port": 9103, "cidr": "2620:2d:4000:1::68/128"}
+    nagios-monitored:
+        # Allow monitoring from devops-nagios.ps5.internal.
+        - {"protocol": "tcp", "family": "IPv4", "port": 22, "cidr": "10.131.26.77/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 873, "cidr": "10.131.26.77/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 5666, "cidr": "10.131.26.77/32"}
+        - {"protocol": "icmp", "family": "IPv4", "cidr": "10.131.26.77/32"}
+    pgbouncer:
+        # stg-launchpad@launchpad-bastion-ps5 (firewall rules apply finer-grained ACLs)
+        - {"protocol": "tcp", "family": "IPv4", "port": 5433, "cidr": "10.132.54.0/24"}
+        # Staging SSO DB
+        - {"protocol": "tcp", "family": "IPv4", "port": 5433, "cidr": "10.22.112.57/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 5433, "cidr": "10.22.112.58/32"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 5433, "cidr": "10.132.34.0/24"}
+        - {"protocol": "tcp", "family": "IPv4", "port": 5433, "cidr": "10.132.211.0/24"}
+    postgresql:
+        # stg-launchpad@launchpad-bastion-ps5 (firewall rules apply
+        # finer-grained ACLs; this is just for launchpad-db-update)
+        - {"protocol": "tcp", "family": "IPv4", "port": 5432, "cidr": "10.132.54.0/24"}
+    push-backups:
+        # wildcherry
+        - {"protocol": "tcp", "family": "IPv4", "port": 22, "cidr": "91.189.90.38/32"}
diff --git a/lp-db/manifest b/lp-db/manifest
new file mode 120000
index 0000000..e1c38b1
--- /dev/null
+++ b/lp-db/manifest
@@ -0,0 +1 @@
+manifests/deploy
\ No newline at end of file
diff --git a/lp-db/manifest-verify b/lp-db/manifest-verify
new file mode 120000
index 0000000..6e02de4
--- /dev/null
+++ b/lp-db/manifest-verify
@@ -0,0 +1 @@
+manifests/verify
\ No newline at end of file
diff --git a/lp-db/manifests/deploy b/lp-db/manifests/deploy
new file mode 100644
index 0000000..3ee5195
--- /dev/null
+++ b/lp-db/manifests/deploy
@@ -0,0 +1,5 @@
+script config=predeploy
+bundle config=bundle.yaml local=deploy-secrets status-timeout=3600
+juju-check-wait
+include config=manifests/secgroups
+include config=manifests/verify
diff --git a/lp-db/manifests/secgroups b/lp-db/manifests/secgroups
new file mode 100644
index 0000000..8c438ee
--- /dev/null
+++ b/lp-db/manifests/secgroups
@@ -0,0 +1 @@
+script config=utils/custom-secgroups.py SKIP_STAGES=devel
diff --git a/lp-db/manifests/verify b/lp-db/manifests/verify
new file mode 100644
index 0000000..f25f902
--- /dev/null
+++ b/lp-db/manifests/verify
@@ -0,0 +1,4 @@
+juju-check-wait
+# It occasionally takes a little while for all the servers to start
+# accepting connections.
+verify retry=3
diff --git a/lp-db/predeploy b/lp-db/predeploy
new file mode 100755
index 0000000..bf98e70
--- /dev/null
+++ b/lp-db/predeploy
@@ -0,0 +1,9 @@
+#! /bin/sh
+set -e
+
+TOP="${0%/*}"
+
+# Ensure that deploy-secrets exists.
+"$TOP/utils/set-local-config" --bundle lp-db
+
+exit 0
diff --git a/lp-db/qastaging/extra_pg.conf b/lp-db/qastaging/extra_pg.conf
new file mode 100644
index 0000000..bfda731
--- /dev/null
+++ b/lp-db/qastaging/extra_pg.conf
@@ -0,0 +1,30 @@
+autovacuum = True
+autovacuum_analyze_scale_factor = 0.01
+autovacuum_analyze_threshold = 50
+autovacuum_vacuum_cost_delay = -1 # RT#130656
+autovacuum_vacuum_scale_factor = 0.05
+datestyle = 'iso, mdy'
+default_statistics_target = 500
+effective_cache_size = 150GB
+fsync = True
+full_page_writes = True
+hot_standby_feedback = True
+log_autovacuum_min_duration = 0
+log_destination = 'stderr'
+log_lock_waits = True
+log_min_duration_statement = 10000
+log_temp_files = 20MB
+log_timezone = UTC
+maintenance_work_mem = 1GB
+max_connections = 4250
+max_prepared_transactions = 0
+max_wal_size = 256MB
+random_page_cost = 1.2
+search_path = '"$user",public'
+shared_buffers = 8GB
+standard_conforming_strings = False
+synchronous_commit = on
+temp_buffers = 1MB
+wal_keep_segments = 10000
+wal_level = replica
+work_mem = 20MB
diff --git a/lp-db/qastaging/pgbouncer_databases.ini b/lp-db/qastaging/pgbouncer_databases.ini
new file mode 100644
index 0000000..a7ea046
--- /dev/null
+++ b/lp-db/qastaging/pgbouncer_databases.ini
@@ -0,0 +1,4 @@
+[databases]
+session_qastaging = dbname=session_qastaging host=database-ps5-1.qastaging.lp.internal port=5432
+launchpad_qastaging = dbname=launchpad_qastaging host=database-ps5-1.qastaging.lp.internal port=5432
+launchpad_qastaging_standby1 = dbname=launchpad_qastaging host=database-ps5-2.qastaging.lp.internal port=5432
diff --git a/lp-db/qastaging/pgbouncer_extra_config.txt b/lp-db/qastaging/pgbouncer_extra_config.txt
new file mode 100644
index 0000000..46d9525
--- /dev/null
+++ b/lp-db/qastaging/pgbouncer_extra_config.txt
@@ -0,0 +1,14 @@
+client_login_timeout = 5
+default_pool_size = 100
+ignore_startup_parameters = application_name
+log_connections = 1
+log_disconnections = 1
+log_pooler_errors = 1
+max_client_conn = 1000
+pool_mode = session
+reserve_pool_size = 10
+reserve_pool_timeout = 3
+server_check_delay = 60
+server_idle_timeout = 30
+stats_users = postgres,nagios,pgbouncer
+tcp_keepalive = 1
diff --git a/lp-db/staging/extra_pg.conf b/lp-db/staging/extra_pg.conf
new file mode 100644
index 0000000..96f3a9f
--- /dev/null
+++ b/lp-db/staging/extra_pg.conf
@@ -0,0 +1,31 @@
+autovacuum = True
+autovacuum_analyze_scale_factor = 0.01
+autovacuum_analyze_threshold = 50
+autovacuum_vacuum_cost_delay = -1 # RT#130656
+autovacuum_vacuum_scale_factor = 0.05
+datestyle = 'iso, mdy'
+default_statistics_target = 500
+effective_cache_size = 150GB
+fsync = True
+full_page_writes = True
+hot_standby_feedback = True
+log_autovacuum_min_duration = 0
+log_destination = 'stderr'
+log_lock_waits = True
+log_min_duration_statement = 10000
+log_temp_files = 20MB
+log_timezone = UTC
+maintenance_work_mem = 1GB
+max_connections = 4250
+max_prepared_transactions = 0
+max_wal_size = 256MB
+random_page_cost = 1.2
+search_path = '"$user",public'
+shared_buffers = 8GB
+standard_conforming_strings = False
+synchronous_commit = on
+temp_buffers = 1MB
+# 320GB!  Streaming replication buffer for staging restores.
+wal_keep_segments = 20000
+wal_level = replica
+work_mem = 20MB
diff --git a/lp-db/staging/pgbouncer_databases.ini b/lp-db/staging/pgbouncer_databases.ini
new file mode 100644
index 0000000..956b901
--- /dev/null
+++ b/lp-db/staging/pgbouncer_databases.ini
@@ -0,0 +1,4 @@
+[databases]
+session_staging = dbname=session_staging host=database-ps5-1.staging.lp.internal port=5432
+launchpad_staging = dbname=lpmain_staging host=database-ps5-1.staging.lp.internal port=5432
+launchpad_staging_standby1 = dbname=lpmain_staging host=database-ps5-2.staging.lp.internal port=5432
diff --git a/lp-db/staging/pgbouncer_extra_config.txt b/lp-db/staging/pgbouncer_extra_config.txt
new file mode 100644
index 0000000..46d9525
--- /dev/null
+++ b/lp-db/staging/pgbouncer_extra_config.txt
@@ -0,0 +1,14 @@
+client_login_timeout = 5
+default_pool_size = 100
+ignore_startup_parameters = application_name
+log_connections = 1
+log_disconnections = 1
+log_pooler_errors = 1
+max_client_conn = 1000
+pool_mode = session
+reserve_pool_size = 10
+reserve_pool_timeout = 3
+server_check_delay = 60
+server_idle_timeout = 30
+stats_users = postgres,nagios,pgbouncer
+tcp_keepalive = 1
diff --git a/lp-db/utils b/lp-db/utils
new file mode 120000
index 0000000..468ba70
--- /dev/null
+++ b/lp-db/utils
@@ -0,0 +1 @@
+../utils
\ No newline at end of file
diff --git a/lp-db/verify b/lp-db/verify
new file mode 100755
index 0000000..df9e6ef
--- /dev/null
+++ b/lp-db/verify
@@ -0,0 +1,8 @@
+#! /bin/sh
+set -e
+
+TOP="${0%/*}"
+
+export EXTRA_SKIP_CHECKS="check_swap${EXTRA_SKIP_CHECKS:+|${EXTRA_SKIP_CHECKS}}"
+
+exec "$TOP/utils/verify"