Neues vom PostgreSQL Planet

In this blog we demonstrate how to deploy PostgreSQL or EDB Advanced Server in the EDB BigAnimal Cloud using pgAdmin.

There are several ways to build a sharded database on top of distributed postgres instances. One of the most interesting and general approach is a built-in support for sharding. Historically postgres has fdw and partitioning features that can be used together to build a sharded database. There were concerns in the past about adoption them for a complete solution. We will review the current state of postgres fdw along with patches, that fix some significant bottlenecks in the planner and demonstrate the latest results in TPC-C with comparison to existing sharding solutions.

For a long time I was looking for SQL pretty printer.

In September 2021, a patch for the upcoming PostgreSQL version 15 was committed which introduces a visible change for users: the CREATE privilege for the public schema is no longer set by default. This is a recommendation from CVE-2018-1058.

What does that mean for the average (non-superuser) user?

Running ALTER TABLE … ADD COLUMN can have side effects which have the potential to cause serious issues in production. Changing data structures is an important issue and happens often, therefore it’s important to understand what is really going on.

Let’s dive in and see how to run ALTER TABLE … ADD COLUMN in a way that doesn’t hurt operations due to locking or extensive I/O.

Getting started with ALTER TABLE… ADD COLUMN

The following statement creates a simple table which is going to serve as a test dummy for the operations we plan to run:

PostgreSQL Person of the Week Interview with Douglas Hunley: My name is Douglas Hunley, and I live outside Columbus, OH (USA). I’m a former Oracle Jr DBA turned Unix Sys Admin, turned EDI Administrator, turned Linux Sys Admin, turned PostgreSQL DBA who is now focused on PostgreSQL security and Ansible automation of highly-available PostgreSQL.

This week the PostgreSQL pet is a lovely Hen presented by Elizabeth Garrett Christensen.

The current hype is to put PostgreSQL databases into Kubernetes. Obviously it is adding another layer on top of the stack.
That is something, that one always should have in mind, especially, if it is planned, to have it running in production.

The PostGIS Team is pleased to release PostGIS 3.3.0.

Anyone trying the upcoming PostgreSQL 15 might observe that one of the background processes is missing.

This blog introduces a new benchmark framework for performance testing of PostgreSQL and EDB Postgres Advanced Server. Based on Ansible, it offers a highly configurable and easy to use way to run pgbench, TPROC-C, and TPROC-H tests across multiple servers at once in a consistent and reproducible manner. PostgreSQL and the operating system kernel can be configured and tuned as required, all using Ansible's Jinja2 templating to allow different runs to automatically use different parameters.

This blogpost is about understanding the network interaction between a postgres client and a postgres server. YugabyteDB implements the postgres server on top of its distributed storage, and therefore acts identical to standard postgres.

The first thing to look at is postgres query processing. Any query executed with postgres needs to go through a number of phases before it return results. These steps are:

As of PostGIS 3.1, the PostGIS sfcgal support library is no longer part of the postgis core library, but instead spun off into a new library postgis_sfcgal-3.

This change is not an issue for people doing regular, soft-upgrades from a PostGIS < 3.1 compiled with SFCGAL to a PostGIS >= 3.1 with SFCGAL using ALTER EXTENSION postgis_sfcgal UPDATE; or SELECT postgis_extensions_upgrade();. However if you are using pg_upgrade, you might get errors like postgis-3 does not contain function postgis_sfcgal_version() (which is part of the postgis_sfcgal extension).

Why migrate to pg_timetable from pgAgent?

There are many reasons, actually, why people might want to export pgAgent jobs to pg_timetable.

PostgreSQL Person of the Week Interview with Antonín Houska: My name is Antonín Houska, but Antonín sounds rather formal. English-speaking people (but not only those) usually call me Tony.

The PostGIS Team is pleased to release PostGIS 3.3.0rc2! Best Served with PostgreSQL 15 beta2 ,GEOS 3.11.0 , and SFCGAL 1.4.1

Lower versions of the aforementioned dependencies will not have all new features.

This release supports PostgreSQL 11-15.

3.3.0rc2

A long-standing bug, perhaps dating back to April 2003, has been fixed. It was an optimization in a trigger: if the values in the trigger have not changed, let’s not run the trigger. Turns out, that was a bad idea. The table The ports_categories table documents the connections between a port and the categories in [...]

This week the PostgreSQL pet is a lovely Hovawart presented by Gunnar “Nick” Bluth.

A small patch to ease the use of pgagroal tools.

We have posted our schedule of talks and training. Individual sessions are still subject to change until they have started, life plans as well as travel arrangements of presenters can change at any time.

Registration is open and selling fast. We recommend you secure your seats before they run out! Some of our training sessions are also close to selling out.