PostgreSQL Restore

Restore PostgreSQL second to establish application data and authentication.

Restore Process:

The pgsql-restore.sh script performs the following operations:

(Click the file name above to download the script.)

  1. Validates the backup file exists and is a .sql.gz file
  2. Displays a warning about data replacement
  3. Requires explicit confirmation (type 'yes' or use --yes flag)
  4. Retrieves PostgreSQL credentials
  5. Locates the PostgreSQL pod and namespace
  6. Prefers superuser 'postgres' if available, falls back to 'gv'
  7. Tests database connectivity
  8. Streams the decompressed SQL into psql
  9. Reports completion status

Running the Restore:

./pgsql-restore.sh postgres-backup-20251103-130603.sql.gz

Or skip confirmation prompt:

./pgsql-restore.sh postgres-backup-20251103-130603.sql.gz --yes

Expected Output:

========================================
PostgreSQL Restore Script
========================================

WARNING: This restore will DROP and recreate roles, databases, and objects.
- Existing data may be overwritten
- Mappings and permissions will be replaced
- Proceed only if you understand the risk

Type 'yes' to continue: yes
[i] Retrieving PostgreSQL password...
[✓] Retrieved
[i] Finding PostgreSQL pod...
[✓] Found: gv-postgresql-0 (namespace: default)
[✓] Using superuser: postgres
[i] Testing PostgreSQL connection...
[✓] Connected
[i] Starting restore from postgres-backup-20251103-130603.sql.gz...
[✓] Restore completed

========================================
Restore Complete
========================================
File: postgres-backup-20251103-130603.sql.gz

Important Notes:

The restore process is idempotent - it creates or updates objects without attempting to drop existing roles or databases. This approach avoids dependency conflicts and allows the restore to complete successfully even if some objects already exist.

Verification: After restore, verify PostgreSQL data:

# List all databases
kubectl exec -it <postgresql-pod> -- psql -U gv -c '\l'

# Check table counts in key databases
kubectl exec -it <postgresql-pod> -- psql -U gv -d auth -c '\dt'
kubectl exec -it <postgresql-pod> -- psql -U gv -d keycloak -c '\dt'

# Verify data integrity with sample queries
kubectl exec -it <postgresql-pod> -- psql -U gv -d auth -c 'SELECT COUNT(*) FROM users;'