DEV Community

Cover image for How to Build a WordPress Staging Site and Sync Data Safely (and What Never to Sync)
AumCreate
AumCreate

Posted on

How to Build a WordPress Staging Site and Sync Data Safely (and What Never to Sync)

#database #devops #tutorial #wordpress

Staging is not one copy. It is two copies that behave differently — the files and the database — plus a rule about what is allowed to travel back to production. Most staging accidents I have seen were not broken code. They were a database import that carried production's user table into staging, or staging's wp_options table into production.

This is the setup I use: clone files, clone the database, rewrite the URLs, make staging unable to hurt anyone, then sync back with a narrow allowlist.

Step 0 — write the boundary down before you copy anything

One rule covers almost every case:

Content a human wrote and code a human reviewed may travel. Session state, credentials, environment identity, and anything a customer typed may not.

If you cannot say which side of that line a table is on, it does not sync.

Step 1 — clone the files 

cd /var/www/example.com

rsync -a \
  --exclude 'wp-content/cache/' \
  --exclude 'wp-content/uploads/' \
  --exclude '.git/' \
  ./ /var/www/staging.example.com/
Enter fullscreen mode Exit fullscreen mode

Uploads are excluded on purpose. Copy them once, separately, without overwriting anything already on staging:

rsync -a --ignore-existing \
  /var/www/example.com/wp-content/uploads/ \
  /var/www/staging.example.com/wp-content/uploads/
Enter fullscreen mode Exit fullscreen mode

--ignore-existing matters. If you sync uploads in the other direction later with --delete, you will happily delete an image that marketing replaced by hand.

Step 2 — clone the database, then rewrite the URLs 

cd /var/www/example.com
wp db export /tmp/prod-$(date +%F-%H%M).sql

mysql -e "DROP DATABASE IF EXISTS staging_wp; CREATE DATABASE staging_wp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql staging_wp < /tmp/prod-$(date +%F-%H%M).sql
Enter fullscreen mode Exit fullscreen mode

Point staging at the new database:

wp --path=/var/www/staging.example.com config set DB_NAME staging_wp
wp --path=/var/www/staging.example.com config set DB_USER staging_user
wp --path=/var/www/staging.example.com config set DB_PASSWORD 'change-me'
Enter fullscreen mode Exit fullscreen mode

Now the URL rewrite. Use a serialisation-aware tool, not UPDATE ... REPLACE():

wp --path=/var/www/staging.example.com search-replace \
  'https://example.com' 'https://staging.example.com' \
  --all-tables-with-prefix \
  --skip-columns=guid \
  --report-changed-only
Enter fullscreen mode Exit fullscreen mode

Two details that people get wrong:

  • --skip-columns=guid — wp_posts.guid is the permanent identifier feed readers use. Rewriting it makes every existing item look new. Leave it alone.
  • Never use a raw SQL REPLACE() on a WordPress database. Serialised values store their string length; changing the domain inside them without fixing the length corrupts the row and you find out weeks later when a widget renders as an empty box.

If you have custom tables with a different prefix, swap --all-tables-with-prefix for --all-tables. Run it with --dry-run first if you have never run it against this site.

Step 3 — make staging harmless

Staging that can send email, charge cards, or get indexed is not staging. It is a second production site with a worse name.

In wp-config.php on staging:

// Above the "That's all, stop editing" line.
define( 'WP_ENVIRONMENT_TYPE', 'staging' );
define( 'DISABLE_WP_CRON', true );
Enter fullscreen mode Exit fullscreen mode

Then a must-use plugin, so the guards survive theme and plugin changes:

<?php
// wp-content/mu-plugins/staging-guard.php
if ( 'staging' !== wp_get_environment_type() ) {
    return;
}

add_action( 'send_headers', function () {
    header( 'X-Robots-Tag: noindex, nofollow, noarchive', true );
} );

add_filter( 'pre_wp_mail', function ( $short_circuit, $atts ) {
    $to = is_array( $atts['to'] ) ? implode( ',', $atts['to'] ) : $atts['to'];
    error_log( '[staging] outgoing mail blocked -> ' . $to );
    return true; // short-circuit: wp_mail() reports success, nothing is sent
}, 10, 2 );
Enter fullscreen mode Exit fullscreen mode

The mail block is the single highest-value line in this article. Without it, a staging site restored from production will eventually email real customers with a test order confirmation or a password reset pointing at staging.example.com.

Put basic auth in front of the whole host as well, so the noindex header is not your only defence:

server {
    server_name staging.example.com;
    auth_basic           "staging";
    auth_basic_user_file /etc/nginx/.htpasswd;
    # ... rest of the server block
}
Enter fullscreen mode Exit fullscreen mode 
printf "dev:%s\n" "$(openssl passwd -apr1)" >> /etc/nginx/.htpasswd
Enter fullscreen mode Exit fullscreen mode

Finally, in the database: replace live payment credentials with test ones, and disable every plugin that writes to a live CRM or mailing list. If a plugin has its own queue table, empty it.

Verify:

curl -sI https://staging.example.com | grep -i x-robots-tag
# expect: x-robots-tag: noindex, nofollow, noarchive
Enter fullscreen mode Exit fullscreen mode

The sync matrix

This is the part to keep next to the ticket.

