Preparing PostgreSQL Server

PostgreSQL server is prepared by the DBMS administrator.

IMPORTANT. The USER_NAME and DATABASE_NAME parameters must be written in the upper case.

Scripts are executed using one of the available methods, for example, via the pgAdmin application available on the pgAdmin official site.

Creating a Database User

To create a user, use the script that runs in the DBMS front end:

CREATE ROLE "USER_NAME" LOGIN ENCRYPTED PASSWORD 'USER_PASSWORD' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

ALTER ROLE "USER_NAME" SET lo_compat_privileges='on';

where:

USER_NAME. Name of the created user (schema).
USER_PASSWORD. Password for the created user (schema).

Creating a Database

To create a database, use the following script that runs in the DBMS front end:

CREATE DATABASE "DATABASE_NAME" WITH OWNER = "USER_NAME" ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = default LC_CTYPE = default CONNECTION LIMIT = -1;

GRANT ALL ON DATABASE "DATABASE_NAME" TO "USER_NAME";

where:

USER_NAME. Name of the previously created user.
DATABASE_NAME. Database name.

Creating a Schema in Current Database

To create a schema, use the following script that runs in the DBMS front end:

CREATE SCHEMA SCHEMA_NAME;

where:

SCHEMA_NAME. Schema name.

NOTE. For details about work with schemas, see PostgreSQL documentation.

Support of the Lo Type

The Lo data type is used to save multibyte information in PostgreSQL. By default, after a database is created, support of working with this data type is disabled. To enable the support, run the following script:

CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidsend' LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );

CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;

CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

CREATE OR REPLACE FUNCTION lo_manage() RETURNS pg_catalog.trigger AS '$libdir/lo' LANGUAGE C;

After all steps are executed successfully, go to repository creation.

Updating Repository

In Prognoz Platform 8.1 the script related with registration of the "lo" type was corrected. Of repository based on PostgreSQL DBMS (version 9.3 or later) was created in the earlier versions of Prognoz Platform, it must be updated by running the following script:

CREATE OR REPLACE FUNCTION losend (lo) RETURNS bytea AS 'oidsend' LANGUAGE internal IMMUTABLE STRICT;

Features of Working with PostgreSQL DBMS

If the Public default scheme is used on working, all repository users will be able to create tables. It can lead to problems of user updates and taking into account access permissions of users that are repository administrators. To avoid possible errors it is recommended to create all objects only under users included into Administrators group.

Default configuration for PostgreSQL servers includes autoclearing - release of space that is occupied by data deleted from tables using VACUUM SQL command. Statistics that is used by scheduler to select query execution method is also updated. If it is supposed that intensive work consisting in changing data in tables, it is recommended to set up configuration in such a way that autoclearing executes in server low-load time (night, weekend). Setting can be performed according to documentation available on the developer's site via URL https://postgrespro.ru/docs/postgrespro/9.5/routine-vacuuming#autovacuum.