Database Server Setup for PostgreSQL on Windows¶

• Database Server Setup for PostgreSQL on Windows
  • Getting the Binary Installer
  • Installation Procedure
  • Locale Selection
  • Creating a FWD-Specific Database Cluster
  • Create Database Roles
  • Configure and Secure the Server
  • Install User Defined Function Support
    • Installation of PL/Java Base Support
      • Prerequisites
      • Obtaining the Source Code
      • Adding patches and support files
      • Build
    • Install
  • Prepare a New Database Instance
    • Create an Empty Database
    • Apply the Converted Schema
    • Enable Java as a Procedure Language
    • Load FWD's User Defined Function Library
    • Run the Data Import

This chapter documents the installation and setup of a PostgreSQL database cluster on Windows OS that will work with applications deployed using FWD.

Getting the Binary Installer¶

Go to:

https://www.openscg.com/bigsql/postgresql/installers.jsp/

And select the Windows counterpart of PostgreSQL 9.5.15. This will start the download of PostgreSQL-9.5.15-1-win64-bigsql.exe.

Installation Procedure¶

Execute the downloaded executable (PostgreSQL-9.5.15-1-win64-bigsql.exe). By default it will install PostgreSQL in C:\PostgreSQL\ and the default cluster in C:\PostgreSQL\pg95\data. The default port for the cluster is 5432. Note that the installation will create this database cluster automatically. You only need to specify the superuser (postgres) password. It is recommended to install the pgAdmin tool so you can access your databases in a more user-friendly interface.

After installation you can find PostgreSQL 9.5 directory in your Start menu, containing pgAdmin application and SQL Shell (psql) shortcut.

It is a good idea to add the bin directory of the PostgreSQL installation (C:\PostgreSQL\pg95\bin) to your system wide PATH, otherwise you need to use fully qualified names when invoking executables. Also, by doing this, the installer of PL/Java will be able to install the files at the right location. Note that all PostgreSQL commands bellow assume the bin directory is in your PATH.

Locale Selection¶

On Windows OS the locale selection is quite different than on Linux. Unlike Linux, the locale cannot be customized in the sense we created the en_US.iso88591_fwd_basic and configured for Linux version of PostgreSQL. Instead, the administrator is required to chose from a list (quite long) of bundled locales. The very same problem we encountered when setting up the Microsoft SQL Server instances.

Creating a FWD-Specific Database Cluster¶

As noted above, the installation created a default cluster, running on default port 5432. If the PostgreSQL path was added to system PATH, to create a new cluster use:

E:\fwd_projects> initdb -D E:\fwd_projects\data -U postgres -W -A md5
The files belonging to this database system will be owned by user "Administrator".
This user must also own the server process.

The database cluster will be initialized with locale "English_United States.1252".
The default database encoding has accordingly been set to "WIN1252".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory E:/fwd_projects/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... windows
creating configuration files ... ok
creating template1 database in E:/fwd_projects/data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password:
Enter it again:
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... not supported on this platform
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    "pg_ctl" -D "E:\fwd_projects\data" -l logfile start

You will be prompted for a superuser password. You should NOT create the new cluster under C:\Program Files and it is preferable to avoid any directories whose names contain spaces. Use a dedicated directory instead, if possible on a separate partition.

As initdb the new file structure is created under current user credentials. Since the ProgreSQL will run as service under Network Service, you need to make sure the privileges on the data directory are setup properly (right click on new directory, Properties | Security | Edit.. | Add...).

Next, use you preferred editor and open the configuration file of the new cluster (for example: E:\fwd_projects\data\postgresql.conf). Uncomment and assign a free port, different than the default one:

    port = 5433

We normally connect only from local host, but if the instance should be reachable from the outside you also need to adjust listen_addresses from same configuration file.

The best way to start the cluster process on Windows is launching it as a service. We create the service using the following command.:

    E:\fwd_projects> pg_ctl register -N postgres_fwd -D E:\fwd_projects\data

