Postgresso 18
After a pause (vacation), we continue to acquaint you with the most interesting PostgreSQL news. We will not adhere to strictly reflecting all releases and events that have occurred since the last, still October release of Postgresso # 17, but we will try to mention the most important one that happened back in late 2019.
Releases
PostgreSQL 12.2
As well as 11.7, 10.12, 9.6.17, 9.5.21, and 9.4.26 saw the light February 13. The last one on the list is the last one: the 27th will be gone. In 12.2, a huge number (over 70) of bugs found in 12.1 were fixed. Of these, many are in sectioning.
We solved the rights problems in the ALTER … DEPENDS ON EXTENSION construct. In 12.1, DROP EXTENSION rights holders could crack down on objects dependent on this extension.
Parallel hash join performance was improved for processors with a large number of cores and for hash join with very large tables.
Postgres Pro Standard 12.1.1
This version was released at the end of December 2019 and is based, respectively, on PostgreSQL 12.1. You can read about the features of this version in this article. There are discussed in detail and with examples:
– Verification of ICU versions;
– optimization of locks, joins and GROUP BY;
– support for PTRACK;
– WaitLSN;
and much more.
MobilityDB 1.0 beta1
Despite his youth (see version number), this open-source extension for spatio-temporal objects is actively used both here and abroad to analyze sea and land traffic flows, which is clear from two reports at the PGConf.Russia conference. Videos will be later, and we will print mini-extracts from these (including) reports on the hub Works this extension with PostgreSQL and PostGIS.
PostGIS 3.1.0alpha1
New alpha upgraded for PostgreSQL 12.1 and GEOS 3.8.0.
Details here, download link is on this page.
Krahodb
This Brazilian DBMS with a strange-sounding name for the Russian ear is built on top of PostgreSQL versions 10, 11 and 12 for clusters in bi-directional logical replication mode. Read and download here.
PGSpider
The first version has been released. This is a patch for PostgreSQL (until 11.6), which, according to the creators, provides PG with a high-performance cluster engine on FDW that works with distributed data. Requests are processed in parallel and there is a pushdown. Developed by Toshiba Software Engineering & Technology Center. Details here.
PostgreSQL TDE 12
it development Cybertec – a patch for PostgreSQL for transparent encryption (Transparent Data Encryption), independent of the OS and the file system.
Database Lab 0.2.0
This is the first public version Utilities for cloning multi-terabyte PostgreSQL databases. The creator, Nikolai Samokhvalov from Postgres.ai claims that now it takes a couple of seconds. All components now work in containers: install Docker and ZFS, the rest is inside. Details in changelog and on github project.
Joe DBA-bot for optimizing PostgreSQL queries
Another product of this company. Works on top of Database Lab.
Aurora PostgreSQL Supports Machine Learning
Amazon added machine learning in AWS. No additional payment is required from users. They get all the models from Amazon sagemakermay use ML from Amazon comprehend, which analyzes (sentiment analysis) natural language. There is export to Amazon S3 with the new aws_s extension. Works in versions of PostgreSQL 10 and 11. Details here.
Odyssey 1.0
Yandex developers have brought their cloud-based multi-threaded puller to the industrial version – 1.0. They themselves use it in production, where it serves more than 1 million requests per second on hundreds of hosts. I managed to achieve:
– linear scaling on processors;
– support for different modes of operation with the ability to configure authentication, pooling mode, etc .;
– the puller can automatically roll back transactions and kill backend processes when disconnected;
– Can correctly transmit PostgreSQL errors;
– supports replication protocol;
– supports PAM and SCRAM authentication.
Odyssey releases are here.
pgbouncer_fdw 0.2.0
Version Details here. Designed by Cybertec.
pgbouncer_wrapper 1.1.0
With him can be contacted to the balancer in SQL. Also Cybertec.
Pgpool-II 4.1.0
In this version, among the changes:
– load balancing at statement level
– automatic failback;
– increased productivity due to more efficient use of cache and parser.
You can read
here, and upload from here.
pg_probackup 2.2.7
New in this version:
– added support ptrack 2.0;
– packages for ALT Linux versions p7, p8 and p9 appeared in the repository.
The latest version of the documentation is located here. In addition, according to insider information, an article is being written on the geek magazine on pg_probackup. Download from here.
check_pgbackrest 1.7
Utility Gilles Darol monitors backups pgBackRest, is a plug-in Nagios. It helps to check retention retention policies and the consistency of archived WAL segments. In the new version, you can add output in json format, set the format for renaming the output file, and other.
pgAdmin4 4.18
IN this version:
– Added support for multi-level partitioning;
– a tool for comparing two schemes;
– the session closes when the user stops moving the mouse, touching the keys.
– bug fixed: earlier in Windows in server mode it was possible by indicating empty in the settings
STORAGE_DIR, see all Windows volumes. Now it’s impossible.
Download from here.
Details of the latest version bug fixes here.
pg_timetable
Job Scheduler for PostgreSQL by Cybertec. Is able automatically restart failed tasks, build chains, send mail. It is interesting that among the declared advantages there is work not as a background process, but as a demon. The question, to put it mildly, is debatable: recall that the creators pgpro_scheduler We chose architecture with the work of the scheduler “inside” the base precisely as an advantage. Download can from here.
pgCluu 3.1
New version this perl program (also Gilles Darol) for audit of cluster performance with PostgreSQL can be downloaded here. pgCluu collects statistics not only on the DBMS, but also on the cluster system resources.
pitrery 3.0
In the new version of this set of scripts for managing PITR backups (from Dalibo) appeared PostgreSQL 12 support.
Operator 4.2.1
Crunchy PostgreSQL Operator uses Kubernetes to quickly deploy and manage PG clusters. On the this page There is a detailed instruction for installing the latest (and any other) version.
PostgresDAC 3.7
The set of components from Microolap for working in RAD Studio with PostgreSQL has been upgraded for PostgreSQL 12 and RAD Studio 10.3.2. Works with PostgreSQL, EnterpriseDB, Amazon RDS, PostgresPro and Heroku Postgres. Changelog hereupload from here.
pgFormatter 4.2
About the new version of this utility by Gilles Darol here.
explain-running-query
Python program displays PostgreSQL query execution plan.
pg_sqlog 1.0
it extension interface to the logs.
psqlODBC 01/12/0000
About the new version here.
pgmetrics 1.8.1
A version of this utility that collects statistics about the operation of the PostgreSQL server and gives it in the form of text, JSON or CSV can be downloaded from here.
wal2json 2.0
IN new version This logical decoding utility only bugfixes.
psycopg 2.8.4
In this version of the PostgreSQL adapter for Python, bug fixes and Python support are added
3.8 and PostgreSQL 12. Take source code from here.
Ajqvue 3.1
Came out a new version this graphical environment. And more interesting converter plug.
Migrator 1.0
Cybertec’s Graphic utility based on ora_migrator. Completely transactional migration, monitoring. There is a detailed guide installation.
oracle_fdw 2.2.0
New version.
PEV2
Tool Visualization of PostgreSQL plans by Dalibo.
Education
Textbook “Novikov B. A. Fundamentals of Database Technologies” 2nd edition
Postgres Professional website PDF can be downloaded, which now contains both parts. A paper version of the book is due to appear in February.
A new “baby” came out
She’s Postgres. First meeting”. This is already the 6th edition of the popular brochure (the number 12 on the cover does not refer to the edition, but to the PG version, which demonstrates examples). Major changes:
– in the chapter on installation, they completely switched from Postgres Pro Standard to vanilla PostgreSQL 12;
– a chapter has appeared and will continue to be on the new features of the latest version (in this edition – the 12th);
– in the chapter about JSON it is also told about JSON / Path;
– In the chapter on training, a section on certification has appeared.
The book was already handed out at the PGConf.Russia conference.
.
You can download PDF from here.
Articles
An Overview of Job Scheduling Tools for PostgreSQL
Hugo Diaz overlooks on the SeveralNines blog:
– Linux crontab;
– Agent pgAgent;
– Extension pg_cron.
Unfortunately, I didn’t get into the review. pgpro_scheduler.
How To Migrate From Trigger-Based Partitioning To Native in PostgreSQL
Article on the Crunchy Data blog, and migration is parsed by the example of their extension pg_partman, but much is applicable to other partitioning tools.
Creating a PostgreSQL procedural language – Part 2 – Embedding Julia
How to integrate a procedural language into Postgres – in this case, using the not-so-common language, Julia, which is execution-oriented in LLVM. Mark Wong writes to 2ndQuadrant Blog.
Migrating from MS SQL to PostgreSQL: Uppercase vs. Lowercase
Hans-Jürgen Schönig writes in a Cybertec blog about a seemingly special case of migration – the translation of object names from upper case to lower case, but this is, firstly, a common practice, and secondly, warns primarily from loopholes for SQL injection.
Replication configuration changes in PostgreSQL 12
in article not only changes are listed, but also unobvious nuances are sorted out.
PostgreSQL 13: parallel VACUUM
This is a standalone article by Yegor Rogov, it is not included in his series – 10 articles on indexes, 8 articles on MVCC, 4 articles on locks and 4 articles on WAL.
Webinars and Mitapas
Open PostgreSQL Meetup
Organized by Raiffeisenbank and Postgres Professional. The record is posted on the hub
Tuesdays #RuPostgres
Every Tuesday at 18:30 Moscow time, Nakolay Samokhvalov (Postgres.ai) and Ilya Kosmodemyansky (Data Egret) host online Postgres Tuesdays by Zoom (permalink for calls)
You can participate simply by voice, but preferably with a video. You can remain the viewer.
YouTube – online recordings.
Facebook – notes, discussions.
googlodok with the program, topics are accepted.
GDG Databases. PostgreSQL Pro deep dive (Tyumen)
February 7, 2020 in the Tyumen Technopark, on the site “Boiling Points” was held mitap with this name in Tyumen) It was attended by including Bruce Momjan, Oleg Bartunov, Ivan Panchenko.
Tver.IO PostgreSQL Performance Workshop
February 10, Bruce delivered a three-hour lecture in TverPostgreSQL Performance Tuning“
PostgreSQL-mitap in Minsk
From Tver, Bruce went there. This was the first PostgreSQL-mitap in Belarus; it was held on February 12th. Postgres Professional chief architect Alexander Korotkov also spoke at the rally. Belarus post-congressists have their own group in fb, telegram and mitap.
Conferences
Past
PGConf.Russia
passed at the Faculty of Economics, Moscow State University February 3-5. Video reports will be available in the coming weeks, and many presentations have become available at the conference (see the program) In the coming days, a short summary and articles on the materials of reports will appear here.
Future
pgDay Israel 2020
will pass on March 19 in Tel Aviv.
pgDay Paris 2020
will take place in Paris on March 26th.
PG Day Russia 2020
will take place July 10th applications can be served until April 6th.
Postgres London 2020
will pass July 7-8, applications can be submitted here.
subscribe to the channel postgresso!
Send ideas and suggestions to the mail: news_channel@postgrespro.ru
Previous issues: # 17, # 16, # 15, # 14, # 13, # 12, # 11 (special), # 10, # 9, # 8, # 7, # 6, # 5, #4, # 3, # 2, #1
