QGIS 3x3 on NetBSD 10/11 + PostgreSQL 14/16/18

 

Started making QGIS version 3.44.8 early June 2026 on NetBSD processors

jump ahead:

$ ps auxww | head -2

USER        PID %CPU %MEM      VSZ    RSS TTY    STAT STARTED     TIME COMMAND

root      24310 93.3  1.1   427668 355252 pts/6  O+   12:18AM  0:02.14 /usr/pkg/gcc12/libexec/gcc/x86_64--netbsd/12.4.0/cc1plus -quiet -I /usr/pkgsrc/wip/qgis/work/QGIS-final-4_0_3/cmake-pkgsrc-build/src/core/qgis_core_autogen/include

Wed Jun 10 00:25:37 UTC 2026

$ TZ=EST date

Tue Jun  9 19:25:45 EST 2026

=> Checking file-check results for qgis-3.44.8

=> Creating binary package /usr/pkgsrc/geography/qgis/work/.packages/qgis-3.44.8.tgz

/// pkg_delete postgresql18-client qgis-3.44.8  py313-psycopg2-2.9.11

$ pkgin  show-deps "qgis" | grep -i client

        postgresql16-client>=16.0nb1

https://pkgsrc.se/wip/qgis

Version history: (Expand)

    (2026-06-02) Updated to version: qgis-4.0.3

    (2026-05-10) Updated to version: qgis-4.0.2.18

    (2026-03-09) Updated to version: qgis-4.0.0.0

    (2026-02-22) Updated to version: qgis-3.99.3

    (2026-02-21) Updated to version: qgis-3.99.2

FREEBSD ~~~

foobar:/etc # pkg search qgis

qgis-3.44.11                   Free and Open Source Geographic Information System

qgis-ltr-3.40.15_1             Free and Open Source Geographic Information System

something bollixed. Back to NetBSD.

~~~

amd64-12-core

$ ls -ltr /usr/pkgsrc/packages/All/qg*

-rw-r--r--  1 root  wheel  123201876 Jun  9 17:15 /usr/pkgsrc/packages/All/qgis-3.44.8.tgz

-rw-r--r--  2 root  wheel  124614138 Jun 26 16:10 /usr/pkgsrc/packages/All/qgis-3.44.11.tgz

-rw-r--r--  2 root  wheel  127333035 Jul 13 11:34 /usr/pkgsrc/packages/All/qgis-4.0.3.tgz

-rw-r--r--  2 root  wheel  131910253 Jul 15 23:27 /usr/pkgsrc/packages/All/qgis-4.2.0.tgz

amd64-4-core

5.3$ ls -l /root/pkg-local/qg*

-rw-r--r--  1 root  wheel  127333035 Jul 13 20:02 /root/pkg-local/qgis-4.0.3.tgz

amd64-2-core

[     1.000004] cpu0 at mainbus0 apid 0

[     1.000004] cpu0: Use lfence to serialize rdtsc

[     1.000004] cpu0: Intel(R) Core(TM)2 Duo CPU     L9400  @ 1.86GHz, id 0x1067a

[     1.000004] cpu0: node 0, package 0, core 0, smt 0

[     1.000004] cpu1 at mainbus0 apid 1

[     1.000004] cpu1: Intel(R) Core(TM)2 Duo CPU     L9400  @ 1.86GHz, id 0x1067a

[     1.000004] cpu1: node 0, package 0, core 1, smt 0

[     1.000004] acpi0 at mainbus0: Intel ACPICA 20221020

2 core had faults, see:

https://chaos.social/@jspath55/116834413083346711

old processor no numpy for you

RuntimeError: NumPy was built with baseline optimizations:
(X86_V2) but your machine doesn't support:
(X86_V2).

Pictures and exhibitions

Kept track of long compile times (~18 hours) on the slowest machine, as well as faster, looking at CPU temperature and process load (distributed across 1 or many "cores").

