Neues vom PostgreSQL Planet

Invitation from Dian M Fay

The host for this month’s event is Dian Fay, someone I’ve only met virtually because of some excellent content Dian has published for previous PGSQL Phriday blogging events.

SFTP support has been added in the 2.46 release on 22 May 2022.

In this demo setup, the SFTP host will be called sftp-srv and the PostgreSQL node pg-srv. Both nodes will be running on Rocky Linux 8.

If you’re familiar with Vagrant, here’s a simple Vagrantfile to initiate 3 virtual machines using those names:

I'm sure you already know what I'm going to tell you: "Of course you need that backup_label file. How could you even think that you don't need that backup_label file?" Well, you're right. That is what I'm going to say. But do you know why you need that backup_label file? If you were to remove that backup_label file (or fail to create in the first place, in cases where that is your responsibility), what exactly is the bad thing that would happen to you?

Your tables have to be much bigger for performance tests than they are typical in your development or staging environments. Writing run-once scripts to fill a table is the most used approach to solve that problem. But either it will take very long by inserting one row after another, or the development gets more complex by implementing optimal chunked inserts. You can just write a single SQL query to insert an arbitrary amount of rows all at once and let the database make it fast.

The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let’s take a look at some exciting new updates it introduced.

I hope that this review together with the previous articles in the series (2022-07, 2022-09, 2022-11, 2023-01) will give you a coherent idea of the new features of PostgreSQL 16.

Last month I was invited to give a keynote talk at the CUGOS Spring Fling, a delightful gathering of “Cascadia Users of Open Source GIS” in Seattle. I have been speaking about open source economics at FOSS4G conferences more-or-less every two years, since 2009, and took this opportunity to somewhat revisit the topics of my 2019 FOSS4GNA keynote.

It's almost Phriday again! This is a monthly blogging event for the PostgreSQL community. The rules:

PostgreSQL Person of the Week Interview with Florin Irion: I was born and raised in Romania in a little town called Campulung. When I was 20 years old I came to Italy, to Prato, where I started working in the hospitality industry, eventually becoming a Chef.

In a previous life, I worked on a CRM system that really loved the idea of tags. Everything could be tagged, users could create new tags, tags were a key organizing principle of searching and filtering.

The trouble was, modeled traditionally, tags can really make for some ugly tables and equally ugly queries. Fortunately, and as usual, Postgres has an answer.

Today I’m going to walk through working with tags in Postgres with a sample database of 🐈 cats and their attributes

We had a meetup on May 17, and I am really happy with how it went. It was the first time since the pandemic that we had local speakers talking about their experiences.

Before the pandemic, I tried to make every other event “made in Chicago,” which appeared to be the most difficult to revive, and I am glad we got there. I am also very happy that we had new PUG members: people who are starting to use Postgres and are looking for resources, guidance, and advice. And a place where they could ask questions.

I wrote about using the H3 extension last year in Using Uber's H3 hex grid in PostGIS and H3 indexes for performance with PostGIS data. Naturally, things have changed over the past 12 months, specifically version 4 of the H3 Postgres extension was released.

Backing up your PostgreSQL database is a critical task for ensuring the safety and availability of your data. In the event of a hardware failure, software error, or other disaster, having a recent backup of your database can mean the difference between a brief outage and a catastrophic data loss. In this blog post, we’ll […]

The post PostgreSQL Backup Best Practices appeared first on Stormatics.

Introduction

Recently I had an opportunity to look into deploying PostgreSQL and pgpool on Kubernetes. The deployment is straightforward, but I also need to obtain the metrics information such as CPU and memory usages that each deployed pod is using. There are several ways to do this, but today I am sharing my way, which utilizes k8s’s native metrics server + cronjob to achieve.

I have just completed the first draft of the Postgres 16 release notes. It includes developer community feedback but still needs more XML markup and links.

A question I hear from time to time with Crunchy Data clients and the Postgres community is:

When was my Postgres database table created?

Postgres does not store the creation date of tables, or any other database object. But fear not, there are a plethora of direct and indirect ways to find out when your table creation happened. Let's go through some ways to do this, ranging from easy to somewhat hard. All these solutions apply to indexes and other database objects, but tables are by far the most common request.

In the first two articles of this series about PostgreSQL privileges, we reviewed how to create roles, grant them privileges to database objects, and how object ownership is an important aspect in managing access and control within the database.

A look at how PostgreSQL handles multi-dimensional arrays.

Multi-Dimensional Arrays in PostgreSQL

PostgreSQL supports arrays of various types, and handles also multi-dimensional arrays. Except that it does not support multi-dimensional arrays!

Allow me to better explain. Multi-dimensional arrays are just an array that contains other arrays. In this sense, PostgreSQL does not provide a pure native multi-dimensional array, even if you can specify them.

Let’s see this in action by means of pg_typeof:

pgVis is a PostgreSQL extension for building simple visualisation dashboards with SQL. pgVis aims to make it easy to express data visualisations directly from SQL queries. Letting you quickly visualise some data for adhoc reports in psql or to build and share reporting dashboards in your organisation via pgvis-server. Either way, pgVis is designed to be PostgreSQL centric and to fit with your existing database workflows.

This is a short story about how quickly charts can be created between the handful of plotting engines that I've experimented with.

Over the years I've used a few different plotting engines for visualizing system characteristics for various performance work efforts.  The first requirement is to use tools that can be scripted as part of the work, then deciding between how the charts look and how long it took to generate the charts.

2023 June 15th Meeting 6:30pm:8:30pm

Location:

PDX Code Guild
407 NE 12th Avenue, Portland, OR 97232
The parking lot is behind the building on Flanders.