PostgreSQL: temBoard version 7.9 released with support for PostgreSQL 14 - ptdb

Dalibo released temBoard version 7.9 on the 27.12.2021, which is quite an important release because it supports now officially PostgreSQL version 14. Besides that, a lot of bugfixes went into this release as well. The most important changes in release 7.9 can be found on the temBoards documentation page.

https://temboard.readthedocs.io/en/v7/CHANGELOG/

[7.9]

Support PostgreSQL 14
Fix deadlock in monitoring
Fix UI glitches
Restructured documentation
Improved logging messages
Dropped Debian Jessie support
agent: Monitor only local filesystem size
agent: Set umask 027 in auto_configure.sh
agent: Details components version in temboard-agent –version output
agent: Fix sysv init script shipped on systemd by debian package

Some other very important fixes, which made it into this release are the following fixes:

Issue: If too many instance groups are configured, you can’t edit an instance and select one of the last one’s
https://github.com/dalibo/temboard/issues/925

Issue: Locking Issues
https://github.com/dalibo/temboard/issues/931

Another thing that you might notice very quickly, is that the number of rollbacked transactions on the repository DB will drop considerably. In my case the rollbacked transactions dropped by 60%.

Rollbacked Transactions

In case you are interested in the complete list of changes between version 7.8 and 7.9 I would recommended to use the Github compare.

https://github.com/dalibo/temboard/compare/7.8…7.9

The latest version for Red Hat/CentOS 7.x can be found here: https://yum.dalibo.org/labs/CentOS7-x86_64/

temboard-7.9-1.el7.noarch.rpm
temboard-agent-7.9-1.el7.noarch.rpm

Fortunately, the upgrade from 7.8 to 7.9 is quite simply. In general, you need only to stop the service, upgrade the rpm package, run the temboard-migratedb script and start the service again.

Stop the temBoard server and agent and start with a clean log file.

$ sudo systemctl stop temboard.service
$ sudo systemctl stop temboard-agent@12-pg58888.service
$ sudo systemctl reset-failed
$ sudo systemctl is-system-running
running

$ cd /var/log/temboard/
$ sudo mv temboard.log temboard.log.before_upgrade_to_7.9
$

Upgrade the temBoard (UI) from 7.8 to 7.9.

$ sudo yum localupdate temboard-7.9-1.el7.noarch.rpm
Loaded plugins: enabled_repos_upload, langpacks, package_upload, product-id, search-disabled-repos, subscription-manager
Examining temboard-7.9-1.el7.noarch.rpm: temboard-7.9-1.el7.noarch
Marking temboard-7.9-1.el7.noarch.rpm as an update to temboard-7.8-1.el7.noarch
Resolving Dependencies
-- Running transaction check
--- Package temboard.noarch 0:7.8-1.el7 will be updated
--- Package temboard.noarch 0:7.9-1.el7 will be an update
-- Finished Dependency Resolution
rhel-7-server-extras-rpms/x86_64 | 2.0 kB 00:00:00
rhel-7-server-optional-rpms/x86_64 | 2.0 kB 00:00:00
rhel-7-server-rh-common-rpms/x86_64 | 2.1 kB 00:00:00
rhel-7-server-rpms/x86_64 | 2.0 kB 00:00:00
rhel-7-server-satellite-tools-6.9-rpms/x86_64 | 2.1 kB 00:00:00
rhel-7-server-supplementary-rpms/x86_64 | 2.3 kB 00:00:00
rhel-server-rhscl-7-rpms/x86_64 | 2.3 kB 00:00:00

Dependencies Resolved

==============================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================
Updating:
temboard noarch 7.9-1.el7 /temboard-7.9-1.el7.noarch 3.9 M

Transaction Summary
==============================================================================================================================================================
Upgrade 1 Package

Total size: 3.9 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Updating : temboard-7.9-1.el7.noarch 1/2
Cleanup : temboard-7.8-1.el7.noarch 2/2
Uploading Package Profile
Loaded plugins: langpacks, product-id, subscription-manager
Verifying : temboard-7.9-1.el7.noarch 1/2
Verifying : temboard-7.8-1.el7.noarch 2/2

Updated:
temboard.noarch 0:7.9-1.el7

Complete!
Uploading Enabled Repositories Report
Loaded plugins: langpacks, product-id, subscription-manager

Check if the new version is correct.

$ temboard --version
INFO: Starting temboard 7.9.
temBoard 7.9
System Red Hat Enterprise Linux Server 7.9 (Maipo)
Python 2.7.5 (/usr/bin/python)
psycopg2 2.5.1 (dt dec pq3 ext)
Tornado 4.2.1
SQLAlchemy 0.9.8
alembic 0.8.3

