PostgreSQL schema initialization failure during Kubernetes deployment

PostgreSQL deployed with an incomplete schema, missing childcare-specific tables and fields required by the application. This led to application failures due to schema mismatches, such as missing tables (horse_references, safety_verifications, bookings) and critical fields in the users table like hourly_rate and subscription_active.

Issue

• Missing schema tables: The database lacked specialized tables required for childcare platform functionality.
• Incomplete user table: The users table was missing fields like hourly_rate, subscription_active, and location.
• Broken relationships: Foreign key constraints and relationships were absent, causing application errors.
• Test failures: Attempts to create test users failed due to missing required columns.

Culprit

Three technical failures in the deployment sequence caused the incomplete schema:

1. Missing ConfigMap Application: The postgres-init-configmap.yaml was not applied during deployment, preventing the schema initialization scripts from being mounted.
2. Incorrect Deployment Order: The ConfigMap, which contains schema initialization scripts, must be created and mounted before the PostgreSQL pod starts.
3. PostgreSQL Init Timing: PostgreSQL init scripts only execute on the first startup with an empty data directory, requiring careful sequencing of resource creation.

The deployment script applied PostgreSQL resources but omitted the critical ConfigMap application step, leading to an incomplete schema.

Solution

1. Deployment Script Fix

• Reordered deployment steps: Modified the deployment script to apply the ConfigMap before deploying PostgreSQL, ensuring the schema initialization scripts are mounted before the database starts.

2. Database Recreation Procedure

• Clean removal of existing resources: Removed existing PostgreSQL resources (deployment and PVC) to ensure a clean initialization.
• Proper initialization sequence: Applied the ConfigMap, followed by the PVC and PostgreSQL deployment, to guarantee schema initialization occurs correctly.

3. Schema Verification

• SQL validation: Verified the schema application through SQL queries to confirm the presence of required tables, fields, and constraints.

The solution ensures future deployments correctly initialize the database schema by enforcing proper Kubernetes resource ordering and PostgreSQL initialization timing.