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 PostgreSQL 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.

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

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

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