Neues vom PostgreSQL Planet

A reliable backup tool with an ability to perform a point in time recovery (PITR) is a usual enterprise need. Postgres includes a backup tool called pg_basebackup for full backups. It is great for a database of a few hundreds of GBs in size but currently lacks the support for parallel, incremental and differential backups. This makes it slightly challenging for bigger postgres databases that are Terrabytes in size. pgBackRest is our savior in such a case.

The pgBackRest team is pleased to announce the introduction of multiple repository support in v2.33. Backups already provide redundancy by creating an offline copy of your PostgreSQL cluster that can be used in disaster recovery. Multiple repositories allow you to have copies of your backups and WAL archives in separate locations to increase your redundancy and provide even more protection for your data.

I have previously blogged about scaling of read-only and read/write workloads. Inspired by this email thread, I want to be more specific about exactly what scaling you can expect by using read-only replicas. First, as the email poster pointed out, read replicas still have to replay write transactions from the primary server.

A hierarchical query is built upon parent-child relationship, the relationship exist in the same table or view. The relationship dictates that each child can have one parent while a parent can have many children. Hierarchical query is a SQL query that handles data of hierarchical model i.e. an organisation structure where every employee has one manager and one manager who is also an employee can have many employees in his reporting, another example is a family tree where one person can only have one parent while a person can have many children.

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 can be used free of charge. It is a powerful tool to edit and visualize spatial data which is ideally stored in a PostGIS-enabled PostgreSQL database.

In the 1990's, most server operating systems were closed-source and produced by a few billion-dollar companies. In recent years, Red Hat Enterprise Linux (RHEL) became the default enterprise server operating system, with CENTOS filling the need for installations on smaller platforms and in the cloud.

Join us virtually on April 13, 2021 at 12:00PM,

Muffy Barkocy presents, “Tidying Up Your Data”.

Crunchy Data is pleased to announce its most recent release of pgBackRest: 2.33 with a number of new features including multiple repository support and GCS support.

Deploying PostgreSQL for High Availability with Patroni, etcd and HAProxy – Part 2 l by Federico Campoli  6 Apr, 2021 

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 to retrieve information on roles and role membership in PostgreSQL.

Preparing users and roles

To show how users can be analyzed, we first create a couple of users and roles:

Databases are often a critical part of enterprise infrastructure, so when and how to upgrade them is a common discussion topic among database administrators, and Postgres is no exception. The Postgres community has a web page about when to upgrade, and the Postgres documentation has a section about upgrading.

Hello everyone! This is the first (and maybe last) time I won’t have anything technical to publish on this blog. I worked hard on a project lately and I’m proud and excited to announce that Psql Tips is now published! This URL is great if you’d like to get a random tip, but you can look here at all of them on a single page! For now, there are only 100 tips but stay tuned, I plan to add more in the following weeks!

In my day to day, I'm surrounded by great database engineers. They talk about things like HA and raft protocol and the right and wrong approach for configuring synchronous vs. asynchronous replication. There is a lot of value in all that deep technical knowledge, but for when interacting with customers, I like to boil it down a bit.

Something that can be useful when running PostgreSQL on FreeBSD.

Preventing FreeBSD to kill PostgreSQL (aka OOM Killer prevention)

There are a lot of interesting articles on how to prevent the Out of Memory Killer (OOM killer in short) on Linux to ruin you day, or better your night. One particular well done explaination about how the OOM Killer works, and how to help PostgreSQL to survive, is, in my humble opinion, the one from Percona Blog.

On 29th of March 2021, Peter Eisentraut committed patch: Add unistr function   This allows decoding a string with Unicode escape sequences. It is similar to Unicode escape strings, but offers some more flexibility.

Sometimes you just have to work with binary in your PostgreSQL database, and when you do the bytea type is what you’ll be using. There’s all kinds of reason to work with bytea:

On 26th of March 2021, Noah Misch committed patch: Add "pg_database_owner" default role.   Membership consists, implicitly, of the current database owner. Expect use in template databases. Once pg_database_owner has rights within a template, each owner of a database instantiated from that template will exercise those rights.   Reviewed by John Naylor.

The previous series addressed isolation and multiversion concurrency control, and now we start a new series: on write-ahead logging.

Ansible has a very useful module "postgresql_ping" which checks connectivity to the database server.

On 24th of March 2021, Peter Eisentraut committed patch: Add date_bin function   Similar to date_trunc, but allows binning by an arbitrary interval rather than just full units.