Dalibo released on the 18th of October temBoard version 9.0.0. The new release comes with a lot of new features.
The breaking changes are:
- Replace instance groups and user groups by Environment. An instance must be in one and single environment
- temBoard agent v8 can’t register to temBoard UI v9
- Use temboard register to register a new temBoard v8 agent on a temBoard v9 UI. Or upgrade agent to v9. temBoard UI v9 can still manage agent v8
- Drop support for 7.x agents
- Drop python 2.7 support
- Drop rhel7 and buster support, minimum versions are now 6.0.2 for tornado and 1.3.2 for sqlalchemy
- Drop plugin hotplug. Just restart temBoard UI or agent
- Drop daemonization. Use nohup or systemd
Other changes:
- Postgres 17 support
- Visualize Plan with PEV2
- Packages for Ubuntu 24.04 Noble
- Fix deletion of host metrics when removing an instance of multi-instances host
- agent: Recover admin shutdown, backend terminated, etc
- Fix error handling in agent plugins
- A lot of modernization : moved to Vue3, Bootstrap 5, Flask and ruff
- A lot of UI tweaks & fixes
- Accept editing instance even when agent is down
- Improved postgresql.conf handling and error management
- temboard query-agent command now accepts –delete and –post to set HTTP verb
- Improve home dashboard performance
For more information take a look at the following link
https://temboard.readthedocs.io/en/latest/changelog/
You can compare changes between release 8.2.1 and 9.0 and see how many changes went into this release
https://github.com/dalibo/temboard/compare/v8.2.1…v9.0.0
However, in this blog post I would like to concentrate on the upgrade procedure to the version 9.0.
Before you start, read the upgrade docs carefully. You can find them here.
https://temboard.readthedocs.io/en/latest/server_upgrade/
From a high level view, the most important steps are:
- Download the rpm files for your Linux release (RHEL 9.x in may case)
- Backup your repository database
- Backup temboard.conf, temboard-agent.conf and other temboard related files
- Upgrade first the agents to at least 8.2.1 if not already done
- Upgrade temboard and the agent to 9.0
In this blog post, I will jump right into the temBoard upgrade process.
Check the installed version first. In case you got the development version installed, you can’t upgrade the package directly. You need to remove it first with the “rpm –noscripts” command. A development version, is the one with the <dev0> in the package name.
$ rpm -qa | grep temboard temboard-8.2.1.dev0-1.el9.noarch temboard-agent-8.2.1-1.el9.noarch
Stop the temBoard server and agent and start with a clean log file. An empty log file is very useful in case you need to debug the installation process.
$ sudo systemctl stop temboard-agent@PGMP01A.service $ sudo systemctl stop temboard.service $ sudo systemctl reset-failed $ sudo systemctl is-system-running running $ cd /var/log/temboard/ $ sudo mv temboard.log temboard.log.before_upgrade_to_9.0 $ cd /etc/temboard/; cp -p temboard.conf temboard.conf.20241021a
To delete the old temboard 8.2.1 dev package, use the following rpm command
$ rpm -qa | grep temboard temboard-8.2.1.dev0-1.el9.noarch $ sudo rpm --noscripts --erase temboard-8.2.1.dev0-1.el9.noarch $ rpm -qa | grep temboard temboard-agent-8.2.1-1.el9.noarch
After the dev package is uninstall successfully, you can use the dnf command to install the new package.
$ sudo dnf localinstall temboard-9.0.0-1.el9.noarch.rpm Updating Subscription Management repositories. Red Hat Satellite Client 6 for RHEL 9 x86_64 (RPMs) 27 kB/s | 2.3 kB 00:00 Red Hat CodeReady Linux Builder for RHEL 9 x86_64 (RPMs) 36 kB/s | 2.9 kB 00:00 Red Hat Enterprise Linux 9 for x86_64 - AppStream (RPMs) 33 kB/s | 2.9 kB 00:00 Red Hat Enterprise Linux 9 for x86_64 - BaseOS (RPMs) 30 kB/s | 2.6 kB 00:00 Red Hat Enterprise Linux 9 for x86_64 - Supplementary (RPMs) 22 kB/s | 2.0 kB 00:00 Dependencies resolved. ====================================================================================================================================================================================================== Package Architecture Version Repository Size ====================================================================================================================================================================================================== Installing: temboard noarch 9.0.0-1.el9 @commandline 1.3 M Transaction Summary ====================================================================================================================================================================================================== Install 1 Package Total size: 1.3 M Installed size: 4.1 M Is this ok [y/N]: y Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : temboard-9.0.0-1.el9.noarch 1/1 Running scriptlet: temboard-9.0.0-1.el9.noarch 1/1 Verifying : temboard-9.0.0-1.el9.noarch 1/1 Installed products updated. Last metadata expiration check: 0:00:01 ago on Mon 21 Oct 2024 08:50:38 AM CEST. Installed: temboard-9.0.0-1.el9.noarch Complete!
Now it is time to upgrade the temBoard agent.
$ sudo dnf localupdate temboard-agent-9.0.0-1.el9.noarch.rpm Updating Subscription Management repositories. Red Hat Satellite Client 6 for RHEL 9 x86_64 (RPMs) 39 kB/s | 2.3 kB 00:00 Red Hat CodeReady Linux Builder for RHEL 9 x86_64 (RPMs) 53 kB/s | 2.9 kB 00:00 Red Hat Enterprise Linux 9 for x86_64 - AppStream (RPMs) 49 kB/s | 2.9 kB 00:00 Red Hat Enterprise Linux 9 for x86_64 - BaseOS (RPMs) 43 kB/s | 2.6 kB 00:00 Red Hat Enterprise Linux 9 for x86_64 - Supplementary (RPMs) 34 kB/s | 2.0 kB 00:00 Dependencies resolved. ====================================================================================================================================================================================================== Package Architecture Version Repository Size ====================================================================================================================================================================================================== Upgrading: temboard-agent noarch 9.0.0-1.el9 @commandline 207 k Transaction Summary ====================================================================================================================================================================================================== Upgrade 1 Package Total size: 207 k Is this ok [y/N]: y Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: temboard-agent-9.0.0-1.el9.noarch 1/2 Upgrading : temboard-agent-9.0.0-1.el9.noarch 1/2 Running scriptlet: temboard-agent-9.0.0-1.el9.noarch 1/2 Running scriptlet: temboard-agent-8.2.1-1.el9.noarch 2/2 Cleanup : temboard-agent-8.2.1-1.el9.noarch 2/2 Running scriptlet: temboard-agent-8.2.1-1.el9.noarch 2/2 Verifying : temboard-agent-9.0.0-1.el9.noarch 1/2 Verifying : temboard-agent-8.2.1-1.el9.noarch 2/2 Installed products updated. Upgraded: temboard-agent-9.0.0-1.el9.noarch Complete!
A quite important step starts now. The upgrade of the repository database itself. This step has to be executed as the temboard OS user.
$ su - temboard Password: Last login: Mon Oct 21 03:13:16 CEST 2024 from 10.86.48.22 on pts/3 temboard@host01:/var/lib/temboard $
In case you are interested in the full command line options which are available, just use the –help option.
$ /usr/bin/temboard --help
INFO: app: Starting temboard 9.0.0.
usage: temboard [-h] [-c CONFIGFILE] [--verbose] [-V] COMMAND ...
temBoard UI 9.0.0.
COMMAND is optional. Default command is `serve`, the combined
service. See available commands below.
optional arguments:
-h, --help show this help message and exit
-c CONFIGFILE, --config CONFIGFILE
Configuration file
--verbose, --debug Enable verbose messages for temBoard.
-V, --version show version and exit
Available commands:
COMMAND Name of one sub-command described bellow.
apikey Manage API keys.
generate-key Generate signing key.
migratedb Manage temBoard own database.
query-agent Query a temBoard agent
register-instance Out-of-bound registration of a PostgreSQL instance.
routes List HTTP routes map.
serve Combined web server and background workers.
tasks Manage background tasks.
web Standalone web server.
$ temboard migratedb upgrade INFO: app: Starting temboard 9.0.0. INFO: app: Using config file /etc/temboard/temboard.conf.
… entries from the postgresql log file
2024-10-21 08:55:32.892 CEST [3443528] LOG: statement: BEGIN;
ALTER TABLE application.instances DROP COLUMN IF EXISTS agent_key;
COMMIT;
2024-10-21 08:55:32.904 CEST [3443528] LOG: statement: ------------------------------------------------
-- Migrating instance groups to environments. --
------------------------------------------------
CREATE TABLE application.environments (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
description TEXT,
color TEXT,
cdate TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
-- Migrate instance groups to environments.
INSERT INTO application.environments (name, description)
-- Create an environment per group combination.
-- An instance in two groups will create a single environment combining both.
SELECT DISTINCT
string_agg(DISTINCT ixg.group_name, '-' ORDER BY ixg.group_name) AS name,
string_agg(DISTINCT g.group_description, ' / ') AS description
FROM application.instances AS i
JOIN application.instance_groups AS ixg
ON i.agent_address = ixg.agent_address
AND i.agent_port = ixg.agent_port
AND ixg.group_kind = 'instance'
JOIN application.groups AS g
ON ixg.group_kind = g.group_kind
AND ixg.group_name = g.group_name
GROUP BY i.agent_address, i.agent_port
UNION
-- Create an environment per empty group.
SELECT DISTINCT g.group_name AS name, g.group_description AS description
FROM application.groups AS g
LEFT OUTER JOIN application.instance_groups
ON instance_groups.group_kind = g.group_kind
AND instance_groups.group_name = g.group_name
WHERE g.group_kind = 'instance'
AND instance_groups.agent_address IS NULL
UNION
SELECT DISTINCT
'temboard-noenv-fallback' AS name,
'Fallback environment for instances without group' AS description
FROM application.instances AS i
LEFT OUTER JOIN application.instance_groups AS ixg
ON i.agent_address = ixg.agent_address AND i.agent_port = ixg.agent_port
AND ixg.group_kind = 'instance'
WHERE ixg.agent_address IS NULL
ORDER BY 1
;
-- Associate instance to one single environment.
ALTER TABLE application.instances
ADD COLUMN environment_id INTEGER REFERENCES application.environments(id);
CREATE VIEW application.vinstances AS
SELECT
i.agent_address || ':' || i.agent_port AS agent,
hostname || ':' || pg_port AS postgres,
e.name AS environment_name,
array_agg(p.plugin_name ORDER BY p.plugin_name) FILTER (WHERE p.plugin_name IS NOT NULL) AS plugins
FROM application.instances AS i
JOIN application.environments AS e
ON i.environment_id = e.id
LEFT OUTER JOIN application.plugins AS p ON p.agent_address = i.agent_address AND p.agent_port = i.agent_port
GROUP BY 1, 2, 3;
-- Attach instance to corresponding environment.
WITH mapping AS (
SELECT DISTINCT
i.agent_address AS agent_address,
i.agent_port AS agent_port,
string_agg(DISTINCT ixg.group_name, '-' ORDER BY ixg.group_name) AS environment_name
FROM application.instances AS i
JOIN application.instance_groups AS ixg
ON i.agent_address = ixg.agent_address AND i.agent_port = ixg.agent_port
AND ixg.group_kind = 'instance'
JOIN application.groups AS g
ON ixg.group_kind = g.group_kind AND ixg.group_name = g.group_name
GROUP BY i.agent_address, i.agent_port
)
UPDATE application.instances AS i
SET environment_id = e.id
FROM mapping
JOIN application.environments AS e ON e.name = mapping.environment_name
WHERE i.agent_address = mapping.agent_address
AND i.agent_port = mapping.agent_port;
UPDATE application.instances
SET environment_id = (SELECT id FROM application.environments WHERE name = 'temboard-noenv-fallback')
WHERE environment_id IS NULL;
ALTER TABLE application.instances
ALTER COLUMN environment_id SET NOT NULL;
--------------------------------------------
-- Migrating role groups to environments. --
--------------------------------------------
ALTER TABLE application.groups RENAME TO legacy_groups;
-- New groups table without kind, and with an integer primary key.
CREATE TABLE application.groups (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
description TEXT,
cdate TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
ALTER TABLE application.environments
ADD COLUMN dba_group_id INTEGER REFERENCES application.groups(id) ON DELETE CASCADE;
-- Use a secondary table to link group to their role members.
-- Replaces application.role_groups.
CREATE TABLE application.memberships (
id SERIAL PRIMARY KEY,
role_name TEXT NOT NULL REFERENCES application.roles(role_name) ON DELETE CASCADE,
group_id INTEGER NOT NULL REFERENCES application.groups(id) ON DELETE CASCADE,
cdate TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
UNIQUE (role_name, group_id)
);
CREATE VIEW application.vroles AS
SELECT
r.role_name AS role,
r.is_active AS active,
r.is_admin AS admin,
array_agg(DISTINCT g.name ORDER BY g.name) FILTER (WHERE g.name IS NOT NULL) AS groups
FROM application.roles AS r
LEFT OUTER JOIN application.memberships AS m
ON r.role_name = m.role_name
LEFT OUTER JOIN application.groups AS g
ON m.group_id = g.id
GROUP BY 1, 2, 3
ORDER BY 1
;
-- Create a */dba group for each environment.
INSERT INTO application.groups (name, description)
SELECT DISTINCT
e.name || '/dba' AS name,
-- Profile description in /settings/environments/EEE/members
'DBA' AS description
FROM application.environments AS e
ORDER BY 1;
-- Assign the */dba group to the corresponding environment.
UPDATE application.environments AS e
SET dba_group_id = g.id
FROM application.groups AS g
WHERE g.name = e.name || '/dba';
ALTER TABLE application.environments
ALTER COLUMN dba_group_id SET NOT NULL;
-- Migrate membership from legacy role_groups.
WITH legacy AS (
-- List all role belonging to a DBA groups.
SELECT DISTINCT
rxg.role_name,
e.dba_group_id
-- instance_groups has no entry for empty environments.
-- We'll handle this later.
FROM application.instances AS i
JOIN application.environments AS e ON e.id = i.environment_id
JOIN application.instance_groups AS ixg ON ixg.agent_address = i.agent_address AND ixg.agent_port = i.agent_port
JOIN application.access_role_instance AS ari
ON ari.instance_group_name = ixg.group_name
AND ari.instance_group_kind = ixg.group_kind
JOIN application.role_groups AS rxg
ON rxg.group_name = ari.role_group_name
AND rxg.group_kind = ari.role_group_kind
)
INSERT INTO application.memberships (role_name, group_id)
SELECT role_name, dba_group_id FROM legacy
UNION
-- Create a membership for each empty environments.
SELECT DISTINCT
rxg.role_name,
e.dba_group_id
FROM application.legacy_groups AS ig
JOIN application.environments AS e
ON e.name = ig.group_name
AND ig.group_kind = 'instance'
JOIN application.access_role_instance AS ari
ON ari.instance_group_name = ig.group_name
AND ari.instance_group_kind = ig.group_kind
JOIN application.role_groups AS rxg
ON rxg.group_name = ari.role_group_name
AND rxg.group_kind = ari.role_group_kind
LEFT OUTER JOIN application.instance_groups AS ixg
ON ixg.group_name = ig.group_name
AND ixg.group_kind = ig.group_kind
WHERE ixg.group_name IS NULL AND ixg.group_kind IS NULL
ORDER BY 1, 2
;
DROP TABLE application.instance_groups;
DROP TABLE application.access_role_instance;
DROP TABLE application.role_groups;
DROP TABLE application.legacy_groups;
Now it’s time to add the Environment with the correct path to the lib directory to the systemd files.
$ sudo vi temboard.service $ cat temboard.service [Unit] Description=temBoard Server After=network.target [Service] Type=simple User=temboard Group=temboard Environment="SYSTEMD=1" Environment="LD_LIBRARY_PATH=/app/lib/postgres/pgproduct/pg-14.13/lib" ExecStart=/usr/bin/temboard -c /etc/temboard/temboard.conf serve ExecReload=/bin/kill -HUP $MAINPID [Install] WantedBy=multi-user.target [sourcecode language="plain"] $ cd /etc/systemd/system/multi-user.target.wants/ $ sudo vi temboard-agent@PGMP01A.service postgres@host01:/etc/systemd/system/multi-user.target.wants/ [PGMP01A] cat temboard-agent@PGMP01A.service [Unit] Description=PostgreSQL Remote Control Agent %I After=network.target postgresql@%i.service AssertPathExists=/etc/temboard-agent/%I/temboard-agent.conf [Service] Type=simple User=postgres Group=postgres Environment="SYSTEMD=1" Environment="LD_LIBRARY_PATH=/app/lib/postgres/pgproduct/pg-14.13/lib" ExecStart=/usr/bin/temboard-agent -c /etc/temboard-agent/%I/temboard-agent.conf serve # Increase OOM Score to ensure agent is killed before Postgres. OOMScoreAdjust=15 [Install] WantedBy=multi-user.target $ sudo systemctl reset-failed $ sudo systemctl daemon-reload $
Now we can do some checks if the temBoard server and agent are installed correctly, and afterwards we can start the server and the agent.
$ rpm -qa | grep temboard temboard-9.0.0-1.el9.noarch temboard-agent-9.0.0-1.el9.noarch
$ sudo systemctl start temboard.service $ sudo systemctl start temboard-agent@PGMP01A.service
$ sudo systemctl status temboard.service
● temboard.service - temBoard Server
Loaded: loaded (/etc/systemd/system/temboard.service; disabled; preset: disabled)
Drop-In: /etc/systemd/system/temboard.service.d
└─override.conf
Active: active (running) since Mon 2024-10-21 09:00:57 CEST; 16s ago
Main PID: 3446580 (temboard)
Tasks: 30 (limit: 306229)
Memory: 642.5M
CPU: 7.033s
CGroup: /system.slice/temboard.service
├─3446580 "temboard: web"
├─3446584 "temboard: scheduler"
├─3446585 "temboard: worker pool"
├─3446592 "temboard: task temboardui.plugins.monitoring.aggregate_data_worker"
├─3446593 "temboard: task temboardui.plugins.monitoring.history_tables_worker"
├─3446595 "temboard: task temboardui.plugins.monitoring.purge_data_worker"
├─3446599 "temboard: task temboardui.plugins.statements.pull_data_worker"
├─3446600 "temboard: task temboardui.plugins.statements.statements_purge_worker"
├─3446620 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446621 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446624 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446625 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446626 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446630 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446631 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446634 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446635 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446636 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446637 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446639 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446640 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446642 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446647 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446649 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446650 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446652 "temboard: task temboardui.plugins.monitoring.collector_batch"
├─3446653 "temboard: task temboardui.plugins.monitoring.collector_batch"
└─3446655 "temboard: task temboardui.plugins.monitoring.collector_batch"
Oct 21 09:00:57 host01 systemd[1]: Started temBoard Server.
Oct 21 09:00:58 host01 temboard[3446580]: INFO: app: Starting temboard 9.0.0.
Oct 21 09:00:58 host01 temboard[3446580]: INFO: app: Using config file /etc/temboard/temboard.conf.
$ sudo systemctl status temboard-agent@PGMP01A.service
● temboard-agent@PGMP01A.service - PostgreSQL Remote Control Agent PGMP01A
Loaded: loaded (/usr/lib/systemd/system/temboard-agent@.service; enabled; preset: disabled)
Active: active (running) since Mon 2024-10-21 09:01:05 CEST; 19s ago
Main PID: 3446757 (temboard-agent)
Tasks: 6 (limit: 306229)
Memory: 37.2M
CPU: 381ms
CGroup: /system.slice/system-temboard\x2dagent.slice/temboard-agent@PGMP01A.service
├─3446757 "temboard-agent: PGMP01A: web"
├─3446764 "temboard-agent: PGMP01A: scheduler"
├─3446765 "temboard-agent: PGMP01A: worker pool"
└─3446834 "temboard-agent: PGMP01A: task temboardagent.plugins.dashboard.dashboard_collector_batch_worker"
Oct 21 09:01:05 host01 systemd[1]: Started PostgreSQL Remote Control Agent PGMP01A.
Oct 21 09:01:05 host01 temboard-agent[3446757]: INFO: app: Starting temboard-agent 9.0.0.
Check if the new temboard ui and temboard agent version is correct.
$ temboard --version INFO: app: Starting temboard 9.0.0. temBoard 9.0.0 (/usr/bin/temboard) System Red Hat Enterprise Linux 9.4 (Plow) Python 3.9.18 (/usr/bin/python3) cryptography 36.0.1 Tornado 6.1 Flask 2.0.3 libpq 13.11 psycopg2 2.8.6 (dt dec pq3 ext lo64) SQLAlchemy 1.4.45 $ temboard-agent --version INFO: app: Starting temboard-agent 9.0.0. temBoard agent 9.0.0 (/usr/bin/temboard-agent) System Red Hat Enterprise Linux 9.4 (Plow) Python 3.9.18 (/usr/bin/python3) bottle 0.12.21 cryptography 36.0.1 libpq 13.11 psycopg2 2.8.6 (dt dec pq3 ext lo64)
Now, everything is fine and we are ready to go.
Conclusion
That was quite a huge new release from Dalibo with quite a lot cool new stuff. I will write a few blog posts about the new features in temBoard version 9.0 very soon. Have fun with the new version.