Data staging → production production → staging
Theme / child theme files Yes, after a reviewed diff Yes
Custom plugin files Yes, after a reviewed diff Yes
Posts and pages you authored Yes, by slug, one at a time Yes
New media files only Yes, with --ignore-existing Yes
Comments Rarely Yes, if you need realistic data to test
wp_users, wp_usermeta Never Never
Orders, customers, subscriptions, carts Never Never
Form entries, CRM leads Never Never
siteurl, home Never Never
active_plugins Never Never
Permalink structure / rewrite rules Never Never
Cron option, transients, object cache Never Never
SMTP, API keys, licence keys, payment keys Never Into a gitignored file, never into the DB
Analytics, tag manager, consent IDs Never Never

Why wp_users is a hard no in both directions: password hashes, roles, capabilities and session tokens travel with the row. Pushing staging users to production can promote an account. Pulling production users to staging spreads real credentials into an environment with weaker access control.

If you need to log in to staging, create a local admin there:

wp --path=/var/www/staging.example.com user create dev \
  dev@example.invalid --role=administrator --user_pass="$(openssl rand -base64 18)"
Enter fullscreen mode Exit fullscreen mode

Recipe A — code-only push (the default) 

rsync -av --dry-run --delete \
  --exclude '.git/' --exclude 'node_modules/' \
  /var/www/staging.example.com/wp-content/themes/acme-child/ \
  /var/www/example.com/wp-content/themes/acme-child/
Enter fullscreen mode Exit fullscreen mode

Read the dry-run output. Then run the identical command without --dry-run. Then:

wp --path=/var/www/example.com cache flush
Enter fullscreen mode Exit fullscreen mode

This is the only sync that is safe on a Friday afternoon, and it covers the majority of real work.

Recipe B — push one page's content

Post IDs differ between environments, so match on slug:

SLUG='shipping-and-returns'
STAGE=/var/www/staging.example.com
PROD=/var/www/example.com

STAGE_ID=$(wp --path="$STAGE" post list --post_type=page --name="$SLUG" --field=ID)
PROD_ID=$(wp --path="$PROD"  post list --post_type=page --name="$SLUG" --field=ID)

echo "staging=$STAGE_ID production=$PROD_ID"

wp --path="$STAGE" post get "$STAGE_ID" --field=post_content > /tmp/page.html
diff -u <(wp --path="$PROD" post get "$PROD_ID" --field=post_content) /tmp/page.html || true

# after reading the diff
wp --path="$PROD" post update "$PROD_ID" /tmp/page.html
Enter fullscreen mode Exit fullscreen mode

This replaces post_content and nothing else. If the page uses custom fields, compare those too:

wp --path="$STAGE" post meta list "$STAGE_ID"
Enter fullscreen mode Exit fullscreen mode

Recipe C — full database push, only when production had zero writes

A whole-database push is legitimate in exactly one situation: production has received no orders, no signups, no comments and no content edits since you took the staging copy. If you cannot state that with confidence, use Recipe A or B.

Checklist, in order:

  1. wp --path="$PROD" maintenance-mode activate
  2. Take a backup and confirm it is real: 
wp --path=/var/www/example.com db export /root/prod-before-push-$(date +%F-%H%M).sql
ls -lh /root/prod-before-push-*.sql
Enter fullscreen mode Exit fullscreen mode
  1. Restore that backup into a scratch database and load the homepage from it. A backup you have not restored is a hope, not a backup.
  2. Push the staging database over the production one.
  3. Rewrite the URLs in the other direction: 
wp --path=/var/www/example.com search-replace \
  'https://staging.example.com' 'https://example.com' \
  --all-tables-with-prefix --skip-columns=guid --report-changed-only
wp --path=/var/www/example.com cache flush
Enter fullscreen mode Exit fullscreen mode
  1. wp --path="$PROD" maintenance-mode deactivate, then load a product page and a form immediately.

Expect session loss: carts, logged-in sessions and password-reset links issued before the push are gone. That is why this belongs in a quiet window, not at 11am.

Post-sync checklist

  1. wp option get home and wp option get siteurl — correct environment.
  2. wp plugin list --status=active — no debug or profiling plugin that was only meant for staging.
  3. Stray URLs: 
wp --path=/var/www/example.com db query \
  "SELECT COUNT(*) FROM wp_options WHERE option_value LIKE '%staging.example.com%';"
Enter fullscreen mode Exit fullscreen mode
  1. Load the homepage, one deep page, one form, one product.
  2. Submit the form and confirm it lands where you expect.
  3. curl -sI https://example.com — there must be no X-Robots-Tag: noindex.
  4. wp cron event list — no duplicated events. If you disabled cron during the push, wp cron event run --due-now.
  5. Take a fresh backup once you are satisfied. The pre-push one is no longer current.

The five things people sync by accident

  • wp_users — password hashes, roles and sessions ride along.
  • wp_options as a whole — it mixes environment identity, credentials, cron schedules and throwaway transients in one table.
  • The uploads folder, in the wrong direction — overwrites hand-edited images.
  • The cron option — the same job then runs twice, or runs against the wrong data.
  • wp-config.php and .htaccess — they carry database credentials and environment-specific rules.

If you get the boundary right, staging becomes boring. Boring is the goal.

Disclosure: I work at AumCreate, a small WordPress shop in Singapore. We deploy and maintain sites on the client's own host; if you would rather not run this yourself, that service is described here: aumcreate.com/setup.

Top comments (0)