Database Configuration

Introduction

The ESP application doesn’t need any permissions outside of its own PostgreSQL database and it is recommended to create a single database for the application, as well as a dedicated role with matching name, and grant all permissions for that role to that database.

Currently, there isn’t a role separation between “read-only”, “read/write”, “read/write/DDL modification permissions” users at the application level. The same user is used with the l7-init (DDL migrations) as in normal application execution. These credentials are stored in the database config file /opt/l7esp/data/conf/database.json.

Prerequisites 64-bit Linux VM with the following minimum specs:

• Debian or RHEL based OS

• 2vCPUs

• 8GB of RAM

• 30GB of available storage

• Timezone configured for UTC/GMT

• PostgreSQL version 10.x or 11.x

Cloud

If you are hosting L7|ESP in the cloud, see the following guides:

• Amazon Aurora PostgreSQL

• Azure Database for PostgreSQL

Instructions

ESP DB creation

Most installations of PostgreSQL done via a package manager include a user called “postgres”. This user has full super admin privileges to the PostgreSQL instance installed on your system. Using the su command switch to the postgres user account then use the PostgreSQL interactive terminal (psql) to create the database, user role, and assign the permissions.

postgres=# CREATE DATABASE <esp_db_name>;
postgres=# CREATE USER <esp_db_user> WITH ENCRYPTED PASSWORD '<esp_db_password>';
postgres=# GRANT ALL PRIVILEGES ON DATABASE <esp_db_name> TO <esp_db_user>;

ESP DB configuration

The database configuration file at /opt/l7esp/data/conf/database.json should take the following format when using an external hosted PostgreSQL solution such as AWS RDS:

{
    "host": "<postgres_server_url_or_ip>",
    "port": 5432,
    "user": "<esp_db_user>",
    "pass": "<esp_db_password>",
    "name": "<esp_db_name>",
    "start_service": false
}

Note

When using an external PGSQL server, it will either need the data migrated from the existing PGSQL server or the new PGSQL server will need to be initialized, to prepare it for use with ESP.

Preparing PostgreSQL for ESP

Once the ESP database configuration file has been configured to use an external DB and before starting ESP the PostgreSQL configuration file should be modified as follows for use with ESP. The location of the configuration file can be found in the PostgreSQL terminal by running the following query SHOW config_file;.

PostgreSQL settings

• max_connections: L7 recommends that this setting should be a minimum of 6x the total number of ESP processes listed when running the l7 status command, L7 routinely sets this value to 100 and for systems configured with more web workers (the “http” processes) sets this value to 200.

• shared_buffers: This should be set to ~25% of the available system RAM*, but not more than 8GB. (assumes you have a dedicated DB server OR enough RAM to handle the load of the DB + the app servers).

• temp_buffers: Not less than 64MB. L7 routinely sets this to 128MB on dedicated DB servers in production.

• work_mem: Not less than 256MB. L7 routinely sets this to 1GB on dedicated DB servers in production.

• maintenance_work_mem: Not less than 128MB. L7 routinely sets this to 256MB on dedicated DB servers in production.

• effective_cache_size: On a dedicated DB server - 75% of available RAM. On other servers, 2x the shared_buffer size.