[

mastodon cc me

https://assets.chaos.social/media_attachments/files/116/846/959/384/561/931/original/96c70f8d89d6e49f.png

Go Speed Racer Go!

Pre 3-way build side quest:

Initial launch 4.0.3 June 2026

-rw-r--r--  1 jim  jim 20978 Jun 10 12:46 GIS/spots-rooms-q4.qgz

Side quest:

QGIS 3.44 on the machine I chose did not build, so the check with PG SQL 14 was inadequate. I changed the SQL version on that machine to 18, and it runs with the broken CPU message disabling various plugins (yet to be found).

This is the override of the database version when building from source, not installing pre-fabs.

$ pwd

/usr/pkgsrc/mk

$ diff pg*

28,30c28

< # PGSQL_VERSION_DEFAULT?=             16

< # Fri Jun 26 04:32:43 UTC 2026

< PGSQL_VERSION_DEFAULT?=               18

---

> PGSQL_VERSION_DEFAULT?=               16

Screengrab needs to be built from scratch; what I previously did with an X capture app has fallen into disrepair. At least xli still works.

$ ls -l `which qgis`

-rwxr-xr-x  1 root  wheel     390320 Jul  3 12:43 /usr/pkg/bin/qgis

-rw-r--r--  1 root  wheel  124615383 Jul  3 12:45 ./pkg-local/qgis-3.44.11.tgz

$ psql --version

psql (PostgreSQL) 18.4

Post 3-way build side quest of 3x3, going to 4:

Back to the fast machine and the newest (l+g). The work-in-progress version went from 4.0.3 to 4.2.0. Wow, quick change!

Build time ~8 hours with or without fumbles. Needs to be redone with a clean slate and only 2026Q4 pkgsrc to get things level. But it starts and connects to databases as it should.

$ time qgis --version

QGIS 4.2.0-Belém do Pará 'Belém do Pará' (exported)

real    0m2.522s

user    0m2.458s

sys     0m0.039s

Quick duck back to 3.44.11

Comparison table of the 12-core with 4.2.0 and the 2-core with 3.44.11.

Sidebar issue with Python caused the active plugins to be empty on the 2-core, but otherwise they are pretty much the same beyond QGIS itself and the Qt version going from 5 to 6.

Neither system shows a value for SFCGAL or GeographicLib. Not sure what impact that has.

Working on a list of projects for QGIS 4.2, including waypoints of Historic Trails, paths in the woods, and geocaching at the Bengies Drive-In Theatre.

Forward with QGIS 4 and NetBSD 11 and more

 A week ago, based on a tip from Greg T, I build QGIS 4.0 on a NetBSD 10.1 system. It worked, after arranging the work-in-progress snapshot.

Peripherally, I got into gdal (for georeferenced PDFs), postgis (with PostgreSQL 18), and fence-posting of PGSQL 14 through 18 as allowed by the pkgsrc tools and all.

I put the problems first, instead of the splash screen showing the build worked; this showed it work-worked.

Splash - QGIS 4.0 starting banner, with an auth fail flag.

To show the build time (as opposed to package download/install time), I used Zabbix charts of CPU time and/or temperature.

First is the fastest machine I have, compiling QGIS 3 followed by 4. A few hours each. Ish.

Sidebar in the future about single-threaded builds, as I have a collection of antique laptops that I've kept out of the hopper by installing BSD on them with new SSDs. 2 or 4 core, plus one "tower" that has a laptop-type board inside (HP made money doing that).

The glitch for using other than one PGSQL versions I found was the packaged build use 16 (as of this writing), but the pkgsrc make process allows one (and only one) of 14 through 18. For some reason I've got even number versions running, not on purpose, just the time I jumped into the pool for some app/reason.

A bigger test/build/test cycle is brewing, with the rough starting time after 2026 Q2 packages are distributed in a few weeks. Meanwhile I built 3.4.8 on a system with PostgreSQL 14 server. 18 to 24 hours on this box.

===> Extracting for qgis-3.44.8

=> Full dependency postgresql14-client>=14.17nb2: found postgresql14-client-14.23

I like this chart as Zabbix allows dashboards with arbitrary charts to go on the same page. Easier than trying to line up 2 Y-axes unless they are on the same scale (like classic 1/5/15 minute processor loads).

Issues above (and beyond)

• First encounter with an authorization message. Doesn't stop use, but it's unfamiliar territory.
• Building a current work-in-progress sub-tree broke apps on my main machine, maybe not the version 4.0.
• One core dump in the short time I ran 4.0.
• I had/have outdated pkgsrc trees, getting up to date with 2026 Q1 then Q2.
• Wishful thinking I could cross-build an aarch64 QGIS 4.0 as a Pi 4 may take as long to build as an old Intel (unsure)
• Geo-referenced PDF exports depend on the gdal library; Greg imported a change prior to freeze that I have yet to try.
• PostGIS works with version 18 now.
• The PKGSRC.se site has some new data, some old data on their pages. Images at the end. #ToDo
• The QGIS.org site shows build steps for FreeBSD and OpenBSD, not NetBSD. (Whom do I know?)
• pkg_admin version?
• The NetBSD wiki and pkgsrc docs could explain more how to/why to use a PGSQL version with QGIS or any other DB app.
• e.g 

https://wiki.netbsd.org/pkgsrc/how_to_install_a_postgresql_server/

Last edited 5 years and 9 months ago

...

The recommended way is to install the prebuilt postgresql binaries.

The PostgreSQL Server depends on the postgreSQL Client.

? road less traveled way - how to use a version other than default ?

cd geography/qgis

set version ?

"make install"

PHP and PostgreSQL

You may wish to install the postgres Module for PHP, e.g. for PHP 7.0 use:

# pkg_add -v php70-pgsql

3$ pkgin se php".*"-pgsql

php56-pgsql-5.6.40nb1  PHP extension for PostgreSQL databases

php74-pgsql-7.4.33nb1  PHP extension for PostgreSQL databases

php82-pgsql-8.2.31nb1  PHP extension for PostgreSQL databases

php83-pgsql-8.3.31nb1  PHP extension for PostgreSQL databases

php84-pgsql-8.4.20nb1  PHP extension for PostgreSQL databases

php85-pgsql-8.5.7nb1  PHP extension for PostgreSQL databases

add Python example:

$ pkgin se psyco

py311-psycopg2-2.9.11  PostgreSQL database adapter for Python

py312-psycopg2-2.9.11  PostgreSQL database adapter for Python

py313-psycopg2-2.9.11 = PostgreSQL database adapter for Python

py314-psycopg2-2.9.11  PostgreSQL database adapter for Python

import psycopg2

add Perl via DBI/DBD:

$ pkgin se p5-dbd-postgresql

p5-DBD-postgresql-3.14.2nb5  Perl DBI/DBD driver for PostgreSQL databases

use DBI qw(:sql_types);

use DBD::Pg qw(:pg_types);

export PGUSER=scott

export PGDATABASE=emp

export PGHOST=dept

? Read the manual ?

https://www.netbsd.org/docs/pkgsrc/pkgsrc.html

pgsql.buildlink3.mk will accept any of the Postgres versions in the variable PGSQL_VERSIONS_ACCEPTED and default to the version PGSQL_VERSION_DEFAULT. See the file for more information.

Explain default version "middle of the road" at a point in time for more users than other versions (subject to debate) not "most recent" version of PostreSQL.

https://pkgsrc.se/wip/qgis

https://pkgsrc.se/geography/qgis

Others in scope:

https://pkgsrc.se/geography/gdal-lib

https://pkgsrc.se/databases/py-psycopg2

Delaminating the Onion, OGR style

 After I learned about geospatial PDF documents with layers of GIS "goo" embedded I've tried out maps and similar graphics for events and travel. New challenge--having a layered PDF for a theater stage floor plan. The idea came when I found first one, then a different paper drawing, detailed but lacking those "mechanical drawing" features such as a title block, date, scale, source, things like that.

New version, showing 3 of 8 layers

The old drawings could have come from Corel Draw for all I know.

I wanted to use LibreOffice Draw if possible, to create a layered PDF, as I've learned how to do most of what I could do with MS Visio. The above image is the fix for dimensions in Imperial units that show up as "10.01 feet" for unknown reasons. Truncating to 1 decimal place makes it better as "10.0 feet."

Here's one with a rounding error, and also a scaling error as the scale "rod" should be 10 feet, not 9 and a half-ish.

The ODG, seat numbers and scan layers were supposed to have boxes to check if that layer was included in the view. 

But no, while the LibreOffice drawing can manage internal layer juggling, writing a PDF with defined layers does not work.

https://bugs.documentfoundation.org/show_bug.cgi?id=138142

Bug 138142 - "Draw should have an option to export to PDF keeping separate layers"

A workaround posted here:

https://graphicdesign.stackexchange.com/questions/169487/how-to-export-a-drawing-odg-file-from-libreoffice-25-8-to-a-multi-layered-pdf-f 

Load the ODG file into Scribus, then export to a layered PDF. It works, but only to a degree, as the layers get shuffled and renamed.

Extra layers can be added in Scribus, so I chose to do the measured baselines (aisles etc.) in ODG and the icing of title blocks and guides for lining out skewed scanned of paper drawings in Scribus.

I've also added an SVG layer from Inkscape.

Looking at an imported JPEG is awful from Scribus. But the PDF looks fine.

Layers and more

Almost had an issue with "back-loading" a Scribus file on Windows 1.6, after creating a document in NetBSD with 1.7. So I'll use the higher version and save as" a lower if anyone needs to look for themselves.

The scans ended up slightly different in shape, added to the task of alignment.

 

 

Embellishing with seat designs and numbers in Inkscape turned out to be a bigger task than imagined. For one thing, the web site has a dark theme, but printing the images for human note-taking needs a light theme, as these are.

The seat counts differ between the two, I finally noticed also.

NetBSD 11 RC3 heat, RC4, and more

The last post was about NetBSD 11, Release Candidate 3. Meanwhile, RC4 was just announced, so this write-up is mainly about testing RC3, with a little RC4 overlap as I upgraded several machines this week.

CPU metrics during User Testing

CPU temperature pattern, where I scheduled the automated test framework to run twice per day on a Raspberry Pi 3 (aarch64).

Now, running processes; the test cycles kick off a couple at a time. The spikes line up with heat generation, unsurprisingly.

Another Zabbix chart for a 48 hour period showing 1 minute average per core. Spikes above 0.5 are rare, and not always aligned in a test "burst", presumably due to the Zabbix data collection cycle where short duration events might be missed.

Interrupts per second. Not much above normal background load other than one obvious spike per test run. Might be interesting to drill down to the specific timeframe and see what tests were running.

Context switches per second, with occasional spikes. Some correspond to other metrics, some not. Closest match is interrupts.

 Test run failures

As with prior ATF runs, the errors differ from architecture to architecture. The one-core Raspberry Pi0W had the most faults, while the 4-core Pi3 had the least.

grep "failed test cases" tests-*txt | grep -v expected |  awk '{print $2}' | sort -n | uniq -c
   1 4
   4 5
   8 6
   5 7
  13 8
  11 9
   5 10
   3 11
   1 12
   1 13

Above is for an AMD64, which being the fastest of the systems tested, has more runs in a day/week. I did scatter plots to show the variations.

AMD64:

i386:

Pi0W:

Pi3:

These charts are not the standard distribution bell curve, but close, where the number of errors must be zero or a positive number. The variation in errors from one run to the next means the next analytical step is measuring frequencies of specific test failures

Doesn't look like any one test fails every time for this machine:

net/npf/t_npf:npf_guid, usr.sbin/tcpdump/t_tcpdump:promiscuous, fs/tmpfs/t_vnode_leak:main, modules/t_x86_pte:svs_g_bit_set, crypto/libcrypto/t_libcrypto:threads

net/carp/t_basic:carp_handover_ipv6_halt_nocarpdevip, net/npf/t_npf:npf_guid, fs/tmpfs/t_vnode_leak:main, fs/vfs/t_renamerace:puffs_renamerace_cycle, modules/t_x86_pte:svs_g_bit_set, crypto/libcrypto/t_libcrypto:threads

net/net/t_unix:sockaddr_un_fstat, net/carp/t_basic:carp_handover_ipv6_halt_nocarpdevip, net/ndp/t_ndp:ndp_cache_expiration, net/npf/t_npf:npf_guid, fs/tmpfs/t_vnode_leak:main, crypto/libcrypto/t_libcrypto:threads

net/carp/t_basic:carp_handover_ipv6_halt_nocarpdevip, net/carp/t_basic:carp_handover_ipv6_ifdown_nocarpdevip, net/npf/t_npf:npf_guid, fs/tmpfs/t_vnode_leak:main, fs/vfs/t_renamerace:msdosfs_renamerace, fs/vfs/t_renamerace:puffs_renamerace_cycle, modules/t_x86_pte:svs_g_bit_set, crypto/libcrypto/t_libcrypto:threads

net/carp/t_basic:carp_handover_ipv6_ifdown_nocarpdevip, net/ndp/t_ndp:ndp_cache_expiration, net/npf/t_npf:npf_guid, fs/tmpfs/t_vnode_leak:main, fs/vfs/t_renamerace:puffs_renamerace_cycle, crypto/libcrypto/t_libcrypto:threads

$ sort  /tmp/ers | uniq -c
   5 crypto/libcrypto/t_libcrypto:threads
   5 fs/tmpfs/t_vnode_leak:main
   1 fs/vfs/t_renamerace:msdosfs_renamerace
   3 fs/vfs/t_renamerace:puffs_renamerace_cycle
   3 modules/t_x86_pte:svs_g_bit_set
   3 net/carp/t_basic:carp_handover_ipv6_halt_nocarpdevip
   2 net/carp/t_basic:carp_handover_ipv6_ifdown_nocarpdevip
   2 net/ndp/t_ndp:ndp_cache_expiration
   1 net/net/t_unix:sockaddr_un_fstat
   5 net/npf/t_npf:npf_guid
   1 usr.sbin/tcpdump/t_tcpdump:promiscuous

NetBSD 11 RC3 upgrade foibles

 I like this peaking pattern, particularly since it stopped. Looping test case, repeated twice maybe?

Then, closer scrutiny shows the major test sections as cron restarts them daily. Faster systems I'd schedule more per day. This might squeak in 2 a day on the Pi 0. Maybe.

48 hours - 1 CPU, 2 test cycles

The upgrade process from 11.0 RC2 to RC3 was more complicated on the Pi 0W than on the 3 or 4, where the kernel resides in the root directory and can be replaced during sysupgrade or another method. For the 0w, the kernel and supporting files are in a boot filesystem and aren't replaced by the upgrade. I have used different ways to put the newer files into place; this time I installed a fresh image to another SD card, then carted over the necessary files under /boot.

$ ls -l /boot/
total 19320
-rwxr-xr-x  1 root  wheel     1594 Apr  4 10:08 LICENCE.broadcom
drwxr-xr-x  1 root  wheel     1024 Mar 10 15:48 System Volume Information
-rwxr-xr-x  1 root  wheel    52476 Apr  4 10:08 bootcode.bin
-rwxr-xr-x  1 root  wheel      115 Apr  4 10:08 cmdline.txt
-rwxr-xr-x  1 root  wheel      382 Apr  4 10:08 config.txt
drwxr-xr-x  1 root  wheel     2048 Mar  4 21:02 dtb
-rwxr-xr-x  1 root  wheel     7269 Apr  4 10:08 fixup.dat
-rwxr-xr-x  1 root  wheel     3180 Apr  4 10:08 fixup_cd.dat
-rwxr-xr-x  1 root  wheel  8055184 Apr  4 10:08 kernel.img
-rwxr-xr-x  1 root  wheel  7865424 Apr  4 10:08 kernel7.img
-rwxr-xr-x  1 root  wheel  2979264 Apr  4 10:08 start.elf
-rwxr-xr-x  1 root  wheel   808060 Apr  4 10:08 start_cd.elf

I could have "cherry picked" which dtb driver files to replace, but chose the slower copy-them-all. In prior upgrades, space was at a premium on the boot device. This iteration is not tight.

$ df /boot

Filesystem      1K-blocks         Used        Avail %Cap Mounted on
/dev/ld0e           81269        19655        61614  25% /boot

$ uname -a

NetBSD rpi 11.0_RC3 NetBSD 11.0_RC3 (RPI) #0: Sat Apr  4 06:08:56 UTC 2026  mkrepro@mkrepro.NetBSD.org:/usr/src/sys/arch/evbarm/compile/RPI evbarm

An issue from the RC2 tests hasn't reappeared on RC3, where a run triggered a runaway core or something, shown on the first image above. I have an open PR though it seems the report isn't a surprise.

The Pi3 and amd64 upgrades to RC3 worked well, and the former has the lowest test failure rate of the architectures I have available. The i386 port also has few failures, a couple of them caused by me using the CD image for an upgrade instead of using the sysupgrade package. Mainly because I wanted to test a different mode.

To fit the install code into 700MB or less, the NetBSD developers seem to have left out a couple of the test sets. I noticed the messages on running the upgrade, as they were unusual in my experience.

As I half-expected issues, I went ahead with the install without the 2 distribution sets. Eventually the test suite results flagged the glitch.

Failed test cases:

dev/audio/t_audio:AUDIO_SETINFO_pause_WRONLY_2, dev/audio/t_audio:open_audioctl_RDWR, lib/libutil/t_snprintb:snprintb, net/carp/t_basic:carp_handover_ipv6_halt_nocarpdevip, net/if_wg/t_misc:wg_rekey, net/npf/t_npf:npf_guid, usr.bin/mtree/t_sets:set_base, usr.bin/mtree/t_sets:set_xbase, fs/vfs/t_renamerace:ext2fs_renamerace_cycle

The list [] to be installed is determined by the optional arguments
passed to the command or, if none, from the value of the SETS
configuration variable.

< SETS=AUTO  # Guess from /etc/mtree/set.* files.

> SETS="tests xbase"

Finally downloaded the entire set (apparently ${SETS} applies to the install, *not* the fetch).

After effects (success in reinstalling missing sets):

Failed test cases:

net/carp/t_basic:carp_handover_ipv6_halt_nocarpdevip, net/carp/t_basic:carp_handover_ipv6_ifdown_nocarpdevip, net/if_wg/t_misc:wg_rekey, net/npf/t_npf:npf_guid, crypto/opencrypto/t_opencrypto:ioctl

RC3 info:

$ uname -a

NetBSD neti386 11.0_RC3 NetBSD 11.0_RC3 (GENERIC) #0: Sat Apr  4 06:08:56 UTC 2026  mkrepro@mkrepro.NetBSD.org:/usr/src/sys/arch/i386/compile/GENERIC i386 i386 Intel 686-class NetBSD

I should summarize the test suite results across the several machine types I've installed 11.0 RC3 on, and analyze for frequency given many tests do not pass or fail 100% of the time. I had coined the term Heisenbergars for those maybe maybe not cases.

Weather rock uncovering

 After putting off finishing the rest of the environmental or weather readings from the nearest airport into a Zabbix suite long enough, I standardized the newest release level 7.4 using FreeBSD.

You can't use the Zabbix send tool for random text without it going somewhere structured so I first set up the variables, naming with Ambient and tagging with Environment. A typical METAR weather file pull looks like:

Baltimore / Martin, MD, United States (KMTN) 39-20N 076-25W
Jun 11, 2022 - 06:54 PM EDT / 2022.06.11 2254 UTC
Wind: Calm:0
Visibility: 10 mile(s):0
Sky conditions: partly cloudy
Temperature: 69 F (21 C)
Dew Point: 59 F (15 C)
Relative Humidity: 68%
Pressure (altimeter): 29.96 in. Hg (1014 hPa)
ob: KMTN 112254Z 00000KT 10SM SCT180 21/15 A2996
cycle: 23

In hindsight, I erred not adding a separator on the pressure column names. Visibility is reported only in miles; adding Km could be a derived field in Zabbix.

The command line I used is, ish:

export ZABBIX_SEND="zabbix_sender -vv -z "${ZABBIX_SERV}" -p 10051 -s "${ZABBIX_HOST}" -k "

Each key is from a row and column in the weather dump, neatly prefixed line-by-line and at most 2 or 3 datapoints per row. I have wind rose in text style, but not "sky conditions"

Wind: from the S (190 degrees) at 7 MPH (6 KT):0

The early-2026 cold snap is quite evident, over days, looking back at a 7.0 level Zabbix server.

The shell script has a bunch of grep/sed/awk when Perl or whatever would be slicker. This just grew piece by piece. We want it to run for instance:

  zabbix_sender -vv -z 127.0.0.1 -p 10051 -s place -k  "enviro[Temperature.Celsius]" -o  8

# echo C

grep  "^Temperature: "           $DATAFILE  | sed -e "s/(//" -e "s/)//"   | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Temperature.Celsius\]\"    -o ", $4}'

