Updating the Postgres Image (Non Keycloak)

Applicable to on-premise deployments

You are expected to have access directly to the Kubernetes or Openshift pods and have the ability to delete/create pods and deployments to progress this this.

Purpose of this article:

Instructions how to safely update the Postgres instance within the K Platform with any new image releases from KADA.

Instructions

1. Preparing for an Update

1.1. Backup Existing Data in the Postgres Instance

Execute the following SQL to create a back-up of the existing data as any Postgres image updates will incorporate a new Job that will do any additional changes on top of the existing Tables.

This is a pre-cautionary step and is highly recommended. Skip this step at your own risk.

DROP TABLE IF EXISTS data_operations_by_time_backup;
DROP TABLE IF EXISTS insights_backup;
DROP TABLE IF EXISTS job_control_backup;
DROP TABLE IF EXISTS job_scheduler_backup;
DROP TABLE IF EXISTS metadata_change_log_backup;
DROP TABLE IF EXISTS node_activity_backup;
DROP TABLE IF EXISTS object_keys_backup;
DROP TABLE IF EXISTS procedure_control_backup;
DROP TABLE IF EXISTS query_by_user_by_day_backup;
DROP TABLE IF EXISTS relationship_activity_backup;
DROP TABLE IF EXISTS trust_score_by_node_by_time_backup;
DROP TABLE IF EXISTS user_by_table_by_day_backup;
DROP TABLE IF EXISTS user_group_rel_backup;
DROP TABLE IF EXISTS user_report_control_backup;

CREATE TABLE data_operations_by_time_backup AS SELECT * FROM data_operations_by_time;
CREATE TABLE insights_backup AS SELECT * FROM insights;
CREATE TABLE job_control_backup AS SELECT * FROM job_control;
CREATE TABLE job_scheduler_backup AS SELECT * FROM job_scheduler;
CREATE TABLE metadata_change_log_backup AS SELECT * FROM metadata_change_log;
CREATE TABLE node_activity_backup AS SELECT * FROM node_activity;
CREATE TABLE object_keys_backup AS SELECT * FROM object_keys;
CREATE TABLE procedure_control_backup AS SELECT * FROM procedure_control;
CREATE TABLE query_by_user_by_day_backup AS SELECT * FROM query_by_user_by_day;
CREATE TABLE relationship_activity_backup AS SELECT * FROM relationship_activity;
CREATE TABLE trust_score_by_node_by_time_backup AS SELECT * FROM trust_score_by_node_by_time;
CREATE TABLE user_by_table_by_day_backup AS SELECT * FROM user_by_table_by_day;
CREATE TABLE user_group_rel_backup AS SELECT * FROM user_group_rel;
CREATE TABLE user_report_control_backup AS SELECT * FROM user_report_control;

1.2. Updating Postgres

KADA uses a public image for the Non Keycloak instance of Postgres. It uses a Timescale Database Image so there is generally no need to update the Image unless we specify that you need to update the version of the Timescale Database image.

In the event that the version requires updating you need to change the image name for the following files:

postgres-setup.yaml
redash-postgres.yaml

The general format for the image is

image: timescale/timescaledb:<version>

Once you have updated the image names you can proceed to delete the previous postgres-setup job

kubectl delete job postgres-setup

OR

oc delete job postgres-setup

Then proceed to deploy all of Postgres again

The Postgres setup job is designed to be idempotent so it will only apply incremental changes where they have not already been applied for the latest update. If you are multiple versions of the platform behind then it will apply all relevant changes to your Postgres database instance.