Page MenuHomeSoftware Heritage
Create Task
Maniphest T2736

Configure db1 as new staging db server
Closed, MigratedEdits Locked
Actions

Description

We want to aligned the staging db as closed as possible than the production server

The following datasets will be configured configured :

  • (already created) Dataset /data/postgres-main-12 mount on /srv/softwareheritage/postgres/12/main (postgresql:5433)
    • softwareheritage
  • Dataset /data/postgres-secondary-12 mount on /srv/softwareheritage/postgres/12/secondary (postgresql:5434)
    • swh-vault
    • swh-lister
    • softwareheritage-scheduler
    • softwareheritage-deposit
  • Dataset data/postgres-indexer-12 mounted on /srv/softwareheritage/postgres/12/indexer (postgresql:5435) [1]
    • softwareheritage-indexer

[1] On production the indexer is using postgresql 11 at the moment

  • Puppet
    • change the db0.internal.staging.swh.network node to use a wildcard selector
    • configure the new database node as the db host for the swh services

Revisions and Commits

rDENV Development environment
D4394rDENVebbf8fbf9789 vault: let postgresql initialize the db and use the swh db init-admin command
D4394rDENV0ec06a10426d listers: let postgresql initialize the db and use the swh db init-admin command
D4394rDENV38c5cc77eb58 deposit: let postgresql initialize the db
D4394rDENVf8fdb69b10eb vault: let postgresql initialize the db and use the swh db init-admin command
D4394rDENVbdb9f1b3085f scheduler: remove PG* variable from the db configuration
D4394rDENV1a921fc509d7 storage: let postgresql initialize the db and use the swh db init-admin command
D4377rDENV2a5d9a2776dd Simplify the scheduler's database initialization
rDCIDX Metadata indexer
D4378rDCIDXd910ad3ef2ea Rename sql files according to new conventions
rDVAU Software Heritage Vault
D4379rDVAU7b4b59c6246a Rename sql files according to new conventions
rSPSITE puppet-swh-site
D4391rSPSITE370e0af7750a Use the same postgresql data dir as the production servers
D4372rSPSITE676d35156450 staging: update all the services to use the new db1 server
D4375rSPSITEa098f1f60758 postgresql/server: Allow remote 'postgres' connection to postgresql
D4370rSPSITE8590a5dc4379 refactor the postgresql declaration to configure the main cluster instance
D4370rSPSITE1edb2d3d5da2 Update postgresql repository signing key
D4370rSPSITEe77721c27515 Include db1 as a db server for staging
D4370rSPSITEe0480b9ce36f Use the same role for staging and production db servers
rDCORE Foundations and core functionalities
D4374rDCORE745448399ebd cli.db: Open init-admin subcmd to initialize superuser-level scripts

Related Objects

Event Timeline

vsellier changed the task status from Open to Work in Progress.Oct 26 2020, 4:18 PM
vsellier triaged this task as Normal priority.
vsellier created this task.
vsellier created this object with edit policy "System administrators (Project)".
vsellier added a comment.Oct 26 2020, 4:24 PM
  • Create the postgresql:5434 dataset
zfs create data/postgres-secondary-12 -o mountpoint=/srv/softwareheritage/postgres/12/secondary
  • Create the postgresql:5435 dataset
zfs create data/postgres-indexer-12 -o mountpoint=/srv/softwareheritage/postgres/12/indexer
  • check datasets :
root@db1:~# zfs list -t all
NAME                         USED  AVAIL     REFER  MOUNTPOINT
data                        1.20M  26.4T       96K  /data
data/postgres-indexer-12      96K  26.4T       96K  /srv/softwareheritage/postgres/12/indexer
data/postgres-main-12         96K  26.4T       96K  /srv/softwareheritage/postgres/12/main
data/postgres-misc           104K  26.4T      104K  /srv/softwareheritage/postgres
data/postgres-secondary-12    96K  26.4T       96K  /srv/softwareheritage/postgres/12/secondary
vsellier mentioned this in rSPSITE81fdce11248b: Include db1 as a db server for staging.Oct 26 2020, 4:41 PM
vsellier added a subscriber: ardumont.Oct 26 2020, 6:26 PM

For the puppet part, the actual staging configuration needs some adaptations as the configuration install postgresql on version 11 and 13. Another point is the different clusters are not managed by puppet but it's the same for the production.

At least, the configuration needs to be adapted to install only the version 12 to have the same version as in the production environment (except for the indexer still using the version 11).
We will dig with @ardumont if it's also possible to manage the 3 psql clusters via puppet in a reasonable amount of time

