Install DBmarlin with remote PostgreSQL

info

💡 Requires DBmarlin 4.11.0 or above.

By default DBmarlin will create a local PostgreSQL database when you install it. This is convenient in many cases since you only need to provide a single machine to run DBmarlin and everything is self-contained on that one machine.

However, for larger installations where you are monitoring many database instances, you might want to split the load across two machines where DBmarlin application runs on one server and the DBmarlin database runs on another. It is also possible for those requiring a HA (High Availability) setup for DBmarlin that you could use a PostgreSQL cluster with two or more nodes.

Supported platforms

The DBmarlin application must be on a Linux server.

The remote PostgreSQL could run any platform. The instructions below are for Red Hat Enterprise Linux 8 or later but could be adapted if you wish to run PostgreSQL on another platform.

Setup PostgreSQL server

On Red Hat Enterprise Linux 8 and later, disable the built-in PostgreSQL module:
```
sudo dnf -qy module disable postgresql
```

Install PostgreSQL 15

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install postgresql15

yum install postgresql15-contrib

Install TimeScale 2.x

sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL

yum install timescaledb-2-postgresql-15

Initialise the database

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

TimeScale tune to set optimal postgresql.conf parameters.

sudo timescaledb-tune --pg-config=/usr/pgsql-15/bin/pg_config

Enable and start postgres

sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Edit PostgreSQL config:

Edit /var/lib/pgsql/15/data/postgresql.conf

# Change to listen on all network interfaces
listen_addresses = '*'

# Make sure timescaledb and pg_stat_statements are preloaded
shared_preload_libraries = 'timescaledb,pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

# Important for the performance of DBmarlin
synchronous_commit = off
max_wal_size = 4GB (or the value set by timescale-tune.sh if higher)
max_worker_processes = 2 * CPU_cores
max_parallel_workers_per_gather = 4
autovacuum_nap_time = 15s
max_locks_per_transaction = 512
max_connections = 100 (or the value set by timescale-tune.sh if higher)

Edit /var/lib/pgsql/15/data/pg_hba.conf to allow connections from the DBmarlin Server. You can limit it to a specific IP or a subnet range.
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             240.0.0.0/8         md5
```

Restart PostgreSQL
```
systemctl restart postgresql-15
```

Connect to PostgreSQL locally and set password for postgres user, and create the dbmarlin user

su - postgres
psql

# Inside psql
\password postgres

create user dbmarlin WITH PASSWORD 'securepassword';
alter role dbmarlin with superuser;

On the DBmarlin Server

The steps are the same as a normal Linux installation with these additions.

What determines whether to use a remote PostgreSQL?

In order to use the remote PostgreSQL server you need to setup a .pg_service file in the root of the DBmarlin installation directory (E.g. /opt/dbmarlin). The existence of this file is used to indicate that you wish to uses these connection details rather than create a local PostgreSQL on the DBmarlin Server. It will be used by:

scripts/dbmarlin/psql.sh
scripts/dbmarlin/psql-admin.sh
configure.sh
start.sh
stop.sh
status.sh

Installation steps

Create dbmarlin Linux user and extract the tar.gz file as normal.
Create the .pg_service.conf file in same directory where DBmarlin is installed (E.g. /opt/dbmarlin) . The file should be read-write only to the dbmarlin user (E.g. chmod 600 .pg_service.conf) There are 2 sections (dbmarlin and admin). The admin connection details will normally point to an admin user like the postgres user which is used to create the dbmarlin user and after that everything else is done as the dbmarlin postgresql user.
```
[dbmarlin]
host=dbmarlin-postgres-remote
port=5432
dbname=dbmarlin
user=dbmarlin
password=securepassword

[admin]
host=dbmarlin-postgres-remote
port=5432
dbname=postgres
user=postgres
password=securepassword
```

Test connectivity using the scripts provided

cd scripts/dbadmin
./psql.sh

# You should be connected to postgres. You can quit out with \q
\q

The above does not connect becuase the default database (dbmarlin) has not   been created yet!

# You should be connected to postgres. You can quit out with \q
./psql-admin.sh

Continue the remaining steps to configure the installation by running configure.sh.

Supported platforms​

Setup PostgreSQL server​

On the DBmarlin Server​

What determines whether to use a remote PostgreSQL?​

Installation steps​

Supported platforms

Setup PostgreSQL server

On the DBmarlin Server

What determines whether to use a remote PostgreSQL?

Installation steps