Page MenuHomeSoftware Heritage
Create Task
Maniphest T3833

Dedicate one admin host to centralize administration dbs
Closed, MigratedEdits Locked
Actions

Description

Centralize the postgresql databases used for admin tools into a single dedicated host
(on which we would be able to do more proper backups and monitoring).

This is also the occasion to use the latest postgresql versions (each have their own and
inconsistent versions).

Impacted services:

  • hedgedoc (host: bardo, db: postgres-12)
  • netbox (host: bojimans, db: postgres-11)
  • grafana (host: pergamon, db: postgres-11)
  • sentry (host: riverside, db: postgres-12)
  • keycloak (host: kelvingrove, db: postgres-12)

This is the size of the current databases:

DatabaseSize
hedgedoc42MB
netbox22MB
grafana18MB
sentry99GB
keycloak15MB

The sentry database will force us to reserve a large amount of disk for the new server

Plan:

  • Leave services' configuration untouched to use local db
  • D6907, D6906: First create host database machine (vm).
  • T3833#76853: Create zfs data mount point (for the dbs' data)
  • D6928: Declare a dedicated puppet profile, this lists all required dbs to create (using profile::postgresql::server).
  • D6906: terraform (/vagrant) to boostrap (this applies puppet so the dbs get created)
  • T3833#76889: firewall: Open flux from vlan 440 (bojimans, kelvingrove, riverside, #pergamon) to vlan 442, port 5432
  • for each service in {D6946: netbox, D6947: hedgedoc, T3817: grafana, D6951: sentry, rSPSITE2b8a33e79d6e49554339e3b70134eb84e8cad7cf: keycloak}:
    • Stop the service (we don't have incremental dump so stop the service first)
    • Export and mount back data dump from old db to the new one
    • Adapt configuration to switch to the new db
    • puppet apply to restart service (which now uses the new db)
    • Ensure service is still ok

Annex actions (outside the scope of this task, like T3817):

  • T3850: Move services {netbox, sentry, keycloak} in the admin vlan (442) and behind the reverse proxy
  • T3849: Clean up leftovers after migration

Related to D6871#178665

Revisions and Commits

rSPRE sysadm-provisioning
D6906rSPRE803e4a62484d Bootstrap dali vm in admin network
rSENV Puppet Environment
D6932rSENVfd0fb2d2da4f vagrant: Make tmpdir configurable on vagrant scripts
D6933rSENV787c2421c66c vagrant: Actually sync the site.pp change
D6931rSENV49b9efc5ea0d Use sync-vagrant-conf wrapper script to deal with puppet manifest
D6930rSENVd7954dc7608f Vagrant: Allow rsync puppet manifest to the puppet master vm
D6920rSENVc00cb258e95d Vagrantfile: Explicit the mount point should happen with rsync
rSPSITE puppet-swh-site
D6951rSPSITE6be93752c27e sentry: Migrate sentry db to use the new one
D6947rSPSITE331a22dcf2a7 hedgedoc: use the centralized admin db
D6946rSPSITE747bd7013f36 netbox: use the centralized admin db
D6928rSPSITEe964bb5a822c dali: Prepare host with admin tools postgresql dbs
D6907rSPSITEd60f5b312146 Reference dali machine dns entries

Related Objects
Search...

Event Timeline

ardumont triaged this task as Normal priority.Jan 4 2022, 4:58 PM
ardumont created this task.
ardumont added a project: System administration.
ardumont mentioned this in D6871: Move grafana on a dedicated server behind the admin RP.
ardumont updated the task description. (Show Details)Jan 10 2022, 11:26 AM
ardumont updated the task description. (Show Details)Jan 10 2022, 11:29 AM
ardumont updated the task description. (Show Details)Jan 10 2022, 11:32 AM
ardumont moved this task from Backlog to Weekly backlog on the System administration board.Jan 11 2022, 9:58 AM
vsellier mentioned this in T3817: Install grafana on its own server.Jan 11 2022, 10:19 AM
vsellier updated the task description. (Show Details)Jan 11 2022, 10:24 AM
vsellier updated the task description. (Show Details)
vsellier updated the task description. (Show Details)Jan 11 2022, 11:09 AM
vsellier updated the task description. (Show Details)
ardumont updated the task description. (Show Details)Jan 11 2022, 11:16 AM
ardumont added a revision: D6906: Bootstrap dali vm in admin network.Jan 11 2022, 11:38 AM
ardumont added a revision: D6907: Reference dali machine dns entries.Jan 11 2022, 11:47 AM
ardumont mentioned this in rSENV053966641225: Vagrantfile: Add dali vm in admin network.Jan 11 2022, 5:08 PM
ardumont added a revision: D6920: Vagrantfile: Explicit the mount point should happen with rsync.Jan 11 2022, 6:21 PM
ardumont added a commit: rSENVc00cb258e95d: Vagrantfile: Explicit the mount point should happen with rsync.Jan 12 2022, 11:28 AM
ardumont added a comment.Jan 12 2022, 3:57 PM

Point of attention for the encoding, collate and type of dbs [1]:

  • netbox (bojimans):
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 netbox    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =T/postgres          +
           |          |          |         |         | postgres=CTc/postgres+
           |          |          |         |         | netbox=CTc/postgres
  • grafana, icinga2, icingaweb2 (pergamon)
postgres=# \l
                               List of databases
    Name    |  Owner   | Encoding | Collate |  Ctype  |    Access privileges
------------+----------+----------+---------+---------+-------------------------
 grafana    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | postgres=CTc/postgres  +
            |          |          |         |         | =T/postgres            +
            |          |          |         |         | grafana=CTc/postgres
 icinga2    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | postgres=CTc/postgres  +
            |          |          |         |         | =T/postgres            +
            |          |          |         |         | icinga2=CTc/postgres
 icingaweb2 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | postgres=CTc/postgres  +
            |          |          |         |         | =T/postgres            +
            |          |          |         |         | icingaweb2=CTc/postgres
  • hedgedoc (bardo):
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 hedgedoc  | hedgedoc | SQL_ASCII | C       | C     | =T/hedgedoc          +
           |          |           |         |       | hedgedoc=CTc/hedgedoc+
           |          |           |         |       | guest=c/hedgedoc
  • sentry, keycloak (belvedere):
postgres=# \l
                                            List of databases
                Name                |    Owner     | Encoding | Collate |  Ctype  |   Access privileges
------------------------------------+--------------+----------+---------+---------+-----------------------
 keycloak                           | keycloak     | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres                           | postgres     | UTF8     | C.UTF-8 | C.UTF-8 |
 sentry                             | sentry       | UTF8     | C.UTF-8 | C.UTF-8 |

We will align all dbs to the following:

|----------+---------+---------|
| Encoding | Collate | Ctype   |
|----------+---------+---------|
| UTF8     | C.UTF-8 | C.UTF-8 |
|----------+---------+---------|

(netbox uses ascii which is a subset of UTF-8, it was badly set because the puppet
module configures that way by default and we did not change that default at the time)

[1] T3772

ardumont added a revision: D6928: dali: Prepare host with admin tools postgresql dbs.Jan 12 2022, 5:03 PM
vsellier updated the task description. (Show Details)Jan 12 2022, 5:05 PM
ardumont updated the task description. (Show Details)Jan 12 2022, 5:25 PM
ardumont updated the task description. (Show Details)Jan 12 2022, 5:29 PM
ardumont updated the task description. (Show Details)
ardumont updated the task description. (Show Details)Jan 12 2022, 5:40 PM
ardumont added a commit: rSPRE803e4a62484d: Bootstrap dali vm in admin network.Jan 12 2022, 5:52 PM
ardumont added a revision: D6930: Vagrant: Allow rsync puppet manifest to the puppet master vm.Jan 13 2022, 9:34 AM
ardumont added a revision: D6931: Use sync-vagrant-conf wrapper script to deal with puppet manifest.Jan 13 2022, 9:41 AM
ardumont added a revision: D6932: vagrant: Make tmpdir configurable on vagrant scripts.Jan 13 2022, 9:55 AM
ardumont added a revision: D6933: vagrant: Actually sync the site.pp change.Jan 13 2022, 10:47 AM
ardumont added a commit: rSENVd7954dc7608f: Vagrant: Allow rsync puppet manifest to the puppet master vm.Jan 13 2022, 10:52 AM
ardumont added a commit: rSENV49b9efc5ea0d: Use sync-vagrant-conf wrapper script to deal with puppet manifest.
ardumont added a commit: rSENV787c2421c66c: vagrant: Actually sync the site.pp change.
ardumont added a commit: rSENVfd0fb2d2da4f: vagrant: Make tmpdir configurable on vagrant scripts.
ardumont added a comment.Jan 13 2022, 11:10 AM
  • build the vm dali (terraform apply)
  • Install the necessary zfs tools:
root@dali:~# cat /etc/apt/sources.list.d/debian-contrib.list
deb https://deb.debian.org/debian/ bullseye contrib
root@dali:~# apt install zfs-dkms
root@dali:~# modprobe zfs
root@dali:~# lsmod | grep -i zfs
zfs                  4558848  0
zunicode              335872  1 zfs
zzstd                 573440  1 zfs
zlua                  184320  1 zfs
zavl                   16384  1 zfs
icp                   323584  1 zfs
zcommon               102400  2 zfs,icp
znvpair               106496  2 zfs,zcommon
spl                   118784  6 zfs,icp,zzstd,znvpair,zcommon,zavl
  • build zfs data pool:
root@dali:~# zpool create data /dev/vdb
root@dali:~# zpool status -v
  pool: data
 state: ONLINE
config:

        NAME        STATE     READ WRITE CKSUM
        data        ONLINE       0     0     0
          vdb       ONLINE       0     0     0

errors: No known data errors
root@dali:~# zfs create -o mountpoint=/srv/softwareheritage/postgres/14/main -o atime=off -o relatime=on -o compression=lz4 data/postgresql
root@dali:~# zfs list
NAME              USED  AVAIL     REFER  MOUNTPOINT
data              165K   193G       24K  /data
data/postgresql    24K   193G       24K  /srv/softwareheritage/postgres/14/main
root@dali:~# mount | grep postgresql
data/postgresql on /srv/softwareheritage/postgres/14/main type zfs (rw,noatime,xattr,noacl)
  • Reboot machine to check the data pool is correctly mounted at startup (it is)
ardumont@dali:~% uptime
 10:09:44 up 0 min,  2 users,  load average: 0.38, 0.15, 0.05
ardumont@dali:~% mount | grep postgresql
data/postgresql on /srv/softwareheritage/postgres/14/main type zfs (rw,noatime,xattr,noacl)

Note: T3487#71271

ardumont updated the task description. (Show Details)Jan 13 2022, 11:10 AM
ardumont added a comment.EditedJan 13 2022, 11:58 AM

Well actually use the right expected folder (as per the puppet manifest actually configured):

root@dali:~# zfs destroy data/postgresql
root@dali:~# zfs list
NAME   USED  AVAIL     REFER  MOUNTPOINT
data   156K   193G       24K  /data
root@dali:~# zfs create -o mountpoint=/srv/postgresql/14/main -o atime=off -o relatime=on -o compression=lz4 data/postgresql
root@dali:~# mount | grep postgresql
data/postgresql on /srv/postgresql/14/main type zfs (rw,noatime,xattr,noacl)
ardumont mentioned this in rSENV79a81181410b: Sync facts with puppet master.Jan 13 2022, 12:03 PM
ardumont added a comment.EditedJan 13 2022, 12:31 PM

Dumped prod hedgedoc db and mounted back the dump
into the vm vagrant to ensure no impact of the encoding/collation
change in between.

No issue whatsoever.

postgres@bardo:~$ pg_dump --clean --if-exists hedgedoc | gzip -c - > $(date +%Y-%m-%dT%H:%M:%SZ)-hedgedoc.sql.gz
...
postgres@dali:~$ gzip -dc 2022-01-13T11\:18\:11Z-hedgedoc.sql.gz | psql hedgedoc
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP INDEX
DROP INDEX
DROP INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP TYPE
CREATE TYPE
ALTER TYPE
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 662
COPY 2164
COPY 5318
COPY 18
COPY 21065
COPY 20611
COPY 0
COPY 0
COPY 27
 setval
--------
    672
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
postgres@dali:~$ psql hedgedoc
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

hedgedoc=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | Authors        | table    | hedgedoc
 public | Authors_id_seq | sequence | hedgedoc
 public | Notes          | table    | hedgedoc
 public | Revisions      | table    | hedgedoc
 public | SequelizeMeta  | table    | hedgedoc
 public | Session        | table    | hedgedoc
 public | Sessions       | table    | hedgedoc
 public | Temp           | table    | hedgedoc
 public | Temps          | table    | hedgedoc
 public | Users          | table    | hedgedoc
(10 rows)
ardumont changed the task status from Open to Work in Progress.Jan 13 2022, 12:43 PM
ardumont moved this task from Weekly backlog to in-progress on the System administration board.
ardumont added a commit: rSPSITEd60f5b312146: Reference dali machine dns entries.Jan 13 2022, 2:47 PM
ardumont added a commit: rSPSITEe964bb5a822c: dali: Prepare host with admin tools postgresql dbs.Jan 13 2022, 2:56 PM
ardumont updated the task description. (Show Details)Jan 13 2022, 2:59 PM
  • dns updated: dali and alias db1 (.internal.admin.swh.network) are ok
$ ping -c3 db1.internal.admin.swh.network
PING dali.internal.admin.swh.network (192.168.50.50) 56(84) bytes of data.
64 bytes from dali.internal.admin.swh.network (192.168.50.50): icmp_seq=1 ttl=63 time=4.51 ms
64 bytes from dali.internal.admin.swh.network (192.168.50.50): icmp_seq=2 ttl=63 time=4.61 ms
64 bytes from dali.internal.admin.swh.network (192.168.50.50): icmp_seq=3 ttl=63 time=4.79 ms

--- dali.internal.admin.swh.network ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 4.505/4.633/4.786/0.116 ms
$ ping -c3 dali.internal.admin.swh.network
PING dali.internal.admin.swh.network (192.168.50.50) 56(84) bytes of data.
64 bytes from dali.internal.admin.swh.network (192.168.50.50): icmp_seq=1 ttl=63 time=4.52 ms
64 bytes from dali.internal.admin.swh.network (192.168.50.50): icmp_seq=2 ttl=63 time=4.00 ms
64 bytes from dali.internal.admin.swh.network (192.168.50.50): icmp_seq=3 ttl=63 time=4.45 ms

--- dali.internal.admin.swh.network ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 3.997/4.320/4.518/0.230 ms
  • dbs installed:
postgres=# \l+
                                                                List of databases
   Name    |  Owner   | Encoding  | Collate |  Ctype  |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+-----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
 grafana   | grafana  | UTF8      | C.UTF-8 | C.UTF-8 | =T/grafana           +| 8625 kB | pg_default |
           |          |           |         |         | grafana=CTc/grafana  +|         |            |
           |          |           |         |         | guest=c/grafana       |         |            |
 hedgedoc  | hedgedoc | UTF8      | C.UTF-8 | C.UTF-8 | =T/hedgedoc          +| 8625 kB | pg_default |
           |          |           |         |         | hedgedoc=CTc/hedgedoc+|         |            |
           |          |           |         |         | guest=c/hedgedoc      |         |            |
 keycloak  | keycloak | UTF8      | C.UTF-8 | C.UTF-8 | =T/keycloak          +| 8625 kB | pg_default |
           |          |           |         |         | keycloak=CTc/keycloak+|         |            |
           |          |           |         |         | guest=c/keycloak      |         |            |
 netbox    | netbox   | UTF8      | C.UTF-8 | C.UTF-8 | =T/netbox            +| 8625 kB | pg_default |
           |          |           |         |         | netbox=CTc/netbox    +|         |            |
           |          |           |         |         | guest=c/netbox        |         |            |
 postgres  | postgres | SQL_ASCII | C       | C       |                       | 8777 kB | pg_default | default administrative connection database
 sentry    | sentry   | UTF8      | C.UTF-8 | C.UTF-8 | =T/sentry            +| 8625 kB | pg_default |
           |          |           |         |         | sentry=CTc/sentry    +|         |            |
           |          |           |         |         | guest=c/sentry        |         |            |
ardumont added a comment.Jan 13 2022, 3:43 PM

Open flux in the firewall

  • 1 rule for pergamon (monitoring):
# without the rule to allow the icinga-server (pergamon for monitoring)
root@pergamon:~# telnet db1.internal.admin.swh.network 5432
Trying 192.168.50.50...
telnet: Unable to connect to remote host: Connection refused
# with the rule applied
root@pergamon:~# telnet db1.internal.admin.swh.network 5432
Trying 192.168.50.50...
Connected to dali.internal.admin.swh.network.
Escape character is '^]'.
^CConnection closed by foreign host.
  • another rule for other admin tools nodes [1]:
root@pergamon:~# for node in bojimans riverside kelvingrove; do ssh $node telnet dali.internal.admin.swh.network 5432; done
Trying 192.168.50.50...
Connected to dali.internal.admin.swh.network.
Escape character is '^]'.
q
quit
Connection closed by foreign host.
Trying 192.168.50.50...
Connected to dali.internal.admin.swh.network.
Escape character is '^]'.
quit
Connection closed by foreign host.
Trying 192.168.50.50...
Connected to dali.internal.admin.swh.network.
Escape character is '^]'.
quit
Connection closed by foreign host.

[1] Temporary rule because those are not in the correct admin vlan yet.

ardumont updated the task description. (Show Details)Jan 13 2022, 3:44 PM
ardumont mentioned this in rSPSITE2335b5e9fe2d: dns: Fix dns alias for db1.internal.admin.Jan 13 2022, 3:51 PM
ardumont added a revision: D6946: netbox: use the centralized admin db.Jan 13 2022, 4:22 PM
vsellier added a commit: rSPSITE747bd7013f36: netbox: use the centralized admin db.Jan 13 2022, 4:26 PM
ardumont added a revision: D6947: hedgedoc: use the centralized admin db.Jan 13 2022, 5:04 PM
ardumont updated the task description. (Show Details)Jan 13 2022, 5:27 PM
ardumont added a commit: rSPSITE331a22dcf2a7: hedgedoc: use the centralized admin db.Jan 13 2022, 5:36 PM
ardumont updated the task description. (Show Details)Jan 14 2022, 12:32 PM
ardumont added a revision: D6951: sentry: Migrate sentry db to use the new one.Jan 14 2022, 3:13 PM
ardumont added a commit: rSPSITE6be93752c27e: sentry: Migrate sentry db to use the new one.Jan 14 2022, 4:10 PM
ardumont updated the task description. (Show Details)Jan 14 2022, 5:22 PM
ardumont updated the task description. (Show Details)
ardumont updated the task description. (Show Details)Jan 14 2022, 5:24 PM
ardumont updated the task description. (Show Details)
ardumont renamed this task from Dedicate one admin host to centralize administration db to Dedicate one admin host to centralize administration dbs.Jan 14 2022, 5:41 PM
ardumont mentioned this in rSPSITE2b8a33e79d6e: keycloak: Migrate keycloak db to use the new admin db host.Jan 17 2022, 5:20 PM
ardumont added a comment.EditedJan 17 2022, 5:45 PM

(tested with vagrant first)

On belvedere: do the dump and send it to the admin db node:

root@belvedere:~# DUMP_NAME=/srv/softwareheritage/postgres/keycloak-20220117.sql.gz; time sudo -i -u postgres pg_dump --clean --if-exists keycloak | pigz -c - > $DUMP_NAME && scp -i .ssh/id_ed25519.borg $DUMP_NAME dali.internal.admin.swh.network:/srv/postgresql/14/main/

On dali: Migrate data to the admin db keycloak:

root@dali:/srv/postgresql/14/main# DUMPNAME=keycloak-20220117.sql.gz; zcat $DUMPNAME | sudo -i -u postgres psql -e keycloak

On kelvingrove: modify the db connection setup and restart service:

cd /opt/keycloak
sed -i -e 's/db.internal.softwareheritage.org/db1.internal.admin.swh.network/g' config.cli
grep db1 config.cli
./bin/jboss-cli.sh --file=config.cli
systemctl restart keycloak
systemctl status keycloak | grep Active

It uses the right connection:

root@kelvingrove:/opt/keycloak# ss -tan | grep 5432
TIME-WAIT 0      0               192.168.100.106:46034           192.168.50.50:5432
ESTAB     0      0               192.168.100.106:46036           192.168.50.50:5432
TIME-WAIT 0      0               192.168.100.106:46042           192.168.50.50:5432
TIME-WAIT 0      0               192.168.100.106:46038           192.168.50.50:5432
ESTAB     0      0               192.168.100.106:46040           192.168.50.50:5432
ESTAB     0      0               192.168.100.106:46044           192.168.50.50:5432

Finally, activate puppet back:

puppet agent --enable && puppet agent --test
ardumont updated the task description. (Show Details)Jan 17 2022, 5:49 PM
ardumont moved this task from in-progress to deployed/landed/monitoring on the System administration board.Jan 18 2022, 9:43 AM
ardumont added a comment.Jan 19 2022, 6:16 PM

No issue encountered on impacted services.
Closing this.

ardumont closed this task as Resolved.Jan 19 2022, 6:16 PM
ardumont claimed this task.
ardumont moved this task from deployed/landed/monitoring to done on the System administration board.
ardumont mentioned this in T3849: Clean up nodes, databases, manifests.Jan 20 2022, 3:55 PM
ardumont changed the status of subtask T3849: Clean up nodes, databases, manifests from Open to Work in Progress.Jan 20 2022, 4:14 PM
ardumont closed subtask T3849: Clean up nodes, databases, manifests as Resolved.Jan 20 2022, 5:04 PM
vsellier mentioned this in T3889: Admin database backup.Jan 25 2022, 5:28 PM
gitlab-migration changed the task status from Resolved to Migrated.Oct 19 2022, 6:05 PM
gitlab-migration claimed this task.
gitlab-migration added a subscriber: gitlab-migration.

This task has been migrated to GitLab.

gitlab-migration changed the status of subtask T3849: Clean up nodes, databases, manifests from Resolved to Migrated.Oct 19 2022, 6:05 PM
About · Developer information · Legal