Restoring a database #

This page is for database administrators who restore an Exponential database, after a failure, a bad upgrade or for a restore test. It answers: in which order to stop, restore and restart, which command restores each engine, what must be cleared afterwards and what to check before the site reopens. Sources: 10.9 Restore and SQLite backup and restore.

Contents #

1. Before you start
2. The procedure
3. The restore command per engine
4. SQLite: .restore or replacing the file
5. After the restore
6. Checks before reopening
7. Testing restores
8. Checklist

Before you start #

• Know which backup you restore and that the files backup (var/storage, settings/override) is from the same moment.
• Keep a copy of the current, broken database: you may need something from it later.
• Tell the editors: anything published after the backup was taken is lost.

The procedure #

1. Maintenance on, so nobody writes:

   php bin/php/maintenance.php on --message="Restoring" --allow-root-user

2. Under Velocity, stop the server, so no worker holds the old database:

   php bin/php/console exp:velocity stop --allow-root-user

3. Restore the database (next section) and unpack the files over the installation; fix the owner and mode.

4. Clear every cache and regenerate the autoloads:

   php bin/php/ezcache.php --clear-all --allow-root-user
   php bin/php/ezpgenerateautoloads.php -e

5. Start Velocity, clear its response cache, switch maintenance off, open the front page and the administration:

   php bin/php/console exp:velocity start --allow-root-user
   php bin/php/console exp:velocity cache clear --apply --allow-root-user
   php bin/php/maintenance.php off --allow-root-user

The restore command per engine #

SQLite: .restore or replacing the file #

Processes keep the database open: PHP-FPM workers while they run a request, Velocity workers for their whole life. Restore through SQLite's own locking rather than by replacing the file under them. .restore copies the backup into the live database page by page under a write lock and prints nothing when it succeeds.

If it reports an error (a writer held the lock, or the backup has another page size, which a database in WAL mode cannot take over), the live database is unchanged. Then replace the file: stop every process that may have it open (Velocity, PHP-FPM, cron), move the old database and its -wal and -shm files aside together, put the backup in place, fix its owner and mode (SQLite files, ownership and permissions), and start the processes again.

After the restore #

• Caches: they still hold pages rendered from the content before the restore. ezcache.php --clear-all clears the kernel's caches, including the SQL query cache; to clear only that one after a write outside Exponential, php bin/php/ezcache.php --clear-id=querycache --allow-root-user (The SQL query cache).
• Sequences: a full pg_restore restores them with the data; after restoring selected tables, correct them (Operating PostgreSQL). On Oracle, ext:ezoracle:sequence-sync --dry-run reports sequences behind their tables.
• Search index: if the search index tables were not in the backup, or after restoring into another engine, rebuild it with php bin/php/updatesearchindex.php --clean --allow-root-user (The built-in search index tables).
• MongoDB: run bin/mongodb/create_indexes.js if the indexes were not restored.

Checks before reopening #

• The version row matches the code: SELECT name, value FROM ezsite_data WHERE name LIKE 'ezpublish-%';
• The row counts of ezcontentobject, ezcontentobject_tree and ezcontentobject_attribute look as expected.
• Sign in to the administration, open a few pages, publish a test object (a publish exercises the writes a page view does not), and delete it.
• var/log/error.log shows nothing new.

Testing restores #

Test a restore on another machine at least once and after every major upgrade. A practical drill:

1. Take last night's backup to a staging machine.
2. Restore it with this procedure; time each step.
3. Run the checks above.
4. Write down the time it took and what was missing; that is your real recovery time.

Runbooks for hosting staff: Restore and restore tests and Restore from backup.

Checklist #

• To do: Maintenance on; Velocity stopped.
• To do: Database and files restored from the same moment.
• To do: All caches cleared, Velocity's response cache too.
• To do: Sequences, search index and MongoDB indexes checked.
• To do: Version row, row counts, sign-in and a test publish checked.
• To do: Maintenance off.

Related pages #

• Backups per engine
• Troubleshooting database errors
• When something goes wrong
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.