Keeping your staging database updated with fresh production data is crucial for accurate testing and debugging. It helps developers catch bugs early by working with real-world scenarios. However, doing this manually every day can be time-consuming and error-prone. That’s where Jenkins, a powerful automation tool, comes in. In this blog, you’ll learn how to automate the daily process of reloading production data into your staging environment using Jenkins. This not only saves time but also ensures consistency across environments.
Reloading production data into your staging environment ensures your tests and development work closely mirror real-world usage. Here are some key reasons why it’s important:
Keeping staging in sync with production ultimately leads to more stable and reliable software releases.
Before setting up the automation to reload production data into your staging database using Jenkins, make sure you have the following tools and access in place:
scp
or rsync
.Make sure to follow proper security practices, especially when dealing with production data, including masking sensitive information where necessary.
The pipeline performs the following steps:
All of this happens automatically, so you don’t have to manually copy snapshots or run scripts.
This stage is responsible for creating a read replica of your production database and promoting it to a standalone instance.
environment {
SOURCE_CRED = credentials('') // Contains prod DB username/password
DEST_CRED = credentials('') // Contains stage DB username/password
ADMIN_CRED = credentials('') // Admin-level credentials for stage DB
SOURCE_ID = "prod-db-instance-id"
REPLICA_ID = "prod-replica-id"
DEST_ID = "stage-db-id"
DEST_HOST = "stage-db-host"
DEST_DB = "stage-db-name"
}
stage('Create and Promote Replica') {
steps {
sh '''
set -euo pipefail
set -x
echo "Creating read replica of production DB..."
aws rds create-db-instance-read-replica \
--db-instance-identifier "$REPLICA_ID" \
--source-db-instance-identifier "$SOURCE_ID" \
--db-instance-class db.m5.large \
--availability-zone us-east-1f
echo "Waiting for replica to become available..."
until aws rds describe-db-instances --db-instance-identifier "$REPLICA_ID" | grep '"DBInstanceStatus": "available"'; do
sleep 30
done
echo "Promoting replica to standalone..."
aws rds promote-read-replica --db-instance-identifier "$REPLICA_ID"
echo "Waiting for promoted replica to become available..."
until aws rds describe-db-instances --db-instance-identifier "$REPLICA_ID" | grep '"DBInstanceStatus": "available"'; do
sleep 30
done
'''
}
}
first, we need to define all necessary environment variables. These include Jenkins credentials for accessing the databases, and the identifiers for AWS RDS instances.
Then we will safely replicate production data without putting load on the live database, this stage involves creating a read replica and preparing it to serve as the new staging environment.
Create Replica: An Amazon RDS read replica is created from the existing production database instance. This ensures that the replica contains an up-to-date copy of the production data, without introducing risk or performance issues to the live system. Depending on the size of your database, this process can take several minutes.
Promote Replica: Once the replica is fully synchronized, it is promoted to a standalone RDS instance. At this point, it functions independently of the production database and is ready to be used as the new staging environment.
This approach allows you to build a fresh staging instance using real production data—without introducing any downtime or performance degradation to your live system. It also offers a reliable way to test with realistic data while preserving the integrity of both environments.
This stage deletes the current staging DB, and renames the newly promoted replica to take its place. It also resets credentials for the new staging instance.
stage('Replace Stage DB with Promoted Replica') {
steps {
sh '''
#!/bin/bash
set -euo pipefail
set -x
echo "Disabling deletion protection for existing stage DB..."
aws rds modify-db-instance --db-instance-identifier "$DEST_ID" --no-deletion-protection --apply-immediately
echo "Deleting old stage DB instance..."
aws rds delete-db-instance --db-instance-identifier "$DEST_ID" --skip-final-snapshot
echo "Waiting for stage DB to be deleted..."
while aws rds describe-db-instances --db-instance-identifier "$DEST_ID" 2>&1 | grep -q '"DBInstanceStatus": "deleting"'; do
sleep 30
done
echo "Renaming promoted replica to stage DB identifier..."
aws rds modify-db-instance --db-instance-identifier "$REPLICA_ID" --new-db-instance-identifier "$DEST_ID" --apply-immediately
echo "Waiting for renamed instance to be available..."
sleep 150
until aws rds describe-db-instances --db-instance-identifier "$DEST_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Re-enabling deletion protection..."
aws rds modify-db-instance --db-instance-identifier "$DEST_ID" --deletion-protection --apply-immediately
sleep 60
echo "Waiting again for DB to be available after modifications..."
until aws rds describe-db-instances --db-instance-identifier "$DEST_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Updating stage DB master password..."
aws rds modify-db-instance \
--db-instance-identifier "$DEST_ID" \
--master-user-password "$DEST_CRED_PSW"
echo "Waiting for DB to become available after password change..."
sleep 180
until aws rds describe-db-instances --db-instance-identifier "$DEST_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Changing admin user password via MySQL..."
mysql --user="$DEST_CRED_USR" --password="$DEST_CRED_PSW" --host="$DEST_HOST" -e \
"ALTER USER '${ADMIN_CRED_USR}'@'%' IDENTIFIED BY '${ADMIN_CRED_PSW}'; FLUSH PRIVILEGES;"
'''
}
}
This stage is responsible for creating a read replica of your production database and promoting it to a standalone instance.This final stage transitions the newly created database replica into an active staging environment.
Delete Old Staging DB: The existing staging database instance is removed to avoid conflicts and reduce resource usage.
Rename Replica: The promoted read replica is renamed to match the original staging database identifier. This ensures seamless integration with existing services and environment configurations.
Reconfigure Credentials: All necessary database users are recreated, passwords are reset, and admin access is restored to match staging requirements.
The result is a clean, up-to-date staging database that accurately reflects production, while remaining isolated and secure for testing and development purposes.
To ensure your staging environment is always refreshed with the latest production data, you can schedule this Jenkins pipeline to run automatically during off-peak hours—typically late at night or early morning, when system traffic is minimal.
triggers {
cron('0 2 * * *') // Runs daily at 2:00 AM
}
This configuration schedules the pipeline to run every day at 2:00 AM, a common low-traffic period. Running during off-hours helps minimize potential impact on resource usage, especially when creating and promoting database replicas.
Here’s a full working Jenkins pipeline that automates the process of creating a read replica from your production database and promoting it to replace the staging environment
pipeline {
agent any
options {
timestamps()
}
triggers {
cron('0 2 * * *')
}
environment {
SOURCE_CRED = credentials('')
DEST_CRED = credentials('')
ADMIN_CRED = credentials('')
SOURCE_ID = ""
REPLICA_ID = ""
DEST_ID = ""
DEST_DB = ""
DEST_HOST = ""
BKP_TABLE = ""
}
stages {
stage('Create Replica of PROD DB') {
steps {
sh '''
#!/bin/bash
set -euo pipefail
set -x
export BKP_PATH="${WORKSPACE}"
export DATE=$(date +"%d-%b-%Y")
echo "Creating read replica of production DB..."
aws rds create-db-instance-read-replica \
--db-instance-identifier "$REPLICA_ID" \
--source-db-instance-identifier "$SOURCE_ID" \
--db-instance-class db.m5.large \
--availability-zone us-east-1f
echo "Waiting for replica to become available..."
until aws rds describe-db-instances --db-instance-identifier "$REPLICA_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Promoting replica to standalone..."
aws rds promote-read-replica --db-instance-identifier "$REPLICA_ID"
echo "Waiting for promoted replica to become available..."
until aws rds describe-db-instances --db-instance-identifier "$REPLICA_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
'''
}
}
stage('Replace Stage DB with Promoted Replica') {
steps {
sh '''
#!/bin/bash
set -euo pipefail
set -x
echo "Disabling deletion protection for existing stage DB..."
aws rds modify-db-instance --db-instance-identifier "$DEST_ID" --no-deletion-protection --apply-immediately
echo "Deleting old stage DB instance..."
aws rds delete-db-instance --db-instance-identifier "$DEST_ID" --skip-final-snapshot
echo "Waiting for stage DB to be deleted..."
while aws rds describe-db-instances --db-instance-identifier "$DEST_ID" 2>&1 | grep -q '"DBInstanceStatus": "deleting"'; do
sleep 30
done
echo "Renaming promoted replica to stage DB identifier..."
aws rds modify-db-instance --db-instance-identifier "$REPLICA_ID" --new-db-instance-identifier "$DEST_ID" --apply-immediately
echo "Waiting for renamed instance to be available..."
sleep 150
until aws rds describe-db-instances --db-instance-identifier "$DEST_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Re-enabling deletion protection..."
aws rds modify-db-instance --db-instance-identifier "$DEST_ID" --deletion-protection --apply-immediately
sleep 60
echo "Waiting again for DB to be available after modifications..."
until aws rds describe-db-instances --db-instance-identifier "$DEST_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Updating stage DB master password..."
aws rds modify-db-instance \
--db-instance-identifier "$DEST_ID" \
--master-user-password "$DEST_CRED_PSW"
echo "Waiting for DB to become available after password change..."
sleep 180
until aws rds describe-db-instances --db-instance-identifier "$DEST_ID" | grep '"DBInstanceStatus": "available"' > /dev/null; do
sleep 30
done
echo "Changing admin user password via MySQL..."
mysql --user="$DEST_CRED_USR" --password="$DEST_CRED_PSW" --host="$DEST_HOST" -e \
"ALTER USER '${ADMIN_CRED_USR}'@'%' IDENTIFIED BY '${ADMIN_CRED_PSW}'; FLUSH PRIVILEGES;"
'''
}
}
}
post {
success {
echo "Database replication pipeline completed successfully!"
}
failure {
echo "Database replication pipeline failed!"
}
}
}
By automating the daily refresh of your staging database with Jenkins, you not only streamline repetitive tasks but also create a more stable and reliable development environment. The combination of AWS RDS capabilities and simple scripting makes the process both powerful and adaptable. As your infrastructure grows, this foundation can evolve—integrating alerts, security controls, and rollback mechanisms—to support a more resilient and scalable pipeline. Ultimately, it’s about building confidence in every release by ensuring your staging environment always reflects the real world.
We use cookies to enhance your browsing experience, analyze traffic, and serve personalized marketing content. You can accept all cookies or manage your preferences.