PostgreSQL

PostgreSQL documentation

www.postgresql.org

Windows:

Installation:

1. Download the installation file.

2. Install the database

PostgreSQL Server

Command Line Tools

PG Admin (for easy access to the database)

The database has been installed and is now available without any specific configuration.

Creating a new database

1. Open the pgAdmin.

2. Select the server on which you want to add a new database.

3. Right-click on the server.

4. Click on Create database.

Another window opens in which you can configure your database.

You have created a new database.

Settings for access via the network

1. Use a text editor to open the file pg_hba.config in C:\Program Files\PostgreSQL\*version*\data\

2. Adjust the entry host all all 127.0.0.1/32 scram-sha-256 or add a new one.

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.

3. Configure your firewall according to the port selected during installation (default: 5432/tcp).

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

TwinCAT/BSD

Installation

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. Perform an update of the packages.
doas pkg update

You will now see the available FreeBSD packages.

4. Install the PostgreSQL database server.
doas pkg install xxxx

5. Enable the PostgreSQL service.
doas sysrc postgresql_enable="YES"

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

Creating a new database

1. Initialize the database.
doas /usr/local/etc/rc.d/postgresql initdb

2. Start the database service.
doas service postgresql start

3. Set a password for the default user.
doas passwd postgres

The database has been successfully initialized and started.

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.
doas pfctl -f /etc/pf.conf

Port 5432 has been opened in the firewall.

5. Open postgresql.conf to adjust "listen_addresses".
doas ee /var/db/postgres/data17/postgresql.conf

6. Change the entry listen_addresses to '*'.

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.

# TYPE  DATABASE        USER            ADDRESS                  METHOD
# "local" is for Unix domain socket connections only
local   all             all                                      md5
# IPv4 local connections:
host    all             all             127.0.0.1/32             md5
# IPv6 local connections:
host    all             all             ::1/128                  md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                      md5
host    replication     all             127.0.0.1/32             md5
host    replication     all             ::1/128                  md5

10. Save and exit the editor.

11. Restart the service.
doas service postgresql restart

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

Enter PostgreSQL in System Environments

1. Enter System Environment Variables in the Windows search.

2. Open the Environment Variables window.
PostgreSQL 1:

3. Edit the Path entry.
PostgreSQL 2:

4. Add the path of the PostgreSQL database. C:\Program Files\PostgreSQL\<version>\bin
PostgreSQL 3:

"pg_config" can now be addressed directly in the command line or PowerShell.

Installing TimescaleDB

1. Download the installation file.

2. Stop the PostgreSQL (Windows/PostgreSQL17: net stop postgresql-x64-17).

3. Install the TimescaleDB

Postgresql.conf (Windows/PostgreSQL17: C:\Program Files\PostgreSQL\17\data)

4. Start PostgreSQL (Windows/PostgreSQL17: net start postgresql-x64-17).

The TimescaleDB has been successfully installed

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

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.
select * from pg_settings

The "synchronous_commit" entry should now be set to "off". The functionality is disabled.