Introduction

PostgreSQL is a widely used SQL database engine for various applications. This guide will give you the required steps to get PostgreSQL running on RouterOS. This guide will also give you the required steps to setup Pgadmin (a web interface to manage your PostgreSQL databases).

Configuration

To setup a Postgres Container on your RouterOS device, follow these steps bellow.

Make sure you have created a Container network before proceeding.

  1. Create Postgres Container mount points:
    /container/mounts/add name=MOUNT_POSTGRES src=disk1/volumes/postgres/data dst=/var/lib/postgresql/data
  2. Add environment variables:
    /container/envs/add name=ENV_POSTGRES key=POSTGRES_DB value="myapp"
/container/envs/add name=ENV_POSTGRES key=POSTGRES_PASSWORD value="<changeme>"
/container/envs/add name=ENV_POSTGRES key=POSTGRES_USER value="myapp"
/container/envs/add name=ENV_POSTGRES key=PGDATA value="/var/lib/postgresql/data/pgdata"
/container/envs/add name=ENV_POSTGRES key=POSTGRES_INITDB_ARGS value="--encoding='UTF8' --lc-collate='C' --lc-ctype='C'"
  3. Create a Postgres Container:
    /container/add remote-image=postgres:15 interface=veth1 root-dir=disk1/images/postgres mounts=MOUNT_POSTGRES envlist=ENV_POSTGRES name=postgres start-on-boot=yes logging=yes

    You can specify a different version for Postgres by changing the postgres:15 value.

  4. Start the Postgres Container:
    /container/start [find where name=postgres]

Advanced: Postgres with Pgadmin

This example shows how to configure Pgadmin on RouterOS:

  1. Create mount points for Pgadmin Container:
    /container/mounts/add name=MOUNT_PGADMIN_CONFIG src=disk1/volumes/pgadmin/config dst=/config
/container/mounts/add name=MOUNT_PGADMIN_DATA src=disk1/volumes/pgadmin/data dst=/var/lib/pgadmin C
  2. Create environment variables for Pgadmin Container:
    /container/envs/add name=ENV_PGADMIN key=PGADMIN_LISTEN_PORT value=80
/container/envs/add name=ENV_PGADMIN key=PGADMIN_DEFAULT_EMAIL value="sysadmin@domain.com"
/container/envs/add name=ENV_PGADMIN key=PGADMIN_DEFAULT_PASSWORD value="<changeme>"
/container/envs/add name=ENV_PGADMIN key=PGADMIN_SERVER_JSON_FILE value="/config/servers.json"
/container/envs/add name=ENV_PGADMIN key=PGADMIN_PREFERENCES_JSON_FILE value="/config/preferences.json"
/container/envs/add name=ENV_PGADMIN key=PGPASS_FILE value="/config/pgpass"
/container/envs/add name=ENV_PGADMIN key=PGADMIN_DISABLE_POSTFIX value="True"
  3. Create the Pgadmin Container:
    //container/add remote-image=dpage/pgadmin4 envlist=ENV_PGADMIN mounts=MOUNT_PGADMIN_CONFIG,MOUNT_PGADMIN_DATA interface=veth1 logging=yes name=pgadmin root-dir=disk1/images/pgadmin start-on-boot=yes user=0:0
  4. Disable Webfig:
    /ip service
set www disabled=yes

    You can also change the PGADMIN_LISTEN_PORT value to other than 80 and skip disabling Webfig

  5. Start Pgadmin Container:
    /container start [find where name=pgadmin]



  • No labels