ardumont mentioned this in D4359: Separate profile::devel::postgres into profile::postgres::client.Oct 27 2020, 10:53 AM
ardumont added a comment.Oct 27 2020, 12:11 PM

For the puppet part, the actual staging configuration needs some adaptations as the configuration install postgresql on version 11 and 13

Fixed by D4359.

vsellier added a revision: D4370: Staging: refactor the postgresql declaration to configure the main cluster instance.Oct 27 2020, 6:00 PM
vsellier added a comment.Oct 27 2020, 6:34 PM

The puppetlabs-postgresql module doesn't allow to manage several postgresql clusters. We have made the tradeoff to use only one cluster on db1 at the beginning to be able to deploy db1 via puppet as it's the priority. The module will be extended or replaced by something else later.

vsellier mentioned this in T2738: Configure several postgresql clusters with puppet.Oct 27 2020, 6:40 PM
vsellier added a commit: rSPSITEe0480b9ce36f: Use the same role for staging and production db servers.Oct 28 2020, 9:44 AM
vsellier added a commit: rSPSITEe77721c27515: Include db1 as a db server for staging.
vsellier added a commit: rSPSITE1edb2d3d5da2: Update postgresql repository signing key.
vsellier added a commit: rSPSITE8590a5dc4379: refactor the postgresql declaration to configure the main cluster instance.
vsellier added a revision: D4372: staging: update all the services to use the new db1 server.Oct 28 2020, 2:55 PM
ardumont added a revision: D4374: cli.db: Open init-admin subcmd to initialize superuser-level scripts.Oct 28 2020, 6:35 PM
ardumont added a revision: D4375: postgresql/server: Allow remote postgres access to db (via pgbouncer as well).Oct 28 2020, 6:57 PM
vsellier added a revision: D4377: Simplify the scheduler's database initialization.Oct 29 2020, 10:00 AM
ardumont added a revision: D4378: Rename sql files according to new conventions.Oct 29 2020, 12:15 PM
ardumont added a comment.EditedOct 29 2020, 1:27 PM

Road so far, after the puppet run which creates the dbs with guest, postgres
and swh-admin users.

