TwinCAT/BSD

	These instructions refer to version 17 of PostgreSQL.

Installation of PostgreSQL

Connect to the FreeBSD® PackageManager to install the appropriate package.

1. Open the configuration file "FreeBSD.conf".

doas ee /usr/local/etc/pkg/repos/FreeBSD.conf

2. Change the value "enabled" to yes.

3. Update the packages.

You will now see the available FreeBSD packages.

doas pkg update

4. Search for the PostgreSQL database server.

doas pkg search postgresql17

5. Install the PostgreSQL database server.

doas pkg install postgresql17-server-17.x_x

6. Activate the PostgreSQL service.

The database has been successfully installed and the service has been set up in the autostart.

doas sysrc postgresql_enable="YES"

Initializing PostgreSQL

1. Initialize the database.

doas /usr/local/etc/rc.d/postgresql initdb

2. Start the database service.

doas service postgresql start

3. Switch to the database user

doas -u postgres psql

4. Change the default password of postgres

\password postgres

5. Exit psql

The database has been successfully initialized and started.

\q

Settings for access via the network

1. Open pf.conf to configure the firewall.

doas ee /etc/pf.conf

2. Add the port 5432.

pass in quick proto tcp to port 5432 keep state

3. Save and exit the editor.

4. Reload the firewall configuration.

Port 5432 has been opened in the firewall.

doas pfctl -f /etc/pf.conf

5. Open postgresql.conf to adjust the "listen_addresses".

doas ee /var/db/postgres/data17/postgresql.conf

6. 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)

7. Save and exit the editor.

8. Open pg_hba.conf to adjust the security settings.

doas ee /var/db/postgres/data17/pg_hba.conf

9. 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                                      trust
# IPv4 local connections:
host    all             all             127.0.0.1/32             trust
# IPv6 local connections:
host    all             all             ::1/128                  trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                      trust
host    replication     all             127.0.0.1/32             trust
host    replication     all             ::1/128                  trust

10. Save and exit the editor.

11. Restart the service.

Thanks to the new configuration, your database is now accessible over the network.

doas service postgresql restart

Installing TimescaleDB

1. Update the packages.

doas pkg update

2. Install timescaledb.

doas pkg install timescaledb

3. Restart the PostgreSQL database.

The TimescaleDB has been successfully installed

doas service postgresql restart

Initializing TimescaleDB

1. Open postgresql.conf to enter the TimescaleDB.

doas ee /var/db/postgres/data17/postgresql.conf

2. Add the following entry at the end of the file.

shared_preload_libraries = 'timescaledb'

3. Save and exit the editor.

4. Restart the PostgreSQL database.

doas service postgresql restart

5. Switch to the database user.

doas -u postgres psql

6. Add the TimescaleDB to PostgreSQL.

CREATE EXTENSION IF NOT EXISTS timescaledb;

7. Check whether the installation was successful.

\dx

8. Create a new database for PostgresHistorize.

CREATE DATABASE historize_postgres;

9. Exit psql.

The database has been successfully initialized and started.

\q

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