Database Server Setup for PostgreSQL on Linux¶

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

To ensure the behavior of a FWD application matches that of the original application as closely as possible, certain legacy features must be supported by the database server. These features require special attention when installing the database server.

For instance, a Progress database by default will sort string data differently than a standard PostgreSQL database for the same locale. To make PostgreSQL sort these data in a similar manner to the pre-conversion system, it is necessary to install a custom locale at the operating system level, which the PostgreSQL database cluster can then use to collate its string data.

Likewise, built-in functions used in the where clauses of 4GL data access language statements (e.g., FOR, FIND, etc.) require special consideration. In Progress, these functions run in the client process. In FWD, it would be too expensive to run the equivalent built-in functions on the application server, because this approach would require many more records to be retrieved from the database than is necessary, only to discard many of those records after applying the built-in function(s) as a filter at the application server.

Instead, built-in function references within ABL where clauses are converted to user-defined function (UDF) references within Hibernate Query Language (HQL) queries in the converted application. This approach allows FWD to re-use its ABL-compatible, built-in function runtime implementations as database UDFs. Filtering records using UDFs running within the database server process greatly reduces the number of records sent back to the FWD application server.

This approach presumes support for UDFs implemented in Java exists in the backing database. To provide such support, FWD relies upon PL/Java, an open source project which enables Java as a procedure language within a PostgreSQL database.

A note about Linux package management differences
Different Linux distributions use different mechanisms to install and configure software packages, and not all of them are covered here. Where package management commands and installation details are documented, this chapter provides instructions for Ubuntu. Specifically, this chapter documents the installation and set up of PostgreSQL version 9.5 on Ubuntu 15.04. However, while the specific package management commands and configuration may differ across distributions, the same concepts apply.

Supported PostgreSQL Versions¶

TODO: update docs to reflect that 9.5 should no longer be used; PostgreSQL 14.x is officially supported with FWDv4, and we know of no reason PostgreSQL 15 would not work, but we have not tested with it yet.

At the time of this writing, FWD supports PostgreSQL versions 9.4 and higher on Linux. While older versions have been made to work, the set up of those versions is more complicated and may involve the use of software dependencies that have been deprecated.

Note, however, that at the time of this writing, version 9.5 is the highest version supported by PL/Java, a third party, open source library FWD relies upon for its user defined function support. We therefore recommend not going beyond version 9.5 until PL/Java officially supports later versions of PostgreSQL.

Supported Java Versions¶

Java is needed on the database server to run Java user-defined functions (UDFs). UDFs are used to execute FWD's compatible implementation of ABL built-in functions, which can be used in database queries. Since these UDFs access a subset of the FWD runtime libraries, and there are dependencies in the FWD runtime code on features introduced with Java 8, the minimum supported version of Java is 1.8.x.

Overview and Scope¶

This chapter covers the preparation and installation of the PostgreSQL server software on Linux, including the creation of a FWD-compatible database cluster and an empty database instance, which is ready for the import of data. The migration of data from a Progress database into this new database instance is covered separately, in Data Migration.

At a high level, the steps required to prepare the PostgreSQL database server are:

Install a custom locale into Linux to ensure text data sort properly.
Install PostgreSQL database server software.
Create a FWD-specific database cluster for use with the converted application.
Create database user accounts for database setup/administration and for use by the FWD runtime to provide data access services to an application.
Configure and secure the database cluster.
~~Install the PL/Java software into the database cluster, for User Defined Function (UDF) support.~~ [TODO: integrate SQL UDF setup documentation]
Create an empty database instance which uses the converted schema.
~~Enable Java as a procedure language in the new instance.~~
Load FWD's user defined functions into the new instance.

Prerequisites¶

Conversion¶

The early steps described in this document of installing and configuring the PostgreSQL software can be completed without any conversion prerequisites. However, the later steps of creating new database instances presume that at least the front and middle portions of conversion (see Running Conversion) have completed successfully. Specifically, the data definition language (DDL) generated by the middle portion of conversion is required when preparing a new database instance.

Hardware¶

