AWS RDS PostgreSQL 13 → 17 major version upgrade — Django + Strapi production app (no staging env)

We run a production web application on AWS consisting of: - *Django 4.1.5* on *Python 3.8* (backend API) - *Strapi 4.8.2* on *Node 16.14* (headless CMS) - *Next.js 14.1 / React 18.2* on Node 24 (frontend) - *PostgreSQL 13* on Amazon RDS (production) PostgreSQL 13 has reached end of standard support, and we are now being billed for AWS RDS Extended Support (roughly $150/month extra). We need to get off version 13 and onto a currently-supported major version to eliminate that charge and get back on standard security patching. *Important context, please read before applying:* - We do *not* have a staging or development environment. Production is the only environment. - Our team did *not* build this application and is *not deeply familiar* with its internals. We cannot tell you up front which raw SQL queries, extensions, or stored procedures exist. Discovery is part of the job. - *We have never successfully run this application outside of production.* Please do not plan around us spinning up a local copy — testing on a cloned database will be database-level only, and application testing happens on production immediately after switchover. This shapes the method (see Phase 4). - We are risk-averse. We would rather pay for a careful, rehearsed migration than a fast one. - This engagement is *scoped to the database upgrade only.* The application-level upgrades (Python 3.8, Django 4.1, Node 16, Strapi 4) are known technical debt but are explicitly *out of scope* for this job, except where a minimal change (e.g. bumping a database driver) is strictly required for the new Postgres version to work. ### Objective Move the production database from PostgreSQL 13 to *PostgreSQL 17* (we are open to 16 if you make a case for it) on Amazon RDS, with: - Minimal, planned, and communicated downtime - A tested, working rollback path at every stage - No data loss - No post-upgrade performance regression - The RDS Extended Support line item gone from our next AWS bill ### Scope of work *Phase 1 — Discovery & assessment (report deliverable)* 1. Inventory the current RDS setup: engine version, instance class, storage, Multi-AZ status, parameter group, read replicas, backup retention, whether RDS Proxy is in use, and whether this is RDS or Aurora. 2. Inventory what the database actually uses: installed extensions (pg_extension), custom roles and their privileges, schemas, database size, largest tables, any stored procedures/triggers/views, and any non-default collation settings. 3. Scan the Django and Strapi codebases for raw SQL, custom migrations, and anything that could break across four major versions. 4. Verify the valid RDS upgrade targets from our exact version (`aws rds describe-db-engine-versions --engine postgres --engine-version 13.x --query "DBEngineVersions[].ValidUpgradeTarget[].EngineVersion:EngineVersion"`) and confirm whether 13 → 17 is a single-step upgrade or needs an intermediate hop. 5. Confirm driver compatibility: psycopg2 version pinned by Django, and the pg/knex driver versions used by Strapi 4.8. 6. Deliver a short written *risk assessment and upgrade plan*, including recommended target version, recommended method, and estimated downtime. *Phase 2 — Build a throwaway *database clone** Since we have no staging, you will create one. To be explicit: **we do not want a full duplicate of the application environment.** We are not changing application code, so a database clone plus the two testing methods below is sufficient. Do not spend budget deploying copies of our services. 1. Take a manual snapshot of production RDS. 2. Restore that snapshot into a *temporary, isolated RDS instance* (smaller instance class is fine — keep it cheap, we'll delete it when done). 3. *Capture the application's real query surface without needing to understand the codebase.* On production, set log_min_duration_statement = 0 for a 30–60 minute window during normal traffic. This is a dynamic parameter — no restart or downtime required. Export the RDS logs; this gives a complete factual inventory of every statement Django and Strapi actually issue. If pg_stat_statements is already enabled, pull the top query fingerprints from there instead. - Monitor log volume so we don't run up a CloudWatch bill. - These logs will contain production user data. Handle accordingly and delete after use. 4. Establish a known-good baseline on the clone *before* upgrading: schema dump, row counts, and timings for the captured queries. *Phase 3 — Rehearsal* 1. Perform the full upgrade against the clone, timing every step. 2. Handle the known post-upgrade tasks, including at minimum: - Create and apply a *new parameter group* for the target engine family, porting over any custom parameters (these do not carry over automatically). - Run *ANALYZE* across all databases — pg_upgrade does not carry over planner statistics, and skipping this causes severe query slowdowns. - *REINDEX* text/varchar indexes if the underlying OS/glibc/ICU collation version changed between engine versions. This is the most common silent data-integrity issue in RDS major upgrades and must be explicitly verified, not assumed. - Run ALTER EXTENSION ... UPDATE for every installed extension. - Verify *PostgreSQL 15's public schema privilege change* — non-owner roles lost the default CREATE privilege on public. Confirm the Django migration user and the Strapi user still have the grants they need, or Django migrate and Strapi's schema sync will fail. - Verify password_encryption / SCRAM-SHA-256 behaviour and that both app users can still authenticate. 3. Validate the upgraded clone at the *database level* (no application deployment needed): - pg_dump --schema-only before vs. after, diffed — catches broken views, functions, triggers, and changed types - Row counts and checksums on every table - amcheck (bt_index_check / bt_index_parent_check) across all B-tree indexes to prove index integrity after any collation change - Replay the queries captured in Phase 2 against the clone; compare both results and timings against the pre-upgrade baseline - Confirm both application roles connect successfully and hold the grants they need 4. *Note: our team cannot run this application outside of production.* Do not plan on us pointing a local environment at the clone — it isn't possible. Application-level testing will happen on production immediately after switchover (see Phase 4). Your rehearsal on the clone is therefore database-level only, and needs to be thorough. 5. Write up an exact, timed *runbook* for the production cutover, including the rollback procedure and the rollback decision deadline. *Phase 4 — Production cutover (and the real application test)* Because we cannot run the application anywhere except production, the switchover itself is our application test. *RDS Blue/Green deployment is required for this job* — it swaps the endpoint names so our application needs no config change or redeploy, and it keeps the green database in sync with production right up to the moment of switchover. 1. Agree a maintenance window during our lowest-traffic period. 2. Before the window, confirm and document **how the application resolves its database endpoint** (hardcoded .env, AWS Secrets Manager, DNS CNAME, etc.) and how quickly it could be repointed manually if the automated switchover misbehaves. 3. Take a fresh manual snapshot immediately before starting. 4. Confirm the green instance is configured identically to production where it matters: instance class, storage, Multi-AZ, encryption, security groups, backup retention, and the ported parameter group. After switchover it is production. 5. Execute the switchover per the runbook. 6. Run the post-switchover database tasks immediately: ANALYZE, REINDEX where required, ALTER EXTENSION ... UPDATE, and grant verification. 7. *Joint smoke test with our team, against a hard deadline.* We will exercise the Strapi admin, Django admin, key API endpoints, and content read/write. Writes made after switchover are not replicated back to the old instance, so we agree a **rollback decision deadline in advance** (suggested: 45 minutes). If we have not signed off by that deadline, we roll back. After the deadline we are committed and any issue is fixed forward. 8. If our application supports a maintenance mode or read-only mode, advise whether enabling it during the test window is feasible — this would make rollback fully lossless. 9. Monitor for an agreed period after sign-off (CloudWatch, slow query log, application error rates). 10. Clean up: delete the temporary clone, delete the old blue instance once we sign off, and confirm we are no longer accruing Extended Support charges. ### Deliverables 1. Written discovery report and risk assessment (Phase 1) 2. Timed, step-by-step production runbook including rollback procedure (Phase 3) 3. Successfully upgraded production RDS instance on the target version 4. Post-upgrade verification report (smoke test results, query performance comparison, confirmation that Extended Support billing has stopped) 5. Short handover note listing anything you found that we should fix later (technical debt, misconfigurations, security issues) ### Acceptance criteria - Production is running PostgreSQL 17 (or agreed target) on Amazon RDS - Django and Strapi both start cleanly and serve traffic; manage.py migrate runs without error - No data loss — row counts on all tables match pre-upgrade values - Query performance is equal to or better than pre-upgrade (statistics rebuilt) - All extensions updated to versions compatible with the new engine - All indexes rebuilt where required by collation changes - Actual downtime is within the window we agreed in advance - Temporary resources cleaned up; no orphaned instances still billing - Runbook and reports delivered in writing ### What we provide - AWS IAM access, scoped to what's needed (read-only for discovery, escalated for the cutover) - Read access to the Git repositories - A point of contact available during the maintenance window - Budget approval for the temporary staging RDS instance ### Screening questions (please answer in your proposal) 1. Have you performed an RDS PostgreSQL major version upgrade that skipped multiple major versions? Briefly describe one, including the database size and actual downtime achieved. 2. What is your view on ANALYZE and REINDEX after a pg_upgrade-based RDS major upgrade, and why? 3. What changed regarding the public schema in PostgreSQL 15, and why does it matter for an application that runs migrations at deploy time? 4. Would you recommend Blue/Green deployment or an in-place upgrade for this case, and what's the trade-off? 5. How would you build a test environment for a team that has no staging environment? 6. What is your estimate for total hours, and what's your proposed fixed price?