# echo F

grep  "^Temperature: "           $DATAFILE  | sed -e "s/(//" -e "s/)//"   | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Temperature.Fahrenheit\]\" -o ", $2}'

# echo dew

grep  "^Dew Point: "             $DATAFILE  | sed -e "s/(//" -e "s/)//"   | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[DewPoint.Fahrenheit\]\"    -o " $3}'

grep  "^Dew Point: "             $DATAFILE  | sed -e "s/(//" -e "s/)//"   | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[DewPoint.Celsius\]\"    -o " $5}'

# echo hum

grep  "^Relative Humidity: "     $DATAFILE  | sed -e "s/%//"              | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Humidity\]\"               -o ", $3}'

grep  "^Pressure (altimeter): "  $DATAFILE  | sed -e "s/(//" -e "s/)//"   | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[PressureHG\]\"             -o ", $3}'

grep  "^Pressure (altimeter): "  $DATAFILE  | sed -e "s/(//g" -e "s/)//g" | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[PressurePA\]\"  -o ", $6}'

grep  "^Visibility: " $DATAFILE  | sed -e "s/(//g" -e "s/)//g" | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Visibility]\" -o ", $2}'

grep  "^Wind: " $DATAFILE  | sed -e "s/(//g" -e "s/)//g" | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Wind.Rose\]\"        -o ", $4}'