Upgrade the temBoard agent.

$ sudo yum localupdate temboard-agent-7.9-1.el7.noarch.rpm
Loaded plugins: enabled_repos_upload, langpacks, package_upload, product-id, search-disabled-repos, subscription-manager
Examining temboard-agent-7.9-1.el7.noarch.rpm: temboard-agent-7.9-1.el7.noarch
Marking temboard-agent-7.9-1.el7.noarch.rpm as an update to temboard-agent-7.8-1.el7.noarch
Resolving Dependencies
-- Running transaction check
--- Package temboard-agent.noarch 0:7.8-1.el7 will be updated
--- Package temboard-agent.noarch 0:7.9-1.el7 will be an update
-- Finished Dependency Resolution
rhel-7-server-extras-rpms/x86_64 | 2.0 kB 00:00:00
rhel-7-server-optional-rpms/x86_64 | 2.0 kB 00:00:00
rhel-7-server-rh-common-rpms/x86_64 | 2.1 kB 00:00:00
rhel-7-server-rpms/x86_64 | 2.0 kB 00:00:00
rhel-7-server-satellite-tools-6.9-rpms/x86_64 | 2.1 kB 00:00:00
rhel-7-server-supplementary-rpms/x86_64 | 2.3 kB 00:00:00
rhel-server-rhscl-7-rpms/x86_64 | 2.3 kB 00:00:00

Dependencies Resolved

==============================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================
Updating:
temboard-agent noarch 7.9-1.el7 /temboard-agent-7.9-1.el7.noarch 957 k

Transaction Summary
==============================================================================================================================================================
Upgrade 1 Package

Total size: 957 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Updating : temboard-agent-7.9-1.el7.noarch 1/2
No units found for temboard-agent.
Too few arguments.
Cleanup : temboard-agent-7.8-1.el7.noarch 2/2
Uploading Package Profile
Loaded plugins: langpacks, product-id, subscription-manager
Verifying : temboard-agent-7.9-1.el7.noarch 1/2
Verifying : temboard-agent-7.8-1.el7.noarch 2/2

Updated:
temboard-agent.noarch 0:7.9-1.el7

Complete!
Uploading Enabled Repositories Report
Loaded plugins: langpacks, product-id, subscription-manager

Check if the new agent version is correct.

$ temboard-agent --version
INFO: Starting temboard-agent 7.9.
temBoard agent 7.9
System Red Hat Enterprise Linux Server 7.9 (Maipo)
Python 3.6.8 (/usr/bin/python3)
psycopg2 2.7.7 (dt dec pq3 ext) libpq 9.2.24

Now run the database upgrade as the temboard OS user.

$ su - temboard
Password:
Last login: Mon Jul 5 10:45:32 CEST 2021 on pts/1

$ temboard-migratedb upgrade
INFO: Starting temboard-migratedb 7.9.
INFO: Found config file /etc/temboard/temboard.conf.
INFO: Context impl PostgresqlImpl.
INFO: Will assume transactional DDL.
INFO: Running upgrade 67f52879da15 -&gt; 55ab971bde17, metrics-archive-deadlock
INFO: Database up to date.

$

You might will notice that a few functions have been replaced.

2022-01-03 06:35:05.378 CET [6464] LOG: statement: CREATE SCHEMA IF NOT EXISTS "application";
2022-01-03 06:35:05.379 CET [6464] LOG: statement: ALTER TABLE public.alembic_version SET SCHEMA "application";
2022-01-03 06:35:05.379 CET [6464] ERROR: relation "public.alembic_version" does not exist
2022-01-03 06:35:05.379 CET [6464] STATEMENT: ALTER TABLE public.alembic_version SET SCHEMA "application";
2022-01-03 06:35:05.386 CET [6464] LOG: statement: SET search_path TO monitoring, public;

