Adding a new database¶
From time to time you might need to add a new database to the internal Postgres instance.
Before you do, please ask yourself how valuable the data is: the internal Postgres is not intended to be either highly available or extremely reliable. It’s designed for persistent storage for low-value data, such as the JupyterHub session database, or Gafaelfawr’s authentication tokens, where the worst thing that happens, if it is wiped out, is that a bunch of users lose their running sessions and have to reauthenticate.
Assuming that the internal Postgres is indeed the right choice for your needs, there are several steps.
Decide on a database name¶
In general the database will require three things: a database name, a
username, and a password. Usually the database name and user should be
identical and should reflect the service that will consume the database,
e.g. gafaelfawr
or exposurelog
. We will use exposurelog
as
the model for the remainder of this document.
Add the database to the deployment¶
Go to the services/postgres/templates
directory from the Phalanx
root, and edit deployment.yaml
to add the new database/password
entry. You should copy an existing entry, and it should look like this:
{{- with .Values.exposurelog_db }} - name: VRO_DB_EXPOSURELOG_USER value: {{ .user }} - name: VRO_DB_EXPOSURELOG_DB value: {{ .db }} - name: VRO_DB_EXPOSURELOG_PASSWORD valueFrom: secretKeyRef: name: postgres key: exposurelog_password {{- end }}
Add the database to Phalanx installer¶
Next add a password entry to Phalanx’s installer, so the next time a new
cluster is deployed or an extant cluster is redeployed, the password
will be created. This belongs in installer/generate_secrets.py
in
the _postgres()
method.
Typically we use passwords that are ASCII representations of random 32-byte hexadecimal sequences. The passwords for all the non-root Postgres users already look like that, so copying an existing line and changing the name to reflect your service is usually correct:
self._set_generated("postgres", "exposurelog_password", secrets.token_hex(32))
Finally, go edit the postgres values-<env>.yaml
files and add
a section for your new database with appropriate user
and db
entries:
exposurelog_db: user: 'exposurelog' db: 'exposurelog'
Now start the PR and review process. However, there is a step you still must do before you can synchronize the updated services: put the password into Vault so it appears in the postgres secrets.
Manually add the secret to Vault¶
Since you have already added generation of the password to the installer, you could just generate new secrets for each environment and push them into Vault. That, however, would require that you restart everything with randomly-generated passwords, and that’s a fairly disruptive operation, so you probably are better off manually injecting just your new password.
Consult
1Password
and retrieve the appropriate vault write token for the instance you’re working with fromvault_keys.json
.Set up your environment:
export VAULT_ADDR=vault.lsst.codes ; export VAULT_FORMAT=json ; export VAULT_TOKEN=<retrieved-token>
Run
vault kv patch secret/k8s_operator/<instance>/postgres <database-name>_password=$(openssl rand -hex 32)
to generate and store a new random password.Delete the
postgres
secret from thepostgres
namespace to force Vault Secrets Operator to recreate it.Repeat for each environment where you need the new database.
Restart with new values¶
Now it’s finally time to synchronize Postgres in each environment. There is no new application version, so all you should need to do is resynchronize the deployment from ArgoCD.
This will cause a brief service interruption in the cluster, as the existing deployment is recreated with additional environment variables and PostgreSQL restarts, so bear that and your cluster’s maintenance window policy in mind.
Much of the time, the restart of the postgres
deployment gets stuck
and the old Pod will not terminate and allow the new one to run. If
that happens, you need to identify the ReplicaSet responsible for the
stuck Pod, and delete that ReplicaSet.
Once Postgres restarts, the new database will be present, with the user and password set. At that point it is ready for use by your new service.