Note that the new service will be visible in host's Services window under the name postgres_fwd. Note that it does not have any description, but this can be added manually, if needed. The startup type is, by default, set to Automatic so it will be launched at OS startup. However, the service is not launched when this command is given. To start it we need manually start it from Service panel or by invoking:

    E:\fwd_projects> net start postgres_fwd

As an alternative to starting this cluster instance as service, you can start and stop it manually, using:

    E:\fwd_projects> pg_ctl -D "E:\fwd_projects\data" -l logfile start

    E:\fwd_projects> pg_ctl -D "E:\fwd_projects\data" -l logfile stop

Create Database Roles¶

PostgreSQL user and group accounts are known as roles. As part of the cluster creation, a database superuser role (also named postgres) was created. As a superuser, this role is able to perform all database administrative operations in the new cluster.

An application converted with FWD uses a single database role (separate from the postgres role) for all of its CRUD (create, read, update, delete) operations on the data. Since the postgres role is a superuser, it is recommended for security purposes not to use this role directly with FWD applications. A dedicated database role should be created for FWD runtime use. This role must have unrestricted CRUD access to the database, but it should not have additional, administrative permissions. Data access security policies should be defined at the application level to restrict those operations as appropriate.

Use the postgres system account to create the database role the FWD runtime environment will use to access FWD databases. The following command creates a role suitable for use with a FWD application:

createuser -U postgres -P -E -S -D -R -p {port} {new-name}

where:
-P causes a prompt for the user's password;
-E specifies that the password is to be stored encrypted;
-S indicates this will not be a superuser role;
-D indicates this role will not have the ability to create databases;
-R indicates this role cannot create new roles;
-p specifies that a port number will follow
{port} should be replaced with the port number on which the database cluster is listening;
{new-name} should be replaced with the name of the FWD-specific database role to create.

Choose a password and enter it when prompted (twice).

For example:

    E:\fwd_projects> createuser -U postgres -P -E -S -D -R -p 5433 fwd_user

Note:
Unlike Linux version, PostgreSQL for Windows does not assign names to clusters and does not keep track of them. To access clusters different than the default one (which runs on port 5432) you need to specify its port. Also, there is no postgres user created at OS level. The super-user is a cluster-specific and can have different password for different clusters.

In addition to the FWD runtime user role, we recommend creating a second, superuser role. This role is needed to create, drop, and own FWD databases, and to install UDF support. This role should not be used by the FWD runtime environment. The following command creates a role suitable for this purpose:

createuser -U postgres -P -E -s -g {group} -p {port} {name}

where:
-s indicates this role will have superuser rights;
-g specifies that the new role will be a member of the {group} role;
{group} should be replaced with the name of the FWD user database role created previously;
{name} should be replaced with the name of the FWD admin database role to be created;
all other options have the same meanings as described above.

For example:

    E:\fwd_projects> createuser -U postgres -P -E -s -g fwd_user -p 5433 fwd_admin

Configure and Secure the Server¶

Once the cluster is up an running it is time to set up its security level. Since these are server-specific configuration, they are identical to Linux counterpart. Please follow the following link to configure your server:
Database_Server_Setup_for_PostgreSQL_on_Linux.

Install User Defined Function Support¶

For an overview, please have a look at corresponding Database Server Setup for PostgreSQL on Linux.

Installation of PL/Java Base Support¶

PL/Java is used to provide user defined functions written in Java for use with the PostgreSQL database. This is used to improve performance by significantly reducing the number of rows that are returned from the database, since the filtering of those rows can be implemented on the database server using custom Java logic.

Due to the need to support different versions of PostgreSQL over time, it is usually necessary to checkout the latest version of PL/Java and rebuild it on the platform on which it will run.

Prerequisites¶

In order to build PL/Java from sources you will need Java runtime 1.7.x or higher. Java 8 is recommended. Also make sure that JAVA_HOME environment variable is set to its installation. If not, you can specify path to Java home directory in System Properties | Environment Variables... or using SET command. E.g.:

