To set up AssetExplorer with external PostgreSQL database, configure an external PostgreSQL server and connect it to AssetExplorer.

Please use Linux for the external PostgreSQL server (recommended).

For more information on PostgreSQL installation, click here.

Supported Versions

PostgreSQL Database System Requirements

Set Up PostgreSQL Server

Step 1: Start the external PostgreSQL server and connect to the PostgreSQL console.

Step 2: Create a user and log in as the user.

a. CREATE USER <username> WITH CREATEDB LOGIN REPLICATION ENCRYPTED PASSWORD '<password>';

Example :   CREATE USER sdpadmin WITH CREATEDB LOGIN REPLICATION ENCRYPTED PASSWORD 'password';

b. Log out: \q

c. Log in again as a new user:  psql -h <host> -p <port> -d postgres -U <newly created user>

d. List all roles and users: \du

Step 3: Create a database.

a. CREATE DATABASE <new_db_name> WITH OWNER = <your_owner_name> ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;

b. Navigate to the new database:  \c <database_name>

c.  List all databases: \l

Step 4: Create Extensions

a. Execute the following commands to create an extension.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE EXTENSION IF NOT EXISTS citext;

b. Verify extensions: \dx

Step 5: Configure the IP address.

If the application and the PostgreSQL server are hosted on different machines, configure the address of the machine where the application is hosted.

* Edit pg_hba.conf. and add a host row to enable connections.

host    all    all    XX.XX.XX.XX/XX    md5

Warning: Avoid using '0.0.0.0/0' as it allows access from all machines.

Step 6: Edit the postgresql.conf file and add the following command at the end of the file:

listen_addresses = '<IP address(es)>'

Warning: Using * can pose a security risk as it will listen to all IP interfaces.

Step 7: Restart the PostgreSQL server.

Connect PostgreSQL Server with  AssetExplorer

1. Go to the <prod_home>/bin directory and run the following command:

    * Linux: changeDBServer.sh

    * Windows: changeDBServer.bat

2. Configure the host, database, port, username, and password.

3. Go to <prod_home>/conf/ directory and modify customer-config.xml:

    * Set StartDBServer to "false".

4. Start the application.

Steps to Improve Performance of PostgreSQL Server

1. Go to the postgresql.conf file.

2. Set the value of properties as suggested:

shared_buffers = 2GB

effective_cache_size = 6GB

maintenance_work_mem = 256MB

work_mem = 48MB

wal_buffers = 16MB