Databases and Data Stores¶

As root user, run mariadb from command line, no separate authentication required. But note that this won’t work via sudo, but an interactive root user shell session is required.

• Username = root
• Password = The same as your root login password, default is dietpi

• URL = http://<your.IP>/phpmyadmin
• Username = phpmyadmin
• Password = The same as your root login password, default is dietpi

To create a database and run commands, use the quick start documentation.

The first thing to do in order to check Redis is working properly is sending a PING command:

redis-cli ping

For more commands and an introduction to Redis data types and commands, read the quick start documentation.

After the installation, the data transfers are made via the HTTP requests, and are handled directly by the InfluxDB service running on http://<your.IP>:8086.

Create a database using influxdb via command line tool. This tool also uses HTTP, so it can manage a database on a remote machine setting the -host option:

influx -execute 'create database myfirstdb'

Create a database using a HTTP request and curl tool:

curl -i -X POST http://<your.IP>:8086/query --data-urlencode 'q=CREATE DATABASE myfirstdb'

Post data:

curl -i -X POST 'http://<your.IP>:8086/write?db=myfirstdb' --data-binary 'temperature value=20.12'

Retrieve and display data from the database:

influx -database myfirstdb -execute 'SELECT * FROM temperature'

Retrieve data using a HTTP request and curl tool:

curl -i -XPOST http://<your.IP>:8086/query?db=mydb --data-urlencode "q=SELECT * FROM temperature"

Create users and authorizations using influx CLI

To start the InfluxDB database management interface enter:

influx -username admin -password admin01

Then create the database entries:

CREATE USER admin WITH PASSWORD 'admin01' WITH ALL PRIVILEGES
CREATE USER test_user WITH PASSWORD 'test_user01'
GRANT ALL ON mydb TO test_user
exit

By default the HTTP authentication is disabled. To enable it, follow next two steps:

1. Change next setting in the configuration file /etc/influxdb/influxdb.conf:

   auth-enabled = true
   

2. Restart the service:

   systemctl restart influxdb
   

The data location for InfluxDB is stored respectively linked with symbolic links to the DietPi userdata directory: /mnt/dietpi_userdata/influxdb

While the Debian package by ships PostgreSQL with an active TCP/IP listener, though on localhost only, when installed via DietPi-Software this is disabled by default. We recommend using the UNIX domain socket in /run/postgresql to connect to the database, which has performance benefits. When TCP/IP connections are required, best practice is to create an override config like /etc/postgresql/*/main/conf.d/99local.conf and setting the listening address:

listen_addresses = 'localhost'

Replace localhost with an actual IP address to allow remote access or with * to all access via all LAN and public IP addresses and domain names.

When installed via DietPi-Software, the actual database files are stored in /mnt/dietpi_userdata/postgresql, so that it can easily moved to an external drive, together with other DietPi userdata. For backwards-compatibility, a symlink is created at /var/lib/postgresql.

• Config directory:
  /etc/postgresql/*/main
  with the asterisk being the PostgreSQL version number, e.g. 11 or 13
• Main config file:
  /etc/postgresql/*/main/postgresql.conf
• DietPi config override:
  /etc/postgresql/*/main/conf.d/00dietpi.conf

To add or change settings, best practice is to create a new override configuration, e.g.:

/etc/postgresql/*/main/conf.d/99local.conf

For changes to take effect, the service needs to be reloaded:

systemctl reload postgresql

The systemd service postgresql.service is used to start and control the PostgreSQL server. The following commands can be used:

• Start: systemctl start postgresql
• Stop: systemctl stop postgresql
• Restart: systemctl restart postgresql
• Reload config: systemctl reload postgresql
• Print status: systemctl status postgresql

Service logs are done to the system journal an can be viewed via:

journalctl -u postgresql

The server itself by default logs to a file:

cat /var/log/postgresql/postgresql-*-main.log

Since PostgreSQL is installed via APT, it can be updated via:

apt install postgresql