Skip to main content

Staging Data Copy Runbook

This runbook describes the process for refreshing the Staging database with a copy of the Production data. This is typically done before major releases to ensure realistic testing.

1. When to Copy Data​

  • Before a major feature release.
  • When troubleshooting production-only issues.
  • When staging data has become stale or inconsistent.

2. Prerequisites​

  • pg_dump and pg_restore installed locally.
  • Access to Production and Staging database connection strings.
  • Network access to both databases.

3. Step-by-Step Instructions​

Step 1: Backup Production Data​

Export the production database to a compressed dump file.

pg_dump -h <PROD_HOST> -U <PROD_USER> -d <PROD_DB> -F c -f production_backup.dump

Step 2: Terminate Connections to Staging​

Ensure no active connections are holding locks on the staging database.

-- Connect to 'postgres' database on Staging host
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'almafrica_staging' 
  AND pid <> pg_backend_pid();

Step 3: Restore to Staging​

Restore the production dump into the staging database.

# Drop and recreate the staging database to ensure a clean slate
dropdb -h <STAGING_HOST> -U <STAGING_USER> almafrica_staging
createdb -h <STAGING_HOST> -U <STAGING_USER> almafrica_staging

# Restore the dump
pg_restore -h <STAGING_HOST> -U <STAGING_USER> -d almafrica_staging -v production_backup.dump

4. Data Sanitization​

Mandatory: Immediately after restore, sanitize sensitive user data.

UPDATE users SET 
  email = 'user_' || id || '@example.com',
  phone_number = '0000000000'
WHERE email NOT LIKE '%@almafrica.com';

5. Safety Precautions​

  • NEVER run dropdb on the production host.
  • NEVER restore staging data into the production database.
  • Always verify you are connected to the correct host using SELECT current_database(), inet_server_addr();.

6. Verification Steps​

  1. Log in to the Staging dashboard.
  2. Verify that recent production records (anonymized) are visible.
  3. Run a simple count query on key tables: 
    SELECT count(*) FROM farmers;
    SELECT count(*) FROM production_cycles;
  4. Verify application health: https://api-staging.almafrica.com/health.