Configure external databases for ADB Control

Daria Barysheva

Contents

PostgreSQL
ClickHouse

For production environments, it is recommended to configure external PostgreSQL and ClickHouse databases to store ADB Control data (see Query DB and Metrics DB in Arenadata DB Control overview) for a number of reasons:

You will be able to set up external storages on hosts of required size for desired performance and configure high availability for them.
The ADB Control recovery process will be easier and consist mainly of the service configuration steps.

Starting with ADB Control 4.7.5, to configure external databases, you need to follow the steps that are described below.

PostgreSQL

Perform the following queries in PostgreSQL. The queries should be run under a superuser:

CREATE ROLE <user_name> LOGIN PASSWORD '<user_password>';
ALTER ROLE <user_name> SET search_path TO public, adcc;
CREATE DATABASE <database_name> WITH owner <user_name>;

Connect to the created database <database_name> under a superuser and run:

CREATE SCHEMA adcc;
ALTER SCHEMA adcc OWNER TO <user_name>;

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH schema public;
CREATE EXTENSION IF NOT EXISTS btree_gin WITH schema public;
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH schema public;

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO <user_name>;

CREATE OPERATOR adcc.^| (PROCEDURE = jsonb_exists_any, LEFTARG = jsonb, RIGHTARG = _text, RESTRICT = contsel, JOIN = contjoinsel);
ALTER OPERATOR adcc.^|(jsonb, text[]) OWNER TO <user_name>;
CREATE OPERATOR CLASS adcc.jsonb_ops_custom
FOR TYPE jsonb USING gin AS
OPERATOR 7 pg_catalog.@>(jsonb, jsonb),
OPERATOR 10 adcc.^| (jsonb, _text),
FUNCTION 1  gin_compare_jsonb(text, text),
FUNCTION 2  gin_extract_jsonb(jsonb, internal, internal),
FUNCTION 3  gin_extract_jsonb_query(jsonb, internal, smallint, internal, internal, internal, internal),
FUNCTION 4  gin_consistent_jsonb(internal, smallint, jsonb, integer, internal, internal, internal, internal),
FUNCTION 6  gin_triconsistent_jsonb(internal, smallint, jsonb, integer, internal, internal, internal);
ALTER OPERATOR CLASS adcc.jsonb_ops_custom USING GIN OWNER TO <user_name>;

where <user_name> is a name of the previously created user.

If you can assign superuser rights to a PostgreSQL user (which will be used for connecting to an external database from ADB Control), it is enough to create a role and a database in PostgreSQL before installation or reconfiguration of ADB Control:

CREATE ROLE <user_name> LOGIN PASSWORD '<user_password>' SUPERUSER;
ALTER ROLE <user_name> SET search_path TO public, adcc;
CREATE DATABASE <database_name> WITH owner <user_name>;

Optionally, you can run the additional query to increase performance by disabling synchronous writes to WAL files:
```
ALTER DATABASE <database_name> SET synchronous_commit = OFF;
```
where <database_name> is a name of the created database.
Provide the ADB Control access to the external PostgreSQL database by adding the following record to the pg_hba.conf file on the PostgreSQL server:
host <database_name> <user_name> <adbc_address> trust
where:
- <database_name> — a name of the created database.
- <user_name> — a name of the created user.
- <adbc_address> — an IP address of ADB Control with a subnet number.
On the ADB Control service configuration page, activate the External database parameters toggle and fill in all parameters in the list that opens. The assignment of parameters is described in the Configuration parameters → ADB Control → External database parameters section.

Parameters for connecting to the external PostgreSQL DB
Click Save to save the ADB Control configuration.
Install the ADB Control service using the Install service action or apply the Reconfigure & Restart action to the ADB Control service if it is already installed.

ClickHouse

Run the following queries to create a user with appropriate permissions in ClickHouse:

CREATE USER <user_name> NOT IDENTIFIED;
GRANT SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE ON *.* TO <user_name>

On the ADB Control service configuration page, activate the External ADQM parameters toggle and fill in all parameters in the list that opens. The assignment of parameters is described in the Configuration parameters → ADB Control → External ADQM parameters section.

Parameters for connecting to the external ClickHouse DB
Click Save to save the ADB Control configuration.
Install the ADB Control service using the Install service action or apply the Reconfigure & Restart action to the ADB Control service if it is already installed.

Found a mistake? Seleсt text and press Ctrl+Enter to report it