Usage of PostgreSQL on Linux

Installation and update of PostgreSQL for Eocortex 3.4-3.6

Eocortex versions 3.4-3.6 require PostgreSQL version 11 to store Search for Objects module events. All other events and modules use Firebird.

Warning

PostgreSQL will not be installed automatically during the installation of Eocortex components, so it is necessary to install the required DBMS version manually before installing the Eocortex Server application.

Below are sample instructions on how to manually install PostgreSQL 11 on Linux.

Depending on the availability of Internet on the device, installation can be done in one of the two ways described below:

Online installation when the device has access to the Internet
Offline installation when the device has no access to the Internet

Online

Add the PostgreSQL file repository to the OS configuration:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Download the repository signature key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package list:
```
sudo apt-get update
```
Install PostgreSQL of the required version:
```
sudo apt-get -y install postgresql-11
```

Offline

Download the PostgreSQL installation package of the required version and its dependencies from the official PostgreSQL repository, using a device with Internet access.
Transfer the downloaded packages to the Eocortex server.
Install packages using the dpkg command with the -i parameter.

Database setup for event recording

Due to the fact that this improvement does not provide any new features for users in terms of using Eocortex, using the PostgreSQL database does not require any additional settings other than the already existing database event recording settings. To start writing events to the system database:

Run the Eocortex Configurator application and go to the Servers page.
Select a server in the list.
Select the Information tab on the right part of the window and click Configure.
In the window that opens, select the Archive tab and set for one of available drives the Archive + database or Database only role.
Apply settings.

Warning

Additional PostgreSQL configuration is required if the system uses remote databases for the Face Recognition or License Plate Recognition modules. See the Analytics modules databases section.

Change of default user password

If the default eocortex DBMS user created during installation is used, but it is necessary to use a custom password, follow these steps:

Warning

All the following steps must be made directly on the server for which the changes are being made.

Stop Eocortex operation. To do this, use the following command in Terminal:
```
systemctl stop eocortex.service
```
Use the psql utility to set new password for postgres user. Launch the utility by running the following command in the Terminal:
```
psql -U eocortex -h 127.0.0.1 -d postgres
```
Input the current password to confirm the login (Default: masterkey).
Change the password with the command:
```
ALTER USER CURRENT_USER PASSWORD 'newPassword';
```
where newPassword is a new password for eocortex user.
Open the /opt/EocortexServer/EocortexServerConfigs/ directory and edit the ArchiveSystem.xml configuration file with any text editor.

Find the PostgreSqlConfig section and edit User and Password lines according to new DBMS user credentials.

The following is an example of such settings:

<PostgreSqlConfig>
    <Ip>127.0.0.1</Ip>
    <Port>5432</Port>
    <User>eocortex</User>
    <Password>newPassword</Password>
</PostgreSqlConfig>

To allow the system making backups of database files, make sure that new credentials are added to the DBMS password file. To do this, go to the home directory of the OS user and edit the .pgpass file with any text editor. Check that the selected user has access to the Eocortex database files.
Restore Eocortex operation. To do this, use the following command in Terminal:
```
systemctl start eocortex.service
```

Analytics modules databases

If the Face Recognition or License Plate Recognition modules are used in the system, the DBMS user editing will affect their bases as well.

If the Local Database is used, Eocortex will use the name and password specified in the ArchiveSystem.xml file to access the database.

If the Remote Database is used, changing the PostgreSQL credentials on the server storing the database will require a new name and password to be entered in the settings of all cameras that use the database.

Note

Use case:

Server 1 hosts the face database.

Server 2 hosts a number of cameras that use the face database from Server 1.

Modifying the DBMS user on Server 2 will not affect access to the face database hosted on Server 1.

Modifying the DBMS user on Server 1 will require new data to be specified for all cameras on Server 2 that use the face database hosted on Server 1.

Warning

Remote access to databases is not allowed by default in PostgreSQL and must be additionally configured if remote databases are used for analytics.

The steps below must be performed directly on the server hosting the database of the module.

Go to the directory with the PostgreSQL configuration files.

Default path:
```
/etc/postgresql/12/main/
```
Edit the file postgresql.conf with any text editor and add a line like the following:
```
listen_addresses = '*'
```
The specified example allows receiving requests to databases from all available network adapters of the server. If it is necessary to limit the list of adapters allowed to receive requests, replace the * symbol with the exact address of the network adapter. More details about this setting can be found in the PostgreSQL 12 documentation.
Edit the file pg_hba.conf with any text editor and add a line like the following:
```
host all all 192.168.0.0/24 md5
```
The specified example allows remote connections to all databases stored on the server for all users who are connecting from the 192.168.0.XXX network address range. If other addressing is used in the system network, it is necessary to specify the appropriate address range instead of displayed. It is allowed to enter several lines with different address ranges. More details about this setting can be found in the PostgreSQL 12 documentation.
Restart the service by running the following command in the Terminal:
```
sudo systemctl restart postgresql-12.service
```

Removal of PostgreSQL

Although PostgreSQL is used by Eocortex, a simple removal of Eocortex does not automatically delete PostgreSQL. Below are command samples for uninstalling the system together with the DBMS.

Partial removal

If it is necessary to remove the installed version of PostgreSQL together with Eocortex and save the settings, do the following command:

sudo apt-get --purge remove postgresql postgresql-* sudo apt-get autoremove eocortex

Warning

Removal of PostgreSQL by this method does not automatically remove its settings. When reinstalling the DBMS on the same computer, PostgreSQL will retain the settings from the previously created configuration files.

Complete removal

If it is necessary to completely remove the installed version of PostgreSQL, use the following sequence of commands:

sudo apt-get --purge remove postgresql\*

sudo apt-get purge postgresql

sudo apt-get autoremove postgresql

sudo rm -r /etc/postgresql/

sudo rm -r /etc/postgresql-common/

sudo rm -r /var/lib/postgresql/

sudo userdel -r postgres

sudo groupdel postgres

Usage of PostgreSQL on Linux🔗

Debian, Ubuntu🔗