Configure external databases for ADB Control and ADBM

Daria Barysheva

For production environments, it is recommended to configure external PostgreSQL and ClickHouse databases to store data of ADB Control (see Query DB and Metrics DB in Arenadata DB Control overview) and ADBM (see PostgreSQL in Arenadata DB Backup Manager 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.

To configure external databases in ADB ES (starting with ADB Control 4.16.0), you need to follow the steps that are described below. For information on how to set up external databases for the previous ADB Control and ADBM versions (delivered as ADB services), refer to the corresponding versions of the ADB documentation:

  • For information on the initial ADB ES installation (including configuring external databases), refer to Migration of ADB Control and ADBM metadata during ADB ES installation.

  • Note that actions for ADBM are needed only if you use that service in ADB ES.

  • Since ADB ES provides one common service for storing PostgreSQL data in ADB Control and ADBM (namely Database), you should use one external PostgreSQL server for ADB Control and ADBM and specify its settings in the Database service (see External postgres parameters below). The owner of both databases should be the same.

  • Names of the external PostgreSQL databases for ADB Control and ADBM services in the ADB ES cluster should be generated according to the following pattern:

    • ADB Control — <prefix>_adcc

    • ADBM — <prefix>_adbm

    The <prefix> value should be equal for both services. Subsequently, that value should be specified in the Database service parameter External postgres parameters → Database name prefix. Examples of valid database names: external_adcc and external_adbm (where external is a prefix).

  • If you are switching to external databases after the initial installation of the Database and/or Clickhouse services in ADB ES, it is important to apply the service action Reinstall to the Database and/or Clickhouse service after filling and saving the configuration settings described below.

PostgreSQL

Step 1. Run SQL queries for the ADB Control database

  1. 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 <prefix>_adcc WITH owner <user_name>;

  2. Connect to the created database 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 <prefix>_adcc WITH owner <user_name>;

  3. Optionally, you can run the additional query to increase performance by disabling synchronous writes to WAL files:

    ALTER DATABASE <prefix>_adcc SET synchronous_commit = OFF;

Step 2. Run SQL queries for the ADBM database

  1. Perform the following queries in PostgreSQL. The queries should be run under the default user postgres with the superuser rights:

    ALTER ROLE <user_name> SET search_path TO public, adcc, adbm; (1)
CREATE DATABASE <prefix>_adbm WITH owner <user_name>; (2)
    1 Grant the <user_name> user (previously created for ADB Control) appropriate rights for connecting to the external ADBM database.
    2 Create the database for ADBM data migration.

  2. Connect to the created database under a superuser and run:

    CREATE SCHEMA adbm; (1)
GRANT USAGE, CREATE ON SCHEMA adbm TO <user_name>; (2)
ALTER DEFAULT PRIVILEGES IN SCHEMA adbm GRANT UPDATE, INSERT, SELECT, DELETE ON TABLES TO <user_name>;
CREATE EXTENSION btree_gin WITH schema adbm;
CREATE EXTENSION pg_trgm WITH schema adbm;
GRANT SELECT ON pg_extension TO <user_name>;
    1 Create the adbm schema in the database for ADBM data migration.
    2 Create extensions in the adbm schema and grant their usage to the <user_name> user.

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

    ALTER ROLE <user_name> SET search_path TO public, adcc, adbm;
CREATE DATABASE <prefix>_adbm WITH owner <user_name>;

Step 3. Configure pg_hba.conf on the PostgreSQL server

Provide the ADB Control access to the external PostgreSQL databases by adding the following records to the pg_hba.conf file on the PostgreSQL server.

  • Without SSL

  • With SSL

host    <prefix>_adcc  <user_name>       <adbc_address>      trust
host    <prefix>_adbm  <user_name>       <adbm_address>      trust

where:

  • <prefix>_adcc, <prefix>_adbm — names of the created databases.

  • <user_name> — a name of the created user.

  • <adbc_address> — an IP address of ADB Control with a subnet number.

  • <adbm_address> — an IP address of ADBM with a subnet number.

hostssl    all  all       0.0.0.0/0      md5

Step 4. Configure the Database service in ADB ES

  1. On the Database service configuration page, select External postgres parameter in the Database type field and fill in all parameters in the list that opens. The assignment of parameters is described in the Configuration parameters → Database → External postgres parameters section.

    Parameters for connecting to the external PostgreSQL DB
    Parameters for connecting to the external PostgreSQL DB

  2. Click Save to save the Database configuration.

  3. Install the Database service using the Install service action or apply the Reinstall action to the Database service if it is already installed.

ClickHouse

IMPORTANT

  • To use clustered external ClickHouse as an external database (instead of a standalone node), you should first create a logical cluster called adcc_cluster in ClickHouse.

  • If you use SSL encryption in ADB Control, you need to configure SSL on the ClickHouse side and define the 8443 port in the ClickHouse connection parameters (see External Clickhouse parameters below).

  1. 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>

  2. On the Clickhouse service configuration page, select External Clickhouse parameters in the Database type field and fill in all parameters in the list that opens. The assignment of parameters is described in the Configuration parameters → Clickhouse → External Clickhouse parameters section.

    Parameters for connecting to the external ClickHouse DB
    Parameters for connecting to the external ClickHouse DB

  3. Click Save to save the Clickhouse configuration.

  4. Install the Clickhouse service using the Install service action or apply the Reinstall action to the Clickhouse service if it is already installed.

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