Postgres maintenance: vacuum hell, cluster to the rescue

I have an OpenNMS server (a PC really) sitting under a desk at a company that I don't hear from often, but I still get weekly backup mail-recaps.
This week the recap brought bad news: postgres stopped accepting connections until the db is vacuumed to prevent transaction id wraparound.

I have know of this issue of postgres for some time and planned accordingly: a cron job every month would run a full vacuum. Unfortunately in one month the db grew so much that vacuum never reached completion because the customer simply rebooted the server thinking that it was 'stuck'.

This vacuum-stuck-reboot had gone on for months until today. As I knew from prior experience this was going to be a painful experience: a quick du on the database folder reported 60GB of data. Vacuum would probably take days.

Luckily for me I had heard from the great Postgresql 9.0 high performance book that there is another tool in the toolbox called cluster.
So I decided to execute this plan this time:

  1. delete old events, alarms and notifications (by far the largest tables)
  2. cluster each table (including the 5 largest)
  3. vacuum full
Well, believe it or not the whole process took no longer that a couple of hours, most of which spent deleting records. I am not kidding you when I say that other times I have spent days waiting for vacuum to end.
And best of all the database shrunk from 60GB to 7.9GB! Most of the shrinking happened already after clustering.


Popular posts

Opengrep quickstart

Since I could not find a quickstart to run opengrep with the full set of rules from their fork I thought I'd document what I found out. Setup Download the opengrep binary from github and make it executable with chmod +x . Clone the rules repo: git clone git@github.com:opengrep/opengrep-rules.git and clean it up to make it usable to opengrep: cd opengrep-rules rm -rf ".git",".github",".pre-commit-config.yaml", "elixir", "apex" find . -type f -not -iname "*.yaml" -delete rm -rf .github rm -rf .pre-commit-config.yaml Ensure opengrep can load the rules with: opengrep_manylinux_x86 validate . The same can be done for custom rules maintained in a separate repository. AFAIU Multiple repositories can be specified by repeating -f option as needed, see below. We are now ready to scan a repo, from the repo root directory run: opengrep_manylinux_x86 scan \   -f <path_to>/opengrep-rules \   --error \   --exclude-rule=VAL some ti...
Read more

Mirth: recover space when mirthdb grows out of control

I was recently asked to recover a mirth instance whose embedded database had grown to fill all available space so this is just a note-to-self kind of post. Btw: the recovery, depending on db size and disk speed, is going to take long. The problem A 1.8 Mirth Connect instance was started, then forgotten (well neglected, actually). The user also forgot to setup pruning so the messages filled the embedded Derby database until it grew to fill all the available space on the disk. The SO is linux. The solution First of all: free some disk space so that the database can be started in embedded mode from the cli. You can also copy the whole mirth install to another server if you cannot free space. Depending on db size you will need a corresponding amount of space: in my case a 5GB db required around 2GB to start, process logs and then store the temp files during shrinking. Then open a shell as the user that mirth runs as (you're not running it as root, are you?) and cd in...
Read more

From 0 to ZFS replication in 5m with syncoid

The ZFS filesystem has many features that once you try them you can never go back. One of the lesser known is probably the support for replicating a zfs filesystem by sending the changes over the network with zfs send/receive. Technically the filesystem changes don't even need to be sent over a network: you could as well dump them on a removable disk, then receive  from the same removable disk.
Read more