Skip to content

Install new database VM

Jump to bottom
Romain edited this page Aug 14, 2024 · 5 revisions

Install database VM

These are the steps to setup the whole database machine:

  1. Install ubuntu
  2. setup firewall: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu
  3. let the docker containers access the host: sudo ufw allow from 172.16.0.0/12
  4. Install postgresql and timescaledb: https://docs.timescale.com/self-hosted/latest/install/installation-linux/
  5. Create user and db:
$ sudo su postgres
$ createuser --interactive
Enter name of role to add: romain
Shall the new role be a superuser? (y/n) y
$ createdb ihr
  1. Add access to NAS and Kafka nodes:
  • add to /etc/hosts:
10.0.1.1 nas0
10.0.1.12 zoo1
10.0.1.13 zoo2
10.0.1.14 zoo3
10.0.1.20 zoo4
10.0.1.33 zoo5
10.0.1.34 zoo6
10.0.1.12 kafka1
10.0.1.13 kafka2
10.0.1.14 kafka3
10.0.1.20 kafka4
10.0.1.33 kafka5
10.0.1.34 kafka6
172.16.30.99 rechenknecht
127.0.0.1
  • create mount point:
sudo mkdir -p /export/nas0
  • add to /etc/fstab:
nas0:/iijlab /export/nas0 nfs hard,nolock,noacl,nocto,noatime,tcp,actimeo=1 0 0
  • mount NAS:
sudo apt install nfs-common
sudo mount -a
  1. Create database:
  • create new folder and tablespace:
    sudo su postgres
    mkdir -p /export/nas0/psql/ihr/db_202406
    psql -c "CREATE TABLESPACE nas0 LOCATION '/export/nas0/psql/ihr/db_202406';"
    exit
  • create database in the new tablespace:
    createdb -D nas0 -O django ihr

    psql -h localhost
    CREATE EXTENSION IF NOT EXISTS timescaledb;
  • change id types to bigint:
ALTER TABLE ihr_atlas_delay ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_atlas_delay_alarms ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_delay ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_delay_alarms ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_forwarding ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_forwarding_alarms ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_hegemony ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_hegemonycone ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_hegemony_country ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_hegemony_prefix ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_hegemony_alarms ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_metis_atlas_deployment ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_metis_atlas_selection ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE ihr_tr_hegemony ALTER COLUMN id SET DATA TYPE bigint;

The corresponding sequences should also be converted with commands like:

ALTER SEQUENCE ihr_hegemony_id_seq AS BIGINT;
  • rename django tables:
ALTER TABLE ihr_atlas_delay RENAME TO ihr_atlas_delay_old;
ALTER TABLE ihr_atlas_delay_alarms RENAME TO ihr_atlas_delay_alarms_old;
ALTER TABLE ihr_delay RENAME TO ihr_delay_old;
ALTER TABLE ihr_delay_alarms RENAME TO ihr_delay_alarms_old;
ALTER TABLE ihr_forwarding RENAME TO ihr_forwarding_old;
ALTER TABLE ihr_forwarding_alarms RENAME TO ihr_forwarding_alarms_old;
ALTER TABLE ihr_hegemony RENAME TO ihr_hegemony_old;
ALTER TABLE ihr_hegemonycone RENAME TO ihr_hegemonycone_old;
ALTER TABLE ihr_hegemony_country RENAME TO ihr_hegemony_country_old;
ALTER TABLE ihr_hegemony_prefix RENAME TO ihr_hegemony_prefix_old;
ALTER TABLE ihr_hegemony_alarms RENAME TO ihr_hegemony_alarms_old;
ALTER TABLE ihr_metis_atlas_deployment RENAME TO ihr_metis_atlas_deployment_old;
ALTER TABLE ihr_metis_atlas_selection RENAME TO ihr_metis_atlas_selection_old;
ALTER TABLE ihr_tr_hegemony RENAME TO ihr_tr_hegemony_old;
  • create hypertables:
