Staging over SFTP only: the rsync-and-rewrite playbook

It is 17:40 on a Friday. A WooCommerce store on a Dutch reseller host needs to move to PHP 8.2 before its provider flips the switch on Monday. The site is twelve years old, the developer who originally built it is unreachable, and the hosting plan is the cheap one: cPanel, SFTP, MySQL over the public socket, no SSH shell, no git, no Docker, no staging slot. The brief is “make sure the checkout still works after the PHP bump.” Without a way to test, the brief is unanswerable.

This is the situation a surprising number of legacy sites still live in. The hosting market has bifurcated. Managed plans give you push-to-deploy, container slots, and one-click staging. Bargain plans give you SFTP and a phpMyAdmin tab. Most of the WordPress, Drupal, Joomla and Magento sites we touch sit on bargain plans, because that is what was bought ten years ago and nobody has had a reason to change it.

What follows is the playbook we use when the host gives us SFTP and almost nothing else. The shape is the same across CMSes: pull production down, push a copy to a sibling location, rewrite the URLs in code and database, and lock the copy behind Basic Auth so search engines and customers cannot find it.

The SFTP-only constraint

SFTP is a subsystem of SSH. The protocol is the same. The reason a host can offer SFTP without giving you shell access is that sshd can be configured to accept file transfer and reject everything else. On most cheap hosts the relevant lines in /etc/ssh/sshd_config look something like this:

Match Group sftponly
    ChrootDirectory /home/%u
    ForceCommand internal-sftp
    AllowTcpForwarding no
    X11Forwarding no

That ForceCommand internal-sftp is the gate. You can log in, list files, upload, download. You cannot run a command. That means no rsync on the server, no mysqldump, no wp-cli, no tar. Everything has to happen from your laptop, with the remote treated as a dumb filesystem.

The good news is that the OpenSSH project documents the SFTP subsystem clearly and the protocol is well-supported. sftp(1) on your machine talks to sftp-server(8) on theirs, and that is the entire surface area you have to work with.

The directory plan

Before any files move, decide where the copy will live. Two options work:

• Sibling subdirectory. Place the staging copy at /public_html/staging/ so it lives at https://example.com/staging/. Simplest, cheapest, no DNS changes.
• Subdomain. Create staging.example.com in the host's DNS panel and point it at a sibling directory like /public_html_staging/. Cleaner URLs, easier to scope cookies, easier to lock down separately.

The subdomain version is preferable when the CMS writes absolute URLs into the database, which WordPress, Magento and Joomla all do. A staging URL of https://staging.example.com/ is a single search-replace target. https://example.com/staging/ is two: the host and the path prefix. The difference matters when you have to rewrite ten thousand rows.

If you can only have a subdirectory, pick a name that is not guessable and is not in the host's default DNS template. /staging/ is fine if you are also putting it behind Basic Auth. /dev/, /test/ and /preview/ get found by bots within hours.

Pulling production down with lftp

Without a shell on the server, you cannot run rsync there. You also cannot have the server compress the tree into a tarball for a single download. Both ends of the SFTP session are dumb. So pulling production down means opening enough parallel SFTP transfers that the bandwidth saturates before the protocol overhead becomes the bottleneck.

The tool we reach for is lftp. It speaks SFTP, it does directory mirroring with the same semantics as rsync, and it parallelises transfers without needing anything on the server. A pull looks like this:

lftp -u user,'password' sftp://sftp.example.com -e "
  set sftp:auto-confirm yes;
  set net:connection-limit 8;
  mirror --parallel=8 --only-newer --exclude-glob '*.log' \
         --exclude-glob 'wp-content/cache/*' \
         /public_html/ ~/sites/example.com/prod/;
  bye"

Eight parallel streams is the sweet spot on most consumer connections. More than that and the host's SSH daemon starts refusing connections. The --only-newer flag turns the second pull into an incremental: only files whose mtime is newer than the local copy come down. On a 4 GB WordPress install with 80,000 media files, the first pull takes an evening. The second takes a coffee.

If lftp is not in the toolbox, rclone with the sftp backend works similarly: rclone sync :sftp:public_html ~/sites/example.com/prod --sftp-host sftp.example.com. The pattern is the same.

Exporting the database without shell access

With no shell, mysqldump on the server is out. Three options remain:

1. phpMyAdmin Export. Works for databases under about 200 MB. Above that the PHP execution timeout kills the export halfway.
2. Direct MySQL connection from your laptop. If the host allows remote MySQL (many cPanel hosts do, behind an allowlist), add your IP and run mysqldump -h db.example.com -u user -p dbname > prod.sql. This is the fastest and most reliable option.
3. A throwaway PHP script. Drop a single dump.php into the document root that streams SHOW TABLES and SELECT * output as SQL. Delete it the moment the dump finishes.

For larger databases the third route works because PHP's fputs to php://output streams as fast as the network allows, with no execution timeout if you set ignore_user_abort(true) and chunk the row reads. Three hundred lines of code, one IP allowlist on the script, and an immediate delete after the dump finishes on disk.

Rewriting URLs without breaking serialized data

This is where most SFTP-only staging attempts fall over. The naive move is to open the SQL dump in an editor, find-and-replace https://example.com with https://staging.example.com, and import. On a static-HTML site this works. On WordPress, Magento, or anything else that stores PHP serialized arrays in the database, it corrupts the database.