SET JAVA_HOME="c:\Program Files\Java\jdk1.8.0_172" 

Or alternatively, to be sure the space-in-path issue is eliminated:

SET JAVA_HOME=c:\Progra~1\Java\jdk1.8.0_172

If you do not have Apache Maven installed, go to https://maven.apache.org/download.cgi and download the Binary zip archive. Unzip apache-maven-3.6.0-bin.zip to E:\fwd_projects.

Make sure the MinGW is also installed. Go to https://www.msys2.org/. Download an install msys2-x86_64-20180531.exe to E:\fwd_projects\msys64. In the console that opens after installation, we will update the package database and core system packages with:

pacman -Syu

Then, after reopening msys window:

pacman -Su

Install mingw/gcc for 64bit target:

pacman -S mingw-w64-x86_64-gcc

And add the bin directories to your system PATH variable. Be sure to re-login or reopen the Command Console so that the changes take effect.

To make sure all build tools are correctly installed, execute the following lines from a neutral location (eg. from the directory where the pljava sources were unzipped):

   gcc --version
   mvn --version
   pg_config
   java -version

Each of them should print out the version of the installed tool.

To inform the building environment about the preferred build toolchain use:

SET MSYSTEM=MINGW64

Obtaining the Source Code¶

Download source code distribution for PL/Java v1.5.2 from: pljava_src_1_5_2.zip.

The original distribution can be found at https://github.com/tada/pljava/releases/tag/V1_5_2. Download your preferred archive format and unpack it your preferred archiver (or Windows' internal utility accessible in contextual menu for zip).

Adding patches and support files¶

The MinGW-build version of PostgreSQL is missing some files needed to built the native code of PL/Java.

• Download libintl.h to C:\PostgreSQL\pg95\include;
• Download openssl.zip and unzip to same directory (C:\PostgreSQL\pg95\include).

Build¶

Move to unzipped source code directory pljava-1_5_2.

Download and save to current directory aol.mingw.properties file. It contains some compiler-specific directives for building the native code.
Edit E:\fwd_projects\pljava-1_5_2\aol.mingw.properties and adjust the liker properties to reflect the correct path to PostgreSQL library directory:

amd64.Windows.gpp.linker.options=-m64 -LC:/PostgreSQL/pg95/lib

Then start the build process with:

E:\fwd_projects\pljava-1_5_2> mvn clean install -Dnar.aolProperties=aol.mingw.properties

If everything went smooth, about a minute the process will stop with a summary like this:

[INFO] Reactor Summary for PostgreSQL PL/Java 1.5.2:
[INFO] 
[INFO] PostgreSQL PL/Java ................................. SUCCESS [  5.328 s]
[INFO] PL/Java API ........................................ SUCCESS [  3.922 s]
[INFO] PL/Java backend Java code .......................... SUCCESS [  3.768 s]
[INFO] PL/Java backend native code ........................ SUCCESS [ 33.009 s]
[INFO] PL/Java Deploy ..................................... SUCCESS [  0.717 s]
[INFO] PL/Java Ant tasks .................................. SUCCESS [  1.047 s]
[INFO] PL/Java examples ................................... SUCCESS [  1.313 s]
[INFO] PL/Java packaging .................................. SUCCESS [  0.953 s]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  53.322 s

Otherwise, review the Prerequisites section above.

Install¶

Note that despite the use of the term install, the previous Maven command actually builds the PL/Java software; it installs the result in its repository and makes it available to other projects but does not install it in your database cluster. To really install PL/Java in PostgreSQL use:

E:\fwd_projects\pljava-1_5_2>java -jar pljava-packaging/target/pljava-pg9.5-amd64-Windows-gpp.jar

Be sure you have sufficient credentials to write to PostgreSQL installation directory. If PostgreSQL is installed in Program Files you need to start a new console, with Administrator rights.

If everything goes fine, you should see the output similar to:

