PostgreSQL news digest. Issue No.16
We continue to introduce you to the most interesting PostgreSQL news.
June breaking news
EnterpriseDB acquired by Great Hill Partners Investment Fund . The amount of the transaction was not disclosed. Michael Stonebreaker appointed technical adviser. Andy Palmer joined the board of directors of EDB. He is a renowned IT investor, co-founder of Vertica, and author of the chapter in Making Databases Work: The Pragmatic Wisdom of Michael Stonebraker. Great Hill Partners is a private (non-public) fund managing $ 2.7 billion. The event is no less impressive than the recent purchase of Citus by Microsoft: out of 5 members of the Core Team, two are EDB employees.
Releases
PostgreSQL 11.4, 10.9, 9.6.14, 9.5.18, 9.4.23 and 12 Beta 2
These releases were not expected because of new features, but because it was necessary to close the detected security hole, codenamed CVE-2019 -10164. Any user authenticated using the scram-sha-256 method could overflow the buffer on the stack, changing his password to a specially designed line. In this way, it was possible not only to drop the server, but also to execute arbitrary code on behalf of the user of the OS running PostgreSQL.
A similar overflow possibility existed in libpq, and by exploiting it, a dummy server could drop the client application or execute insidious code on the client on behalf of the user who launched this application.
This vulnerability appeared only in relatively new versions of PostgreSQL: 10 and higher, when SCRAM authentication appeared. On the community site you can see “special thanks” to Alexander Lakhin (Postgres Professional), who discovered the problem.
You can read an article on this topic: eVOL Monkey. Who's affected and how to protect your systems.
Postgres Pro Standard 11.4.1, 10.9.1, 9.6.14.1, 9.5.17.1 and Postgres Pro Enterprise 11.4.1
In these versions, the security hole is already closed. You can read about this and other bug fixes in the documentation.to the corresponding version. From the new: support for the Russian OS Rosa Enterprise Linux Server 7 and ROSA “Cobalt” (server edition) on the ROSA 7 platform (previous versions of these operating systems are no longer supported), AlterOS 7.5, as well as other new versions: OS Ubuntu 19.04 ( Ubuntu 14.04 is no longer supported), SUSE Linux Enterprise Server 12 SP3, and SUSE Linux Enterprise Server 15. The pg_probackup module has been updated to version 2.1.3, and pg_pathman has been updated to 1.5.8. These changes apply to both Standard and Enterprise.
Enterprise Changes: The schedule.is_enabled () function has been added to the pgpro_scheduler extension, which allows checking whether pgpro_scheduler is currently working, and the schedule.enabled configuration parameter that served this purpose earlier has become obsolete. But the configuration parameter schedule.auto_enabled was added, which determines whether pgpro_scheduler will be turned on when the server starts.
The multimaster extension adds support for the declarative syntax pg_pathman (while using the pg_pathman functions with the current multimaster implementation is incompatible).
The remaining updates to the Enterprise lineup will appear in the coming days.
pgAdmin4 4.10
In this versionNow you can drag and drop objects in the Query Tool. And now you can rewrite the path to /pgadmin4/servers.json in the container distribution. PgAdmin updates are frequent, so we recall that previous versions have improved support for PostgreSQL 12; pgAdmin has learned how to display regular tables and inherited or having heirs in different ways; in the graphical interface, it is now possible to fix windows and frames, protecting them from unintentional movement.
pg_probackup 2.1.3
In this version, the default behavior was also changed: when restoring from a backup, the default access mask was used earlier (0700). Nowupon restoration, the file receives the same mask that it had when removing the backup. Bugs when executing backup, restore, validate were fixed. We remind you that since version 2.1.1 pg_probackup supports remote (remote) removal and restoration of a backup copy.
barman 2.8
Among the changes, support for incremental backup (not at the block level) in geo-redundancy configurations. There are performance improvements. Starting with this release, PostgreSQL 9.3 and later is not supported.
WAL-G 0.2.9
In the new version of this backup toolPostgreSQL, written in Go, added support for fast delta backups. This is what they call incremental backup at the block level, when WAL is scanned during archiving and the collected information is used to speed up backup (a similar principle is used in pg_probackup, but scanning is done at the beginning of the backup). Brotli was added to the compression of lz4 and lzma. Added support for PGP encryption without external GPG. S3, GCS, SWIFT, Azure and file system level support are supported.
repmgr 4.4
In the new versionThis 2ndQuadrant replication management environment is new. For example, repmgr standby switchover runtime behavior has changed: inaccessible and marked as inactive nodes are ignored. Previously, when any node was unavailable, repmgr crashed, since it was impossible to verify whether the daemon was alive. Now the daemon can monitor connections and, if it sees that the number of available nodes is less than the specified one, it executes the script created for this case. Fencing is more reliable.
pg_partman 4.1.0
In the new version for PostgreSQL 10+ built-in (native) partitioning, it is not necessary to be a supervisor (but for partitioning based on triggers and for direct access to child tables, a superuser is still needed).
dbForge Studio for PostgreSQL v.2.1
releasednew version of dbForge Studio for PostgreSQL. The graphical interface now shows: a tree of plans; query tree, query execution diagram with the costs of all operations as a percentage of the total cost, output of the execution plan in XML format. Now you can connect to Greenplum DB. Tooltips in Code Completion and Database Explorer have also been improved.
pgFormatter 4.0
This nice utility of Gilles Darol from Dalibo works online, it will format the text entered in the window, bloom keywords, and indent.
Psycopg2 2.8.3
in the new version of the connectorPython for PostgreSQL (which is also used in pgAdmin4) has a change in the way messaging is done with logical replication. The flow of confirmations coming from the client is now arranged more rationally, the load on the server and network has decreased.
Pgpool-II 4.0.5, 3.7.10, 3.6.17, 3.5.21 and 3.4.24
Here you can download 4.0.5 and other updated versions. Tatsuo Ishii accelerated the failover when all backends fell. The pgpool-recovery and pgpool_setup extensions are ready for the next major release of PostgreSQL 12.
HAProxy 2.0
The new version of the load balancer can, among other things, check the availability of servers and evaluate the level of load and even withstand DDoS by primary data filtering. It can be used to coordinate the interaction of components in systems based on microservice architecture. This can be read in Russian .
PostgreSQL JDBC 42.2.6
Driver This version supports PostgreSQL 12; Tested with Java 11 and Java EA. You can find out about changes and bug fixes here .
psqlODBC 11.01.0000 Some errors have been fixed
in this driver version . Including there are some adjustments that take into account the changes that have occurred in the version of PostgreSQL 12.
PostGIS 3.0.0alpaha3
In the new alpha, PostgreSQL 9.5-12beta2 and GEOS are not older than 3.6. They write that "serving is better with PostgreSQL 12beta2." Recall that in 3.0 there have been major changes: all raster functionality has been moved to a separate extension. Details here .
postgres-checkup 1.1
This version, “Young Owl”, is distinguished, for example, by new options
--html, --pdfand --list-limitwhich determine how the data will be displayed. The recommendations and conclusions provided by this health check utility take into account the wraparound approximation, the bloat of the indices and heap. An owl can notify about unnecessary and unused indexes. The very first release (1.0) was released just over a month ago.
pgwatch2 v1.6
This version of the "dashboard" for monitoring the DBMS was released after a period of six months, it has accumulated significant changes. PostgreSQL has been supported since the previous version of pgwatch. The new version also supports the Prometheus TSDB time series database, but the creators recommend using Postgres (as well as InfluxDB) for this purpose. No less important news is the emergence of support for Patroni, as the basis for which PostgreSQL HA clusters are often built. The new version will be able to monitor PostgreSQL 12, supports tracking and visualization of the values of most pg_settings, warns of wraparound and maximum autovacuum time. Developer - Cybertec.
pgBadger 11.0
In this log analyzer appearednew features, for example: you can group queries related to cursors (DECLARE, CLOSE, FETCH, MOVE) in a separate section - CURSOR; You can use the option
--exclude-clientto exclude log lines associated with specific client IP (s); You can maintain incremental reports at the base level. Attention! The new version does not have backward compatibility with old binaries and JSON files, and in incremental mode it will not be possible to read old binaries. pgMustard
This is the user interface for EXPLAIN ANALYZE. He can not only visualize plans, but also knows how to increase productivity.
New Committers and Contributors
David Rowley became a committer. There are 30 commiters in the community now .
Anastasia Lubennikova (Postgres Professional) entered the official list of contributors. In total, there are 90 people on the list of current major and just contributors.
Education
Certification has begun.
The first 3 specialists (already outside of Postgres Professional, who organized the process) received certificates "PostgreSQL 10. Professional Administrator". To obtain such a certificate, you must pass the DBA1-10 test .
DBA2 courses Published
on the organizer's website and all 18 fragments of the DBA2 video course are available for free (PostgreSQL Administration 10. Configuration and Monitoring).
English DBA1
courses Postgres Miktzoanim, Israel’s Israeli DBA course organizer, promotes the PostgreSQL HA solution in Israel at Stolon and Postgres Pro Enterprise in Israel, unveils a 2-day introductory videobased on the DBA1 course taught in Tel Aviv on February 13-14, 2019.
Video of B.A. Novikov's courses We
remind you that slides and videos (via links) can be viewed on the Postgres Professional website in the Education section. All slides are available, and videos are laid out gradually. Now the video series ends on the third topic of the course “Fundamentals of Database Technologies. Part 3 Other data models. " They are posted on youtube on the Postgres Professional channel .
Conferences
Announcement:
PGConf.Russia 2020
The preliminary dates and venue of PGConf.Russia 2020 have been determined:
Faculty of Economics, Moscow State University, February 3-5, 2020. It is already possible to register and submit applications for reports on the site .
Past:
PGCon 2019 Ottawa Held
in Ottawa on May 28-31. Having entered the Program, you can see the slides of many reports .
DevConfX Developer
Conference was held in Moscow on June 21. Among the participants was, for example, Pavel Luzanov from the Postgres Professional education department, who explained how to prepare for certification . Some presentations have been posted, see the program .
pgibz
For the first time in Ibiza on June 19-23, the conference "Postgres on the Beach" was held. From Russia on the beach were: Oleg Bartunov (Postgres Professional) and Andrey Fefelov (Mastery.Pro); Bruce Momjian considersThat experiment with a beach conference is a success. Bali (see below) will prove or disprove this opinion.
The future:
PostgresConf Beijing
Already started July 3 in Beijing, will end July 7. One of the co-chairs of the conference board is Joshua Drake. In an English stream, Oleg Bartunov talked about JSONPath.
PGConf.Brazil 2019
There will be Aug. 1-3 in San Paolo.
PGconf.ASIA 2019, Bali The
long-awaited beach conference on the island of Bali is due to take place on September 9-12. Applications for reports are already accepted. About 500 participants are waiting. There is a panorama of beaches, hotels and congress centers.
PostgresOpen To
be held in Orlando, Florida on September 11 - 13. You can apply for the report here .
PostgreSQL Conference Europe 2019
Held in Milan September 15-18. Hurry up: applications before 15.
PostgresConf South Africa 2019
Scheduled for October 8-9. Place of - Johannesburg. Applications for reports are accepted until June 30.
Highload +
A conference on highly loaded systems will be held in Skolkovo on November 7 and 8 . You can apply here .
Articles
PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 1, 2
A huge two-part article by classic Postgres Joe Conway about how default settings affect security. Parts 1 and 2 have many details and examples.
Postgres 12 highlight - SQL / JSON path
Michel Paquier continues to review the innovations of version 12. The queue has reached SQL / JSONpath. But these are really only highlights for quick reference. Those who need more details should read this article. In the previous Michel series, there were Table Access Methods and blackholes - table access methods and black holes (whichturned out to be an extension template created by the author of the article).
WAL in PostgreSQL: 1. Buffer cache
Egor Rogov (Postgres Professional) opens with this article a new series - about WAL.
Hide and seek with the optimizer. Game over, this is CTE PostgreSQL 12
This article is a continuation of the PostgreSQL 12 theme that started in What Freeze on Feature Freeze 2019. Part I. JSONPath .
Professional Postgres
This is a transcript of Oleg Bartunov's report at the PGConf.Russia 2019 conference. The report is not about the company, but about how Postgres grew from an amateur academic DBMS to a professional industrial one.
Linear Interpolation with PostgreSQL
An article by Philipp Wagner (Philipp Wagner aka bytefish) on a rather unusual topic: how to fill holes in the time series and how to detect them using window functions. He also wrote about how to do this on InfluxDB . So you can compare.
Distributed PostgreSQL on a Google Spanner Architecture - Storage Layer
Here they talk about an interesting project - YugaByte. This document-oriented distributed and cloud-based DBMS has two APIs, one of them PostgreSQL-compatible, and the other rooted in Cassandra QL. The bottom layer, DocDB, is inspired by Spanner. It supports ACID transactions with snapshot isolation and with SERALIZABLE in the future.
PostgreSQL logging best practices
In this blog, promotestrongDM’s own tool, but also explain the different approaches to logging.
Fault tolerance for PostgreSQL DBMS
In the 2nd issue of the Open Systems. DBMS ”this year (both on paper and online) Igor Kosenkov and Igor Levshin talk about Corosync & Pacemaker clusters.
Why the RDBMS is the future of distributed databases
Marco Slot of Citus Data writes : 10 years ago I came to work at Amazon Web Services and even then I realized that the main thing in the architecture of distributed systems is to find a compromise. But in practice, the spectrum of compromises in a distributed system is much wider than in the CAP theorem.
Managing Query Execution Plans for Aurora PostgreSQL
What givesmanaging query plans and how to set up Aurora.
Hypothetical Indexes in PostgreSQL
Avinash Vallarapu from Percona clarifies the meaning of hypothetical indexes. Are they in PostgreSQL? Yes. But you need to install the extension.
Subscribe to the postgresso channel !
Send ideas and suggestions to the mail: [email protected]
Previous issues: # 15 , # 14 , # 13 , # 12 , # 11 (special) , # 10 , # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1
