ZendHQ Database Configuration

The ZendHQ daemon uses database engines to store configuration data and historical data like Z-Ray requests, monitoring events etc. The default database engine is a local SQLite database, but you can choose to configure the ZendHQ daemon to use remote relational databases like PostgreSQL and MariaDB/MySQL. Use cases of these database commands are database migration and disaster recovery.

For more information on the commands used, see The zendhqctl database Module.

Database structure

ZendHQ daemon uses multiple database tables that are created or upgraded automatically when the daemon is started up. All the tables are created within the same database. ZendHQ daemon modules (like Z-Ray, Monitoring etc.) use table name prefixes to identify the module they are used for. Table name prefixes can be changed in the zendhqd.ini file, if the default prefix name cause conflicts with other tables in the same database.

Actual table, index and view names have the following format:${prefix}_name, like zray_requests, zray_requests_idx,monitor_requests etc.

In addition to the module-specific prefixes, you can configure ZendHQ daemon to use a global prefix for all the tables, indexes, and views. The global prefix is prepended to all the table names, if the zendhqd.database_prefix configuration directive is set.

With the zendhqd.database_prefix directive in use, the final table names have the following format: ${global_prefix}_${prefix}_<name>

Notes about changing table name prefixes with existing data

The correct way to change table name prefixes with existing data is the following:

1. Stop the ZendHQ daemon.
2. Drop all the indexes and views with the current table name prefix using database tools.
3. Rename all the tables using database tools.
4. Change the table name prefix in the zendhqd.ini file.
5. Start the ZendHQ daemon, that re-creates indexes and views using the new table name prefix.

Configuration

ZendHQ database engines are configured in separate configuration files that are included into the main ZendHQ configuration file using the include directive.

The following are the default names of ZendHQ database configuration files:

Where ${INSTALL_PREFIX} is the directory where ZendHQ is installed, like /opt/zend/zendphp on most of the Linux systems.

To change the database engine, open the ${INSTALL_PREFIX}/etc/zendhqd.ini configuration file, comment out the current include statement and uncomment the include statement for the new database engine.

For example, to change the database engine from the default SQLite to MariaDB, change the ZendHQ main configuration file to look like the following:

zendhqd.ini file:

# The default SQLite database engine
;include = zendhqd_sqlite.ini
# Mysql/Mariadb database engine
include = zendhqd_mysql.ini		

Then open the corresponding database configuration file, e.g. zendhqd_mysql.ini for the MariaDB/MySQL database engine, and change configuration settings needed to connect to the database server.

Use the zendhqctl database test command to verify the database configuration before starting the ZendHQ daemon:

zendhqctl database test
		

After you have configured it, run the ZendHQ daemon to create tables in the database. From the command line it is possible to use the --init-only command-line argument to create tables and exit (replace ${INSTALL_PREFIX} with the actual directory name where the ZendHQ is installed):

zendhqd -c ${INSTALL_PREFIX}/etc/zendhqd.ini --init-only
		

The most common error is to run the command as root, which prevents the ZendHQ daemon from accessing database files. Use the -u|--useruser and -g|--groupgroup command-line arguments to drop privileges to the user and group running the ZendHQ daemon.

SQLite database configuration

The default SQLite database engine configuration file ${INSTALL_PREFIX}/etc/zendhqd_sqlite.ini creates SQLite databases with the following configuration:

zendhqd_sqlite.ini file:

database.path = ${INSTALL_PREFIX}/var/db
database.journal_mode = DELETE
		

Every ZendHQ module, like Z-Ray and Monitoring, has a separate SQLite database file:

The database.path directive can be used to store SQLite database files in a different directory.

