PostgreSQL Database Setup

⚙️ PostgreSQL Database Setup

📖 Overview

GermainUX supports PostgreSQL as an on-premise relational datastore.

PostgreSQL may be appropriate when:

Elasticsearch remains the preferred datastore for most new, high-volume GermainUX deployments. Review the Datastore Recommendation and Benchmark before selecting PostgreSQL.

✨ PostgreSQL Benefits

The selected architecture must still be validated against the expected GermainUX ingestion, retention, and query workload.

Before You Begin

Confirm with Germain Software:

Do not independently upgrade PostgreSQL without confirming compatibility with the installed GermainUX release.

📋 Requirements

Prepare:

See:

📦 Install PostgreSQL

Use your organization’s approved PostgreSQL distribution and installation process.

🐧 Debian or Ubuntu

Install PostgreSQL through the approved operating-system or PostgreSQL repository:

sudo apt update
sudo apt install postgresql

Confirm that the service is running:

sudo systemctl status postgresql

🔴 Red Hat Enterprise Linux or Compatible Distribution

Install the approved PostgreSQL packages for the operating-system version, initialize the database cluster when required, and start the service.

Example:

sudo dnf install postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Commands and package names vary by PostgreSQL and operating-system version.

🪟 Windows

Install steps:

1. Download the approved PostgreSQL installer.

2. Run it with administrator privileges.

3. Select the PostgreSQL Server components.

4. Configure the data directory.

5. Set a strong administrative password.

6. Configure the listener port.

7. Complete the installation.

8. Confirm that the PostgreSQL Windows service is running.

Install pgAdmin only when a graphical administration tool is required.

🔧 Prepare PostgreSQL

🛰️ Configure Network Access

The default PostgreSQL port is:

5432

Use the actual port configured in your environment.

Update PostgreSQL networking so that only approved GermainUX Enterprise hosts can connect.

Review:

postgresql.conf
pg_hba.conf

Configure:

Do not configure unrestricted access such as allowing every address without an appropriate authentication and firewall policy.

🔒 Configure TLS

Enable TLS for connections between GermainUX Enterprise and PostgreSQL.

Validate:

Require encrypted connections in pg_hba.conf when mandated by your security standards.

🔑 Configure Authentication

Use a secure password-authentication method supported by the selected PostgreSQL and JDBC versions.

Create dedicated GermainUX accounts rather than using the PostgreSQL superuser.

🗄️ Create the GermainUX Databases

GermainUX requires separate storage for:

The exact database names, roles, scripts, and privileges are defined by the installation package for your GermainUX release.

1️⃣ Extract the Service Distribution

Extract the GermainUX Service package to a directory referenced below as:

$SERVICE_HOME

2️⃣ Review the Database Instructions

Open:

$SERVICE_HOME/install/databases/readme.txt

Follow the PostgreSQL instructions and script order supplied with the release.

🔍 3. Locate the PostgreSQL Scripts

Locate the PostgreSQL scripts under the database-installation directory included in the GermainUX Service package.

The package may contain:

✏️ 4. Replace Example Credentials

Before running the setup scripts:

Do not retain default or example passwords in production.

🔁 5. Run the Scripts in Sequence

Run the scripts in the exact sequence specified by the release’s readme.txt.

Typically:

1. Run the initial setup using a PostgreSQL administrative account.

2. Create the configuration database and account.

3. Create the datamart database and account.

4. Run configuration scripts against apm_config.

5. Run datamart scripts against apm_datamart.

6. Create required indexes, functions, and maintenance objects.

Do not skip scripts or change their order unless instructed by Germain Software.

⚙️ Configure GermainUX Enterprise

Configure GermainUX Enterprise with:

Place the JDBC driver in the location specified by the GermainUX Enterprise installation guide.

Avoid storing database passwords directly in source-controlled property files.

✅ Validate the Setup

Before starting production monitoring:

1. Connect to PostgreSQL using the configuration account.

2. Connect using the datamart account.

3. Confirm that both databases exist.

4. Confirm that all required schemas, tables, indexes, sequences, functions, and views were created.

5. Verify that GermainUX Enterprise can connect.

6. Start GermainUX Enterprise.

7. Review logs for JDBC, schema, permission, or TLS errors.

8. Save and retrieve GermainUX configuration.

9. Ingest a limited monitoring workload.

10. Confirm that facts are stored in apm_datamart.

11. Open representative dashboards and analyses.

12. Validate retention and cleanup.

13. Measure ingestion and query performance.

🧰 PostgreSQL Maintenance

🔂 Autovacuum and Analyze

GermainUX can produce a continuous write workload. Confirm that PostgreSQL autovacuum and automatic statistics collection keep pace with:

Monitor for table or index bloat and long-running transactions that prevent cleanup.

Do not disable autovacuum globally.

📁 Partitioning and Retention

Where the GermainUX schema uses partitions:

Partition removal is destructive. Verify the target and backup before deleting data.

🔗 Connection Management

Monitor:

Use an approved connection-pooling architecture when required by scale.

📈 Performance

Monitor:

Tune PostgreSQL based on measured workload rather than generic values.

💾 Backup and Recovery

Back up both:

apm_config
apm_datamart

The backup strategy should include:

A backup is not considered reliable until restoration has been tested.

🔁 High Availability

When required, use an approved PostgreSQL high-availability architecture that provides:

Test GermainUX connectivity during a planned failover before production use.

✔️ Production Checklist

For supported PostgreSQL versions, database scripts, migration assistance, or production sizing, contact Germain team.