There are no hardware requirements for a PostgreSQL database specific to FWD, though generally a modern, multi-core, 64-bit system with good I/O throughput is preferred. Hardware requirements must be determined according to the application's use of the database, which will require tuning and testing.

The database cluster can reside on the same physical machine as the FWD application server, but this is not a requirement. Since the cluster will be prepared using a FWD-specific, custom locale, it should be dedicated for the use of FWD applications.

Install a Custom Locale¶

By default, a PostgreSQL database will not sort text data in the same way as a Progress database does by its default. If not corrected for, this discrepancy could cause unexpected and undesirable application behavior. See Database Collation for details.

To adjust for these differences, a custom locale must be installed on the FWD database server system before a FWD-compatible database cluster is created. The LC_COLLATE portion of the locale defines how FWD databases will sort text data in indexes and in query results. This mechanism is used to ensure sort operations are performed the way a converted application expects, which if uncorrected could lead to unexpected behavior. The PostgreSQL database cluster used for FWD applications must be initialized using this custom locale.

The FWD project provides multiple custom locale implementations for this purpose. These locales are intended to mimic, as closely as possible, a Progress database's default collation behavior using the specified character set. Dynamic collation changes made by individual 4GL language statements currently are not supported by FWD, but each database can be configured with an individual collation. Currently FWD provides out-of-the-box the following locale scripts:

Locale Script	Character Encoding	4GL Collation	Other Language Specific Categories
`en_US@iso88591_fwd_basic` (default)	`ISO-8859-1`	`basic`	`en_US`
`en_US@iso885915_fwd_basic`	`ISO-8859-15`	`basic`	`en_US`
`en_US@cp1252_fwd_basic`	`Windows-1252`	`basic`	`en_US`
`en_GB@iso88591_fwd_basic`	`ISO-8859-1`	`basic`	`en_GB`
`en_GB@iso885915_fwd_basic`	`ISO-8859-15`	`basic`	`en_GB`
`en_GB@cp1252_fwd_basic`	`Windows-1252`	`basic`	`en_GB`
`nl_NL@iso88591_fwd_basic`	`ISO-8859-1`	`basic`	`nl_NL`
`nl_NL@iso885915_fwd_basic`	`ISO-8859-15`	`basic`	`nl_NL`
`nl_NL@cp1252_fwd_basic`	`Windows-1252`	`basic`	`nl_NL`

It's easy to notice that the name of the locale scripts are compounded of four tokens linked together by either the _ or the @ character. The first is the language which dictates the LC_TIME, LC_NUMERIC, LC_MONETARY, LC_MESSAGES, and other categories of the locale. The second is the code page/character encoding. The third is the equivalent 4GL collation. At the moment, all available scripts are based on 4GL's basic collation. The full set of these can be seen in the 4GL's convmap.cp file. The last token is an application specific marker.

Before setting up the environment, the best suited locale must be chosen. In the next steps we will use the default, en_US@iso88591_fwd_basic locale script.

The custom locale must be installed into the Linux operating system before the PostgreSQL FWD cluster is created.

The following instructions will install this onto an Ubuntu Linux system (at the time of this writing, there is not yet an analog to this procedure for the Windows operating system).

