We upgraded our internal PostgreSQL cluster to the latest version last week.

Time passes by so quickly: we installed our PostgreSQL cluster around 2008. At least, this was the time of the first public MirrorBrain release 2.2, which was the reason to run a PostgreSQL installation for openSUSE. But MirrorBrain (and therefor the PostgreSQL cluster behind it) is way older. So maybe it’s fair to say that MirrorBrain started with openSUSE in 2005…?

Anyway: if you maintain a database for such a long time, you don’t want to loose data. Downtimes are also not a good idea, but that’s why we have a cluster, right?

While the MirrorBrain database is currently still the biggest one (>105GB in size and ~120 million entries alone in the table listing the files on the mirrors), our new services like Matrix, Mailman3, Gitlab, Pagure, lnt or Weblate are also not that small any more. All together use currently 142GB.

We already upgraded our database multiple times now (starting with version 7. in the past). But this time, we decided to try a major jump from PostgreSQL 11 to 13, without any step in between.

So how do we handle an upgrade of a PostgreSQL database? - In general, we just follow the documentation from upstream - only adjusting the values to our local setup:

Local setup details

• Our configuration files are stored in /etc/postgresql/ - and symlinked into the current data directory. This makes it not only easier for us to have them in a general backup, we also set their file ownership to root:postgres - editable just by root and readable just for the postgres group (file permissions: 0640).
• Below the generic data directory for PostgreSQL on openSUSE (/var/lib/pgsql), we have “data” directories for each version: data11 for the currently used PostgreSQL 11 version.
• A Symlink /var/lib/pgsql/data points to the currently active database directory (data11 in the beginning)

Step-by-Step

Preparation

First let us set up some shell variables that we will use through out the steps. As we need these variables multiple times as user ‘root’ and user ‘postgres’ later, let’s place them into a file that we can refer to (source) later…

cat > /tmp/postgresql_update << EOL
export FROM_VERSION=11
export TO_VERSION=13
export DATA_BASEDIR="/var/lib/pgsql/"
export BIN_BASEDIR="/usr/lib/postgresql"
EOL

Don’t forget to source the file with the variables in the steps below.

Install new RPMs

Install the new binaries in parallel to the old ones (find out, which ones you need either via rpm or zypper):

source /tmp/postgresql_update
zypper in $(rpmqpack | grep "^postgresql${FROM_VERSION}" | sed -e "s|${FROM_VERSION}|${TO_VERSION}|g")

Initialize the new version

Now change into the database directory and create a new sub-directory for the migration:

su - postgres
source /tmp/postgresql_update
cd ${DATA_BASEDIR}
install -d -m 0700 -o postgres -g postgres data${TO_VERSION}
cd ${DATA_BASEDIR}/data${TO_VERSION}
${BIN_BASEDIR}/bin/initdb .

For the exact parameters for the initdb call, you can search the shell history of the last run of initdb. But we go with the standard setup above.

You should end up in a completely independent, fresh and clean PostgreSQL data directory.

Now start to backup the new config files and create Symlinks to the current ones. It’s recommended to diff the old with the new config files and have a close look at the logs during the first starts. Worst case: the new server won’t start with old settings at all. But this can be found in the log files.

su - postgres
source /tmp/postgresql_update
cd ${DATA_BASEDIR}/data${TO_VERSION}

for i in  pg_hba.conf pg_ident.conf postgresql.conf postgresql.auto.conf ; do 
 old $i
 ln -s /etc/postgresql/$i .; 
 # diff $i $i-$(date +"%Y%m%d")
done

Downtime ahead: do the migration

Next step is to finally do the job - this includes a downtime of the database!

rcpostgresql stop

su - postgres
source /tmp/postgresql_update
pg_upgrade --link             \
 --old-bindir="${BIN_BASEDIR}${FROM_VERSION}/bin"     \
 --new-bindir="${BIN_BASEDIR}${TO_VERSION}/bin"       \
 --old-datadir="${DATA_BASEDIR}/data${FROM_VERSION}/" \
 --new-datadir="${DATA_BASEDIR}/data${TO_VERSION}/"

The –link option is very important, if you want to have a short downtime:

–link link instead of copying files to new cluster

In our case, the operation above took ~20 minutes.

Hopefully you end up with something like:

[...]
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Switch to the new PostgreSQL version

Switch to the new database directory. In our case, we prefer a Symlink, which points to the right directory:

source /tmp/postgresql_update
cd ${DATA_BASEDIR}
ln -fs data${TO_VERSION} data

As alternative, you can switch the database directory by editing the configuration in /etc/sysconfig/postgresql:

source /tmp/postgresql_update
echo "POSTGRES_DATADIR='${DATA_BASEDIR}/data${TO_VERSION}'" >> /etc/sysconfig/postgresql

