PostgreSQL Database Setup

ServiceEnterprise

Availability2024.1

When to choose PostgreSQL

Prepare PostgreSQL

Server prerequisites before install

Create the GermainUX Databases

Separate storage and privileges

PostgreSQL Maintenance

Keep the database healthy

Overview

GermainUX supports PostgreSQL as an on-premise relational datastore. PostgreSQL may be appropriate when:

Your organization already operates PostgreSQL.
Database administrators have established PostgreSQL security, backup, and maintenance procedures.
An open-source relational datastore is preferred.
The expected GermainUX ingestion and analytics workload has been validated against PostgreSQL.

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

PostgreSQL Benefits

Capability	Benefit
Reliability	Provides ACID transactions, write-ahead logging, and mature recovery capabilities
Performance	Supports query optimization, parallel execution, and multiple indexing strategies
Scalability	Supports large datasets, connection pooling, partitioning, and replication
Security	Provides role-based access, TLS, authentication controls, and auditing options
High availability	Supports streaming replication and multiple failover architectures
Extensibility	Provides a broad ecosystem of tools, drivers, extensions, and managed services
SQL support	Supports advanced SQL, window functions, recursive queries, and complex analytics

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

Before You Begin

Confirm with Germain Software:

The PostgreSQL version supported by your GermainUX release
Required PostgreSQL JDBC driver
Required encoding and collation
Schema scripts supplied with the release
Expected daily AA Unit volume
Raw and aggregated data-retention periods

Show all 10

Session Replay volume
Partitioning and maintenance requirements
High-availability requirements
Migration or upgrade requirements

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

Requirements

Prepare:

A supported 64-bit operating system
A supported PostgreSQL version
PostgreSQL administrative access
A supported PostgreSQL JDBC driver
Network connectivity from GermainUX Enterprise
Sufficient CPU, memory, SSD storage, and IOPS

Show all 10

TLS certificates
Backup and recovery procedures
The GermainUX Service distribution package
Installation scripts supplied for the GermainUX release

See:

Hardware Requirements
Network Requirements
Storage Requirements

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:

Download the approved PostgreSQL installer

Run it with administrator privileges

Select the PostgreSQL Server components

Configure the data directory

Set a strong administrative password

Configure the listener port

Complete the installation

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:

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:

Listening addresses
Approved client networks
Authentication method
TLS
Connection limits
Logging
Time zone

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:

Server certificate
Private key permissions
Certificate chain
Hostname
Expiration date
Client trust
Approved TLS versions

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:

Database	Purpose
`apm_config`	GermainUX configuration
`apm_datamart`	Monitoring, analytical, and aggregated data

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:

Initial database and role creation
Configuration-schema creation
Datamart-schema creation
Index creation
Upgrade scripts
Retention or maintenance configuration

4. Replace Example Credentials

Before running the setup scripts:

Replace all example passwords.
Use separate credentials for configuration and datamart access.
Review database ownership.
Review granted permissions.
Confirm tablespace or storage locations.
Remove unnecessary superuser privileges.

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:

Run the initial setup using a PostgreSQL administrative account

Create the configuration database and account

Create the datamart database and account

Run configuration scripts against apm_config

Run datamart scripts against apm_datamart

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:

PostgreSQL hostname
PostgreSQL port
apm_config database name
apm_datamart database name
Dedicated usernames
Securely stored passwords

Show all 10

TLS settings
Connection-pool settings
Connection and query timeouts
Supported PostgreSQL JDBC driver

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:

Connect to PostgreSQL using the configuration account

Connect using the datamart account

Confirm that both databases exist

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

Verify that GermainUX Enterprise can connect

Start GermainUX Enterprise

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

Save and retrieve GermainUX configuration

Ingest a limited monitoring workload

Confirm that facts are stored in apm_datamart

Open representative dashboards and analyses

Validate retention and cleanup

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:

Inserts
Updates
Expired-data deletion
Partition maintenance
Index growth

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:

Confirm that new partitions are created as required.
Confirm that expired partitions are removed.
Validate partition boundaries before deletion.
Monitor tablespace and filesystem utilization.
Align partition retention with GermainUX retention policies.

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

Connection Management

Monitor:

Active connections
Idle connections
Connection-pool utilization
Maximum connections
Long-running queries
Locks and blocked sessions

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

Performance

Monitor:

CPU and memory
Disk capacity and latency
WAL generation
Checkpoint frequency and duration
Cache-hit ratio
Query latency

Show all 11

Sequential scans
Index usage
Replication lag
Vacuum progress
Backup duration

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:

Full backups
WAL archiving when point-in-time recovery is required
Off-host or protected backup storage
Encryption
Backup retention
Replication where required
Automated backup monitoring
Periodic restore testing

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

High Availability

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

Streaming replication
Automated or controlled failover
Connection redirection
Replication monitoring
Split-brain prevention
Documented recovery procedures

Test GermainUX connectivity during a planned failover before production use.

Production Checklist

The PostgreSQL version is supported by GermainUX.
The approved JDBC driver is installed.
TLS is enabled.
Network access is restricted.
Dedicated GermainUX accounts are configured.
Example passwords have been replaced.

Show all 15

apm_config has been initialized.
apm_datamart has been initialized.
All scripts ran in the documented sequence.
GermainUX Enterprise connects successfully.
Backups are configured.
A restore has been tested.
Autovacuum and retention are operating correctly.
Capacity and performance monitoring are enabled.
High availability has been tested when required.

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

Get Help

Contact GermainUX Support

The GermainUX team helps you get up and running faster, empower your team, turn data into actionable insights, and optimize performance to deliver measurable business outcomes.

Can't find what you need?

Search all GermainUX docs with Ctrl + K, or use the feedback buttons at the bottom of this page to tell us what is missing.

The content of this document is proprietary to GERMAIN SOFTWARE.