Neues vom PostgreSQL Planet

TRAKTOR now has the capability to analyze the PostgreSQL server logs through file_fdw or log_fdw, without requiring direct access to the server filesystem any more.

Postgres 16 includes new columns in pg_stat_&ast_tables to record the last sequential and index scans on tables, and a new column in pg_stat_&ast_indexes to record the last index scan for specific indexes.

As a seasoned C/C++ developer, I’ve always considered GDB (GNU Debugger) as my “best friend” in software development due to its indispensable role in assisting the development and debugging process. With this powerful tool, a developer can:

Thanks to DRW, all our meetups are now recorded and uploaded in a timely manner, so the October 15 meetup recording is available!

Don’t miss the last meetup of 2023 on November 23! Come to listen to Dian Fay’s presentation: Exploring Postgres Databases with Graphs! RSVP here!

Another little improvement to the interface for pgagroal

pgagroal new commands: ‘ping’ and ìstatus details’

When I committed the major command refactoring in pgagroal-cli, I introduced also a simple way to deprecate a command, so that the user running the old version of a command is warned about switching to the new interface.

In 2014, someone in Korea requested that we add a query_id to pg_stat_activity to match the queryid in pg_stat_statements. This would allow administrators to get duration estimates of currently running queries.

Whenever you actively use transaction blocks in PostgreSQL “ERROR: current transaction is aborted, commands ignored until end of transaction block” is by far most often seen error message of them all. However, this does not imply that it is also the most widely understood error message of them all. This blog will hopefully provide some enlightenment to those out there who want to understand what is really going on, who want to know what this message means and how it actually works.

pg_cron is an open source PostgreSQL extension that provides a cron-based scheduler to periodically run SQL commands. Almost every managed PostgreSQL service supports pg_cron and it has become a standard tool for many PostgreSQL users. Since Citus has been my full-time job, pg_cron has always been a side project for me, and so I tried to architect it for simplicity, reliability, and low maintenance.

Learn how Explain plans can empower you to understand and optimize PostgreSQL queries.

The post Understand Explain Plans in PostgreSQL appeared first on Stormatics.

FOSDEM PGDay 2024 is a one day conference that will be held ahead of the main FOSDEM event in Brussels, Belgium, on February 2nd, 2024. This will be a one-day PostgreSQL focused event with a single track of talks. This conference day will be for-charge with a registration of EUR 60, and will be held at the Brussels Marriott Hotel. Registration is required to attend and since we have a limited number of seats available for this event, we urge everybody to register as soon as possible once open.

It's great to see creative work based on Postgres, and I know of two songs based on Postgres:

• Explain Analyze
• Nothing Compares To VACUUM

They were done a few years ago but are still enjoyable. I am not sure I am going to hear Feliz Navidad the same ever again.

I want to share my concerns about Planet PostgreSQL content. Some authors’ posts use spam and black SEO techniques, whether consciously or not.

What are the essential properties of such posts? These “posts” are short, contain external links, and without any meaningful supplement.

Couple of months ago I wrote how to do human sort in Pg by using arrays, and splitting string. This works, but Matt mentioned in comments that it could be done with ICU collations. So I looked into it … First, I figured I'll write longer blogpost about collations, but to be honest – I … Continue reading "Human/version sort in PostgreSQL – revisited"

On 16th of October 2023, Alexander Korotkov committed patch: Add support event triggers on authenticated login   This commit introduces trigger on login event, allowing to fire some actions right on the user connection. This can be useful for logging or connection check purposes as well as for some personalization of environment. Usage details are … Continue reading "Waiting for PostgreSQL 17 – Add support event triggers on authenticated login"

Modern resilient data infrastructure ensures that there's not a single point of failure. In the cloud, this means eliminating single points of failure at the data center and availability zone. So when deploying highly available Postgres in Kubernetes, you will want to ensure that each availability zone has one or more Postgres pods running in it.

Someone on X wanted to know how to get an anonymous dump of Postgres data, but doesn't want to install an extension in their production DB. I want to show how you can start a local database, dump the production data there, then do an anonymized dump from that without too much hassle.

Getting set up​

Dockerfile:

I have written extensively about the performance benefits of allowing caches to be in write-back mode, rather than write-through mode, and for databases this is only safe by preventing cache data loss during power failure.

We’re excited to unveil pg_timetable v5.6, a significant release that takes your PostgreSQL job scheduling to a new level. In this release, we’ve introduced several key enhancements and features that empower you to manage and automate your database tasks precisely and flexibly.

A recap on how to comile pgBackRest on Amazon Linux.

Installing pgBackRest on Amazon Linux (by sources)

I had the need to install pgBackRest on Amazon Linux machines.

Unluckily, even if Amazon Linux 2023 is a Red-Hat like operating system, the official PGDG repository did not install in any version. Therefore, I decided to install from sources, compiling the latest 2.48 version.

A little trick to monitor your own running transaction in term of time and data size.

Using psql Variables to Introspect Your Script

psql is by far my favourite SQL text client, it has features that even the most expensive database tools provide. One very interesting property of psql is to support internal variables, pretty much like the variables you can find in a shell.