C:/POSTGR~1/9.5/lib\pljava-so-1.5.2.dll as bytes
C:/POSTGR~1/9.5/share\pljava\pljava-1.5.2.jar as bytes
C:/POSTGR~1/9.5/share\pljava\pljava-api-1.5.2.jar as bytes
C:/POSTGR~1/9.5/share\pljava\pljava-examples-1.5.2.jar as bytes
C:/POSTGR~1/9.5/share\extension\pljava.control as lines (ASCII)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--unpackaged--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.1--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.1-BETA3--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.1-BETA2--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.1-BETA1--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.0--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.0-BETA3--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.0-BETA2--1.5.2.sql as lines (UTF8)
C:/POSTGR~1/9.5/share\pljava\pljava--1.5.0-BETA1--1.5.2.sql as lines (UTF8)

Once PL/Java is built on this system, copy the pljava.jar to your FWD specific directory.

Open postgresql.conf in your favourite editor. Edit or add the following lines:

pljava.libjvm_location = '<path-to-jvm.dll>'
pljava.classpath = '<path-to-pljava.jar>'
pljava.vmoptions = '-Xmx16m'

Note that the backslashes (\) must be escaped. With default path, the lines should look like these:

pljava.libjvm_location = 'C:\\PROGRA~1\\Java\\JDK18~1.0_1\\jre\\bin\\server\\jvm.dll'
pljava.classpath = 'C:\\PostgreSQL\\pg95\\share\\pljava\\pljava-1.5.2.jar'
pljava.vmoptions = '-Xmx16m'

Restart the postgres_fwd service. If it fails to restart, probably there are issues with the path. Fix them, and the service will start, eventually.

Prepare a New Database Instance¶

This section illustrates the preparation of a single database instance. It should be repeated for each database instance needed by a FWD application.

Create an Empty Database¶

At this point, the database cluster has been initialized and configured, and the PL/Java software is installed at the cluster level. We are ready to create a database instance. For this, we need to use the fwd_admin role:

createdb -U {admin role} -h {host} -p {port} {database}

where:
{admin role} is replaced with the name of the admin role (e.g., fwd_admin);
{host} is the host name or IP address of the database server host;
{port} is replaced with the port number on which the database cluster is listening;
{database} is the name of the database instance to create.

For example:

    E:\fwd_projects> createdb -U fwd_admin -h localhost -p 5433 fwd_db

Now, when we list the databases for the cluster using the following command:

    E:\fwd_projects> psql -U fwd_admin -h localhost -p 5433 -l

...we should see the following:

                                                 List of databases
   Name    |   Owner   | Encoding |          Collate           |           Ctype            |   Access privileges
-----------+-----------+----------+----------------------------+----------------------------+-----------------------
 fwd_db    | fwd_admin | WIN1252  | English_United States.1252 | English_United States.1252 |
 postgres  | postgres  | WIN1252  | English_United States.1252 | English_United States.1252 |
 template0 | postgres  | WIN1252  | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |           |          |                            |                            | postgres=CTc/postgres
 template1 | postgres  | WIN1252  | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |           |          |                            |                            | postgres=CTc/postgres
(4 rows)

Note that fwd_admin is the owner of the database, rather than fwd_user. Ownership of the database implies the ability to drop it, which we do not want the fwd_user to have.

Apply the Converted Schema¶

The new database needs a schema before data can be imported into it. For this, we use the PostgreSQL-specific data definition language (DDL) script created during conversion and the PostgreSQL psql client utility. The following assumes the current directory is the application project root directory:

psql -U {user role} -h {host} -p {port} -f ddl/schema_table_{application}_postgresql.sql {database}

where:
{user role} is replaced with the name of the user role (e.g., fwd_user);
{host} is the host name or IP address of the database server host;
{port} is replaced with the port number on which the database cluster is listening;
{application} should be replaced with the converted application project name;
{database} is the name of the database instance.

For example:

psql -U fwd_user -h localhost -p 5433 -f ddl/schema_table_myapp_postgresql.sql fwd_db

