Blog

  • Mac quick action – edit PDF documents

    I had another Automator / quick action post before: shrink images

    Problem: My health insurance has the stupidest billing letters imaginable, but at least they come as PDF. The 2nd page is the one you want, the first is useful for the letter’s date. Third and fourth pages are just useless.

    Solution: Another OS X Finder Quick Action using Automator, this time based on pdftk. It keeps pages two and one (in this order) and removes the rest.

    Prerequisites: pdftk (brew install pdftk-java)

    In case you don’t speak German:

    • Receive PDF files from Finder (the image shows “all applications” though – if you want you can do that as well)
    • Files come as script arguments

    Here we go. Automator config:

    And the script for copy-pasting.

    export PATH=$PATH:/usr/local/bin
    
    # now we iterate over a script's arguments, not using stdin as file input
    for f in "$@"
    do
        # ONLY NEEDED IF YOU WANT TO KEEP BACKUPS. Since we're modifying PDF
        # file contents, of files that could be important, I'm paranoid.
        BASE=${f%.*}
        EXT=${f##*.}
        ORIG_FILE="$BASE.orig.$EXT"
        COUNT=0
        while [ -f "$ORIG_FILE" ] ; do
            COUNT=$(($COUNT + 1))
            ORIG_FILE="$BASE.orig.$COUNT.$EXT"
        done
        # HERE IS THE MAGIC.
        # you need pdftk installed (brew install pdftk - probably)
        mv "$f" "$ORIG_FILE"
        pdftk A="$ORIG_FILE" cat A2 A1 output "$f"
    done
    
  • “Old” K8S API reference docs

    I don’t know what happened, but old K8S API reference docs are unavailable from the “original” kubernetes.io website. Try it out, prove me wrong. I still don’t believe it.

    Some netlify-guy to the rescue:

    k8s-dev-ko.netlify.app/…/v1.18 <– open link & change version here.

    Some more info:

    Or just click here:

    You’re welcome.

    (Links working as of 2022-09-03)

    Updates:

    • 2022-09-03: added v1.25
    • 2022-07-20: added v1.24
  • Postgres – restore database with different owner

    Situation:

    • You have a database
    • You want to “clone” this database into another database
    • That one should get a different owner

    Go.

    Step one, dump the database.

    pg_dump -h database.host            \
      -U database_user                  \
      -W                                \
      -O -Ft                            \
      database                          \
      > database.dump.tar
    

    Important here is -O, which means --no-owner, which means “do not include owner information.

    Step two, restore the database. The database in which the dump file is restored has to exist. If you don’t know how to create it, see here.

    pg_restore                          \
      -h database.host                  \
      -U different_database_user        \
      -W                                \
      -d different_database             \
      --no-owner                        \
      --role=different_database_user    \
       -Ft                              \
       database.dump.tar
    

    DISCLAIMER: I have no idea if there is a better way. That just worked.

  • “Fix” Raindrop addon in Firefox

    If you want to use a keyboard shortcut for the Raindrop addon in Firefox, and you keep getting “Please log in” windows, do this:

    https://help.raindrop.io/login-problems/#add-raindropio-to-your-browsers-whitelist

    Or copy-pasted here:

    • open settings
    • go to “Privacy & Security”
    • go to “Cookies and Site Data”
    • click on “Manage Permissions”
    • add https://api.raindrop.io

    Done.

    firefox preferences with raindrop exception

  • Mac quick action – shrink images

    Problem: A list of images, which are big. And one wants to make them smaller, maybe for the web.

    Solution: A Mac Automator quick action.

    Prerequisites: GNU parallel, ImageMagick (both via homebrew)

    You …

    • open Automator
    • create a new quick action
    • add Shell script action
    • paste in the script below
    • make sure arguments are passed via stdin

    automator quick action for shrinking images

    export PATH=$PATH:/usr/local/bin
    export size=1280
    
    # that SHOULD work, but DOES NOT (at least not on my machine)
    #parallel -a - convert {1} -resize "${size}x${size}\>" -quality {2} {1.}.${size}px.{2}.{3} ::: 50 80 ::: webp jpg
    
    # that works.
    TMP=$(mktemp)
    cat > "$TMP"
    parallel -r convert {1} -geometry "${size}x${size}\>" -quality {2} {1.}.${size}px.q{2}.{3} :::: "$TMP" ::: 50 80 ::: webp jpg
    rm "$TMP"
    

    … or use the easier to understand, non-parallel version:

    export PATH=$PATH:/usr/local/bin
    export size=1280
    
    while read f ; do
      for ext in webp jpg ; do
        for q in 50 80 ; do
          convert "$f" -resize ${size}x${size} -quality $q "${f%.*}.$size.q$q.$ext"
        done
      done
    done
    
  • Location of Mac “Quick Actions”

    I created a “quick action” with Automator on my Mac. Once saved, there are a few ways you can find them:

    • “Open” in Automator, then choose “quick actions” in the “type” dropdown
    • Look in $HOME/Library/Services

    That’s about it.

  • Excel – convert columns to comments

    Situation: You have table rows containing short headings, and the cell next to it a long text description. You want to “convert” the description column into comments on the “heading” cells to make the table smaller or save vertical space. Maybe a two images explain that better.

    So we want to go from “before” …

    setup screen

    … to “after” …

    setup screen

    Now this is the Excel VBA code to achieve this:

    Sub convert_to_comment()
    ' convert_to_comment Makro
    ' Tastenkombination: Strg+k
        ' step 1 - create (VISIBLE!) comments
        ' visible because excel has a bug. yeah ... .
        Dim commentText As String
        ActiveCell.Comment.Delete
        commentText = ActiveCell.Offset(0, 1).Text
        ActiveCell.AddComment commentText
        ActiveCell.Comment.Visible = True
        ActiveCell.Comment.Shape.Width = 450
        ActiveCell.Comment.Shape.Height = 150
        ActiveCell.Offset(1, 0).Select
    
        ' make comments invisible in step 2
        ' just comment all lines from step1, and uncomment all of the following
        ' lines, and re-run the macro with each cell. see notes below in the
        ' blog post.
        'ActiveCell.Comment.Visible = False
        'ActiveCell.Offset(1, 0).Select
    End Sub
    

    Some notes:

    • Excel does not seem to support auto-sized comments since v2016. Don’t get me started on how much I “dislike” this pile of … whatever.
    • At least Excel v16.55 has a bug which makes this 2-step-approach necessary (otherwise the comment boxes just won’t show).
  • Migrating Postgres databases in containers

    Run-through

    You should have two directories next to each other – one is the “old” postgres data dir, one is the new one. My setup was this:

    • /srv/postgres/data/10/data
    • /srv/postgres/data/14/data

    First, initialize the data dir of the “new” version:

    # note: mounting -v /.../14:/var/lib/postgresql will _NOT_ work.
    # no idea why. some volume issue.
    docker run --rm \
        -v /srv/postgres/data/14/data:/var/lib/postgresql/14/data \
        -e PGDATA=/var/lib/postgresql/14/data \
        -e POSTGRES_USER=postgres \
        -e POSTGRES_PASSWORD=postgres \
        postgres:14
    

    The migration afterwards is as simple as this:

    docker run --rm \
        -v /srv/postgres/data:/var/lib/postgresql \
        tianon/postgres-upgrade:10-to-14 --link
    

    The docker image tag (here: 10-to-14) describes the migration step.

    Source: tianon/docker-postgres-upgrade.

    Troubleshooting

    Unclean exit

    docker run -v $(pwd):/var/lib/postgresql --rm -u 999 -ti postgres:10 /bin/bash
    

    Source: Also somewhere in the websters.

    Adjust database collations

    $ docker exec -ti -u 999 silly_wescoff psql
    postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='postgres';
    postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template0';
    postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template1';
    

    Source: Stackoverflow.

    Re-set passwords

    $ docker exec -ti -u 999 postgres_container_name psql
    postgres=# \password nextcloud
    Enter new password:
    Enter it again:
    postgres=# _
    

    Source: Stackoverflow.

  • VS Code keymaps in Sublime Text

    I am used to VS code keymaps now, but I keep realizing how utterly fast Sublime Text is compared to the Microsoft behemoth. So I am gradually switching over, sloooowly, for some tasks.

    A major pain was my muscle memory. Here’s the start of my solution:

    [
      { "keys": ["alt+up"], "command": "swap_line_up" },
      { "keys": ["alt+down"], "command": "swap_line_down" },
    
      { "keys": ["super+alt+up"], "command": "select_lines", "args": { "forward": false } },
      { "keys": ["super+alt+down"], "command": "select_lines", "args": { "forward": true } },
    
      { "keys": ["shift+alt+up"], "command": "duplicate_line" },
      { "keys": ["shift+alt+down"], "command": "duplicate_line" }
    ]
    

    (And yes, the final comma is actually parsed correctly by Sublime)

  • Create user and database in postgres

    Since I always google this. Here now a couple of methods.

    Method 1 – create and grant all privileges

    CREATE DATABASE yourdbname;
    CREATE USER youruser WITH PASSWORD 'yourpass';
    GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
    
    # for PG 15, you need to do more now (https://stackoverflow.com/a/75876944)
    \c yourdbname adminuser
    GRANT ALL ON SCHEMA public TO youruser;
    

    The owner of the database will not be “youruser”. This seems to be just fine in 99% of all cases. If you need database ownership for “myuser”, read on.

    Method 2 – create with a certain owner

    CREATE USER myuser WITH PASSWORD 'mypassword';
    GRANT myuser TO your_current_admin_user;
    CREATE DATABASE mydatabase WITH OWNER myuser ;
    

    DISCLAIMER: I have no idea what the 2nd line does and whether this is a good idea. It just worked.

    Note on encryption

    Note: Sometimes you read CREATE USER ... WITH ENCRYPTED .... Starting with Postgres 10 the ENCRYPTED keyword has no longer any significance:

    The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. (v10)

    Whereas the docs of Postgres 9.6 state:

    These key words control whether the password is stored encrypted in the system catalogs.

    UPDATES:

    • 2022-03-24: Added with owner creation
    • 2023-05-30: postgres 15 update