Introduction to the database set up

In the background, a SQL server is used to store the data that is measured. The database of choise for this project is PostgreSQL.

To set up the connection to the SQL server, there are tree options:

  1. Install your own local PostgreSQL server. Save data only locally.

  2. Use a remote PostgreSQL server. This is the recommend configuration for analysis computers.

  3. It is also possible to combine 1+2. In this case, the measurements are saved locally and synced in parallel to the server. This is the recommend option for measurement computers.

Setting up a local database

The instructions below are tested on windows. On Linux/Mac, the mindset is the same, but instructions might be slightly different.

Steps:

  1. Download PostgreSQL

  2. Go through the installer and install the database.

  3. Launch the psql program and make a database user and a database (press enter until the shell asks for the password configured in the installation). Type the following commands:

    • CREATE USER myusername WITH PASSWORD ‘mypasswd’;

    • CREATE DATABASE ‘mydbname’;

    • GRANT ALL PRIVILEGES ON DATABASE ‘mydbname’ TO ‘myusername’;

    • GRANT CREATE ON SCHEMA public TO ‘myusername’

    Note: The last line is required since release 15 of Postgresql.

In case you are running along with a server set up, it is recommended to have ‘mydbname’ to be the same as the one on the server.

In python you can run the following code to set up the database:

from core_tools.data.SQL.connect import set_up_local_storage
set_up_local_storage("myusername", "mypasswd", "mydbname",
	"project_name", "set_up_name", "sample_name")

Arguments are:

  • user (str) : name of the user to connect with (the the one just configured using psql)

  • passwd (str) : password of the user

  • dbname (str) : database to connect with (e.g. ‘vandersypen_data’)

  • project (str) : project for which the data will be saved

  • set_up (str) : set up at which the data has been measured

  • sample (str) : sample name

Setting up a remote database

In this case we will be connecting with a remote server. This can be set up using the following code:

from core_tools.data.SQL.connector import set_up_remote_storage
set_up_remote_storage(server, port, user, passwd, dbname, project, set_up, sample)

The arguments are:

  • server (str) : server that is used for storage, e.g. “spin_data.tudelft.nl” for global storage

  • port (int) : port number to connect through, the default it 5421

  • user (str) : name of the user to connect with

  • passwd (str) : password of the user

  • dbname (str) : database to connect with (e.g. ‘vandersypen_data’)

  • project (str) : project for which the data will be saved

  • set_up (str) : set up at which the data has been measured

  • sample (str) : sample name

Note that the admin of the server has to provide you with login credentials for the storage.

admin set up.

Example for a linux server running ubuntu.

Install postgres:

sudo apt install postgresql

Set up the datasbase, in your shell swich to the postgres user and run psql, e.g.,

sudo apt install postgresql

psql

Now, make the databases and users,

Then set up a database and related users:

CREATE USER myusername WITH PASSWORD 'mypasswd';
CREATE DATABASE "mydbname";
GRANT ALL PRIVILEGES ON DATABASE 'mydbname' TO 'myusername';
\c mydbname
GRANT CREATE ON SCHEMA public TO 'myusername'

Note: The last line is required since release 15 of Postgresql. It must be executed on the new database.

The default install of postgress does not allow external connections. We can adjest this by typing

sudo vim /etc/postgresql/12/main/postgresql.conf

and adding the following line:

listen_addresses = '*'

This means the postgress process will listen to all incomming requests from any ip. Now, let’s also tell postgress that users are allowed to authenticate, change the following config file,

sudo vim /etc/postgresql/12/main/pg_hba.conf

and add the following line,

host    all     all     0.0.0.0/0               md5

Now restart the postgres services to apply the changes,

sudo systemctl restart postgresql.service

Note : also make sure port 5432 is open, e.g.:

sudo ufw allow 5432

TODO :: add certificates to ensure no random people can login with passwords found on github (currentl protectected using VLAN’s).

Setting up a remote and local database

In this case you have to configure both the remote and local database at the same time To acquire the log in information, check out the information on setting up the remote and local database.

After this, you can set up the python connection using:

from core_tools.data.SQL.connector import set_up_local_and_remote_storage
set_up_local_and_remote_storage(server, port,
                                    user_local, passwd_local, dbname_local,
                                    user_remote, passwd_remote, dbname_remote,
                                    project, set_up, sample)