grep  "^Wind: " $DATAFILE  | sed -e "s/(//g" -e "s/)//g" | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Wind.Direction\]\"   -o ", $5}'

grep  "^Wind: " $DATAFILE  | sed -e "s/(//g" -e "s/)//g" | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Wind.Speed\]\"    -o ", $8}'

# eof

grep  "^ob" $DATAFILE  | sed -e "s/(//g" -e "s/)//g" | awk -v zs="$ZABBIX_SEND" '{print zs, "\"enviro\[Metar\]\" -o \"" substr($0,5,99) "\""  }'

 

After collecting wind direction for a few hours, the results look as expected.

Timestamp	Value
2026-04-27 05:21:01 PM	S
2026-04-27 04:21:01 PM	S
2026-04-27 03:21:01 PM	S
2026-04-27 02:21:01 PM	S
2026-04-27 01:21:01 PM	S
2026-04-27 12:21:01 PM	S
2026-04-27 11:21:01 AM	S
2026-04-27 03:21:01 AM	NNW
2026-04-26 10:21:01 PM	SE
2026-04-26 07:21:01 PM	ESE
2026-04-26 06:21:01 PM	ESE

Key flipping from sheet to form

 After I wrangled 2 spreadsheets (and 1 Google sheet) into database tables, my first picks for primary keys needed to be changed based on how the data were and how I wanted things to be.

