Month: April 2021
TLS: Demystifying communication encryption in PostgreSQL
In this post, we want to give you a brief introduction to TLS (Transport Layer Security), which is a technology widely used in combination with PostgreSQL to encrypt client / server connections. What is TLS? TLS is short for “Transport Layer Security“, which is a means of making sure that whatever data you are sending […]
PostgreSQL: Understanding deadlocks
Many might have seen PostgreSQL issue the following error message: “ERROR: deadlock detected”. But what does it really mean? How can we prevent a deadlock and how can we reproduce the problem? Let’s dive into PostgreSQL locking and understand what deadlock and deadlock_timeout really mean. How does a deadlock happen? Many people approach us because […]
What to return from a PostgreSQL row level trigger?
© Laurenz Albe 2021 In this article, I’ll talk about row level triggers, which are the most frequently used kind of triggers. I will describe what the return value of the trigger function means and suggest a useful code simplification. Triggers in PostgreSQL A trigger in PostgreSQL consists of two parts: a trigger function the […]
Finally – a system level “read all data” role for PostgreSQL!
I don’t usually post about upcoming PostgreSQL features and rather concentrate on tools available / versions released… but this feature got me excited and will certainly be a huge relief for real-life usage, especially for beginners! I had almost lost hope we might see this day – but after many years of wishing for it, […]
Cleaning up a large number of BLOBs in PostgreSQL
PostgreSQL allows end users to store BLOBs (binary large objects) in the database. Many people use these functions to handle data directly in SQL. There has long been a discussion about whether this is a good thing or not. We expect that particular discussion to go on indefinitely. However, if you are in the “pro […]
Getting started with QGIS, PostgreSQL and PostGIS
Visualizing spatial data is an important task. In this mini-tutorial, you will learn how to quickly visualize OpenStreetMap (OSM) data with PostGIS and QGIS. The idea is to get you started as fast as possible, and to make the most out of your data. Installing QGIS QGIS is freely available as Open Source software and […]
PostgreSQL: Get member roles and permissions
PostgreSQL provides a highly sophisticated and powerful security and permission system. It allows you to define users (= roles), groups and so on. However, without a graphical user interface, it is usually a bit tricky to figure out which role is assigned to whom. The following blogpost explains how this can be done. Learn how […]