Serialized strings encode their own length. A row like:

s:18:"https://example.com";

becomes, after a dumb replace:

s:18:"https://staging.example.com";

The s:18 still says the string is 18 bytes long. The string is now 26 bytes. PHP's unserialize will return false, and the option, the widget, the theme setting, the address record, whatever it was, will silently disappear from the admin UI.

The fix is to use a tool that walks the database, unserializes each value, performs the replacement on the strings inside the structure, and reserializes. For sites where you can run PHP on the host but not a shell, Search Replace DB is the standard. Upload the folder via SFTP, hit it in the browser, run the replace, delete the folder.

For Drupal 9 and 10, the equivalent is the search_replace drush command, but drush needs a shell. Without one, exporting the database to your laptop, running a local PHP script with the same logic, and uploading the rewritten dump is the route. Magento 2 stores its base URL in core_config_data; a plain SQL update is safe because the URLs are not nested inside serialized arrays:

UPDATE core_config_data
SET value = 'https://staging.example.com/'
WHERE path IN ('web/unsecure/base_url', 'web/secure/base_url');

For WordPress, after the search-replace runs, the second pass to remember is wp_options: rows like siteurl, home, and any cached transient that hardcoded the old URL. The interconnect/it tool catches all of them. WP-CLI's search-replace docs explain the serialized-data problem in more detail and are worth a read even when you cannot run WP-CLI directly on the host.

Locking the staging copy down

A staging site that anyone can reach is not a staging site. It is a duplicate-content penalty waiting to land, a checkout funnel that confuses real customers, and on a WooCommerce site, a way to accidentally take real orders. Three things go in the staging document root before anyone clicks the URL.

Basic Auth on the whole subdir. Generate an .htpasswd file locally (htpasswd -c .htpasswd staging) and drop both files in:

# .htaccess in /staging/ or the staging docroot
AuthType Basic
AuthName "Staging"
AuthUserFile /home/example/staging/.htpasswd
Require valid-user

Apache's authentication howto is the reference if the host runs anything other than mod_auth_basic.

A robots.txt that blocks everything. Belt and braces, in case Basic Auth ever lapses during a deploy:

User-agent: *
Disallow: /

A meta noindex on every page. For WordPress, the easiest path is a one-line mu-plugin that adds <meta name="robots" content="noindex,nofollow"> to wp_head when the host header matches staging. For Magento, set Stores > Configuration > Design > HTML Head > Default Robots to NOINDEX, NOFOLLOW. Google's indexing-block documentation covers the meta-tag and header variants if you need both.

One non-obvious gotcha: payment gateways. Stripe, Mollie, Adyen and PayPal all whitelist the production webhook URL. Your staging copy will appear to take payments and then silently fail to confirm them. Switch the keys to test mode in the staging copy's config file (wp-config.php, env.php, settings.php) before the first checkout test, not after.

The refresh routine

The first build of a staging copy is the expensive one. After that, you want a one-command refresh that pulls production deltas down, ships them to the staging directory, and re-runs the database rewrite. Once it is scripted, refreshing takes minutes:

#!/usr/bin/env bash
set -euo pipefail
SITE=example.com
LOCAL=~/sites/$SITE

# 1. Pull production
lftp -u "$FTP_USER,$FTP_PASS" sftp://sftp.$SITE \
  -e "mirror --parallel=8 --only-newer /public_html/ $LOCAL/prod/; bye"

# 2. Push to staging directory (preserve env-specific files)
lftp -u "$FTP_USER,$FTP_PASS" sftp://sftp.$SITE \
  -e "mirror -R --parallel=8 --only-newer \
       --exclude-glob 'wp-config.php' \
       --exclude-glob '.htaccess' \
       $LOCAL/prod/ /public_html_staging/; bye"

# 3. Dump prod DB to laptop
mysqldump -h db.$SITE -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > $LOCAL/prod.sql

# 4. Rewrite URLs in dump (handles WP serialized data)
php $LOCAL/scripts/serialized-replace.php \
   "https://$SITE" "https://staging.$SITE" \
   < $LOCAL/prod.sql > $LOCAL/staging.sql

# 5. Import to staging DB
mysql -h db.$SITE -u "$DB_USER" -p"$DB_PASS" "${DB_NAME}_staging" < $LOCAL/staging.sql

echo "Staging refreshed: https://staging.$SITE"

Excluding wp-config.php and .htaccess on the push is the part people forget. Both files are environment-specific. The staging wp-config.php points at the staging database. The staging .htaccess has the Basic Auth lines. Overwriting either from production breaks the staging site silently, usually halfway through a refresh you started because you needed to test something before the end of the day.

When we built Pier we ran into this exact shape of problem with one of our beta customers, a small Dutch agency keeping six WooCommerce sites on a bargain reseller. The way we ended up handling it was to treat the remote SFTP filesystem and the MySQL connection as a single dockable target, with the version history sitting on top of both so a serialized-safe rewrite happens before any file or row touches the staging copy. The MySQL editor took the longest to get right because it had to detect serialized blobs and refuse to rewrite them by hand.

If you have an SFTP-only site you have been avoiding touching, the smallest first step is to time how long an lftp mirror of the docroot takes on your connection tonight. The number it gives you back is the floor on every other decision in this playbook.