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: 11.17, 15.2, 15.8, 15.10, 15.14.
PostgreSQL Database System Requirements
Processor | RAM | Hard Disk |
2.4GHz to 3.0 GHz 30MB Cache 16 cores | 32GB | 1TB |
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