Support of PostgreSQL in D2000 System

PostgreSQL database system for all types of databases (configuration, log, archive) is fully supported in D2000 System from version V10.0.38. In lower version V10.0.37, user may use PostgreSQL only in connection with archive databases.

PostgreSQL installation

To use the PostgreSQL, it must be installed on the target computer. Installation can be done in two ways either manually or through D2000 System installer, which fully installs all of necessary components of PostgreSQL.

Automatic installation

Installation by D2000 System installer is more user-friendly. It ensures installing of all necessary components for fully integration with D2000 System. This method is mainly recommended for users who have not any experience with the PostgreSQL.

To install PostgreSQL, user must check Install the PostgreSQL in D2000 System Configuration Guide in this dialog window:

In some situations, this checkbox could be disabled, which means that installer detected PostgreSQL on computer and any other installation is not necessary. The installer does not allow to install other PostgreSQL.

If PostgreSQL installation is enabled, the installer of PostgreSQL is started along with the configurator of D2000 System. It requires to set some important parameters concerning the PostgreSQL and configure appropriate database service (see the section PostgreSQL structure). PostgreSQL installer guides the user through the dialog windows which enables to set some necessary parameters. Most of the windows contains the default value which is recommended for standard purposes.

Process of PostgreSQL installation

1. Process of installation starts with the welcome dialog window:
   
2. In second step, user must select or enter a location on disc where the installation will be done:
   
3. Then select the location where data files for PostgreSQL service will be stored:
   
   This directory will contain the configuration and auxiliary files for PostgreSQL service (see more information in section PostgreSQL structure). The directory does not contain database files from D2000 System databases.
   It is shared by all databases on target computer, therefore it should not be placed in such a directory, which can be deleted (for example directory structure of D2000 System).
4. Enter the password for main user. Each PostgreSQL service contains the main user with name postgres who has an administrator rights (creating new databases, users, ...). This password is very important because it will be required every time when user will create application or archive on PostgreSQL.
   
5. Then enter the port number the service will listen on.
   
   Mostly, default port is convenient. If the process that listens on given port already exists on the target computer, you should change port number to avoid the conflict between processes.
6. Select the location.
   
   Default value [Default locale] is the location that is set according to operating system.
7. In the next dialog windows, confirm the settings that are necessary for installation.
8. After finishing the installation of D2000 instance, all necessary components of PostgreSQL are installed for cooperation of these two systems.

Manual installation

User can install PostgreSQL also separately. For proper integration with D2000, it is necessary to install even another components according to these steps:

1. First, you must install PostgreSQL according to the steps describe in the section Process of PostgreSQL installation. The installer can be downloaded from this address http://www.enterprisedb.com/products-services-training/pgdownload#windows. It is recommended to install the latest stabile version of this system. The minimum requirements are mentioned in section D2000 System requirements.
2. Install ODBC driver for PostgreSQL. The installer can be downloaded from this address http://www.postgresql.org/ftp/odbc/versions/msi/. ODBC driver must be of the same architecture as D2000 system which the database system will cooperate with. It means, that for 64-bit D2000 System, 64-bit ODBC driver must be installed. It is also recommended to install the latest stabile version.
3. ODBC driver installation wizard will guide even new user through all steps necessary for installing ODBC driver for PostgreSQL.

After installation (manual or automatic), PostgreSQL is configured and may be used in D2000 System.

D2000 System requirements

D2000 System requires for its operation on PostgreSQL these programs configured correctly:

• PostgreSQL version 9.3.x and higher
• PostgreSQL ODBC driver

When PostgreSQL is installed through D2000 System installer, all necessary components are installed automatically.

PostgreSQL structure

After installation, the target computer contains the files of PostgreSQL in selected directory. For example, the utility psql.exe is placed in subdirectory bin. It allows to work with database on command line.

When installing, default PostgreSQL service with name postgresql-XXX-VV is created (where XXX defines the architecture x86/x64 and VV defines the version of database system, e.g. 9.3). This service is the main part of PostgreSQL. You may work (create databases, connect to database,...) with the database system unless this service is running.

After starting the service, PostgreSQL begin to listen on the configured network connection, the port of which was entered when installing. This interface can be changed in the configuration file of database service.

Each service has own directory (the path was defined when installing), which contains its configuration and data files.

The most important configuration files of database service are:

• postgresql.conf
  It is the basic configuration file, in which you may change the configuration of service, such as the configuration of network interfaces which database listen on, cache size,...
• pg_hba.conf
  It is configuration file that specifies the users who can connect to database system and which way. This configuration file is necessary change only in case of connection to database system from external network. More information is mention in the section Connection to  PostgreSQL database running on dedicated machine.

Reloading of configuration database service

Most of the changes in the configuration files requires reloading of configuration by the database service. It can be done in two ways:

• starting the command:
  
  pg_ctl reload
  
  from the command line (utility pg_ctl.exe is placed in the subdirectory bin in PostgreSQL installation directory)
• starting the SQL query:
  
  SELECT pg_reload_conf();

Both these commands should not interrupt any currently executed operation or connection to the database service.

On the target computer, more than one database service can run which will listen on independent network interface / port and will contain own directory with configuration. The manual configuration of other service requires special knowledge about PostgreSQL. In D2000 System, new service can be simply created  through the configuration tool D2000 Management Console.

Several independent databases can operate under one database service. Each database is identified by a unique name. Considering this fact, it is sufficient that one (default) database service runs on one computer. This service should cover several different databases. Partition of databases on several database services on one computer brings improvement of performance only in some specific situations.

In PostgreSQL, data are stored on disk to one data directory of database, which contains many data files. When creating the new database in a standard way, data directory is stored in the subdirectory of given database service. It means, when creating the new database, the new subdirectory will be created in the directory structure of its service. All data will be stored here.

In some situations, data storage into the directory structure of service is not desired. For example, if it is necessary to divide data directories of databases to separate directories. This problem can be solved by using so-called tablespaces. The tablespace represents some directory that is saved on physical disc and is defined for all database service. Tablespace can contain all database or only some entities such as tables, indexes,... In such case, data files that represent given entity (table, index,...) are stored into subdirectory of tablespace but not into directory of database service. When creating the database, user may select default tablespace. The entities can be moved between tablespaces but this feature is not described in this instruction.

The features of tablespace are also used by D2000 System, which stores data files of its databases to the application directory.

To manage PostgreSQL databases, user may use either utility psql.exe (it is placed in bin subdirectory of installation directory), which enables to browse and manage the system by command line, or graphic tool pgAdmin III, which is installed together with the database system.