Deploying pgVis Server

The pgVis visualisations server is packaged and runable as a container, you can easily use Docker or Podman to run pgvis-server .

The only configuration the server needs is the know where your PostgreSQL database is, these are provided by the following environment variables:

  • DB_HOST - the PostgreSQL server hostname or IP address (required)
  • DB_PORT - the PostgreSQL server port if different from 5432 (optional)
  • DB_DATABASE - the databses within the PostgreSQL server (required)

To start the pgVis visualisations server with Docker: 

# Pull the latest image
docker pull registry.gitlab.com/pgvis/pgvis-server:latest
# Create the pgVis server container
docker create --name pgvis-server -p 80:8080 --env DB_HOST=172.30.4.209 --env DB_PORT=5414 --env DB_DATABASE=vis registry.gitlab.com/pgvis/pgvis-server:latest
# Start the pgVis server container
docker start pgvis-server

The pgVis server container is intended to sit in front of a TLS terminating loadbalancer and will use the standard x-forwarded-proto header.

Authentication

The visualisations server uses PostgreSQL for authentication, so simply login with your PostgreSQL username and password, make sure pg_hba is configured to allow TCP connections to your database.

Since stored visualisations are just PostgreSQL stored functions, access control to visualisation is managed with normal PostgreSQL access controls. Simply ensure that your roles provide execute permissions on the stored visualisation functions.

Periodically Refreshing Dashboards

When running visualisations via pgvis-server to use as monitoring dashboards, you often want to periodically refresh the page. This can be done by adding the option vis.option('refresh', 30) to the visualisation to specify the number of seconds to wait before refreshing.

For example: 

CREATE OR REPLACE FUNCTION public.ssd_performance_dashboard()
RETURNS vis.t_definition LANGUAGE SQL AS $$
WITH write_4k AS (
    SELECT device, buffered_bw, direct_bw, buffered_iops, direct_iops, count
    FROM
    (
        VALUES
        ('Crucial_CT750MX300SSD1', 553260, 183169, 138315, 45792, 1),
        ('CT1000P5PSSD8', 2057752, 184133, 514438, 46033, 1),
        ('CT250MX500SSD4', 584018, 58884, 146004, 14721, 1),
        ('INTEL SSDSC2BB016T6', 524797, 165071, 131199, 41268, 1),
        ('INTEL SSDSC2BB800G4', 527433, 178580, 131858, 44645, 1),
        ('INTEL SSDSC2KB480G7', 408940, 184056, 102235, 46014, 1),
        ('INTEL SSDSC2KB480G8', 516621, 184821, 129155, 46205, 2),
        ('Micron_5100_MTFDDAV960TCB', 553489, 261468, 138372, 65367, 2),
        ('SAMSUNG MZ7KM480HMHQ-00005', 556298, 189078, 139075, 47270, 1),
        ('SAMSUNG MZ7LM960HMJP-00005', 550248, 186309, 137562, 46577, 1),
        ('Samsung SSD 980 PRO 1TB', 2046890, 23510, 511723, 5877, 1)
    ) data(device, buffered_bw, direct_bw, buffered_iops, direct_iops, count)
)
SELECT vis.visualise('SSD Performance',
    vis.bar_chart('write-bw', 6, 'SSD Write Throughput (KiB/s)',
        vis.dataset( (SELECT jsonb_agg(q.*) FROM write_4k q) ),
        vis.series('Buffered IO Throughput', 'device', 'buffered_bw'),
        vis.series('Direct IO Throughput', 'device', 'direct_bw')
    ),
    vis.bar_chart('write-iops', 6, 'SSD Write IOPs',
        vis.dataset( (SELECT jsonb_agg(q.*) FROM write_4k q) ),
        vis.series('Buffered IO IOPs', 'device', 'buffered_iops'),
        vis.series('Direct IO IOPs', 'device', 'direct_iops')
    ),
    vis.option('refresh', 30)
);
$$;