Skip to main content

Refreshing staging from production

Staging can be reloaded with a copy of the production data using a single script. The script never touches the production databases. It works from the daily backups AWS already takes, restores them into a temporary clone, and dumps from the clone.

The load stage destroys staging data

Loading drops and recreates the staging nexgen, nexgen_free, and nexgen_mdb databases. Anything only present in staging is gone. The staging nutkat database is not touched.

Production is never read from

Production is only ever referenced by describe-db-snapshots, which lists its backups. No query, dump, or connection is ever made to a production database, and production credentials are never read.

What this does​

The script runs six stages in order.

  1. Restore. Finds the newest available snapshot of nexgen-v3-prod-mysql and nexgen-v3-prod-docdb, then restores both into a throwaway clone placed in the staging VPC. This is the slow part, usually 30 to 60 minutes.
  2. Dump. Resets the clone's master passwords to the staging passwords, then runs mysqldump and mongodump against the clone from inside the staging bastion and uploads the results to S3.
  3. Download. Copies the dumps from S3 to this machine and checks them against the SHA-256 sums in the manifest.
  4. Load. Drops and reloads the staging databases from the dumps.
  5. Verify. Compares every table and collection count in staging against the counts taken from the clone.
  6. Cleanup. Deletes the clone.

The clone exists because RDS and DocumentDB snapshots are binary. There is no way to read one without first restoring it onto a running instance. Restoring into the staging VPC is what lets the staging bastion reach it, since production and staging sit in different VPCs.

The clone's master password is reset to the staging password so that the bastion can connect using the credentials already in its environment. This is why the process needs no access to production secrets at all.

Prerequisites​

The AWS CLI, jq, and the Session Manager plugin must be installed, and your AWS credentials must be for account 202077789656 in us-east-1.

aws sts get-caller-identity

The staging bastion service must be running. If it is not, start it:

copilot svc deploy -a nexgen-v3 -e staging -n bastion

The script lives in the nexgen_v3 repo, not in this one.

Running the refresh​

Always start with a dry run. It makes no changes and prints every command it would issue, including the full text of the scripts it would run inside the bastion.

cd ~/git/axxya/nexgen_v3
./scripts/refresh-staging-from-prod.sh --dry-run

Then run it for real:

./scripts/refresh-staging-from-prod.sh

The load stage stops and asks you to type STAGING before it destroys anything. Pass --yes-overwrite-staging to skip that prompt when running unattended.

Expect the whole run to take about an hour, nearly all of it waiting for the restore. The dump and load stages print a progress line every 30 seconds so the exec session is not closed by the Session Manager idle timeout.

Where the files land​

In S3, under one prefix per run:

s3://nexgen-v3-backups/prod-refresh/20260814/mysql_nexgen_20260814.sql.gz
s3://nexgen-v3-backups/prod-refresh/20260814/mysql_nexgen_free_20260814.sql.gz
s3://nexgen-v3-backups/prod-refresh/20260814/mongo_nexgen_mdb_20260814.tar.gz
s3://nexgen-v3-backups/prod-refresh/20260814/counts-source.json
s3://nexgen-v3-backups/prod-refresh/20260814/manifest.json

manifest.json records which snapshot each dump came from, along with file sizes and checksums, so a local copy can always be traced back to a specific production backup.

The bucket is created by the script on first use, with public access blocked, encryption on, and a lifecycle rule that expires anything under prod-refresh/ after 30 days. The bastion's IAM role already grants it read and write on this bucket.

Locally, the files are copied to a new dated directory:

~/Desktop/Axxya Private Local Files/db_backups/db_backups_prod_20260814/

Resuming a failed run​

If any stage fails, the clone is deliberately left running so you do not have to sit through another restore. The script prints the resume command when it exits. Runs are identified by a run ID, which defaults to today's date in YYYYMMDD form.

./scripts/refresh-staging-from-prod.sh --run-id 20260814 --from dump

A single stage can also be run on its own:

./scripts/refresh-staging-from-prod.sh --run-id 20260814 --only verify

Cleaning up a leftover clone​

A clone left behind by a failed run keeps billing until it is deleted, so check for one after any failure.

./scripts/refresh-staging-from-prod.sh --run-id 20260814 --cleanup

To check whether any clone is still running:

aws rds describe-db-instances --query 'DBInstances[?starts_with(DBInstanceIdentifier, `nexgen-clone`)].DBInstanceIdentifier'
aws docdb describe-db-clusters --query 'DBClusters[?starts_with(DBClusterIdentifier, `nexgen-clone`)].DBClusterIdentifier'

If the script cannot be used, the clone can be deleted by hand:

aws rds delete-db-instance --db-instance-identifier nexgen-clone-20260814-mysql --skip-final-snapshot --delete-automated-backups
aws docdb delete-db-instance --db-instance-identifier nexgen-clone-20260814-docdb-1
aws docdb delete-db-cluster --db-cluster-identifier nexgen-clone-20260814-docdb --skip-final-snapshot

After a refresh​

Staging now holds production accounts, so staging logins are production logins. Your usual staging test accounts will not exist unless they also exist in production.

Check that the migration state matches what the V3 code expects, since production can be behind staging:

copilot svc exec -a nexgen-v3 -e staging -n nexgen-backend --command "sh -c 'su -s /bin/sh www-data -c \"php artisan migrate:status\"'"

Environment reference​

These are the resources the process touches. Nothing else on this site records them.

Production, read only:

  • MySQL instance nexgen-v3-prod-mysql, MySQL 8.4, in vpc-034ed46905e08c69f
  • DocumentDB cluster nexgen-v3-prod-docdb, version 5.0.0
  • Snapshots come from the daily RDS automated backups and from the nexgen-v3-prod-db-backup-vault AWS Backup plan, which runs a daily backup kept for 7 days and a monthly backup kept for a year

Staging, the refresh target:

  • MySQL instance nexgen-v3-staging-mysql, DocumentDB cluster nexgen-v3-staging-docdb, both in vpc-0b4d8b05701305828
  • Bastion service nexgen-v3-staging-bastion in cluster nexgen-v3-staging-Cluster-FXymq0ZtfuP7, running in security group sg-018b6027add495f7e

The clone, created and destroyed by the script:

  • Subnet group docdbsubnetgroup-oiahuiinsxyu, which places it in the staging VPC
  • Security groups sg-057f094bc904c8c87 for MySQL and sg-01a1edb2e14232d54 for DocumentDB, both of which already allow the bastion in
  • Parameter groups nexgen-v3-staging-addonsstack-op7ltx6hb4hj-rdsparametergroup-zszqrrtcjb5i for MySQL, which asserts authentication_policy so the V2 PHP 5.6 client can still connect, and docdbparametergroup-avptilu13tpo for DocumentDB, which has TLS disabled

Production and staging use the same KMS key, so a production snapshot restores into the staging VPC with no key sharing or cross-account copy.