Ensure the Linux package which provides the libc locale database sources is installed on the target machine.
```
sudo apt-get install libc-bin
```
Run localedef --help and review the bottom of the output to determine which directories contain these source files (as well as the path which contains the system's binary locales). The source will often reside in some derivative of i18n, such as /usr/share/i18n. The binaries will often reside in subdirectories of /usr/lib/locale.
Change to the directory containing the charmap files (e.g., /usr/share/i18n/charmaps). Locate the archive containing the ISO-8859-1 charmap definition. If ISO-8859-1 is not the character encoding your chosen locale is based on, replace it accordingly. Decompress it if necessary (it may be gzip'd or otherwise archived). For example:
```
sudo gunzip ISO-8859-1.gz
```
Copy the custom FWD locale source file from the $P2J/locale subdirectory into the system's locales source directory (e.g., /usr/share/i18n/locales). For example:
```
sudo cp <path_to_fwd>/locale/en_US@iso88591_fwd_basic /usr/share/i18n/locales
```
Use a text editor as super user and append one-line locale definition to /etc/locale.gen file. For example, add
```
en_US@iso88591_fwd_basic ISO-8859-1
```
for the default 4GL locale and
```
nl_NL@cp1252_fwd_basic CP1252
```
for a locale based on Dutch language, using CP1252 character encoding.
Execute
```
sudo locale-gen --no-archive
```
After that you can use locale -a to check whether the newly added locales are listed as active system locales.

Install PostgreSQL Database Server Software¶

To install the PostgreSQL server and client software on Ubuntu, we use the apt package manager. For PostgreSQL 9.5, the command is:

sudo apt-get install postgresql postgresql-client-9.5 postgresql-common postgresql-contrib postgresql-server-dev-9.5

This command installs the PostgreSQL server and client software, a database cluster manager, and development libraries that will be needed to enable running user-defined functions implemented by FWD in the database server.

In case the command line will generate one or multiple errors like: E: Unable to locate package <package>, use the following set of commands in advance:

# Create the file repository configuration:
sudo sh -c 'echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# If you are on a newer Ubuntu, you may get a warning: "Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8))." 
# If so, you can correct this with the following command:
sudo mv /etc/apt/trusted.gpg /etc/apt/trusted.gpg.d/

# Update the package lists:
sudo apt-get update

# Now install the specific version of PostgreSQL with the command above.

These instructions install precompiled PostgreSQL binaries. If you have a need to compile PostgreSQL with custom settings, you will need to consult the PostgreSQL documentation, as that requirement is beyond the scope of this document.

The PostgreSQL installation process creates a operating system user account called postgres. This account will be used by further steps of this setup process.

Create a FWD-Specific Database Cluster¶

If the PostgreSQL server software was installed from a binary package, the package probably created a default database cluster named main. Do not use this default cluster; it will not have the appropriate collation behavior. Instead, create a new cluster which uses the custom, en_US.iso88591_fwd_basic locale described above. A locale cannot be applied to a PostgreSQL cluster once it has been created, so it is necessary to create a new cluster in order to specify the locale. This cluster should only be used with converted FWD applications. It will collate string data like a Progress database, which may not be appropriate for other applications.

To create a cluster, determine the data subdirectory in which PostgreSQL stores its databases by consulting the packager's documentation, or the postgresql.conf file (the data_directory setting), if a default cluster was installed. If you built PostgreSQL from source yourself, and installed it from scratch, take note of the data directory you specified. You will need it to initialize a cluster.

PostgreSQL provides the initdb utility to create a new database cluster. Ubuntu allows multiple versions of PostgreSQL to co-exist on the same system and provides a version-aware wrapper for this utility, called pg_createcluster. We will illustrate the use of both commands here. Either way, the command must override the default locale with the en_US.iso88591_fwd_basic locale. In this case, we are specifying /opt/fwd_data/9.5 as our data directory, but this is an arbitrary choice:

sudo initdb --locale=en_US@iso88591_fwd_basic -D /opt/fwd_data/9.5/

sudo pg_createcluster 9.5 fwd --locale=en_US@iso88591_fwd_basic -d /opt/fwd_data/9.5/

The output should look something like this:

Creating new cluster 9.5/fwd ...
  config /etc/postgresql/9.5/fwd
  data   /opt/fwd_data/9.5/
  locale en_US.iso88591_fwd_basic
  socket /var/run/postgresql
  port   5433
Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run:
  sudo systemctl daemon-reload

Take note of the port reported in this output, which may differ from the example above, depending upon your installation. This information will be needed as input to future commands. This is the network port on which the new database cluster is listening. The default for the first installed PostgreSQL cluster is 5432. However, if you have multiple versions of PostgreSQL installed, or multiple clusters for a given version, or if you specified the port explicitly when creating the cluster (not shown above), the port number will differ from the default.

To start the new cluster automatically on boot and to treat the cluster as a service, heed PostgreSQL's suggestion and run the recommended command:

sudo systemctl daemon-reload

This only needs to be done once. Initially (assuming you haven't rebooted since the last command), the new cluster will not be running, so it will need to be started manually:

sudo pg_ctlcluster 9.5 fwd start

Create Database Roles¶

PostgreSQL user and group accounts are known as roles. In the previous step, the postgres operating system account was used to create the database cluster. 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 separate 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 operating 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:

sudo -u postgres createuser -P -E -S -D -R [-p {port}] {name}

where:
pg version is the major.minor release version of PostgreSQL you are using;
-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 a port number will follow
{port} should be replaced with the port number on which the database cluster is listening; this parameter is optional if using the default PostgreSQL port 5432;
{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:

sudo -u postgres createuser -P -E -S -D -R -p 5433 fwd_user

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:

sudo -u postgres createuser -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:

sudo -u postgres createuser -P -E -s -g fwd_user -p 5433 fwd_admin

Configure and Secure the Server¶

Connection Security¶

The choice of how best to secure connections differs by environment and organization. The policy of how users may connect to databases is managed in the /etc/postgresql/9.5/fwd/pg_hba.conf file (assuming the default installation location).

For development or simple test environments in which the database server and FWD application server are running on the same physical system, the default configuration will work acceptably for many users.

For production use or more complicated test environments, particularly those in which the database server and FWD application server reside on physically separate systems, consult with a DBA to determine how to best secure your specific environment.

At minimum, the FWD user role (e.g., fwd_user) will need TCP/IP socket access to any FWD database, from the host on which the application server runs. If, for example, the FWD application server host on the local network was fwdappsrv01, and the target database was fwddb, this could be represented in the pg_hba.conf file by replacing:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

with:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    fwddb           +fwd_user       fwdappsrv01/32          md5

Note the plus sign (+) prepended to the fwd_user role. This indicates this is a group role which may include other roles, such as our fwd_admin role.

The FWD application server does not yet support encrypted connections from the FWD server to PostgreSQL; however, PostgreSQL does support SSL/TLS connections. If encrypted database connections are needed, changes will have to be made to the FWD runtime code base.

All connectivity between FWD clients and servers is fully secured, it is only the database connections which are unsecured.

Important PostgreSQL Configuration Parameters¶

Parsing Dates¶

By default, PostgreSQL parses formatted date strings as month-first, then date-of-month, then year. If your application uses formatted date strings in queries, and it expects the date-of-month value to be parsed first, replace the following line in postgresql.conf:

datestyle = 'iso, mdy'

with

datestyle = 'iso, dmy'

Performance and Resources¶

When performance tuning the database(s) backing a FWD application, there are several important points to keep in mind:

FWD implements converted ABL temp-tables in a separate, embedded, in-process database at the application server, so configuration settings which influence temporary tables in PostgreSQL will only have an effect insofar as PostgreSQL uses temporary tables for its own, internal purposes.
The FWD conversion and runtime seek to minimize round trips to the database through various optimizations; however, the nature of the ABL code being converted often results in more database round trips (typically for relatively simple, index-based queries) than one would normally expect from a database application written from scratch to leverage set-based operations.

The following table is based on information provided in the PostgreSQL 9.5 User Manual. It summarizes a number of configuration values which are likely to influence the performance of a PostgreSQL database backing an application deployed with FWD. We strongly recommend referring to the more detailed information about these parameters in the PostgreSQL documentation. This is intended as a rough guide only. Finding the right balance of performance and resource use is a matter of testing and tuning; it depends on the characteristics of the application and the type of database server hardware in use. Where specific values/ranges are recommended, they are based on the database running in a dedicated server environment. You may wish to consult with a DBA for additional ways to find the best balance of performance and resource use in your particular environment.

Note that it is quite possible to make performance worse with inappropriate changes to these configuration values (particularly the cost estimates which affect the database's query planning). Thus, it is important to make changes in isolation, and to test their effects thoroughly.

Parameter change	Purpose of parameter	Considerations
increase `max_connections`	Maximum number of concurrent connections to the database server	The default is typically 100 connections, but might be less (determined during cluster initialization, depending on kernel settings). Ensure PostgreSQL cluster supports enough connections for peak use, and add at least one additional connection for an admin user. Implemented using shared memory; may require an increase to `SHMMAX`.
increase `shared_buffers`	Amount of memory the database server uses for shared memory buffers. One of the most influential settings on FWD database performance.	The default is typically 128MB. A reasonable value for `shared_buffers` is 25-40% of the memory in your system. Implemented using shared memory; may require an increase to `SHMMAX`. Larger settings for `shared_buffers` usually require a corresponding increase in `checkpoint_segments`, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.
increase `work_mem`	Amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files	Defaults to 4MB. Increasing this up to a certain point may increase query response time, in that managing sorting and hashing in memory usually is faster than using temporary disk files. Note that several such operations might be running in parallel per query, and many queries may be running in parallel; the `work_mem` limit applies to each sort/hash operation individually. So, the actual memory used could be many times the value of `work_mem`. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries. All of these except DISTINCT are used by FWD.
increase `wal_buffers`	Amount of shared memory used for WAL (Write Ahead Log) data that has not yet been written to disk	Increasing this might improve write performance. The default setting of -1 should be reasonable in most cases; it indicates 1/32nd of `shared_buffers` (but not less than 64KB nor more than the size of one WAL segment, typically 16MB) will be allocated. If this is not ideal, setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once.
increase `maintenance_work_mem`	Specifies the maximum amount of memory to be used by maintenance operations, such as `VACUUM`, `CREATE INDEX`, and `ALTER TABLE ADD FOREIGN KEY`.	Default is 64MB. Larger settings might improve performance for vacuuming and for restoring database dumps. Note that other than autovacuum, which conducts vacuum operations while the system is running normally, the other operations this setting affects do not occur at runtime, but rather during data migration and restoring database dumps. Since only one of the maintenance operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than `work_mem`.
review `effective_cache_size`	Sets the query planner's assumption about the effective size of the disk cache that is available to a single query, factored into estimates of the cost of using an index. a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used.	Default is 4GB. Consider both shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter is used only for query planner estimation purposes; it has no effect on the actual size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache. A FWD database schema carries over the indices of the original Progress database; both rely heavily on index scans. As such, it may be useful to bias the query planner toward index scans with a high setting for this parameter.
reduce `random_page_cost`	Sets the planner's estimate of the cost of a non-sequentially-fetched disk page	Reducing this value relative to `seq_page_cost` will cause the system to prefer index scans; raising it will make index scans look relatively more expensive. You can raise or lower both values together to change the importance of disk I/O costs relative to CPU costs (described by `cpu_tuple_cost`, `cpu_index_tuple_cost`, `cpu_operator_cost`).
increase `checkpoint_warning`	Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happen closer together than this many seconds (which suggests that `checkpoint_segments` ought to be raised).	Default is 30 seconds. 0 disables the warning (not recommended).
increase `checkpoint_segments`	Maximum number of log file segments between automatic WAL checkpoints (each segment is normally 16 megabytes)	Default is 3 segments. Consider increasing this value if you are getting checkpoint warnings in the database log, but be aware that this can increase the amount of time needed for crash recovery. Increasing the number of checkpoint segments is correlated with increasing `shared_buffers` and can improve performance while loading.
increase `default_statistics_target`	Sets the default statistics target for table columns without a column-specific target set via `ALTER TABLE SET STATISTICS`.	Default is 100. Larger values increase the number of samples taken, and thus the time needed to do `ANALYZE`, but might improve the quality of the planner's estimates. Tables which benefit most from a higher value are those with irregular data distributions within columns that are leading components in an index. If there are few of these, consider using `ALTER TABLE SET STATISTICS` on those particular tables instead of this global change.

Install User Defined Function Support¶

Overview¶

OpenEdge's built-in functions can be referenced in ABL code, including within the WHERE clause of a data access language statement (e.g., FOR EACH, OPEN QUERY, FIND, etc.). These functions are executed within the Progress client process, whether they occur in data access statements or within other business logic.

FWD includes a compatible, Java implementation of these built-in functions. These can be executed in two places:

within the FWD application server process, for functions which occur outside data access statements;
within the database server process, for functions which occur within the WHERE clauses of data access statements.

The second case exists because FWD attempts to push as much data access processing to the database server as possible, for efficiency. It is inefficient to fetch a set of records from the database to the application server and filter them using built-in functions at the application server. This nearly always will fetch many more records than are actually necessary to satisfy a query. Instead, FWD lets the database perform the filtering, using the Java implementation of the built-in functions.

FWD converts data access statement WHERE clauses to equivalent snippets of Hibernate Query Language (HQL), which are used at runtime to compose a query which is sent to the backing database (PostgreSQL in this case). When the FWD conversion encounters an ABL built-in function in a WHERE clause, it determines whether the sub-expression containing that function can be factored out into a query substitution parameter, or whether the function must execute at the database server. The latter case occurs when the function references a table which is part of the query. In such a case, FWD converts that function into an inline, HQL reference to the FWD Java implementation of the equivalent built-in function.

For example, the ABL WHERE clause:

DECIMAL(my-table.num-text) = 1.234

would convert to the HQL snippet:

toDec(myTable.numText) = 1.234

which would correspond with the following SQL phrase, which is ultimately sent to the database:

toDec(my_table.num_text) = 1.234

By default, the database will not have a native toDec function, so in order for such queries to work, any built-in function that can be referenced from a FWD query has to be defined in the database as a user-defined function (UDF). FWD re-uses its compatible, Java implementations of the ABL built-in functions for this purpose. The mechanism to define such functions differs by database implementation.

TODO: integrate SQL/native UDF documentation into this wiki.

Please note: starting with FWDv4, the project no longer uses PL/Java for UDF support. Instead, please use Native UDFs for PostgreSQL to set up UDF support for PostgreSQL databases.

For PostgreSQL, FWD uses an open source project called PL/Java. PL/Java enables the use of Java as a procedure language within a PostgreSQL database, thus allowing FWD to define its built-in functions as UDFs in the database.

There are two phases of enabling FWD's built-in function support. The first, described in the next section, is to install the PL/Java software on the database server. This provides the basic support of defining and enabling Java as a procedure language in PostgreSQL. The second phase, described later in this chapter, completes the installation. It operates on each database which requires UDF support.

Installation of PL/Java Base Support¶

This section is obsolete as of FWDv4. PL/Java is no longer used for UDF support in FWD databases. Please ignore this section and refer instead to Native UDFs for PostgreSQL.

TODO: integrate Native UDFs for PostgreSQL into this document.

The recommended way is to install the appropriate, pre-built PLJava binaries for your target operating system. There is the repository on the web containing precompiled binaries for Linux: https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-pljava/. For Ubuntu 18.04, the file is: postgresql-9.5-pljava_1.5.2-1.pgdg18.04+1_amd64.deb.

1. download the file to local drive
2. install it with your favorite package installer application/command. For example, open a terminal in directory of the downloaded package and execute:

sudo dpkg -i postgresql-9.5-pljava_1.5.2-1.pgdg18.04+1_amd64.deb

3. ensure the binaries are properly installed:

ls /usr/share/postgresql/9.5/pljava/

pljava--1.5.0--1.5.2.sql        pljava--1.5.1-BETA3--1.5.2.sql
pljava--1.5.0-BETA1--1.5.2.sql  pljava-1.5.2.jar
pljava--1.5.0-BETA2--1.5.2.sql  pljava--1.5.2.sql
pljava--1.5.0-BETA3--1.5.2.sql  pljava-api-1.5.2.jar
pljava--1.5.1--1.5.2.sql        pljava-examples-1.5.2.jar
pljava--1.5.1-BETA1--1.5.2.sql  pljava--unpackaged--1.5.2.sql
pljava--1.5.1-BETA2--1.5.2.sql

ls /usr/lib/postgresql/9.5/lib/libpljava*

/usr/lib/postgresql/9.5/lib/libpljava-so-1.5.2.so

It is also possible (however, more complex) to build PL/Java from source on the system on which PostgreSQL is installed. Instructions can be found in Patching and Building Customized 3rd Party Libraries. Once PL/Java is built on this system, install it as follows:

cd pljava-1_5_0
sudo java -jar pljava-packaging/target/pljava-pg9.5-amd64-Linux-gpp.jar

You should see:

/usr/lib/postgresql/9.5/lib/libpljava-so-1.5.0.so as bytes
/usr/share/postgresql/9.5/pljava/pljava-1.5.0.jar as bytes
/usr/share/postgresql/9.5/pljava/pljava-api-1.5.0.jar as bytes
/usr/share/postgresql/9.5/pljava/pljava-examples-1.5.0.jar as bytes
/usr/share/postgresql/9.5/extension/pljava.control as lines (ASCII)
/usr/share/postgresql/9.5/pljava/pljava--1.5.0.sql as lines (UTF8)
/usr/share/postgresql/9.5/pljava/pljava--unpackaged--1.5.0.sql as lines (UTF8)
/usr/share/postgresql/9.5/pljava/pljava--1.5.0-BETA3--1.5.0.sql as lines (UTF8)
/usr/share/postgresql/9.5/pljava/pljava--1.5.0-BETA2--1.5.0.sql as lines (UTF8)
/usr/share/postgresql/9.5/pljava/pljava--1.5.0-BETA1--1.5.0.sql as lines (UTF8)

Next, edit /etc/postgresql/9.5/fwd/postgresql.conf, adding the following lines to the CUSTOMIZED OPTIONS section (adjust the paths as appropriate for your installation):

pljava.libjvm_location = '/usr/lib/jvm/default-java/jre/lib/amd64/server/libjvm.so'
pljava.classpath = '/usr/share/postgresql/9.5/pljava/pljava-1.5.0.jar'
pljava.vmoptions = '-Xmx16m -Djava.awt.headless=true'

The first line tells PL/Java where to find the Java virtual machine. The second sets the classpath which will be used by that JVM, so it can find and load the PL/Java classes. The final line limits the maximum heap available to each JVM instance (one is loaded per database connection that uses Java UDFs) to 16MB. This should be sufficient for the UDFs which FWD uses as built-in functions. If you define your own UDFs for a converted application's use (an advanced feature), you may need to adjust this value, depending on the memory your UDFs consume.

If necessary, ensure the permissions are set correctly for the installed PL/Java directory:

sudo chmod o+rx /usr/share/postgresql/9.5/pljava/

Per-Database PL/Java Installation¶

To complete the installation of FWD's UDFs, PL/Java has to be installed as an extension module into each FWD database instance, and a FWD-specific jar file has to be installed into each FWD database instance as well. This final step is described in the next section.

Restart the Database Cluster¶

To apply any and all configuration changes, restart the PostgreSQL cluster:

sudo pg_ctlcluster 9.5 fwd restart

Note that this is an Ubuntu-specific command; consult your distribution's documentation for the appropriate cluster restart command on your platform.

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 [--cluster={pg version}/{cluster}] -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:

createdb -U fwd_admin -h localhost -p 5433 fwd_db

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

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 | LATIN1   | en_US.iso88591_fwd_basic | en_US.iso88591_fwd_basic | 
 postgres  | postgres  | LATIN1   | en_US.iso88591_fwd_basic | en_US.iso88591_fwd_basic | 
 template0 | postgres  | LATIN1   | en_US.iso88591_fwd_basic | en_US.iso88591_fwd_basic | =c/postgres          +
           |           |          |                          |                          | postgres=CTc/postgres
 template1 | postgres  | LATIN1   | en_US.iso88591_fwd_basic | en_US.iso88591_fwd_basic | =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¶

This section is obsolete as of FWDv4. PL/Java is no longer used for UDF support in FWD databases. Please ignore this section and refer instead to Native UDFs for PostgreSQL.

TODO: integrate Native UDFs for PostgreSQL into this document.

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¶

This section is obsolete as of FWDv4. PL/Java is no longer used for UDF support in FWD databases. Please ignore this section and refer instead to Native UDFs for PostgreSQL.

TODO: integrate Native UDFs for PostgreSQL into this document.

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@', '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 /opt/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:///opt/fwd/build/lib/p2jpl.jar', 'p2j', true);
select sqlj.set_classpath('public', 'p2j');

Note: the triple forward slash (///) in the path is correct.

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, which normally runs under the postgres Unix user account, 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 postgres user account. The best way to ensure this is to place this file in a location where the "world" execute (x) permission bit is enabled for each directory in the path to this file, and the file itself has its "world" read (r) permission bit enabled. 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. A good place for p2jpl.jar is /usr/share/java directory.

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

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.

Back Up the New Database¶

Once data has been migrated into the new database, it is a good idea to make two backups of this "pristine" database instance:

a live backup which can be quickly restored as a template within the same cluster;
a dump which can be archived and restored into the same or a different cluster.

The former can be restored relatively quickly, for iterative testing and development; it can only be used within the current cluster. The latter is slower to restore, but it is useful for archival purposes, or when moving to a new version of PostgreSQL, or to restore across clusters.

Live Backup¶

A live backup is a copy of the database which exists in the cluster and is ready to use at any time. It can serve as a template to create new copies of the database much more quickly than a restore from a dump. This is especially useful for testing, when a new copy of a pristine data set is required to begin a new test cycle. The limitation of this type of backup is that it cannot be restored in a different cluster. Thus, it is not suited to move a data set to a new version of PostgreSQL or to a different host.

To make a live backup from a newly imported database, use the following command:

createdb -U {admin role} -h {host} -p {port} -T {source database} {target 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;
{source database} is the name of the newly imported database instance;
{target database} is the backup database instance to be created.

For example:

createdb -U fwd_admin -h localhost -p 5433 -T fwd_db fwd_db_pristine_backup

This command may take a while to complete, depending on the size of the source database. Note that the backup will take up as much space on disk as the source database. The backup will behave as any other database. Specifically, it will not be write-protected, so be careful to not use it as a runtime database, nor make any updates to it.

When you need to create a clean database instance from the backup, you would use the backup as the source database and the new instance as the target. For example, if you wanted to create a test database instance from the backup, you might use:

createdb -U fwd_admin -h localhost -p 5433 -T fwd_db_pristine_backup fwd_test_db

Dump Backup¶

A dump backup is useful for archival purposes, and for moving across clusters, hosts, and PostgreSQL versions. For this type of backup, use the PostgreSQL pg_dump utility. Please consult the PostgreSQL documentation for details of that utility's use.

For larger databases, we recommend the following command, which compressed the dump as it is being created:

pg_dump -U {admin role} -h {host} -p {port} -O {database} | bzip2 > {archive}.bz2

For example:

pg_dump -U fwd_admin -h localhost -p 5433 -O fwd_db | bzip2 > pg_dump_fwd_db_20170322.bz2

Note that this command can take a while to complete for larger data sets.

To restore this archive to a database named fwd_db_restored, first create a new, empty database:

createdb -U fwd_admin -h localhost -p 5433 fwd_db_restored

Then, issue the following command to perform the restore:

bunzip2 -c pg_dump_fwd_db_20170322.bz2 | psql -U fwd_user -h localhost -p 5433 fwd_db_restored

Note: restore with the user role (i.e., the database role the FWD runtime will use), rather than with the admin role.

Reload FWD User Defined Function Library¶

After restoring either a live backup or a dump archive, you should always reload the FWD UDF library in the restored database. This is important in case the version of FWD in use has changed since the time the backup was made. It will ensure the version of the FWD UDF library used by the database server is consistent with the version used by the FWD application server.

Project

General

Profile

FWD

Wiki

Database Server Setup for PostgreSQL on Linux¶

Supported PostgreSQL Versions¶

Supported Java Versions¶

Overview and Scope¶

Prerequisites¶

Conversion¶

Hardware¶

Install a Custom Locale¶

Install PostgreSQL Database Server Software¶

Create a FWD-Specific Database Cluster¶

Create Database Roles¶

Configure and Secure the Server¶

Connection Security¶

Important PostgreSQL Configuration Parameters¶

Parsing Dates¶

Performance and Resources¶

Install User Defined Function Support¶

Overview¶

Installation of PL/Java Base Support¶

Per-Database PL/Java Installation¶

Restart the Database Cluster¶

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¶

Back Up the New Database¶

Live Backup¶

Dump Backup¶

Reload FWD User Defined Function Library¶