(Maybe you want to edit the file directly instead and set the correct values right at the point.) The prefix in the file should match the ${DATA_BASEDIR} variable.

Start the new server

systemctl start postgresql

Cleanup

Postgres created some scripts in the folder where the pg_upgrade started. Either execute these scripts (as postgres user) directly or use the following commands:

sudo -i -u postgres
source /tmp/postgresql_update
${BIN_BASEDIR}${TO_VERSION}/bin/vacuumdb \
 --all \
 --analyze-in-stages
${BIN_BASEDIR}${TO_VERSION}/bin/reindexdb \
 --all \
 --concurrently

Please note that the two commands above have influence on the performance of your server. When you execute them, your database might become less responsive (up to not responsive at all). So you might want to use a maintenance window for them. On the other side, your new database server will perform much better once you executed these commands. So don’t wait too long.

Check everything

Now it might be a perfect time to check monitoring, database access from applications and such. After that, you might remove the old database directory and de-install the old binaries as well together with an rm /tmp/postgresql_update.

But in general, you can mark this migration as finished.

Motivation

I got lucky with my original hackweek project and I have managed to set up my Leap 15.3 based NAS and private cloud running on NextCloud earlier than planned.

So I though that as an extra project I will set up a proper system monitoring service. The monit service is very handy (thanks for the idea to Paolo Stivanin) but by default it wants to send emails when something goes wrong. Instead of emails I would prefer a real instant message. I am using mostly Telegram for personal purposes. Sure I am using Signal, Matrix, Slack and Rocket.Chat too and technically I have WhatsApp account too. But I decided to start with Telegram.

GitHub pages are super powerful and very easy to use for creating markdown based static websites.

In this post I will walk through how I made this very page.