Whoever did the manual entry into rows and columns used month and year as the key separators, since the events were generally produced every month (for literal decades). So it made sense that the first attempt used these as keys. I don't like to use a serial number key for tasks like this as part of the outcome should be normalizing the data; getting an error message that there are duplicate months in a year needs to be investigated.

Among other things I found were complete cast/crew duplicates for 1 show that ran for 2 months (others had only ditto marks, making them easier to spot and tidy up). I needed to switch from a monthly key to a sequence value because after 2000, the shows were scheduled for several week runs but not staying within a month. And there were weekend shows, plus multi-play productions in single ticket access.

 References at the end, one LibreOffice and two DBA Stack Exchange

I started with this key: 

   primary key (year_str, month_str, production, director, person, role)

 The year and month are strings, instead of integers because there were originally a variety of formats which I fixed after initial load.

The propose change: 

ALTER TABLE cast_cooked DROP CONSTRAINT cast_cooked_pkey;
ALTER TABLE cast_cooked ADD PRIMARY KEY (season, sequence, production, person, role, band_cast_crew);
 

Using the example questions, I deleted duplicate rows as I found them, mainly trial-and-error because the first line above always works and the second only when all dupes are clear.

I was overzealous in thinking I should include what turned out to be an indistinct key:

psql:alter_key.sql:3: ERROR:  constraint "cast_cooked_pkey" of relation "cast_cooked" does not exist