Note that we only apply the table DDL script at this point, not the index DDL script. The latter is for re-indexing and recovery purposes, but is not used under normal circumstances. All indexes will be created when we import the data (see the Data Migration chapter).

It is important to use the database user role for this step, rather than the admin role. Otherwise, the FWD application server (which uses the database user role) will not be able to access the tables in the database for normal CRUD operations.

Enable Java as a Procedure Language¶

Earlier, we installed the PL/Java software at the cluster level. Now, we need to enable Java as a procedure language in the new database instance by creating a PostgreSQL extension module for PL/Java:

psql -U {admin role} -h {host} -p {port} -c "create extension pljava" {database}

where the substitution parameters have the same meaning as described above.

For example:

psql -U fwd_admin -h localhost -p 5433 -c "create extension pljava" fwd_db

Load FWD's User Defined Function Library¶

To complete the support for UDFs for each database instance, we need to load the FWD UDF library into the database. This involves executing several SQL statements as a superuser. The simplest way to do this is to begin with a template SQL script provided with FWD.

Copy the $P2J_HOME\\pl\\install_p2j.sql.template file into a new file, say, install_p2j.sql. The contents of that file are as follows:

select sqlj.remove_jar('p2j', true);
select sqlj.install_jar('file:<p2jpl_jar_location>', 'p2j', true);
select sqlj.set_classpath('public', 'p2j');

Replace p2jpl_jar in the second line with the fully qualified path of the p2jpl.jar file created when you built the FWD project. For example, if the FWD project resides at E:\fwd_projects\fwd on your system, the new contents of the install_p2j.sql file should be:

select sqlj.remove_jar('p2j', true);
select sqlj.install_jar('file:E:\\fwd_projects\\fwd\\build\\lib\\p2jpl.jar', 'p2j', true);
select sqlj.set_classpath('public', 'p2j');

Notes:

• file: is correct. The file:/ form is also accepted. The backslashes (\) must be escaped;
• The first statement removes any jar file assigned to the identifier p2j, which may have been installed previously. This is not strictly necessary for a newly created database and will generate a harmless error message. However, the same script can be used when applying a newer version of the jar file with an existing database when upgrading to a newer version of FWD;
• the second statement installs the p2jpl.jar file into the database for use with PL/Java, assigning the identifier p2j. Important: the PostgreSQL database server process must be able to access the p2jpl.jar file in the file system in order to install it. Thus, it is important that the path to this file is accessible to the Network Service user account. Failure to set these permissions properly for this file will be indicated by "Permission denied" errors in the PostgreSQL log file in response to this step of the installation;
• the third statement allows PL/Java to set a classpath when it launches a Java Virtual Machine within the database process. This allows the JVM to find and load FWD's UDF classes when needed.

Execute the script against the database instance as follows:

psql -U {admin role} -h {host} -p {port} -f install_p2j.sql {database}

where the substitution parameters have the same meaning as described above.

For example:

psql -U fwd_admin -h localhost -p 5433 -f install_p2j.sql fwd_db

This should produce the following output:

psql:install_p2j.sql:1: ERROR:  java.sql.SQLException: No Jar named 'p2j' is known to the system
 install_jar 
-------------

(1 row)

 set_classpath 
---------------

(1 row)

Finally, run a quick smoke test by invoking the toDec UDF, which accepts a string representation of a numeric value and converts it to a decimal data value:

psql -U {admin role} -h {host} -p {port} -c "select toDec('1.234')" {database}

where the all substitution parameters have the same meaning values are as described above.

For example:

psql -U fwd_admin -h localhost -p 5433 -c "select toDec('1.234')" fwd_db

This should produce the following output:

    todec
--------------
 1.2340000000
(1 row)

If so, you are ready to import data into the database.

Run the Data Import¶

At this point, the database is ready to import data from the original, corresponding, Progress database. Please follow the instructions provided in the Data Migration chapter.