Neues vom PostgreSQL Planet

Installing PostGIS 3.1 on CentOS 8 requires a few steps: Continue reading " Installing PostGIS 3.1 and PostgreSQL 13 on CentOS 8"

It has been a while since PostgreSQL 13 has been out, and v13 RPMs have some new features for end users. In this short blog post, I would like to summarize them:

Continue reading "What is new in PostgreSQL 13 RPMs?"

Recently I spent some time thinking about what can be improved when it comes to helping new users start using PostgreSQL. One thing that almost immediately jumped to my mind is – how to install PostgreSQL? The task is theoretically simple. But there are always some caveats – which packages to use, what to configure … Continue reading "How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 1"

psql as classic TUI application doesn't support clipboard. But it isn't hard to redirect result to clipboard in good format for LibreOffice. First You need an psql from PostgreSQL 13 release. Next you need some clipboard manager application like xclip (XWindows) or pbcopy (MacOS) or wl-clipboard (Wayland). After some testing I found so LibreOffice Calc reads tsv format without problems (for numbers and texts without tabs). It uses identification application/x-libreoffice-tsvc for this format.

I released Orafce 3.14.0. It is mostly bugfix release. Only implementation of unistr function is new. The most important fixed issue was missing dependency between functions (n)varchar2 and (n)varchar2_transform. An result of this missing dependency was bad order of functions in dump used by binary upgrade - and at end the binary upgrade was broken. After fixing the binary upgrade between major Postgres releases (by pg_upgrade) is possible.

On 20th of December 2020, Alexander Korotkov committed patch: Multirange datatypes   Multiranges are basically sorted arrays of non-overlapping ranges with set-theoretic operations defined over them.   Since v14, each range type automatically gets a corresponding multirange datatype. There are both manual and automatic mechanisms for naming multirange types. Once can specify multirange type name … Continue reading "Waiting for PostgreSQL 14 – Multirange datatypes"

I recently wrote about building a Django app that stores uploaded image files in bytea format in PostgreSQL. For the second post in this series, we're now going to take a look at applying a blur filter to the uploaded image using PL/Python. 

On 18th of December 2020, Fujii Masao committed patch: pg_stat_statements: Track time at which all statistics were last reset.   This commit adds "stats_reset" column into the pg_stat_statements_info view. This column indicates the time at which all statistics in the pg_stat_statements view were last reset.

On 11st of December 2020, Tom Lane committed patch: Allow subscripting of hstore values.   This is basically a finger exercise to prove that it's possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be … Continue reading "Waiting for PostgreSQL 14 – Allow subscripting of hstore values."

What if I told you that you can create an out-of-the-box active-active, federated PostgreSQL cluster on Kubernetes?

Now that OverlayNG has landed on JTS master, it is getting attention from downstream projects interested in porting it or using it.  One of the JTS ports is the Net Topology Suite (NTS) project, and it is very proactive about tracking the JTS codebase.  Soon after the OverlayNG commit an NTS developer noticed an issue while running the performance tests in JTS: the Int

Repository, Tuning Guide and API for your postgresql.conf

postgresqlco.nf (aka postgresqlCO.NF, or simply “CONF”) was born a little bit more than two years ago. CONF’s main goal was to help Postgres users find more and easier help to understand and tune their postgresql.confs. Fast-forward to today, CONF has helped more than 150K users with their Postgres configuration settings, and is on the way to serving 1M page views every year, growing at double digits month-over-month.

The PostGIS raster extension has a steep learning curve, but it opens up some unique possibilities for data analysis and accessing non-standard data from within PostgreSQL. Here's an example that shows how to access raster data from PostGIS running on Crunchy Bridge.

Just to remind you, we already discussed issues related to isolation, made a digression regarding low-level data structure, and then explored row versions and observed how data snapshots are obtained from row versions.

Free 1GB Postgres Database on AWS CloudShell TL;DR

AWS CloudShell is a CLI embedded in the AWS Web Console. It is meant to make it easier to run the AWS CLI, SDK and other scripts from your web browser, without having to install anything locally or having to deal with local credential and profiles management. It is a free service.

I wrote about the new pg_timetable 3 major release not so long ago. Two essential features were highlighted:
– new session locking implementation
– new jackc/pgx Golang library used

Today I want to reveal one more advanced feature! Fasten your seat belts!
First, we need to distinguish exclusive client session mode from exclusive chain execution mode.

This post originally appeared on the Crunchy Data blog.

While we talk about “PostGIS” like it’s one thing, it’s actually the collection of a number of specialized geospatial libraries, along with a bunch of code of its own.

This post originally appeared on the Crunchy Data blog.

Summarizing data against a fixed grid is a common way of preparing data for analysis. Fixed grids have some advantages over natural and administrative boundaries:

This post originally appeared on the Crunchy Data blog.

Open source developers sometimes have a hard time figuring out what feature to focus on, in order to generate the maximum value for end users. As a result, they will often default to performance.

Performance is the one feature that every user approves of. The software will keep on doing all the same cool stuff, only faster.

I was browsing through the logical replication restrictions when I noticed that a primary key was not mandatory to be able to perform logical replication. (see Postgres documentation) Actually, I was even surprised that it’s never been mandatory. I guess my brain was confused with other logical replication systems like slony. So, the next logical question here is : How does it work ? How does it picks a row to update in case there is no primary key ?