Skip to main content

LIDS Application Server Database Installation Guide - PostgreSQL

This guide details the steps required to install the PostgreSQL database backend for the LIDS Application Server. It covers prerequisites, file organization, configuration, and execution of the installation scripts.

1. Prerequisites​

Before proceeding, ensure the following requirements are met on the target database server:

  • PostgreSQL Server Installed and running (Version 9.5 or higher recommended based on script comments).

  • SQL Tool: A command-line tool to execute the scripts.

  • PostGIS Extension The PostGIS binaries must be installed on the server (the scripts will attempt to enable the extension, but the software must be present).

  • Tablefunc Extension Required for hierarchical queries.

  • Privileged Access You must have a database user with SUPERUSER privileges (e.g., postgres) to run the initial setup script, as it creates users and databases.

2. Directory Structure​

Currecntly, the installation scripts for each database are stored inside the .ward archive of lids-as. So please extract the postgresql folder from the lids-as.war/sql/postgresql path and place it in a convenient location on your database server. The main installation script (create-lids-user.sql) expects a specific file hierarchy. You must organize the provided files into the following folder structure before running the installation:


/postgresql/
├── create-lids-user.sql        # The main entry point script
├── dbinstall.sql               # System tables installation
├── scripts/
│   ├── create-license-tables.sql
│   └── create-security-tables.sql
└── functions/
    ├── ST_Contains_.sql
    ├── ST_Covers_.sql
    ├── ST_Equals_.sql
    ├── ST_Intersects_.sql
    └── ST_RelateMatch_.sql

info

The installation scripts for each database are stored inside the .war archive of lids-as. Extract the postgresql folder from the lids-as.war/sql/postgresql path.

3. Configuration​

There are two ways to configure the installation: manually editing the script or passing parameters via the command line (useful for automation).

Option A: Manual Configuration (Standard)​

Open create-lids-user.sql in a text editor. Modify the variables at the top of the file to match your desired environment configuration:

-- \set location_dir '\'C:\\Postgres\\test\'' -- Uncomment and set if using specific tablespace locations

\set database_name 'lids_db'    -- Set your desired database name
\set owner 'lids_user'          -- Set the username for the LIDS application
\set pass '\'SecurePass123\''   -- Set the password for the new user
\set schema 'lids_user'         -- Set the schema name (Recommended: keep same as owner)
warning

The script drops the public schema and creates a schema matching the :schema variable. Make sure you have backed up any existing data before running this script.

Option B: Parameterized Configuration (Automation)​

To run the installation without manually editing the values for every deployment, you can pass parameters via the command line.

Prerequisite: You must first comment out the default \set lines in create-lids-user.sql so they do not override your command-line arguments:

-- \set database_name 'test'
-- \set owner 'test'
-- \set pass '\'test\''
-- \set schema 'test'

You can then define these variables dynamically when running the psql command (see Phase 1 below).

4. Installation Instructions​

The installation is performed in two phases:

  1. Environment Setup – Creating the user, database, extensions, and security tables
  2. System Tables – Installing the core LIDS application tables

Phase 1: Environment Setup​

Run the create-lids-user.sql script using psql. Connect as a superuser (usually postgres).

Manual Configuration​

cd /installation_root/
psql -h localhost -U postgres -d postgres -f create-lids-user.sql

Prompts: You may be prompted for the postgres user password.

What this does:

  • Creates the user and database defined in your configuration
  • Installs postgis and tablefunc extensions
  • Runs the scripts in the scripts/ and functions/ folders
  • Sets up Security and License tables

Phase 2: System Tables Installation​

The file dbinstall.sql creates the core application tables (layers, settings, auditing, etc.). This script is not automatically run by Phase 1.

Important Pre-requisite​

The dbinstall.sql script contains a placeholder ${DB_USER}. Unlike standard psql variables, this syntax (${...}) requires manual replacement or external processing.

Manual Edit​

  1. Open dbinstall.sql
  2. Replace ${DB_USER} with your database name (e.g., lids_db)
  3. Run:
psql -h localhost -U lids_user -d lids_db -f dbinstall.sql

5. Verification​

To ensure the installation was successful, log in to the new database and check for the following objects.

Extensions​

SELECT * FROM pg_extension;

Should list:

  • plpgsql
  • postgis
  • tablefunc

Schemas​

  • The public schema should not exist
  • A schema named after your user (e.g., lids_user) should exist and contain the tables

Key Tables​

  • sec_user – Should contain the default ADMINISTRATOR
  • lids_settings – Should exist
  • lids_geomtype – A custom type that should be present

Spatial Functions​

Custom spatial functions should be present, including:

  • ST_Contains_
  • ST_Covers_
  • ST_Equals_
  • ST_Intersects_
  • ST_RelateMatch_

6. Default Credentials​

The installation script creates a default administrator account:

  • ID: 0
  • Username: ADMINISTRATOR
  • Initial Role: LIDS 7 Administrators
  • Security Status: Enabled

The account name and password can be changed to any value after installation.

warning

The user with ID=0 is required for certain internal system operations and must never be deleted.

caution

Changing the default password is strongly recommended. Do so immediately via the Security Manager after the first login.

7. Troubleshooting​

  • "Extension not found" error Ensure postgresql-XX-postgis-X packages are installed on your OS.

  • Path errors If psql cannot find files included via \i, ensure you are running the command from the /installation_root/ directory where create-lids-user.sql is located.

  • Permission denied

    • Phase 1 must be run by a Superuser
    • Phase 2 should be run by the new Owner to ensure table ownership is correct