My setup will be two GitHub repositories, one for the source of the page (https://github.com/bzoltan1/blog-source) and the other where the html artifacts are deployed (https://github.com/bzoltan1/bzoltan1.github.io)

Here I would like to note that it is possible to use a single repository with two branches, one for holding the the source and the other where the website is deployed. I just personally find the two repository setup more elegant without any particular reason.

Motivation

I used to have a Synology DS414 server what worked  well for about 8 years. Naturally, occasionally I had to change disks in the RAID5 system in it, but other than that it did its job. But regardless of the really smooth user experience and the low maintenance needs I never really liked that system as the Synology Disk Station Manager OS is not like many “real” Linux distributions and the community behind that OS is basically non existent. And to be honest I do not really feel that Synology is very eager to build and maintain a community around their OS. It looks more like that they just barely comply with the GPL. All in all, I had just enough motivation to migrate my private cloud and NAS to a proper OS.

Ich wurde zum Thema Kraft von den sehr aktiven und sachkundigen Aktiven hinter https://gnulinux.ch/ kontaktiert und interviewt. Es war ein sehr angenehmes und ausführliches Gespräch zur Geschichte, dem Hintergrund und der Entwicklung von Kraft.

Das vollständige Interview ist im Podcast GLN009 erschienen. Wer vorspulen möchte: Es startet etwa bei 1:20h, aber auch die anderen Themen lohnen sich

Three openSUSE Tumbleweed snapshots were released since last Thursday.

Most of the package updates focused on libraries and YaST packages as well as documentation and nmap.

The snapshot from Tuesday, 20210330, updated an enormous amount of YaST translations and minor style adjustments and improvements were made with the yast2-theme 4.3.8 update. Extra validations were added to yast2-storage-ng 4.3.50 when creating a striped volume and when editing the physical volumes. The update to the 4.3.63 version of yast2-network brought about a dozen improvements to include adding support to write bridge and bonding configurations. Abstraction library libyui removed a dependency on Xlib and has a new packaging system in the update to 4.1.2. Network scanner nmap fixed a MySQL library that was not properly parsing responses in version 7.91 and the update of purple-lurch, which does secure multi-client end-to-end encryption, had some memory handling improvements in the 0.7.0 version update.

Topping the list of package updates for snapshot 20210329 was an update of setools 4.4.0 that added a configuration file driven analysis tool and Xfce file manager thunar 4.16.6 removed a dialog box and revamped documentation across components. A memory leak and an integer overflow fix was made in the update of checkpolicy 3.2. File system utility e2fsprogs 1.46.2 fixed warnings when resizing small file systems to a super-large ones. Spell checking library enchant 2.2.15 had some minor build system improvements and requires nuspell 4.1.0 or greater. Other packages that received updates were ffmpeg-4 4.3.2, perl-Net-HTTP 6.21, man-pages 5.11, rubygem-rspec-rails 5.0.1 and more.

The pixel encoding and color space conversion engine babl 0.1.86 had an a change to a profile for creating RGB spaces in snapshot 20210325. Xfce application library exo fixed a keyboard navigation item in its 4.16.1 update. A few Python Package Index updates were also made in the snapshot; a fix was made with LVM root volume inclusion in fstab with the update of python-kiwi 9.23.22 and mailing list management system python-mailman 3.3.4 improved the speed of members lookup via REST Application Programming Interface. Other packages updated in the snapshot were updates to libressl 3.2.5, mpg123 1.26.5 and the transactional-update 3.3.0 package used for MicroOS and Kubic added support for more package managers by bind mounting their directories; the package also linked the RPM database correctly with older zypper versions.

Google Pixel phones support what they call ”Motion Photo” which is essentially a photo with a short video clip attached to it. They are quite nice since they bring the moment alive, especially as the capturing of the video starts a small moment before the shutter button is pressed. For most viewing programs they simply show as static JPEG photos, but there is more to the files.

I’d really love proper Shotwell support for these file formats, so I posted a longish explanation with many of the details in this blog post to a ticket there too. Examples of the newer format are linked there too.

There are actually two different formats, an old one that is already obsolete, and a newer current format. The older ones are those that your Pixel phone recorded as ”MVIMG_[datetime].jpg", and they have the following meta-data:

Xmp.GCamera.MicroVideo                       XmpText     1  1
Xmp.GCamera.MicroVideoVersion                XmpText     1  1
Xmp.GCamera.MicroVideoOffset                 XmpText     7  4022143
Xmp.GCamera.MicroVideoPresentationTimestampUs XmpText     7  1331607

The offset is actually from the end of the file, so one needs to calculate accordingly. But it is exact otherwise, so one simply extract a file with that meta-data information:

#!/bin/bash
#
# Extracts the microvideo from a MVIMG_*.jpg file

# The offset is from the ending of the file, so calculate accordingly
offset=$(exiv2 -p X "$1" | grep MicroVideoOffset | sed 's/.*\"\(.*\)"/\1/')
filesize=$(du --apparent-size --block=1 "$1" | sed 's/^\([0-9]*\).*/\1/')
extractposition=$(expr $filesize - $offset)
echo offset: $offset
echo filesize: $filesize
echo extractposition=$extractposition
dd if="$1" skip=1 bs=$extractposition of="$(basename -s .jpg $1).mp4"

The newer format is recorded in filenames called ”PXL_[datetime].MP.jpg”, and they have a lot of additional metadata:

Xmp.GCamera.MotionPhoto                      XmpText     1  1
Xmp.GCamera.MotionPhotoVersion               XmpText     1  1
Xmp.GCamera.MotionPhotoPresentationTimestampUs XmpText     6  233320
Xmp.xmpNote.HasExtendedXMP                   XmpText    32  E1F7505D2DD64EA6948D2047449F0FFA
Xmp.Container.Directory                      XmpText     0  type="Seq"
Xmp.Container.Directory[1]                   XmpText     0  type="Struct"
Xmp.Container.Directory[1]/Container:Item    XmpText     0  type="Struct"
Xmp.Container.Directory[1]/Container:Item/Item:Mime XmpText    10  image/jpeg
Xmp.Container.Directory[1]/Container:Item/Item:Semantic XmpText     7  Primary
Xmp.Container.Directory[1]/Container:Item/Item:Length XmpText     1  0
Xmp.Container.Directory[1]/Container:Item/Item:Padding XmpText     1  0
Xmp.Container.Directory[2]                   XmpText     0  type="Struct"
Xmp.Container.Directory[2]/Container:Item    XmpText     0  type="Struct"
Xmp.Container.Directory[2]/Container:Item/Item:Mime XmpText     9  video/mp4
Xmp.Container.Directory[2]/Container:Item/Item:Semantic XmpText    11  MotionPhoto
Xmp.Container.Directory[2]/Container:Item/Item:Length XmpText     7  1679555
Xmp.Container.Directory[2]/Container:Item/Item:Padding XmpText     1  0

Sounds like fun and lots of information. However I didn’t see why the “length” in first item is 0 and I didn’t see how to use the latter Length info. But I can use the mp4 headers to extract it:

#!/bin/bash
#
# Extracts the motion part of a MotionPhoto file PXL_*.MP.mp4

extractposition=$(grep --binary --byte-offset --only-matching --text -P "\x00\x00\x00\x18\x66\x74\x79\x70\x6d\x70\x34\x32" $1 | sed 's/^\([0-9]*\).*/\1/')

dd if="$1" skip=1 bs=$extractposition of="$(basename -s .jpg $1).mp4"

UPDATE: I wrote most of this blog post earlier. When now actually getting to publishing it a week later, I see the obvious ie the ”Length” is again simply the offset from the end of the file so one could do the same less brute force approach as for MVIMG. I’ll leave the above as is however for the ❤️ of binary grepping.

^{(cross-posted to my other blog)}

Last week, we celebrated the 20th edition of Hack Week. During this time, we are meant to invest our working hours in any project we want: it can be related to your daily duties, something you want to learn or just a crazy experiment. It is up to you. But the idea is to foster collaboration and innovation. And it is not limited to SUSE; the openSUSE community is welcome to join us.

In this blog post, we would like to share some of the projects the YaST team members were working on, even if they are not related to YaST (and some of them aren’t). We encourage you to join the discussion if you are interested in any of them.

YaST Rake Tasks: Run GitHub Actions Locally

Let’s start presenting something that is actually related to YaST. Ladislav Slezak brought some cool stuff to Yast::Rake, a package that provides YaST developers with useful helpers for our daily tasks like running test suites, submitting new package versions and so on.

Now, containers are first class citizens for Yast::Rake, so it is possible to run YaST client directly on containers and, even better, run GitHub actions locally. If you are interested, you should readh Ladislav’s announcement on the mailing list.

Type Check YaST with Sorbet

Martin Vidner has been working for some time to bring type checking to YaST using Sorbet, a gradual type checker for Ruby. YaST is a rather big and old project, and given the dynamic nature of Ruby, we routinely get bug reports caused by typos, wrong method names, and so on.

At this point, we can check a big part of yast2-ruby-bindings.rpm and a small portion of yast2.rpm, which is a significant step forward. But if you want to know more about this promising project, do not hesitate to have a look at the project’s page.

QDirStat: Finding Files that are Shadowed by a Mount

QDirStat is a pretty neat application that helps you to know how your disk space is used, so you can keep your file system clean and tidy. Stefan Hundhammer, author and maintainer of QDirStat, has done extensive research to find files that are shadowed by a mount. The problem with those files is that they will occupy disk space, although they are not accessible.

As a result, Stefan has written a detailed document about that matter, including a nice script to help you in your quest for shadowed files.

gfxboot2: a graphical interface to bootloaders

Steffen Winterfeldt is one of our experts when it comes to system booting (and stack-based languages :wink:). During this Hack Week, he decided to work in gfxboot2, a rework of the original gfxboot that he maintains but written in C. In case you do not know, gfxboot is the software behind the graphical menu that you get when you boot an openSUSE installation medium.

If you are interested, read the project’s README because it contains useful information and a cat picture. :smiley:

UCMT: Unified Config Management Tool

Josef Reidinger has been exploring an interesting concept: the Unified Config Management Tool. This tool allows you to configure your local system and export the configuration to multiple machines. It is a pretty convenient way to go from local to 1:N management.

Wait a minute, it sounds like another configuration management tool, isn’t it? Well, not really. UCMT sits on top of those tools, and it is able to write the configuration in a way that can be used by Salt, Ansible, and so on. Moreover, the plan is to provide additional features like a good-looking user interface and a plugins system.

Do you want to know more? Then, check the project’s repository because it contains many ideas, use cases and even a screencast. You know, a picture is worth a thousand words.

Tracking Horses

Perhaps this is the most original project. Michal Filka started to work on a low-power GPS tracker for animals, although he targets horses at this point. He has identified several problems with the current solutions, so he decided to work on an alternative.

The project is still in the research phase, but it is worth reading his notes. Initially, he thought about using RFID, but lately, he decided to go for an Arduino-based solution adding a GPS or GPRS chip. Even an Android application is on his roadmap!

Even if most of us do not own any horse :smiley:, we cannot wait to see how this project evolves in the future.

Playing with WebAssembly

Finally, the rest of the team (Áncor González, David Díaz, Imobach González, Jose Iván López and Knut Andersen), decided to spent the Hack Week playing around with WebAssembly, the Rust programming language and JavaScript.

The project’s main goal was to see whether the WebAssembly’s promise of using the same compiled code in multiple environments was true. For that matter, they decided to write a simplified data model representing the network configuration, compile it to WebAssembly and try to use it from both environments.

They learned that things are not that straightforward, and you need to keep separate bindings for each platform. So, in the end, it might be better to use JavaScript bindings for the web and FFI based ones for the local system.

By the way, they took the opportunity to play with Glimmer, an interesting DSL framework for writing GUI applications in Ruby.

Closing Words

We are sure that this Hack Week was rather challenging for the organizers, as they needed to work-around the limitations imposed by the COVID-19 mess. But we think they did a great job! We had a lot of fun meeting our colleagues in Work Adventure, we enjoyed the social hours, learned a lot with the Rust Bootcamp and we managed to share our projects and collaborate with each other.

So thanks a lot to everyone involved!