CREATE TABLE ihr_atlas_delay (LIKE ihr_atlas_delay_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_atlas_delay_alarms (LIKE ihr_atlas_delay_alarms_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_delay (LIKE ihr_delay_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_delay_alarms (LIKE ihr_delay_alarms_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_forwarding (LIKE ihr_forwarding_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_forwarding_alarms (LIKE ihr_forwarding_alarms_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_hegemony (LIKE ihr_hegemony_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_hegemonycone (LIKE ihr_hegemonycone_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_hegemony_country (LIKE ihr_hegemony_country_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_hegemony_prefix (LIKE ihr_hegemony_prefix_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_hegemony_alarms (LIKE ihr_hegemony_alarms_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_metis_atlas_deployment (LIKE ihr_metis_atlas_deployment_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_metis_atlas_selection (LIKE ihr_metis_atlas_selection_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
CREATE TABLE ihr_tr_hegemony (LIKE ihr_tr_hegemony_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);

SELECT create_hypertable( 'ihr_atlas_delay', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_atlas_delay_alarms', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_delay', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_delay_alarms', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_forwarding', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_forwarding_alarms', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_hegemony', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_hegemonycone', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_hegemony_country', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_hegemony_prefix', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_hegemony_alarms', by_range('timebin', INTERVAL '2 day') );
SELECT create_hypertable( 'ihr_metis_atlas_deployment', by_range('timebin', INTERVAL '7 day') );
SELECT create_hypertable( 'ihr_metis_atlas_selection', by_range('timebin', INTERVAL '7 day') );
SELECT create_hypertable( 'ihr_tr_hegemony', by_range('timebin', INTERVAL '2 day') );
  • recreate indexes:
CREATE INDEX ON ihr_atlas_delay (endpoint_id, timebin DESC);
CREATE INDEX ON ihr_atlas_delay (startpoint_id, endpoint_id, timebin DESC);
CREATE INDEX ON ihr_atlas_delay (startpoint_id, timebin DESC);

CREATE INDEX ON ihr_atlas_delay_alarms (startpoint_id, timebin DESC);
CREATE INDEX ON ihr_atlas_delay_alarms (endpoint_id, timebin DESC);
CREATE INDEX ON ihr_atlas_delay_alarms (timebin DESC);

CREATE INDEX ON ihr_delay (asn_id, timebin DESC);
CREATE INDEX ON ihr_delay (timebin DESC);

CREATE INDEX ON ihr_delay_alarms (asn_id, timebin DESC);
CREATE INDEX ON ihr_delay_alarms (timebin DESC);

CREATE INDEX ON ihr_forwarding (asn_id, timebin DESC);
CREATE INDEX ON ihr_forwarding (timebin DESC);

CREATE INDEX ON ihr_forwarding_alarms (asn_id, timebin DESC);
CREATE INDEX ON ihr_forwarding_alarms (timebin DESC);

CREATE INDEX ON ihr_hegemony (asn_id, timebin DESC);
CREATE INDEX ON ihr_hegemony (originasn_id, timebin DESC);
CREATE INDEX ON ihr_hegemony (asn_id, originasn_id, timebin DESC);

CREATE INDEX ON ihr_hegemonycone (asn_id, timebin DESC);

CREATE INDEX ON ihr_hegemony_country (asn_id, timebin DESC);
CREATE INDEX ON ihr_hegemony_country (country_id, timebin DESC);

CREATE INDEX ON ihr_hegemony_prefix (prefix, timebin DESC);
CREATE INDEX ON ihr_hegemony_prefix (asn_id, timebin DESC);
CREATE INDEX ON ihr_hegemony_prefix (originasn_id, timebin DESC);
CREATE INDEX ON ihr_hegemony_prefix (country_id, timebin DESC);

CREATE INDEX ON ihr_hegemony_alarms (timebin DESC);
CREATE INDEX ON ihr_hegemony_alarms (asn_id, timebin DESC);
CREATE INDEX ON ihr_hegemony_alarms (originasn_id, timebin DESC);

CREATE INDEX ON ihr_metis_atlas_deployment (timebin DESC);
CREATE INDEX ON ihr_metis_atlas_deployment (metric, rank, timebin);

CREATE INDEX ON ihr_metis_atlas_selection (timebin DESC);
CREATE INDEX ON ihr_metis_atlas_selection (metric, rank, timebin);

CREATE INDEX ON ihr_tr_hegemony (dependency_id, timebin DESC);
CREATE INDEX ON ihr_tr_hegemony (origin_id, timebin DESC);

CREATE INDEX ON ihr_atlas_location (af, name, type);
  1. Set timescaledb compression on certain tables:
ALTER TABLE ihr_hegemonycone 
SET (
        timescaledb.compress, 
        timescaledb.compress_segmentby='af,asn_id', 
        timescaledb.compress_orderby='timebin'
);
ALTER TABLE ihr_hegemony
SET (
        timescaledb.compress, 
        timescaledb.compress_segmentby='af,originasn_id,asn_id', 
        timescaledb.compress_orderby='timebin'
);
ALTER TABLE ihr_atlas_delay
SET (
        timescaledb.compress, 
        timescaledb.compress_segmentby='startpoint_id,endpoint_id', 
        timescaledb.compress_orderby='timebin'
);
SELECT add_compression_policy('ihr_hegemony', INTERVAL '7 days');
SELECT add_compression_policy('ihr_atlas_delay', INTERVAL '7 days');
SELECT add_compression_policy('ihr_hegemonycone', INTERVAL '7 days');
  1. Create ssh keys and add public key to ihr-archive and github
  2. postgres config:
    • Add ihr-website and localhost to psql ACL (/etc/postgresql/16/main/pg_hba.conf). also change permission to trust for localhost:
#docker
host    ihr             all             172.16.0.1/12           trust
#localhost
host    all             all             127.0.0.1/32            trust
  • Make postgres listen on all interfaces (/etc/postgresql/16/main/postgresql.conf)
  1. Clone required repositories and librairies
    mkdir -p /home/romain/Projects/perso
    cd /home/romain/Projects/perso
    git clone git@github.com:InternetHealthReport/infra.git
    git clone git@github.com:InternetHealthReport/ip2asn.git
    git clone git@github.com:InternetHealthReport/kafka-toolbox.git
    git clone git@github.com:InternetHealthReport/psql-dump.git
    git clone git@github.com:InternetHealthReport/AS-names.git
    sudo apt install pipx
    pipx install rov
    sudo apt install python3-psycopg2

  1. Install docker and run post-installation: https://docs.docker.com/engine/install/ubuntu/

  2. Optional: run a test:

docker run --rm --name ihr_hegemony_sink_4   -e KAFKA_HOST="kafka1:9092"  --add-host=kafka1:10.0.1.12   -e DB_CONNECTION_STRING="host=ihr-db.iijlab.net dbname=ihr user=romain"   internethealthreport/psql_consumers ASHegemony.py ihr_hegemony 4
  1. setup cron: copy from https://github.com/InternetHealthReport/infra/blob/main/eric/crontab.txt
  2. setup permanent sinks, clone kafka-toolbox and run docker compose:
git clone git@github.com:InternetHealthReport/kafka-toolbox.git
cd kafka-toolbox/psql/consumers
docker compose up -d
Clone this wiki locally