Post database installation action items

Overview

When your PostgreSQL instance has been installed, various changes must be actioned before you start using the instance. These are listed below and apply to both Microsoft Windows and Linux installations.

Default database users/roles and passwords

By default, PostgreSQL includes a system account user named postgres with the role postgres. CAST recommends creating the following additional users:

To create the CAST default users, use the following commands:

psql
*create user operator with SUPERUSER password 'CastAIP';
*create user guest with password 'WelcomeToAIP';
*grant postgres to operator;

You are also free to create your own users and then use them with CAST Imaging if you prefer — you need at minimum one user with the SUPERUSER permission, with the postgres role granted to that user. For example:

psql
*create user my_user with SUPERUSER password 'my_password';
*grant postgres to my_user;

If you prefer to create custom users that do NOT require the SUPERUSER permission, this is also possible. For example, this script creates a role my_role with the password my_password that can login, with only the minimum permissions required to operate CAST Imaging:

psql
*create role my_role LOGIN PASSWORD 'my_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
*grant create, connect, temporary on database postgres to my_role;

Configure network access via pg_hba.conf

Out of the box PostgreSQL instances are configured to allow incoming connections on IPv4 from anywhere via the following line in the pg_hba.conf file:

host   all   all   0.0.0.0/0   scram-sha-256

Therefore you may wish to secure your instance using the pg_hba.conf file to restrict access. There are many ways to do this which will entirely depend on your own environment and organization — for example you may wish to allow access only from certain IP addresses/hosts, or specific PostgreSQL users. The pg_hba.conf file is explained in more detail here: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html . Any changes to this file require that the instance is restarted before they are taken into account.

The pg_hba.conf can be found in the following locations:

Windows:
%PROGRAMFILES%\PostgreSQL\<version>\data\pg_hba.conf

Linux:
/etc/postgresql/<version>/main/pg_hba.conf

Restart the PostgreSQL instance to ensure your changes are applied.

Configure the instance via postgresql.conf

The postgresql.conf file controls how your PostgreSQL instance operates. Proper configuration is essential for optimal performance. You must manually configure the parameters listed below for both Windows and Linux installations.

Restart the PostgreSQL instance to ensure your changes are applied.

Locating postgresql.conf

Windows:
%PROGRAMFILES%\PostgreSQL\<version>\data\postgresql.conf

Linux (Ubuntu/Debian):
/etc/postgresql/<version>/main/postgresql.conf

Linux (Docker images):
/var/lib/postgresql/data/postgresql.conf

Configuration parameters

The values below are optimized for an instance with 8GB RAM. If you have more RAM, see Using PGTune.

Using PGTune

If your PostgreSQL host has more than 8GB RAM, use PGTune  to optimize these parameters:

PGTune settings

• DB version: Your installed PostgreSQL version
• OS Type: Linux or Windows (as applicable)
• DB Type: Mixed type of application
• Total Memory: Your available RAM
• Number of connections: 300
• Data Storage: SSD or HDD (as applicable)

Parameters to recalculate with PGTune:

• checkpoint_completion_target
• default_statistics_target
• effective_cache_size
• effective_io_concurrency
• huge_pages
• maintenance_work_mem
• max_connections
• max_wal_size
• min_wal_size
• random_page_cost
• shared_buffers
• wal_buffers
• work_mem

Starting and stopping your instance

PostgreSQL on Microsoft Windows

A Windows Service called postgresql-x64-<version> is automatically created as part of the installation process and set to start automatically when the host is rebooted. You can manage it via the Windows Services console (services.msc) or from the command line:

net stop postgresql-x64-<version>   --> TO MANUALLY SHUTDOWN
net start postgresql-x64-<version>  --> TO MANUALLY START

PostgreSQL on Linux

If you have used the official PostgreSQL repository for your distribution’s package manager system to install the instance, a SystemD service entry will be created to ensure that the service starts automatically when the host is rebooted.

You can also use this to start/stop/restart etc. your instance:

systemctl stop postgresql-<version>.service --> TO MANUALLY SHUTDOWN
systemctl start postgresql-<version>.service --> TO MANUALLY START
systemctl restart postgresql-<version>.service --> TO MANUALLY RESTART
systemctl status postgresql-<version>.service --> TO CHECK STATUS
systemctl reload postgresql-<version>.service --> TO RELOAD CONFIGURATION

Define your database instance in CAST Imaging

During a from scratch installation

When installing CAST Imaging from scratch:

• on Microsoft Windows you will need to define your database instance as part of the installation process.
• on Linux via Docker, a database instance is provided automatically as an image.

As an additional database

If you need to declare the database instance as an additional resource to help load balance your analyses, use the CSS and Measurement Settings in the CAST Imaging admin UI.