D2000 System databases

In the D2000 system, since version V10.0.38, these databases can be run on PostgreSQL:

• configuration
• log
• archive
• depository
• application

The log depository databases are not supported for PostgreSQL. The new databases, with the exception of the application databases (see the section Application databases in D2000 System), are created in D2000 Application Manager when creating the application or archive. This program also enables the creation and management of database services.

For the common users, the creation and management of databases on PostgreSQL is hidden and the only requirement is to have this database installed. In certain situations (creating the application or archive), the user must enter the password of the main user (postgres).

When creating the application with the name MyApp using the PostgreSQL database, the user must select the existing database service (which ensures the database is running), and the password for its main user with the name postgres. Then, the D2000 Application Manager will create the databases myapp_syscfg and myapp_logfile  in this database service and store them in separate tablespaces (ts_myapp_syscfg, ts_myapp_logfile), placed in subdirectories syscfg and logfile in the application directory of the D2000 System. It also creates DSN records MyApp.SysCfg and MyApp.LogFile for these databases. The created user dba is the owner of these databases and has full access to them.

When creating the archive for the application with the name MyApp using the PostgreSQL database, the user must select the existing database service (which ensures the database is running), and the password for its main user with the name postgres. Then, the D2000 Application Manager will create the database myapp_archiv in this database service and store it in a separate tablespace (ts_myapp_archiv), placed in the subdirectory archiv in the application directory of the D2000 System. It also creates a DSN record MyApp.Archiv for this database. The created user dba is the owner of this database and has full access to it.

Each depository, or depository segment, is implemented as a separate database. The names are derived from the application name (<application>_TS_#ID#, where #ID# is the depository number, or <application>_TS_#ID#_#SEG#, where #SEG# is the depository segment number). Depositories are created automatically by the D2000 Archive process.

Backup of D2000 System databases

Since backing up PostgreSQL databases can be done in various ways, the D2000 system does not contain any tool or functionality that would ensure the backup of such databases, with the exception of the configuration and application databases (commands BACKUP_SYSCFG and BACKUP_LOGFILE). Backup of the databases, therefore, should be done manually according to one of the methods described in this manual: https://www.postgresql.org/docs/current/backup.html.

A list of databases, which should be backed up, can be derived from the name of the application as follows:

• <application name>_syscfg
• <application name>_logfile
• <application name>_archiv
• <application name>_TS_<ID>

Application databases in the D2000 System

If the D2000 System should work with the external application database, for its integration user must create a system DSN record in the ODBC Administrator on the computer where the D2000 DBManager is running.

Warning: Both 32 and 64-bit ODBC Administrators exist in 64-bit OS Windows. DSN record must be created in the 32/64 ODBC Administrator, depending on the version of D2000 DBManager (as 32-bit applications cannot use 64-bit DSN and vice versa).

In case more types of DSN drivers for PostgreSQL are installed, the user should select the one that contains Unicode in the name.

The configuration of the DSN record for PostgreSQL should be done according to these steps:

1. Specify these parameters in the main window:
   
   Database – name of existing database.
   Server – address of the server, on which the PostgreSQL service runs (or localhost in case of the same computer),
   Port – port number, which PostgreSQL service listens on.
   
    After entering the name and password and clicking the Test button, D2000 should connect to the database successfully. If the connection fails, you must check the entered data and settings of the PostgreSQL service, on which the database is located (see the section Connection to the PostgreSQL database running on a dedicated machine). Warning! For security reasons, do not keep the name and password filled in the final state!
   
    For proper functioning with the D2000 System, it is necessary to change other default parameters.
    
2. In the Datasource/Page1 tab, set the following parameters:
   
   CacheSize can be set at your discretion.
   
   
3. In the Datasource/Page2 tab, keep these settings:
   
   The  highlighted parameters must be set as follows:
   Updateable Cursors – the ability to use cursors for writing (enables updateable cursor emulation in the driver)
   bytea as LO - enables support for writing Large Objects (blobs) into bytea columns
   Server side prepare - compiled cursors (e.g., for parameterized queries) on the server side can improve performance. If possible, we recommend that you enable this option.
   Level of rollback on errors - if transaction operations are used and there may be an error during command execution (e.g., INSERT fails because of a unique index violation), then if the parameter is set to Statement, only the last command is rolled back. If the parameter is set to Transaction, the entire transaction is rolled back. On the other hand, the Statement setting makes "microcommits" (savepoints) after each operation, which both reduces performance and consumes transactional IDs, which in turn leads to increased vacuuming activity of the PostgreSQL database.

This DSN record can be used for connection to an external PostgreSQL database in the D2000 System.

Connection to the PostgreSQL database running on a dedicated machine

If the D2000 System should connect to the remote server, it is necessary to configure the DSN record in the ODBC administrator on the client according to the previous instructions, with the only difference - enter the name or IP address of the server, on which the PostgreSQL database runs, into the Server parameter.

On a dedicated machine with a running database, which you want to connect to, you must properly define the rights for connection. The default settings of PostgreSQL enable connection to the running database service only from the computer on which it is started. When necessary to connect from the network, you must change this setting in the configuration file pg_hba.conf, which is placed in the selected directory of the database service.

It contains these default settings:

This setting enables connection to the database server only for the user from the same computer (127.0.0.1 represents the localhost).

If the user wants to connect from any computer, the configuration file must contain this line:

This line enables connecting all users to all databases of the running service from all addresses. However, the password is required (md5 method).

The setting, mentioned above, has an illustrative character, and it is not recommended for use on production systems because it can represent a security risk when using weak passwords. The configuration file pg_hba.conf enables limiting the rights for connection to the database (based on the user or database), which is documented by https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Each change of this configuration file requires reloading of the configuration (Reloading of the configuration database service) or restarting the database service.

Specialities of PostgreSQL

Using the quotation marks for names of tables and columns

The identifiers of tables and columns in PostgreSQL, which are not in quotation marks, are not case sensitive. It means the column can be created in this way:

create table t (id integer)

Then, these queries can be created:

• select id from t
• select Id from t
• select ID from t
• select "id" from t

All forms of querying of the given column are functional.

The identifiers of tables and columns can be written in quotation marks, too. However, within queries, the identifier must use the same letter case as when it was created, and it must always be in quotation marks.

It means, when creating this column:

create table t ("Id" integer)

it is possible to refer to it only in the same letter case format:

select "Id" from t

Using any other case (eg. "ID") will not work.

Considering the complications that can occur in case of using the identifiers with quotation marks, we do not recommend using this format of entry. In some situations, this format is necessary, e.g., if the name of the column is identical to a keyword in PostgreSQL.

When using the application database in the D2000 system, we do not recommend using the identifiers in quotation marks. D2000 System supports both formats of entries, but when using quotation marks, they must be used even in selection and sorting conditions. This can relate to both the use of database operations in ESL scripts (with WHERE conditions), and displaying a database table in D2000 HI (if some selection or sorting condition is entered).

D2000 Dbmanager supports the /NQ startup parameter, which disables the use of quotation marks for PostgreSQL databases (and thus case sensitivity in table and column names). We recommend its use.