CREATE OR REPLACE FUNCTION archive_current_metrics(table_name TEXT, record_type TEXT, query TEXT) RETURNS TABLE(tblname TEXT, nb_rows INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
v_table_current TEXT;
v_table_history TEXT;
v_query TEXT;
i INTEGER;
BEGIN
v_table_current := table_name || '_current';
v_table_history := table_name || '_history';
-- Lock _current table to prevent concurrent updates
EXECUTE 'LOCK TABLE ' || v_table_current || ' IN SHARE MODE NOWAIT';
v_query := replace(query, '#history_table#', v_table_history);
v_query := replace(v_query, '#current_table#', v_table_current);
v_query := replace(v_query, '#record_type#', record_type);
-- Move data into _history table
EXECUTE v_query;
GET DIAGNOSTICS i = ROW_COUNT;
-- Truncate _current table
EXECUTE 'TRUNCATE '||v_table_current;
-- Return each history table name and the number of rows inserted
RETURN QUERY SELECT v_table_history, i;
END;
$$;

DROP FUNCTION IF EXISTS history_tables();

CREATE OR REPLACE FUNCTION aggregate_data_single(table_name TEXT, record_type TEXT, query TEXT) RETURNS TABLE(tblname TEXT, nb_rows INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
t JSON;
v_agg_periods TEXT[] := array['30m', '6h'];
v_agg_table TEXT;
i_period TEXT;
v_query TEXT;
i INTEGER;
BEGIN
-- Build and run 'aggregate' query for type of metric.
FOREACH i_period IN ARRAY v_agg_periods LOOP
v_agg_table := table_name || '_' || i_period || '_current';
v_query := replace(query, '#agg_table#', v_agg_table);
v_query := replace(v_query, '#interval#', i_period);
v_query := replace(v_query, '#record_type#', record_type);
v_query := replace(v_query, '#name#', table_name);
EXECUTE v_query;
GET DIAGNOSTICS i = ROW_COUNT;
RETURN QUERY SELECT v_agg_table, i;
END LOOP;
END;
$$;

DROP FUNCTION IF EXISTS aggregate_data();

I usually adjust the temboard service as well with the nice value of -19. (Nice=-19). This is an optional step and not mandatory. I do it only to give the temBoard service the highest CPU priority.

$ cd /etc/systemd/system/multi-user.target.wants/
$ sudo vi temboard.service
$ cat temboard.service
[Unit]
Description=temBoard Web UI
After=network.target
 
[Service]
Type=simple
User=temboard
Group=temboard
ExecStart=/usr/bin/env SYSTEMD=1 temboard -c /etc/temboard/temboard.conf
Nice=-19
 
[Install]
WantedBy=multi-user.target

Startup the temBoard UI and the agent.

$ sudo systemctl daemon-reload
$ sudo systemctl start temboard.service
$ sudo systemctl start temboard-agent@12-pg58888.service

Check if the services are running correctly.

$ sudo systemctl status temboard.service
temboard.service - temBoard Web UI
Loaded: loaded (/usr/lib/systemd/system/temboard.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/temboard.service.d
override.conf
Active: active (running) since Mon 2022-01-03 06:38:10 CET; 23s ago
Main PID: 9641 (temboard)
CGroup: /system.slice/temboard.service
 9641 temboard: web
 9648 temboard: worker pool
 9649 temboard: scheduler
 9660 temboard: worker pool
 9663 temboard: worker pool
 9840 temboard: worker pool
10052 temboard: worker pool
10151 temboard: worker pool
10154 temboard: worker pool
...
...

$ sudo systemctl status temboard-agent@12-pg58888.service
 temboard-agent@12-pg58888.service - PostgreSQL Remote Control Agent 12/pg58888
Loaded: loaded (/usr/lib/systemd/system/temboard-agent@.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2022-01-03 06:38:21 CET; 54s ago
Main PID: 9885 (temboard-agent)
CGroup: /system.slice/system-temboard\x2dagent.slice/temboard-agent@12-pg58888.service
9885 temboard-agent: 12/pg58888: main process
9896 temboard-agent: 12/pg58888: worker pool
9897 temboard-agent: 12/pg58888: scheduler

Jan 03 06:39:10 host01 env[9885]: Running host probe loadavg.
Jan 03 06:39:10 host01 env[9885]: Running instance level probe wal_files.
Jan 03 06:39:10 host01 env[9885]: Running instance level probe replication_lag.
Jan 03 06:39:10 host01 env[9885]: Running instance level probe temp_files_size_delta.
Jan 03 06:39:10 host01 env[9885]: Running instance level probe replication_connection.
Jan 03 06:39:10 host01 env[9885]: Running database level probe heap_bloat.
Jan 03 06:39:10 host01 env[9885]: Running database level probe btree_bloat.
Jan 03 06:39:10 host01 env[9885]: Finished probes run.
Jan 03 06:39:10 host01 env[9885]: Add data to metrics table.
Jan 03 06:39:10 host01 env[9885]: Collect done.

Ready. 🙂

Conclusion

The temBoard 7.9 is very important new release which comes with PostgreSQL 14 support and a lot of important bug fixes.

Post Views: 180

Conclusion

You Might Also Like

PostgreSQL: How To Upgrade temBoard 7.11 to 8.0

PostgreSQL: temBoard Rollbacked Transactions

PostgreSQL: How to Upgrade temBoard Version 7.6 to 7.7