Install ApiDQ database #

Install postgresql #

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo apt-get update
sudo apt install postgresql postgresql-contrib postgis

Create database and user #

db_apidq - database name
user_apidq - database user
p_a_s_s_w_o_r_d - user password

sudo su postgres
createdb db_apidq
psql db_apidq -c "CREATE ROLE user_apidq WITH LOGIN PASSWORD 'p_a_s_s_w_o_r_d' SUPERUSER;"
psql db_apidq -c "GRANT ALL PRIVILEGES ON DATABASE db_apidq TO user_apidq;"
psql db_apidq -c 'CREATE EXTENSION hstore;'
psql db_apidq -c 'CREATE EXTENSION pg_trgm;'
psql db_apidq -c 'CREATE EXTENSION postgis;'
exit

Setting database server #

Add access rule and authentication method for the data server. Edit /etc/postgresql/13/main/pg_hba.conf and add new line

host    db_apidq        user_apidq      192.168.0.0/24          md5

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

Open /etc/postgresql/13/main/postgresql.conf and edit 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.

Restart postgresql server

 sudo service postgresql restart

Restore dumps #

Download dictionaries for this instruction

Restore dictionaries from dumps

 pg_restore --no-owner --no-acl -Fc -U user_apidq -h 127.0.0.1 -d db_apidq services_20210831.dump
 pg_restore --no-owner --no-acl -Fc -U user_apidq -h 127.0.0.1 -d db_apidq ru_20210801.dump