Linux®

Linux® 1:

These instructions refer to version 17 of PostgreSQL.

Installation of PostgreSQL

Connect to the PackageManager of TwinCAT Runtime Linux® to install the corresponding package.
1. Update the packages.
sudo apt update
2. Install the PostgreSQL database server.
sudo apt install postgresql-17
3. Activate the PostgreSQL service.
The database has been successfully installed and the service started.
sudo systemctl start postgresql

Initializing PostgreSQL

1. Switch to the database user
sudo -i -u postgres
2. Start psql
psql
3. Initialize the database.
Initdb
4. Change the default password of postgres
\password postgres
5. Exit psql
\q
6. Log out.
The database has been successfully initialized and started.
exit

Settings for access via the network

1. Create a configuration file in the /etc/nftables.conf.d/  directory with the name 00-postgre.conf, for example
sudo nano /etc/nftables.conf.d/00-postgre.conf
2. Insert the following content and adjust the values to your network requirements:
table inet filter {
  chain input {
    # accept PostgresSQL
    tcp dport 5432 accept
  }
}
3. Save and close the configuration file.
4. Load the new rule with the command
sudo systemctl reload nftables
5. Check the settings and make sure that the configuration has been applied correctly.
Port 5432 has been enabled for the PostgreSQL database.
sudo nft list ruleset
6. Open postgresql.conf to adjust the "listen_addresses".
sudo nano /etc/postgresql/17/main/postgresql.conf
7. Change the entry listen_addresses to '*' and remove the # to activate the line.
# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
8. Save and exit the editor.
9. Open pg_hba.conf to adjust the security settings.
sudo nano /etc/postgresql/17/main/pg_hba.conf
10. Adjust the entry host all all 127.0.0.1/32 or add a new one. Change the method to md5.
By default, 127.0.0.1/32 is entered as local only. If other systems are to have access to the database, the corresponding IP address must be set.
Notice Alternatively, 0.0.0.0/0 can be used as the IP address, in which case all requests will be allowed. This can pose a safety risk!
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
11. Save and exit the editor.
12. Restart the service.
Thanks to the new configuration, your database is now accessible over the network.
sudo systemctl restart postgresql

Installing TimescaleDB

1. Update the packages.
sudo apt update
2. First, install additional programs.
- gnupg
- apt-transport-https
- LSB-release
- wget
sudo apt install gnupg apt-transport-https lsb-release wget
3. Add the TimescaleDB package source to Linux®.

echo "deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
4. Install the GPG key from TimescaleDB. This is a signature to install packages from the TimescaleDB package source.

wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
5. Update the packages.
sudo apt update
6. Install timescaledb.
sudo apt install timescaledb-2-postgresql-17
7. Optimize the configuration for TimescaleDB.
sudo timescaledb-tune
8. Restart the PostgreSQL database.
The TimescaleDB has been successfully installed
sudo systemctl restart postgresql

Initializing TimescaleDB

1. Switch to the database user.
sudo -i -u postgres
2. Start psql
psql
3. Add the TimescaleDB to PostgreSQL.
CREATE EXTENSION IF NOT EXISTS timescaledb;
4. Check whether the installation was successful.
\dx
5. Create a new database for PostgresHistorize.
CREATE DATABASE historize_postgres;
6. Exit psql.
\q
7. Log out.
The database has been successfully initialized and started.
exit

Optimizing the PostgreSQL configuration

There are various ways to optimize the PostgreSQL database for use with TimescaleDB. You can find a detailed overview on the TimescaleDB page. Disable "synchronous_commit" to reduce the write operations on the hard disk.

Notice

Loss of data

Disabling the "synchronous_commit" function can lead to data loss within the database. This must be observed in conjunction with Audit Trail.

Disabling the “synchronous_commit” function (pgAdmin is an additional program. Alternatively, the command display can also be used)

1. Open pgAdmin.
2. Select or create the database in which the historical data are to be saved.
3. Open PSQL under "Tools/PSQL Tool".
4. To disable, enter the following command.
alter system set synchronous_commit = 'off';
5. Reload the database configuration with the following command.
SELECT pg_reload_conf();
6. Open the Query Tool window under "Tools/Query Tool"
7. Check the configuration using the SQL command.
The "synchronous_commit" entry should now be set to "off". The functionality is disabled.
select * from pg_settings
Further Information