RunAI Backup, Restore and Migration of Postgres

kubectl get pods -n runai | grep postgresql
# Expected: postgresql-master pod in Running state

mkdir -p ~/backup_workloads_$(date +%Y%m%d)
cd ~/backup_workloads_$(date +%Y%m%d)

kubectl exec -n runai deploy/postgresql-master -- \
 pg_dump -U postgres -d backend -n workloads \
 > backup_workloads_CRITICAL_$(date +%Y%m%d_%H%M%S).sql

ls -lh backup_workloads_CRITICAL_*.sql

Expected output:
-rw-r--r-- 1 user user 15M Feb 20 14:30 backup_workloads_CRITICAL_20260220_143045.sq

kubectl exec -n runai deploy/postgresql-master -- \
 psql -U postgres -d backend -c \
 "SET search_path TO workloads; SELECT version, dirty FROM schema_migrations;"

Expected output (current state):
 version | dirty
---------+-------
 2220006 | f

Or

 version | dirty
---------+-------
 2220009 | t

kubectl exec -n runai runai-backend-postgresql-0 -- \
 psql -U postgres -d backend -c \
 "SET search_path TO workloads; SELECT tablename FROM pg_tables WHERE schemaname = 'workloads' ORDER BY tablename;"

kubectl exec -n runai runai-backend-postgresql-0 -- \
psql -U postgres -d backend << 'EOF'
SET search_path TO workloads;
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'workloads'
    AND column_name = 'tenant_id'
ORDER BY table_name;
EOF

  table_name               | column_name | data_type
----------------------------+-------------+-----------
 events                    | tenant_id  | bigint
 pods                      | tenant_id  | bigint
 workloads                 | tenant_id  | bigint
 workloads_phase_history   | tenant_id  | bigint

  table_name                      | column_name | data_type

------------------------------------+-------------+-------------------
 events                            | tenant_id  | bigint
 external_workloads_type_config    | tenant_id  | character varying
 pods                              | tenant_id  | bigint
 priorities                        | tenant_id  | text
 workload_type_resource_interfaces | tenant_id  | character varying
 workload_type_statuses            | tenant_id  | character varying
 workloads                         | tenant_id  | bigint
 workloads_categories              | tenant_id  | bigint
 workloads_phase_history           | tenant_id  | bigint

# Replace <VERSION> with the version number shown in Step (1)_
kubectl exec -n runai runai-backend-postgresql-0 -- \
 psql -U postgres -d backend -c \
 "SET search_path TO workloads; UPDATE schema_migrations SET dirty = 'f' WHERE dirty = 't';"

kubectl exec -n runai runai-backend-postgresql-0 -- \
psql -U postgres -d backend -c \
"SET search_path TO workloads; SELECT version, dirty FROM schema_migrations;"


## Expected output:
version | dirty
---------+-------
<VERSION> | f

kubectl get pods -n runai | grep -iE "workload-manager|workload"

# Use the label from step (5). Common labels:
kubectl delete pod -n runai -l app=workload-manager
# OR if the label is different:
# kubectl delete pod -n runai <pod-name>

kubectl logs -n runai -l app=workload-manager -f --tail=100

{"level":"info","message":"Start migration of schema workloads with command"}
{"level":"info","message":"connecting to postgres db:backend, schema: workloads..."}
{"level":"info","message":"Migrating up"}
{"level":"info","message":"Migrations ended successfully"}

{"level":"fatal","message":"failed running migrations: Dirty database version XXXXXXX. Fix and force version."}
# This is expected. Note the version number from the log, then fix it:
kubectl exec -n runai runai-backend-postgresql-0 -- \
 psql -U postgres -d backend -c \
 "SET search_path TO workloads; UPDATE schema_migrations SET version = XXXXXXX, dirty = 'f';"

kubectl delete pod -n runai -l app=workload-manager
# Repeat Step (7) until migrations complete successfully.

kubectl exec -n runai runai-backend-postgresql-0 -- \
psql -U postgres -d backend -c \
"SET search_path TO workloads; SELECT version, dirty FROM schema_migrations;"



# Expected output:
 version | dirty
---------+-------
 2230018 | f

kubectl exec -n runai runai-backend-postgresql-0 -- \
psql -U postgres -d backend `<<` 'EOF'
SET search_path TO workloads;

# Check critical tables exist
SELECT tablename FROM pg_tables
WHERE schemaname = 'workloads'
AND tablename IN ('priorities', 'workload_types', 'crud_templates',
'applications', 'revisions')
ORDER BY tablename;



# Check priorities were seeded
SELECT name, value FROM priorities WHERE source = 'Run:ai' ORDER BY value;

# Check workload types were seeded
SELECT name FROM workload_types ORDER BY name;



# Check views exist
SELECT viewname FROM pg_views
WHERE schemaname = 'workloads'
AND viewname IN ('timeout_events_of_workload_view', 'workloads_phases_view', 'external_workloads_type_config_view')
ORDER BY viewname;
EOF

# Expected output (newer tables should have text/varchar):
   tablename
-----------------
 applications
 crud_templates
 priorities
 revisions
 workload_types
(5 rows)

   name    | value
-------------+-------
 very-low   |   25
 low        |   40
 medium-low |   65
 medium     |   80
 medium-high |   90
 high       |  125
 very-high  |  150
(7 rows)


        name
-----------------------
 AmlJob
 CronJob
 Deployment
 DevWorkspace
 ... (24 rows)


             viewname
-------------------------------------
 external_workloads_type_config_view
 timeout_events_of_workload_view
 workloads_phases_view
(3 rows)

kubectl logs -n runai -l app=workload-manager --tail=100 | grep -iE "error|failed|fatal"

# Expected Output:
No migration-related errors.

kubectl logs -n runai runai-backend-postgresql-0 --tail=200 | grep -iE "duplicate key|foreign key"

# Expected output:
No more duplicate key or foreign key errors. These should stop once the schema is consistent.