PostgreSQL

After considering various options, it was decided that PostgreSQL should be used as our database technology. As an initial implementation, we are deploying a PostgreSQL instance inside Kubernetes, using the open source Helm chart. It is added as a dependency to the ska-db-oda-umbrella chart. As the ODA matures and the requirements grow, this deployment mechanism will likely also change.

PostgreSQL is therefore deployed to the SKA persistent environments like any other service, and can be accessed through the URLs. It is also deployed locally when installing the Helm chart. See the README for more details.

Connect using pgAdmin

pgAdmin is an admin UI for PostgreSQL. We deploy an instance of pgAdmin in a separate pod alongside the PostgreSQL instance.

Once deployed, pgAdmin should be available at <KUBE_HOST>/<KUBE_NAMESPACE>/oda/api/<MAJOR_VERSION>/<RESOURCE>. The links for the CICD persistent environments are available from the info jobs in the pipeline. For example, the staging environment should be available at

https://k8s.stfc.skao.int/staging-ska-db-oda/pgadmin4

Once you are able to see the Pgadmin screen, login using the admin credentials given in the project’s Helm values or CI/CD variables.

The UI can be used to perform admin tasks on PostgreSQL, or use the query tool to query the data using SQL. For example, the following query returns the tables available in the database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='public';

Connect using PSQL

The PostgreSQL database instance can also be connected using the psql from a terminal. The following command can be used with the same login details as above.

$ PGPASSWORD=<PASSWORD> psql -U <USER> -d postgres -h <POSTGRES HOST>

Once you are connected using psql terminal, there are a few basic commands:

$ \du -- to check which user you are connected to
$ \dt -- to get a list of avilable tables
$ select info from tab_oda_sbd; --- to get json column from SBD table
$ \q to quit

Configuring the deployment

The Postgres deployment can be configured through the Helm chart values file. The options can be seen in the documentation.

We overwrite some of the defaults in the ska-db-oda-umbrella values.yaml and do environment specific configuration in the Makefile or CI file. For example, in the dev environment we do not deploy a PersistentVolume as this is not required for the temporary deployment, whereas in staging we do so that data is persisted across redeployments.

Initialisation script and schema

The ska-db-oda-umbrella chart contains SQL scripts which are ran on initialisation of PostgreSQL. They contain table definitions and any other set up tasks. To apply changes to this schema, the deployment must be stopped and restarted.