Moving a site to another engine #

This page is for database administrators who move an existing Exponential site to another database engine, for example from SQLite to PostgreSQL when the site grows onto several servers, or from MySQL to PostgreSQL by organization policy. The content stays the same; only where it is stored changes. Sources: 9.9 Switching an existing site to another engine and Database support.

Contents #

1. Plan
2. Between SQL engines
3. Into Oracle
4. Into MongoDB
5. Switch the settings
6. Check before you reopen
7. After the move
8. Checklist

Plan #

• A maintenance window: no editor may publish between the dump and the moment the site runs on the new database, or that work is lost.
• A rehearsal: run the whole move on a copy first and time it.
• The target prepared: database, account and character set as on the engine's setup page (PostgreSQL, MySQL and MariaDB, MongoDB, Oracle).
• A backup of the source in its native format.
• Extensions reviewed for the target engine (Reviewing extension SQL).

Between SQL engines #

SQLite, PostgreSQL, MariaDB, MySQL, and Oracle with ezoracle, through the engine-neutral .dba format. --type takes a schema handler from settings/dbschema.ini: mysql, postgresql, sqlite3, and with ezoracle oracle. For SQLite the database argument is the file name, as in Database.

php bin/php/maintenance.php on --message="Moving the database" --allow-root-user
# dump schema and data of the current database (MySQL in this example)
php bin/php/ezsqldumpschema.php --type=mysql --host=localhost --user=exponential --password='<password>' \
    --format=generic --output-array --output-types=all exponential var/backup/site.dba --allow-root-user
# load them into the new one
php bin/php/ezsqlinsertschema.php --type=postgresql --host=localhost --user=exponential --password='<password>' \
    --insert-types=all --schema-file=var/backup/site.dba var/backup/site.dba exponential --allow-root-user

--password= is visible in the process list: run the move on a machine you control, or use a temporary password and change it afterwards. Run both scripts with --help first (Schema tools).

Into Oracle #

The extension has its own transfer commands, ext:ezoracle:mysql2oracle-schema and ext:ezoracle:mysql2oracle-data (scripts in extension/ezoracle/bin/php/). Read their --help, then run ext:ezoracle:sequence-sync and ext:ezoracle:gather-stats afterwards (Oracle maintenance commands).

Into MongoDB #

The scripts in bin/mongodb/ (export_mysql.sh, mysql2ndjson.py, validate_ndjson.sh, fixup_ndjson.py, import_all.sh) and section 18 of the porting reference. Then create_indexes.js and a cronjob run (MongoDB: limits and operations).

Switch the settings #

In settings/override/site.ini.append.php, set DatabaseImplementation and the connection keys to the new engine (and ActiveExtensions[]=ezoracle for Oracle). Then:

php bin/php/ezcache.php --clear-all --allow-root-user
php bin/php/console exp:velocity restart --allow-root-user     # when Velocity serves the site

and reload PHP-FPM. A forgotten DatabaseImplementation is the usual reason no page loads after a switch.

Check before you reopen #

1. Count the rows of large tables on both sides; the numbers must match:

   SELECT COUNT(*) FROM ezcontentobject;
   SELECT COUNT(*) FROM ezcontentobject_tree;
   SELECT COUNT(*) FROM ezcontentobject_attribute;

2. On PostgreSQL, correct the sequences if ezsqlinsertschema.php reported a problem with them (Operating PostgreSQL).

3. Run the database consistency check (Checking the schema).

4. Sign in to the administration, open a few pages and publish a test object.

5. Rebuild the search index if results look wrong: php bin/php/updatesearchindex.php --clean --allow-root-user.

6. Maintenance off.

After the move #

• Later upgrades use the update files of the engine the site runs on now, not the one it was installed on.
• Backups change tool: update the backup jobs and test a restore on the new engine.
• Monitoring changes: point your checks at the new server (Monitoring the database).
• Keep the old database read-only for a while, then remove it with its credentials.

Checklist #

• To do: Maintenance window agreed; rehearsal done.
• To do: Target database, account and character set prepared.
• To do: Dump and insert completed without errors.
• To do: Settings switched; caches cleared; workers restarted.
• To do: Row counts match; consistency check clean; test publish done.
• To do: Backups, monitoring and upgrade notes updated for the new engine.

Related pages #

• Changing the database engine (for hosting companies)
• Choosing an engine
• Moving a site between hosts
• Migrations versus migrating a site
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.