Use the database.journal_mode directive to change the journal mode (see #pragma journal_mode in the SQLite documentation). The default DELETE mode deletes the journal file after the transaction is committed.

Notice that ZendHQ daemon is designed to use one single process to access SQLite databases, so the WAL journal mode is not recommended.

MariaDB/MySQL database configuration

For MySQL, only MySQL 8+ is supported.

The default MariaDB/Mysql database engine configuration file ${INSTALL_PREFIX}/etc/zendhqd_mysql.ini is ready to connect to a MariaDB/MySQL server running on the same machine as the ZendHQ daemon, assuming that the user running the ZendHQ daemon has permissions to access the MariaDB/MySQL server and a database called zendhq exists using the following default connection parameters:

zendhqd_mysql.ini file:

host = localhost
port = 3306
dbname = zendhq
user = ${USER}
password = empty
SSL_CERT = empty
SSL_KEY = empty
SSL_CA = empty

Create the database before starting the ZendHQ daemon. The ZendHQ daemon creates all the necessary tables in the database when it is started.

MariaDB/MySQL SSL support

By default, the ZendHQ daemon uses an unsecured connection to the MariaDB/MySQL database server without encryption. In order to enable two-way TLS, change the following directives in the zendhqd_mysql.ini file:

zendhqd_mysql.ini file:

zendhqd.database.SSL_CERT = client-certificate-file.pem
zendhqd.database.SSL_KEY = client-private-key-file.pem
zendhqd.database.SSL_CA = ca-file.pem

Refer to the MariaDB/MySQL documentation for further details.

PostgreSQL database configuration

The default PostgreSQL database engine configuration file ${INSTALL_PREFIX}/etc/zendhqd_psql.ini is ready to connect to a PostgreSQL server running on the same machine than ZendHQ daemon assuming that the user running the ZendHQ daemon has permissions to access the PostgreSQL server and a database called zendhq exists using the following default connection parameters:

zendhqd_psql.ini file:

host = localhost
port = 5432
dbname = zendhq
user = ${USER}
password = empty
sslmode = prefer
sslcert = empty
sslkey = empty
sslrootcert = empty

The database must be created before starting the ZendHQ daemon. The ZendHQ daemon creates all the necessary tables in the database when it is started.

PostgreSQL SSL support

By default, the ZendHQ daemon uses the sslmode=prefer PostgreSQL database connection parameter, which uses encryption if the server supports it, but does not protect against MITM (man-in-the-middle) attacks. Switch to sslmode=verify-ca or sslmode=verify-full for more protection.

The sslmode=verify-ca and sslmode=verify-full options require setting the root certificate file name in the zendhqd_psql.ini file:

zendhqd_psql.ini file:

zendhqd.database.sslrootcert = root-certificate-file.crt

If the PostgreSQL server is configured to require client certificates, set these here:

zendhqd_psql.ini file:

zendhqd.database.sslcert = client-certificate-file.crt
zendhqd.database.sslkey = client-secret-key-file.crt

Refer to the PostgreSQL documentation for further details.

Database migration

You can migrate existing data in ZendHQ databases from one database engine to another. The expected path is to migrate existing data from the local SQLite databases to a remote relational database engine like PostgreSQL and MariaDB/MySQL, but the engine is prepared to migrate data from any supported database engine to another. The new database engine must be configured and ZendHQ database tables created first before existing data can be migrated. All the existing ZendHQ data in the new database will be deleted during the migration.

Both databases must have the same database schema. If not, the migration will fail. Existing data is migrated using the zendhqctl database migrate command that reads data from the old database, stores to a temporary file, and then writes data to the new database.

For example, the following command migrates existing ZendHQ data from the local SQLite database to a new database configured in the main ZendHQ configuration file:

zendhqctl database migrate /opt/zend/zendphp/etc/zendhqd_sqlite.ini

Migration steps

1. Upgrade existing database schema.

If ZendHQ is upgraded to a newer version, ensure that the existing database is upgraded to the latest database schema by running the new ZendHQ daemon version once. In most cases this is done automatically when the ZendHQ daemon is upgraded.

If not, run the ZendHQ daemon manually with the --init-only command-line argument. For example:

zendhqd -c /opt/zend/zendphp/etc/zendhqd.ini --init-only

2. Stop the ZendHQ daemon.

   Ensure that the ZendHQ daemon is stopped and not running during the migration. For example:

   systemctl stop zendhqd.service

3. Configure the new database engine.

   Configure the new database engine by modifying the ZendHQ configuration and verifying that the configuration works using the zendhqctl database test command. For example:

   zendhqctl database test

4. Initialize the new database.

   Run the ZendHQ daemon once with the new database configuration to create and initialize all the database tables in the new database using the --init-only command-line argument. For example:

   zendhqd -c /opt/zend/zendphp/etc/zendhqd.ini --init-only

5. Migrate the existing data.

   Run the zendhqctl database migrate command to migrate existing data from the old database to the new database engine. For example:

   zendhqctl database migrate /opt/zend/zendphp/etc/zendhqd_sqlite.ini

6. Start the ZendHQ daemon.

   Start the ZendHQ daemon again and continue operations. For example:

   systemctl start zendhqd.service

If the database migration fails, you can reset by following these steps:

1. Update the zendhqd.ini to import the original database configuration.

2. Restart the ZendHQ daemon.

Database maintenance

The ZendHQ daemon is configured to delete historical data at regular intervals to prevent the database from growing indefinitely.

SQLite database maintenance

Due to the nature of the SQLite database engine, database files only grow and never shrink even if data is deleted. If disk space becomes an issue, then a regular database maintenance routine should be implemented using the following steps:

1. Stop the ZendHQ daemon.
2. Run the command sqlite3 path-to-database-file VACUUM on all the SQLite database files needing a maintenance.
3. Start the ZendHQ daemon.

The VACUUM command copies the content of the database into a temporary database file and then overwrites the original database file with the content of the temporary file, eliminating holes in the database file. For example:

systemctl stop zendhqd.service
for f in /opt/zend/zendphp/var/db/*.db; do sqlite3 $f VACUUM; done
systemctl start zendhqd.service