PostgresQL DB migration for WFM

1. Clone the database in Postgres

Follow this guide to clone the wfm db in Postgres deployed in the ef-external namespace. Follow the steps below to create one.

1. exec into the ef-postgresql-0 pod in the ef-external namespace

kubectl -n ef-external exec -it ef-postgresql-0 -- bash

2. Execute the environment setup for ef-postgresql-0 (only needed when PostgreSQL runs in non-HA mode, such as without pgpool and multiple PostgreSQL replicas)

/opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash

3. Log into Postgres using the following command

psql --host ef-postgresql -U sa postgres -p 5432

4. The system prompts for the password for user sa. Enter the password configured during PostgreSQL deployment. You should then be logged into the Postgres shell.

5. Clone the databases named with tenants.

   SQL

   -- Connect to postgres database
   \c postgres
   
   -- Terminate active connections to the tenant database
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = '{tenantID}'
     AND pid <> pg_backend_pid();
   
   -- Clone the tenant database
   CREATE DATABASE wfm_{tenantID}
   WITH TEMPLATE {tenantID}
   OWNER sa;
   
   -- Connect to the ORIGINAL tenant database
   \c {tenantID}
   
   -- Enable extension if required
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   
   -- Drop RTA_* and restapi_* tables from the ORIGINAL database
   DO $$
   DECLARE
       r RECORD;
   BEGIN
       FOR r IN
           SELECT tablename
           FROM pg_tables
           WHERE schemaname = 'public'
             AND (
                 tablename ILIKE 'RTA_%'
                 OR tablename ILIKE 'restapi_%'
                 OR tablename ILIKE 'auth_%'
                 OR tablename ILIKE 'restapi_%'
                 OR tablename ILIKE 'django_%'
                 OR tablename ILIKE 'stategroup_states' 
             )
       LOOP
           EXECUTE format('DROP TABLE IF EXISTS public.%I CASCADE;', r.tablename);
           RAISE NOTICE 'Dropped table: %', r.tablename;
       END LOOP;
   END $$;
   

6. Exit the shell using:

\q
exit
exit