As tryouts, from staging-db1 node (and a bit on the actual db1, which
we'll finish during the day):

$ cat init-db.sh
#!/usr/bin/env bash

set -x

export PGUSER=postgres
# export PGHOST=$(ip a | grep eth1 | grep inet | awk '{print $2}' | cut -d'/' -f1)
export PGHOST=db1.internal.staging.swh.network
export PGPORT=5432
# export PGPASSWORD=swh::deploy::db::postgres::password
export PGPASSWORD=<insert-postgres-pass-here>

MODULE=${1}

if [ "${MODULE}" == "storage" ]; then
   DBNAME="swh"
else
   DBNAME="swh-${MODULE}"
fi

# Connect as super-admin to init the extensions
swh db init-admin --db-name $DBNAME $MODULE   # should be changed to init-admin

# Connect as owner of the db to init the schema
export PGUSER=$DBNAME
export PGPASSWORD=${2}
swh db init --db-name $DBNAME $MODULE

# Connect as owner to grant read access to schema to guest user
psql -c "grant select on all tables in schema public to guest;"

The actual initialization calls to bootstrap the different schema below (the
second parameter is the password, for vagrant node, coming from the censored
private data repository, for the actual production, this will be adapted
accordingly):

$ ./init-db.sh scheduler swh::deploy::scheduler::db::password
DONE database for scheduler initialized at version 17
$ ./init-db.sh indexer swh::deploy::indexer::storage::db::password
DONE database for indexer initialized at version 132
$ ./init-db.sh vault swh::deploy::vault::db::password
DONE database for vault initialized at version 1
$ ./init-db.sh storage swh::deploy::storage::db::password
DONE database for storage initialized (flavor default) at version 163

Note:

  • swh db create is used for now, the code on the node is patched to comment out the sql create database call to prevent breaking (D4374).
  • the swh-admin superuser is used to allow privilege installation setup (D4375)

Other dbs have their own initialization way:

ardumont added a revision: D4379: Rename sql files according to new conventions.Oct 29 2020, 1:43 PM
ardumont added a commit: rDCIDXd910ad3ef2ea: Rename sql files according to new conventions.Oct 29 2020, 3:26 PM
ardumont added a commit: rDVAU7b4b59c6246a: Rename sql files according to new conventions.
ardumont mentioned this in rSPSITE02d2bc6c6757: deposit: Add the necessary storage key config.Oct 29 2020, 3:41 PM
ardumont added a commit: rDCORE745448399ebd: cli.db: Open init-admin subcmd to initialize superuser-level scripts.Oct 29 2020, 4:29 PM
vsellier added a commit: rDENV2a5d9a2776dd: Simplify the scheduler's database initialization.Oct 29 2020, 11:04 PM
ardumont added a commit: rSPSITEa098f1f60758: postgresql/server: Allow remote 'postgres' connection to postgresql.Oct 30 2020, 2:35 PM
ardumont mentioned this in rSPPRIVC3afbd19ed087: Fix mismatch user password between postgresql and pgbouncer.Oct 30 2020, 2:40 PM
vsellier added a commit: rSPSITE676d35156450: staging: update all the services to use the new db1 server.Oct 30 2020, 5:49 PM
ardumont mentioned this in rSPSITEbb0001afecfc: staging: Install storage1 as main storage for staging environment.Oct 30 2020, 6:24 PM
ardumont mentioned this in rSPSITE6c45b981bdae: staging: Install storage1 as main storage for staging environment.
ardumont added a comment.Oct 30 2020, 7:49 PM

Staging nodes are now using storage1 (and db1).
So db0 and storage0 can be decomissionned (and thus orsay \o/).

We triggered some loading (swh loader run git..., some are still running btw, linux, nixpkgs, guix, ...).
Icinga checks have been fixed a bit... (firewall rule update, ...)

We need to shake some more other services (save code now, cooking task, trigger listings, ... )

vsellier added a revision: D4391: Use the same postgresql data dir as the production servers.Nov 2 2020, 12:13 PM
vsellier added a comment.EditedNov 2 2020, 2:46 PM

Following the diff D4391, the zfs dsatasets were reconfigured tobe mounted on the /srv/softwareheritage/postgres/* :

systemctl stop postgresql@12-main
zfs set mountpoint=none data/postgres-indexer-12
zfs set mountpoint=none data/postgres-secondary-12
zfs set mountpoint=none data/postgres-main-12
zfs set mountpoint=none data/postgres-misc

zfs set mountpoint=/srv/softwareheritage/postgres data/postgres-misc
zfs set mountpoint=/srv/softwareheritage/postgres/12/indexer data/postgres-indexer-12
zfs set mountpoint=/srv/softwareheritage/postgres/12/secondary data/postgres-secondary-12
zfs set mountpoint=/srv/softwareheritage/postgres/12/main data/postgres-main-12

systemctl start postgresql@12-main

It must be done in this order because there is a hierarchical mount between postgres/ and postgres/*

ardumont mentioned this in D4391: Use the same postgresql data dir as the production servers.Nov 2 2020, 2:48 PM
vsellier added a commit: rSPSITE370e0af7750a: Use the same postgresql data dir as the production servers.Nov 2 2020, 2:49 PM
vsellier mentioned this in D4394: Finalize the database initialization refactoring to use the default of the container.Nov 3 2020, 10:31 AM
vsellier added a revision: D4394: Finalize the database initialization refactoring to use the default of the container.Nov 3 2020, 10:32 AM
vsellier closed this task as Resolved.Nov 3 2020, 12:38 PM

We have performed with @ardumont several tests on the webapp, the vault, the deposit, the loaders and the listers and it seems everything is working well.

The remaining monitoring alerts are due to repositories not existing anymore and not easily importable. The monitored urls will be updated to something more easy to ingest in a separate tasks.

this one can be closed.

vsellier added a commit: rDENV1a921fc509d7: storage: let postgresql initialize the db and use the swh db init-admin command.Nov 3 2020, 12:47 PM
vsellier added a commit: rDENVbdb9f1b3085f: scheduler: remove PG* variable from the db configuration.
vsellier added a commit: rDENVf8fdb69b10eb: vault: let postgresql initialize the db and use the swh db init-admin command.
vsellier added a commit: rDENV38c5cc77eb58: deposit: let postgresql initialize the db.
vsellier added a commit: rDENVebbf8fbf9789: vault: let postgresql initialize the db and use the swh db init-admin command.
vsellier added a commit: rDENV0ec06a10426d: listers: let postgresql initialize the db and use the swh db init-admin command.
gitlab-migration changed the task status from Resolved to Migrated.Oct 19 2022, 6:00 PM
gitlab-migration claimed this task.
gitlab-migration added a subscriber: gitlab-migration.

This task has been migrated to GitLab.

About · Developer information · Legal