Neues vom PostgreSQL Planet

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 23's challenge "Unstable Diffusion" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language.

AOC Day 23

Tech used in this Day:

Invitation from Pavlo Golub

To finish out 2023, Pavlo is inviting us to think about PostgreSQL events and write about our experiences. While the world of in-person events started to open up again in 2022, there’s no doubt that 2023 has been really busy in all parts of the world, especially for our beloved PostgreSQL database.

Learn how to set up Postgres using Docker: this tutorial will help you install a PostgreSQL database using a Docker container.

The post How To Set Up and Run a PostgreSQL Database Using Docker appeared first on Stormatics.

Reading the title, you might think it is about multiplying INTERVAL values. Well, no, it is about something much larger.

If you are an application developer, database administrator, or Postgres internals developer, what value do you give to society? Well, you are paid or people appreciate your work, so you must provide some value. Is it food, clothing, shelter, entertainment — no, not directly. So, what is it? Time multiplication.

As PostgreSQL enthusiasts, we all know that the community’s heartbeat lies in PostgreSQL Events, whether local meetups, big conferences, or virtual gatherings, that have become prevalent during the COVID-19 pandemic.

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 22's challenge "Monkey Map" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language.

AOC Day 22

Tech used:

An introduction to database generated columns, using PostgreSQL and the new GeneratedField added in Django 5.0.

Authentication is the process of verifying the identity of a user or system attempting to access a database. In the realm of PostgreSQL, authentication serves as the first line of defense, ensuring that only authorized individuals or applications gain entry. As the gateway to sensitive data, robust authentication is imperative, safeguarding against unauthorized access and […]

TIMESTAMPs are very precise and flexible in Postgres, but sometimes users want to do index lookups of TIMESTAMP values with less precision, i.e., by date. To illustrate this, let's create a sample table with 100k TIMESTAMP values:

In recent blog post, I outlined a problem I hit with pg_dump. Specifically, pg_dump was picking up and using ~/.pgpass with pg_dump from PostgreSQL 12-14, but with PostgreSQL 15-16, it was failing.

In this blog post:

• FreeBSD 13.2
• PostgreSQL server 12 / 16
• PostgreSQL client 12-16
• Bacula 9.6.7

Today we figured out why: $HOME.

$HOME for the script was set to /

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 21's challenge "Monkey Math" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language.

AOC Day 21

Tech used:

Most people who use PostgreSQL on a regular basis may have seen pg_stat_activity, which is a system view. It gives you real-time information about what is happening on your database server. It has grown over the years to provide ever more information about system processes, database connections, parallelism and a lot more.

Another edition of our complete book is out there!

Learn PostgreSQL - second edition

On the last Halloween, the second edition of our book Learn PostgreSQL has been released!

This morning I encountered this error message:

20-Nov 03:25 dbclone-fd JobId 361156: ClientRunBeforeJob: pg_dump: error: connection to server at "pg02.int.example.org" (10.55.0.32), port 5432 failed: fe_sendauth: no password supplied

In this post:

1. FreeBSD 13.2
2. PostgreSQL 12.16 (server – pg02)
3. PostgreSQL 16.1 (client – dbclone)

The backstory:

The Postgres mailing lists are full of practical discussions, but two years ago there was a 77-email thread titled "The tragedy of SQL" that questioned the utility of the SQL query language; t is worth a review. While opening with "A fun philosophical discussion," it states:

Debian has a long history of supporting diverse machine architectures, but the number of architectures supported on apt.postgresql.org, the repository for PostgreSQL on Debian and Ubuntu, has only been growing slowly. So far, this has been amd64 (Intel/AMD x86_64), ppc64el (IBM POWER), and arm64 (Arm aarch64). The old i386 (Intel 32-bit x86) port is still there, but only for the oldest release (and Debian unstable).

The PostGIS development team is pleased to provide bug fix and performance enhancements 3.4.1, 3.3.5, 3.2.6, 3.1.10, 3.0.10 for the 3.4, 3.3, 3.2, 3.1, 3.0 stable branches.

This post supports my session titled PostgreSQL: Extensions Shape the Future at PASS Data Community Summit 2023 on November 15. Thank you to everyone who joined this session during PASS. I believe the audio recording with slides should be made available at some point a few months in the future.

Slides

The following download is the PDF version of the slide deck.

1. Overview

Write-Ahead Logging (WAL) is a standard method used in PostgreSQL to ensure data integrity. Many key features rely on this WAL design, such as streaming replication, and Point-In-Time recovery, etc. While there is a detailed online book explaining how WAL works in PostgreSQL, there is a lack of detailed documentation or blogs describing the process of adding a new XLOG record to WAL. This blog aims to share the steps on how to add a piece of customized information as an XLOG record for use during WAL REDO.

Introduction

In my previous blog about table access method here, we discussed the basis of PostgreSQL’s table access method APIs and the difference between a heap tuple and Tuple Table Slot (TTS). In this blog, let’s talk more about the particular API calls that helps PostgreSQL core to achieve sequential scan.

APIs Involved

To achieve a sequential scan, the following table access method API callbacks will be involved: