Installing PostgreSQL for KCML
Installing PostgreSQL on the database server
You can compile your own version from source available on the PostgreSQL site or get a precompiled installer for Linux, Mac or Windows from the EnterpriseDB site. KCML requires a minimum PostgreSQL version of 8.4.1.
It is critical that the collation settings are set to the C locale in order that indexing is compatible with KDB and Oracle. This cannot be changed once the database cluster has been created. Use the --locale=C switch to initdb rather than relying on defaults.
If you need to connect from external clients, i.e. the database server and the application server are different machines, then edit $POSTGRES/data/postgresql.conf to ensure that listen_addresses is either '*' or includes the required interfaces. By default only localhost works. You also need to update pg_hba.conf to allow access from external clients. Restart the database if you changed anything.
All the KCML tables must be in a common schema owned by a single user, conventionally KCC, who is the only user granted update access in order to preserve referential integrity. Use the builtin admin account postgres to connect to the default postgres database from KCML and issue a CREATE DATABASE statement that creates the working database and the special KCC user thus:
CREATE DATABASE myfirstdatabase TYPE SQL USER kcc IDENTIFIED BY 'secretpwd'
Do not use the built in postgres database for creating application tables.
Tablespaces
Tablespaces map logical names used by PostgreSQL into directories in the filesystem. They don't belong to any specific database and, in fact, are owned by the Unix postgres account with database users being granted access through the PostgreSQL permissions system. Tables will be created in the default unnamed tablespace unless a specific tablespace is given in the CREATE TABLE statement.
To create your own tablespaces you must be connected to the postgres database as the DBA user postgres. Issue a CREATE TABLESPACE statement thus:
CREATE TABLESPACE MISC '/Users/pjc/tablespaces/MISC' FOR USER kcc
The directory must already exist, be owned by the postgres account with 700 permissions and be empty.
Installing PostgreSQL on the application server
KCML does not require any PostgreSQL client libraries installed.
The database details are specified as the first (database type) and third (connect string) arguments to KI_CONNECT. The database type is "POSTGRES". The connect string is a series of space separated keyword and value pairs. The keywords are not case sensitive but the values generally are. If you duplicate keywords the last one counts. Supported keywords are shown in the table:
| server | The DNS name or IP address of the server hosting the PostgreSQL database. Optional if same as client. |
|---|---|
| host | Same as server. Defined here for backwards compatibility. |
| udspath | Path of uds socket if connecting locally on Linux. Defaults to /tmp. This is the directory containing the file entry .s.PGSQL.5432 |
| database | The name of the PostgreSQL database. Required. |
| dbname | Same as database. Defined here for backwards compatibility. |
| appname | Application name defined in PostgreSQL 9.0 |
| trace | Define trace file |
| tracebuffer | Boolean - set to 1 to enable tracing |
| port | The TCP/IP port of the listener for the PostgreSQL database. Optional, defaulting to 5432. |
If the server is not specified on Windows then KCML will assume the database is on the same computer and will use the network loopback address localhost. On Unix systems it will use a Unix domain socket as these have a performance advantage over a localhost loopback connection.
For historical reasons host is accepted as an alias for server and dbname as an alias for database. Also semicolons can be used as separators.
For example
REDIM dsn$="server=localhost database=K8" CALL KI_CONNECT "POSTGRES", conn, dsn$, "pjc", "secret" TO s
See also