PostgreSQL Setup

set up postgresql databank

  • run the following to see postgresql service status

sudo systemctl start postgresql.service

  • follow steps 2 and 3 to add a new role with the same username as your linux user; postgresql on Ubuntu

  • then create a db with name “red” as in step 4. we do not need ident based authent as in step 5.

  • after this the command “psql -d red” should work.

  • let the port be accessed for DB (replace ‘XY’ with your postgresql version, e.g. ‘12’)

cd /etc/postgresql/XY/main/

open file named postgresql.conf

sudo vi postgresql.conf

add this line to that file to let the postgresql server listen for connections::

listen_addresses = '*'

then open file named pg_hba.conf

sudo vi pg_hba.conf

and add this line to that file to allow access to all databases for all users with an encrypted password:

host  all  all 0.0.0.0/0 md5

and the following line as the first line to let users connect with password not encrypted:

host  all  all 0.0.0.0/0 trust

  • set password of user_name using (assuming logged in as linux user with psql account):

psql -d red

\password

  • allow the DBs port (default 5432) to be accessed through the firewall

  • then restart DB server:

sudo service postgresql start

Create PostgreSQL Schema and Tables

  • go to openburst/db folder

  • change OWNER username to your linux username in file create_tables.sql

  • create scheme

psql red -f create_schemes.sql

  • then call the create tables command

psql red -f create_tables.sql

  • then create triggers (openBURST uses triggers to notify processes waiting for DB changes, e.g. sensors waiting for target movements)

psql red -f create_triggers.sql

  • then write table entries (check ip address hardcoded; TBD better read in correct ip address)

psql red -f write_table_entries.sql

pgadmin to view the DB

  • Install pgadmin4 (if pgadmin4 does not work with python3.10 and your Linux distribution and version, try with a lower Python version e.g. python3.8); see e.g. pgadmin4 on Ubuntu

  • in order to view the DB open pgadmin4 and then add server above with the username and password; Hostname should be “localhost”, port “5432” and maintenance database “postgres”.

  • then you click through the pgadmin GUI to connect to the DB created above and view or query the tables.