A3. Using the PostgreSQL DBMS

General Description

PostgreSQL is an object-relational DBMS distributed as a freeware unlike such commercial DBMS as Oracle Database, Microsoft SQL Server, etc. The PostgreSQL DBMS may be used to arrange an external DB for Dr.Web Enterprise Security Suite in large anti-virus networks.

To use PostgreSQL as an external database

1.Install the PostgreSQL or Postgres Pro server.

2.Set up Dr.Web Server to use the respective external database. You can do this in configuration file or via Dr.Web Security Control Center: in the Dr.Web Server configuration menu, the Database tab.

warning

To connect to the PostgreSQL DB you can use only trust, password and MD5 authorization.

Installation and Supported Versions

1.Download the latest available version of this PostgreSQL free product (the PostgreSQL server and correspondent ODBC-driver), otherwise do not use the version earlier than 8.4 or 11.4.1 for Postgres Pro.

2.Create the PostgreSQL database by one of the following ways:

a)Using the pgAdmin graphical interface.

b)Using the CREATE DATABASE SQL command.

warning

Database must be created in the UTF8 encoding.

For more information about conversion to the external database see p. Changing the Database Type.

Also, please note the system requirements for Dr.Web Server when operating with the PostgreSQL external database (see Installation Manual, p. System requirements).

Parameters

When setting access to PostgreSQL, use parameters described in the table below.

PostgreSQL parameters

Name

Default value

Description

host

<UNIX domain socket>

PostgreSQL server host

port

 

PostgreSQL server port or name extension of the socket file

dbname

drwcs

Database name

user

drwcs

User name

password

drwcs

Password

options

 

Debug/trace options for sending to Dr.Web Server

requiressl

 

1 instructs to request a SSL connection

0 does not instruct to make the request

temp_tablespaces

 

Name space for temporary tables

default_transaction_isolation

 

Transaction isolation mode (see PostgreSQL documentation)

More information can be found at https://www.postgresql.org/docs/.

Dr.Web Server and PosrtgreSQL DB Interaction via the UDS

If Dr.Web Server and the PostgreSQL DB are installed on the same computer, their interaction can be set via the UDS (UNIX domain socket).

To set interaction via the UDS

1.In the postgresql.conf PostgreSQL configuration file, specify the following directory for the UDS:

unix_socket_directory = '/var/run/postgresql'

2.Restart the PostgreSQL.

Configuring the PostgreSQL Database

To increase performance during interaction with the PostgreSQL database, it is recommended to configure it according to the information from the official documentation on the database.

If you use a large database and dispose the appropriate computing resources, it is recommended to configure the following parameters in the postgresql.conf configuration file:

Minimal configuration:

shared_buffers = 256MB

temp_buffers = 64MB

work_mem = 16MB

Extended configuration:

shared_buffers = 1GB

temp_buffers = 128MB

work_mem = 32MB

fsync = off

synchronous_commit = off

wal_sync_method = fdatasync

commit_delay = 1000

max_locks_per_transaction = 256

max_pred_locks_per_transaction = 256

warning

The fsync = off parameter significantly increases performance but may cause the complete loss of data in case of power failure or system crash. It is recommend to disable the fsync parameter only if you have a backup of the database for its full recovery.

 

Configuration of the max_locks_per_transaction parameter can be useful to ensure smooth operation at a mass appeal to the database tables, in particular, when upgrading the database to a new version.