Skip to main content

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​

  1. On Red Hat Enterprise Linux 8 and later, disable the built-in PostgreSQL module:

    sudo dnf -qy module disable postgresql

  2. Install PostgreSQL 12

    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 postgresql12

    yum install postgresql12-contrib

  3. 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-12

  4. Initialise the database

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

  5. TimeScale tune to set optimal postgresql.conf parameters.

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

  6. Enable and start postgres

    sudo systemctl enable postgresql-12
    sudo systemctl start postgresql-12

  7. Edit PostgreSQL config:

    1. Edit /var/lib/pgsql/12/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

    2. Edit /var/lib/pgsql/12/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

  8. Restart PostgreSQL

    systemctl restart postgresql-12

  9. 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:

  1. scripts/dbmarlin/psql.sh
  2. scripts/dbmarlin/psql-admin.sh
  3. configure.sh
  4. start.sh
  5. stop.sh
  6. status.sh

Installation steps​

  1. Create dbmarlin Linux user and extract the tar.gz file as normal.

  2. 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

  3. 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

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