psql:alter_key.sql:4: ERROR:  column "sub_seq" of relation "cast_cooked" contains null values
 

All nulls are the same, not unique. Turns out this key also messed with LibreOffice forms, as mismatched tables showed no rows in one (scary!). Had to be removed from the primary key, or filled somehow; I chose the former as faster.

 

ALTER TABLE
psql:alter_key.sql:4: ERROR:  could not create unique index "cast_cooked_pkey"
DETAIL:  Key (season, sequence, sub_seq, person, role)=(4, 11, C, First Last, Director) is duplicated.

These were usually easy, except for the 44 rows of cast+crew dupes, and then one complete duplicate (don't ask).

db=> delete  from cast_cooked where season = 12 and sequence = 7 and month_str = '5' ;

DELETE 44

 
A simple statement but should be sanity claused.

Drop the table constraint and retry:

psql:alter_key.sql:3: ERROR:  constraint "cast_cooked_pkey" of relation "cast_cooked" does not exist

psql:alter_key.sql:4: ERROR:  could not create unique index "cast_cooked_pkey"

DETAIL:  Key (season, sequence, production, person, role, band_cast_crew)=(26, 3, Scrooge (shocking), First Last, Director, crew) is duplicated.

 db=>  select * from cast_cooked where season = 26 and sequence = 3 and role = 'Director' order by month_str, person;
  year_str  | month_str  |     production     |    director     |     person      |   role   | band_cast_crew | season | sequence | opening | closing | sub_seq
------------+------------+--------------------+-----------------+-----------------+----------+----------------+--------+----------+---------+---------+---------
 87         | 12         | Scrooge (shocking) | First Last| First Last  | Director | crew           |     26 |        3 |         |         |
 87         | 12         | Scrooge (shocking) | Firss Last| First Last | Director | crew           |     26 |        3 |         |         |

 There was another typo I redacted, duplicated, as director was not a key value.

To remove a duplicate you might need a "ROW ID" which in PostgreSQL acts like this:

db => select ctid from cast_cooked where season = 26 and sequence = 3 and role = 'Director' order by month_str, person;
  ctid
--------
 (5,14)
 (5,30)
(2 rows)

db=> delete  from cast_cooked where season = 26 and sequence = 3 and role = 'Director' and ctid = '(5,30)';
DELETE 1
 
Then the anticlimactic "done" success message (along with the "you already did that" warning).

 db=> \i alter_key.sql
psql:alter_key.sql:3: ERROR:  constraint "cast_cooked_pkey" of relation "cast_cooked" does not exist
ALTER TABLE

db=>

 

And now, better queries!

 

Two-step also:

 

~ ~ ~

References:

https://books.libreoffice.org/en/BG72/BG7204-Forms.html#toc41
https://dba.stackexchange.com/questions/138320/duplicate-rows-how-to-remove-one
https://dba.stackexchange.com/questions/159793/how-to-set-a-not-null-column-into-null-in-postgresql