Updating Postgres Image

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

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.

1. Preparing for an Update

1.1. Backup Existing Data in the Postgres Instance (Non Keycloak)

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;