Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 2 Stunden 9 Minuten

Bruce Momjian: Taking Snapshots of Clusters Using Tablespaces

vor 2 Stunden 24 Minuten

Postgres already documents the ability to backup the database cluster using file system snapshots. Unfortunately, database clusters that use tablespaces often cannot use this method if the storage system doesn't support simultaneous snapshots across file systems.

Stefan Fercot: pgBackRest preview - A tour of retention policy options

vor 16 Stunden 24 Minuten

pgBackRest is a well-known powerful backup and restore tool. Old backups and archives are removed by the expire command based upon the defined retention policy.

Since the latest version published last month, new features regarding retention have been committed. We’ll here first overview those changes and then make a tour of the retention policy options that should be available in the next release.

Bruce Momjian: Moving Tables, Indexes, and Tablespaces Between Clusters

22. Mai 2020 - 18:45

Currently, it is impossible to move tables, indexes, and entire tablespaces from one cluster to another — that is because each table and index file is bound to the cluster's infrastructure because of:

Ernst-Georg Schmid: Native (PostgreSQL only) streaming data tables

22. Mai 2020 - 10:48
If you want to see (and analyze) only a window of data over some continuous data stream in PostgreSQL, one way is to use a specialized tool like the PipelineDB extension. But if you can't do that, e.g. because you are stuck with AWS RDS or for some other reason, streaming data tables, or continuous views, can be implemented with pretty much PostgreSQL alone.

Jobin Augustine: Failover of Logical Replication Slots in PostgreSQL

21. Mai 2020 - 15:23
Missing Piece: Failover of the Logical Replication Slot

Logical decoding capability has existed in PostgreSQL for the last several versions, and a lot more functionalities are added over time.  However, one of the very crucial functionalities is still missing in PostgreSQL, which prevents logical replication to be part of critical production environments where downstream expects the logical changes to be streamed over a database connection reliably – even after a high-availability failover.

Semab Tariq: How to use Logistic Regression Machine Learning model with 2UDA – PostgreSQL and Orange (Part 5)

21. Mai 2020 - 12:39
This article gives a step by step guide to utilizing Machine Learning capabilities with 2UDA. In this article, we will use examples of Animals to predict whether they are Mammals, Birds, Fish or Insects. Software versions We are going to use 2UDA version 11.6-1 to implement the Machine Learning model. This version 11.6-1 combines: PostgreSQL […]

Louise Grandjonc: Postgres tips for optimizing Django & Python performance, from my PyCon workshop

21. Mai 2020 - 1:10

This year, I was so excited about doing a workshop about optimizing Python & Django apps with Postgres superpowers for the PyCon 2020 conference.

Working with other developers on performance is something I always find amazing. So props to the Python people at Microsoft who encouraged my team to create a workshop on Postgres for PyCon 2020. Thank you to Nina Zakharenko, Dan Taylor, & Crystal Kelch.

Muhammad Usama: Phoney table columns in PostgreSQL

20. Mai 2020 - 22:25
CREATE TABLE one_column_table (field INTEGER);

The above SQL creates ‘one_column_table’ with only a single column. But does the table actually have only one column?

Let’s query the pg_attribute catalog to find out how many columns our one_column_table has.

Bruce Momjian: Why PGDATA Should Not Be at the Top of a Mount Point

20. Mai 2020 - 15:15

This email thread is illustrative of why it is unwise to place the Postgres data directory (PGDATA) at the top of a mount point. Instead, create a subdirectory under the mount point and put PGDATA there. This has the advantage of avoiding possible data corruption if mounting fails, and allows more efficient use of pg_upgrade.

Ernst-Georg Schmid: MQTT as transport for PostgreSQL events

19. Mai 2020 - 12:13
MQTT has become a de-facto standard for the transport of messages between IoT devices. As a result, a plethora of libraries and MQTT message brokers have become available. Can we use this to transport messages originating from PostgreSQL?

Hans-Juergen Schoenig: How to count hits on a website in PostgreSQL

19. Mai 2020 - 9:30

Recently we have covered “count” quite extensively on this blog. We discussed optimizing count(*) and also talked about “max(id) – min(id)” which is of course a bad idea to count data in any relational database (not just in PostgreSQL). Today I want to focus your attention on a different kind of problem and its solution: Suppose you want to grant a user access to a certain piece of data only X times. How can one implement that safely?

Bruce Momjian: Percona Live Online

19. Mai 2020 - 5:45

I am planning to virtually attend and present at the Percona Live Online conference tomorrow, May 19. It starts at 10am, Eastern USA time, and spans 24 hours, so it covers every time zone. I am speaking at noon, Eastern USA time.

Attendance is free, so you might want to check it out. I saw some interesting topics on the program. I am also curious to experience a 24-hour virtual conference, though I am unlikely to remain awake that long.

Stefan Fercot: pgBackRest preview - Info command and backup/expire running status

19. Mai 2020 - 2:00

pgBackRest is a well-known powerful backup and restore tool. The 2.26 version has been released on Apr 20, 2020. New features have been developed since then.

Today, let’s have a look at: add backup/expire running status to the info command.

Bruce Momjian: Using Non-Login Roles

18. Mai 2020 - 16:15

When we talk about database roles, most people immediately think of login roles, which allow people to log in. However, another user management feature is the ability to create non-login roles, formerly called groups.

Andreas 'ads' Scherbaum: Dimitri Fontaine

18. Mai 2020 - 16:00
PostgreSQL Person of the Week Interview with Dimitri Fontaine: Hi! My name is Dimitri Fontaine and I’m French, currently living in the greater Paris area. When I’m not sitting in front of a computer for work, I like to be with my kids. We play board games, have fun chats, watch movies together… well or at least try to find a single movie that we would each enjoy watching… I also play the guitar.

Pavel Stehule: pspg - two new feature

17. Mai 2020 - 19:54
I wrote two new features to pspg.

First feature is a option --skip-columns-like. Now, it can be used only for browsing csv or tsv documents. When this option is used, then specified columns (specified by substring of name) are not displayed. It can be useful for psql PostGIS data. More time some GEO data has not sense to display. If you have wide table with lot of columns, then can be nasty to write list of columns every time. Example

export PSPG="--skip-columns-like='geom$'

David Z: Build PostgreSQL and Extension on Windows

15. Mai 2020 - 23:55
1. Overview

PostgreSQL is an open-source RDMS and running across many platforms including Linux (all recent distributions), Windows, FreeBSD, OpenBSD, NetBSD, Mac OS X, AIX, HP/UX, IRIX, Solaris, Tru64 Unix, and UnixWare. There are many discussions about how to build Postgres and extensions from source code on a Linux-like environment, but sometimes, a developer may want to quickly setup a Windows environment to check a feature for cross-platform support.