Install ApiDQ database #

The instruction is written for Ubuntu 24.04 LTS and PostgreSQL 18.

Install postgresql #

PostgreSQL and PostGIS are installed from the official PostgreSQL repository (pgdg).

sudo apt update
sudo apt install curl ca-certificates gnupg
sudo curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc -o /usr/share/keyrings/postgresql.asc
echo "deb [signed-by=/usr/share/keyrings/postgresql.asc] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install postgresql-18 postgresql-client-18 postgresql-contrib postgresql-18-postgis-3 postgresql-18-postgis-3-scripts

Setting database server #

It is convenient to keep ApiDQ settings in a separate file in the conf.d directory - it is included automatically and does not touch the main postgresql.conf.

Create file /etc/postgresql/18/main/conf.d/apidq.conf with the following content

# --- Network ---
listen_addresses = '*'
port = 5432

# --- Memory ---
shared_buffers = 3GB
effective_cache_size = 5GB

# --- Extensions ---
shared_preload_libraries = 'pg_prewarm,pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

# Cache warm-up after PostgreSQL restart
pg_prewarm.autoprewarm = on
pg_prewarm.autoprewarm_interval = 300s

# --- Slow query logging (longer than 0.3 s) ---
logging_collector = on
log_min_duration_statement = 300

listen_addresses - specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses.

Memory parameters are set as a percentage of the server RAM:

shared_buffers = 25–40% RAM
effective_cache_size = 60–75% RAM

The example above contains values for a server with 8 GB RAM.

If the database and the application are installed on the same server, take into account the memory of ApiDQ services (about 2 GB) - for a server with 8 GB RAM do not set shared_buffers above 3 GB.

You need to add access rule and authentication method for database server. Edit /etc/postgresql/18/main/pg_hba.conf and add new line

host    db_apidq        user_apidq      192.168.0.0/24          md5

192.168.0.0/24 - subnet from which you will connect to the database

If the application is installed on the same server as the database, the services connect from Docker subnets - specify the subnet 172.16.0.0/12

Restart postgresql server.

sudo systemctl restart postgresql@18-main

Create database and user #

db_apidq - database name user_apidq - user p_a_s_s_w_o_r_d - password

sudo su postgres
psql -c "CREATE ROLE user_apidq WITH LOGIN PASSWORD 'p_a_s_s_w_o_r_d';"
createdb -O user_apidq -E UTF8 db_apidq
psql db_apidq -c 'CREATE EXTENSION IF NOT EXISTS postgis;'
psql db_apidq -c 'CREATE EXTENSION IF NOT EXISTS hstore;'
psql db_apidq -c 'CREATE EXTENSION IF NOT EXISTS pg_trgm;'
psql db_apidq -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'
psql db_apidq -c 'CREATE EXTENSION IF NOT EXISTS pg_prewarm;'
psql db_apidq -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'
exit

Extensions are created as postgres. The user_apidq user does not need superuser privileges - being the database owner is enough.

Restore dumps #

Download dictionaries for this instruction

Restore dictionaries from dumps. The list may differ depending on the connected functionality. You can always check the correct set of dictionaries with support, and the current versions - in the block Requirements for versions of dictionaries on the page changelog.

The -j parameter sets the number of parallel restore jobs - specify the number of server CPUs. Restoring the ru dictionary can take up to 60 minutes.

cd ~/apidq/dumps
export PGPASSWORD='p_a_s_s_w_o_r_d'
pg_restore --no-owner --no-acl -Fc -j 8 -U user_apidq -h 127.0.0.1 -d db_apidq services_20241030.dump
pg_restore --no-owner --no-acl -Fc -j 8 -U user_apidq -h 127.0.0.1 -d db_apidq ru_20260907.dump
pg_restore --no-owner --no-acl -Fc -j 8 -U user_apidq -h 127.0.0.1 -d db_apidq name_20210801.dump

Collect statistics after restore #

This step is mandatory. pg_restore does not transfer planner statistics: without it the very first queries to the address dictionary run as full table scans and can fully load the server CPU and memory.

Run VACUUM ANALYZE for the restored schemas before starting the application:

vacuumdb --analyze --jobs=8 -U user_apidq -h 127.0.0.1 -d db_apidq --schema=services --schema=ru --schema=public

List all restored schemas with --schema. The name dictionary is restored into the public schema.

After that we recommend warming up the PostgreSQL cache - the commands are given in Update and rollback.