Neues vom PostgreSQL Planet

Long time I am playing with psql and I try to enhance and fix some limits of this client. I like it. The work with this client is pretty fast, and with pspg the result's browsing is almost comfortable. But still there is disadvantage - is not possible to see result and query in one time, and isn't possible to work with more results in one time. But with new inotify support these limits are removed. You can create file that will be used as input/output buffer. Then you can start pspg against this file with --inotify option.

After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of charge as a standard PostgreSQL extension.

1. Overview

In previous blog, we have setup Kerberos, added all required principals and verified each principal. This blog will explain all the necessary configuration, i.e. postgresql.conf, pg_hba.conf and pg_ident.conf, in PostgreSQL for user authentication using GSSAPI with Kerberos.

This is my first blog in a series of SQL optimization blogs. So expect some basic information in here along with some nice insights. My aim is to help you walk through a complete process of understanding and optimizing queries for improved performance. A PostgreSQL server attempts to find the most effective way of building a result set for the query. The first step in that direction is the ability to understand what the “EXPLAIN” command is. 

We recently helped migrate a production PostgreSQL database running on a given DBaaS platform… to another DBaaS platform. When it comes to vendor “lock-in”, some providers are easier (and even friendlier) to deal with than others, but it is never a straightforward process.

© Laurenz Albe 2020

 

Embedded SQL is by no means a new feature — in fact it is so old-fashioned that many people may not know about it at all. Still, it has lots of advantages for client code written in C. So I’d like to give a brief introduction and talk about its benefits and problems.

1. Introduction

PostgreSQL and MongoDB are two popular open source relational (SQL) and non-relational (NoSQL) databases available today. Both are maintained by groups of very experienced development teams globally and are widely used in many popular industries for adminitration and analytical purposes. MongoDB is a NoSQL Document-oriented Database which stores the data in form of key-value pairs expressed in JSON or BSON; it provides high performance and scalability along with data modelling and data management of huge sets of data in an enterprise application.

There is a lot of interest and discussions lately in the PostgreSQL world to make it a scale-out solution. Among other possible solutions, one of the most promising ones is to implement the sharding using FDW and table partitioning for distributing the data on multiple servers. As of now, PostgreSQL can only fetch the data from FDW in serial and that is one thing that needs to be improved to increase the performance.

Let’s wrap this up. In part 1 of this blog series, I explained the different Windows build targets supported by PostgreSQL. In part 2, I showed how to test patches for Windows compatibility and how to easily spin up a Windows environment for testing and simple development. Now, in this third and final part, we’ll […]

In our previous posting on tile serving, we showed off how pg_tileserv can use database functions to generate tiles by querying existing tables with user parameters.

We can also use functions to build geometry on the fly without input from tables. For example, hexagons!

Users had often asked where they could find older versions of packages from apt.postgresql.org. I had been collecting these since about April 2013, and in July 2016, I made the packages available via an ad-hoc URL on the repository master host, called "the morgue". There was little repository structure, all files belonging to a source package were stuffed into a single directory, no matter what distribution they belonged to.

ncurses-st-menu is small library over ncurses for pulldown and menubar menu. Stripped size of library has 43kB, so it is not extra light, but it is in category smaller libraries. One use case, that I would to support, is using in smaller application on smaller devices. For this purpose this library supports scrollable menu now. When the content of menu is longer then display, then the content will be scrollable - so cursor will be visible every time.

PostgreSQL Person of the Week Interview with Dave Cramer: I’m a Canadian currently hiding from winter in a warm southern state. As for hobbies: I started taking my car to the race track to see how well I understood physics. Turns out the theory and application are more interesting when you are the object in motion. I’ve been involved in PostgreSQL since around 2000. Through reading and answering many emails on the JDBC list I found myself contributing code to it and eventually maintaining it. I consider myself to be very fortunate to be able to make a living from PostgreSQL.

This post is part of several blog posts (at least two!) about Troubleshooting Postgres. Last time, we found out if Postgres was running and how to start it. Please find here all the troubleshooting posts < ref “/tags/troubleshooting/” >. Here is my checklist of troubleshooting Postgres: [] Try to start postgres (if this works, stop here) [] Locate the log file [] Look at the messages in Postgres logfile To start Postgres, please refer to < relref “content/post/troubleshooting-01.

As for now, there are no strict official restrictions in Ukraine. However, the trend seems to be evident, and the COVID-19 epidemic will be in an active phase until June-July, 2020.

Taking this into account, we are postponing the conference. We’d like to kindly remind you, we’re not canceling the PGDay Ukraine 2020, it’s moved to Fall 2020. All purchased tickets are valid. You are still our participants and guests.

pg_timetable: Advanced PostgreSQL cron-like scheduler released!

Hello there, this is your developer speaking…

This troubled times made me work harder than before to finish my ongoing missions and after that, I found myself somehow left with little to do and that’s kind of disturbing. So, why not take this time to write down that article born into my brain this morning while eating breakfast ? I’m on Postgres slack and Telegram group, more rarely on [IRC[(https://www.postgresql.org/community/irc/). I often see the same question, again and again : “Postgres doesn’t work, can you help me?

1. Overview

PostgreSQL supports many secure ways to authenticate users, and one typical way is to use GSSAPI with Kerberos. However, when I was trying to investigate one issue which is related with GSSAPI in PostgreSQL, I couldn’t find a tutorial that I can follow to setup the environment easily. After some effort spent on Kerberos environment setup, I decided to write a blog to share what I have learned.

Since PostgreSQL GSSAPI user authentication does involve many background knowledge, I separate this blog into 3 parts:

The state of Baden-Württemberg declared that in order to control the rapidly changing COVID-19 situation, all events over 100 participants are banned for at least until mid of June 2020. This affects the German PostgreSQL Conference 2020, which was planned for May 15th in Stuttgart.

check_pgbackrest is designed to monitor pgBackRest backups, relying on the status information given by the info command.

The changes in this new release are:

• missing archives output: the complete list is now only shown in --debug mode;
• new --list-archives argument to print the list of all the archived WAL segments found.

Missing archives

Let’s use the 1.7 release and see the basic output: