Skip to content

Relational and Non-relational Data: PGSQL Phriday #005

Ryan Lambert asks in this month's PGSQL Phriday:

  • What non-relational data do you store in Postgres and how do you use it?
  • Have you attempted non-relational uses of Postgres that did not work well? What was the problem?
  • What are the biggest challenges with your data, whatever its structure?
  • Bonus: How do you define non-relational data?

Looking over the many databases we operate at work, I can say that we are mostly a "relational shop". Almost every database we have uses relational structures, and does not attempt to go in on "full NoSQL", or "store non-relational data". Many of the databases are on the larger size, often in the hundreds of GB or even several TBs. We have good people in our dev teams who understand both PostgreSQL and SQL, and can develop a proper schema for the data, and write performant queries.

There are a few exceptions though, but at least for us it's only a small number. Whenever we retrieve or get data which comes in from web requests, we may store this as JSON, and then we use JSONb. Quite often however it's rather "storage as JSON", not "querying the JSON in the database". It's just more convenient to keep the data in JSON format all the way, instead of transforming it several times.

 

Continue reading "Relational and Non-relational Data: PGSQL Phriday #005"

Fix LXC network issues in Ubuntu 22.04

Ran into a curios problem while updating the GitHub Actions Workflow for a project:

Run sudo lxc exec test-container --env DEBIAN_FRONTEND=noninteractive -- apt-get -y install -y openssh-client openssh-server openssh-sftp-server
Reading package lists...
Building dependency tree...
Reading state information...
openssh-client is already the newest version (1:8.9p1-3).
The following additional packages will be installed:
  libpsl5 libwrap0 ncurses-term publicsuffix python3-distro ssh-import-id wget
Suggested packages:
  molly-guard monkeysphere ssh-askpass ufw
The following NEW packages will be installed:
  libpsl5 libwrap0 ncurses-term openssh-server openssh-sftp-server
  publicsuffix python3-distro ssh-import-id wget
0 upgraded, 9 newly installed, 0 to remove and 0 not upgraded.
Need to get 1371 kB of archives.
After this operation, 7679 kB of additional disk space will be used.
Ign:1 http://archive.ubuntu.com/ubuntu jammy/main amd64 openssh-sftp-server amd64 1:8.9p1-3

...

Ign:9 http://archive.ubuntu.com/ubuntu jammy/main amd64 ssh-import-id all 5.11-0ubuntu1
Err:1 http://archive.ubuntu.com/ubuntu jammy/main amd64 openssh-sftp-server amd64 1:8.9p1-3
  Cannot initiate the connection to archive.ubuntu.com:80 (2620:2d:4000:1::19). - connect (101: Network is unreachable) Cannot initiate the connection to archive.ubuntu.com:80 (2620:2d:4000:1::16). - connect (101: Network is unreachable) Cannot initiate the connection to archive.ubuntu.com:80 (2001:67c:1562::18). - connect (101: Network is unreachable) Could not connect to archive.ubuntu.com:80 (185.125.190.39), connection timed out Could not connect to archive.ubuntu.com:80 (185.125.190.36), connection timed out Could not connect to archive.ubuntu.com:80 (91.189.91.39), connection timed out

...

E: Failed to fetch http://archive.ubuntu.com/ubuntu/pool/main/s/ssh-import-id/ssh-import-id_5.11-0ubuntu1_all.deb  Cannot initiate the connection to archive.ubuntu.com:80 (2620:2d:4000:1::19). - connect (101: Network is unreachable) Cannot initiate the connection to archive.ubuntu.com:80 (2620:2d:4000:1::16). - connect (101: Network is unreachable) Cannot initiate the connection to archive.ubuntu.com:80 (2001:67c:1562::18). - connect (101: Network is unreachable)
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?
Error: Process completed with exit code 100.

For the tests, I'm spinning up a LXC container on the Runner, and then try to install software in it. This specific Runner is using Ubuntu 22.04 (new), and the network connection to archive.ubuntu.com is failing. Another Runner in the same workflow, using Ubuntu 20.04, is working fine. 20.04 was the old test setup, 22.04 is the new one. No other changes. But why is it suddenly failing?

 

Continue reading "Fix LXC network issues in Ubuntu 22.04"

GitHub Actions: Node.js 12 actions are deprecated

If you use GitHub Actions to run Workflows and tests, you might have spotted this warning recently:

Node.js 12 actions are deprecated. For more information see: https://github.blog/changelog/2022-09-22-github-actions-all-actions-will-begin-running-on-node16-instead-of-node12/. Please update the following actions to use Node.js 16: actions/checkout@v2

This warning means that GitHub will deprecate a certain action, which checks out the repository into the runner. This is going on since early 2022 and by summer 2023 they plan to upgrade all actions to v16.

 

Continue reading "GitHub Actions: Node.js 12 actions are deprecated"

GitHub Actions: The `set-output` command is deprecated and will be disabled soon

If you use GitHub Actions to run Workflows and tests, you might have spotted this warning recently:

The `set-output` command is deprecated and will be disabled soon. Please upgrade to using Environment Files. For more information see: https://github.blog/changelog/2022-10-11-github-actions-deprecating-save-state-and-set-output-commands/

This warning means that GitHub will deprecate a certain syntax which populates variables, and disables it by end of May 2023.

 

Continue reading "GitHub Actions: The `set-output` command is deprecated and will be disabled soon"

Install Firefox PPA version over Snap version

It was time to update my laptop, and I already knew that the update will bring Snap, and installs the Snap Firefox version. Along with many known problems. Previously the laptop was on 20.04 LTS, but this version is about to loose support.

I ran through the upgrade, and then added an Ansible Playbook to handle the Firefox installation, remove the Snap version and install the PPA version. Most of my laptop configuration is handled using Ansible Playbooks.

 

 

Continue reading "Install Firefox PPA version over Snap version"

PGSQL Phriday #004: PostgreSQL and Software Development

Henrietta Dombrovskaya asks about Software Development and PostgreSQL. That's a very broad topic.

Not being a big developer for myself, and only occasionally submitting patches here and there, I nevertheless have a vast amount of data available on the topic: the "PostgreSQL Person of the Week" interviews! There are a few questions in the interviews which relate to this topic:

  • Could you describe your PostgreSQL development toolbox?
  • Which skills are a must have for a PostgreSQL developer/user?
  • Do you use any git best practices, which makes working with PostgreSQL easier?
  • Do you think PostgreSQL has a high entry barrier?
  • What is your advice for people who want to start PostgreSQL developing - as in, contributing to the project. Where and how should they start?

And having 143 published interviews at this point, there is really interesting data about this topic. I picked two points which aim directly at developers:

 

 

Continue reading "PGSQL Phriday #004: PostgreSQL and Software Development"

Using fail2ban to block unfriendly web requests

Every time I peek into the webserver logfiles, I find quite a few 404 requests trying to figure out if certain exploits exist on this server. Now I get that these are automated attempts, and the number of requests coming from one IP show that they try several different exploits and path names. Nevertheless I thought that I don’t need this in my log, and on my webserver. fail2ban for the rescue.

 

Continue reading "Using fail2ban to block unfriendly web requests"

PGSQL Phriday #003: What is the PostgreSQL Community to me

Pat Wright is running this month’s #PGSQLPhriday, and the topic is “What is the PostgreSQL Community to you?

The answer for me is very versatile, and covers quite a lot. Took me a moment to gather the facets, and likely I’m still missing some parts.
 

 

 

Continue reading "PGSQL Phriday #003: What is the PostgreSQL Community to me"

Migration from Twitter to Mastodon

Many people (I don’t like this phrase) are leaving Twitter these days, and looking for a new social media home. One of these places is Mastodon. This blog post aims to summarize the steps necessary for a migration, and includes pointers to websites which can help with said move.

 

Image & CC: https://www.pexels.com/photo/3-grey-elephants-under-yellow-sky-68550/

 

What is Twitter?

Twitter: @ascherbaum

Twitter is a Microblogging service. Users post short texts with maximal 140 280 characters, optionally including media attachments. Tweets (that’s the name for the posting) are public by default, however Twitter implemented the ability to protect accounts (make the content private to followers only), or recently implemented functionality to target specific user groups for Tweets. In October 2022 Elon Musk completed the acquisition of Twitter, and took over as CEO. The following weeks have seen erratic and dramatic changes, which are not well-received by all users. Quite a number of users decided to leave Twitter. In addition, as a consequence of the turmoil some companies stopped doing advertisements on Twitter. The future will show if the users and advertisers will come back.

Users have a unique username on the platform, mine is @ascherbaum.

 

What is Mastodon?

 

Mastodon: @ascherbaum@mastodon.social

Mastodon is a Microblogging, which in contrast to the centralized Twitter, runs on decentralized (federated) instances (servers). The instances communicate with each other. The software is open source, and the project started around 2016.

Postings in Mastodon are named Toots, not Tweets. Or Trööt in German. Please let me know the word in other languages, I will update this posting.

After Elon Musk took over at Twitter, users started to migrate to Mastodon as an alternative, and every controversial announcement shows a new wave of users leaving. This will likely keep going for quite a while.

Mastodon users have a unique username on one instance, however the same username on a different instance can be used by someone else. There is no universal verification across instances, instances might implement their own verification. For example the social.bund.de instance is only open to other federal agencies of the German government - therefore every account on this instance is already validated as a government account.

My Mastodon account (the one I’m currently using) is ascherbaummastodon.social. The software allows users to move to new instances and migrate followers over, check the profile settings of your instance how to do that.
 

 

Continue reading "Migration from Twitter to Mastodon"