PostgreSQL news digest. Issue No. 15
We continue to introduce you to the most interesting PostgreSQL news.
news
The main event of the month is, of course, Feature Freeze . March commitfest is closed. The main look of PostgreSQL 12 has been determined. Further there will be improvements and corrections, but not changes in functionality. On the most important features of version 12 in the near future we will make a separate publication.
Is “Vulnerability” Vulnerable?
The cryptic code CVE-2019-9193 hides a politically important cause of concern for the community . It's about the construction of COPY ... PROGRAM, which appeared in 9.3, which makes it possible to execute OS files in a request and write to standard input or read from standard program output.
When a vulnerability is not a vulnerability
However, the classic PostgreSQL Magnus Hagander explains in his blog:
This “vulnerability” is equivalent to the fact that in a typical Unix system you can log in as root and create or edit files, and execute commands as root <...> Being a superuser, you can run files on the OS not only with COPY ... PROGRAM. "<...> So, there is no vulnerability in PostgreSQL, but there are definitely vulnerabilitiesPostgreSQL installations .
Releases
pgAdmin4 V4.5
There are very few changes in this version. Among them are adapter support for Python psycopg2 v2.8 (see below) and ESLinter support . You can download from this page .
Prior to that, in version pgAdmin4 4.4 (released after the previous review), 35 bugs were fixed. Among the new in 4.4:
- Query history in the Query Tool is available from different sessions;
- IDENTITY column support;
- documents can be built in ePub format
- for the data storage directory, the full email is used as the base, and not just its left side.
Details on the PgAdmin page .
Prior to this, in version 4.3, the Commit and Rollback buttons were added to the Query Tool; Now you can run several versions of PostgreSQL utilities in containers. The other 5 new features of that version can be found on the PgAdmin page .
repods
There was a cloud platform repods based on PostgreSQL. The main purpose is analytics. An interesting visualization of database schemas and more.
pg_activity 1.5.0
This version of the monitoring tool has a new feature :
- summary of active compounds;
- Application Name;
- You can save the list of active requests to a CSV file;
- PGSERVICE support;
- when a connection is lost, it tries to reconnect to the PostgreSQL cluster.
You can download from both github and pyri .
Barman 2.7
Fixed parallel copying errors. Previously, a fatal error with a parallel backup led to the fact that the process had to be killed manually and then cleaned the server from the consequences of an unsuccessful backup.
When working in the geo-redundancy mode, cron used to fall when the network was disconnected during synchronization. Accordingly, the following tasks were not performed. Also fixed a bug in I / O in UTF-8. Details and files to download on sourceforge.
Pgpool-ii
Version 4.0.4 was released, and with it also 3.7.9, 3.6.16, 3.5.20 and 3.4.23. From innovations: it is possible to set the client list of ciphers - ssl_ciphers. Thus, Pgpool-II supports the corresponding PostgreSQL feature. The release is here , and RPMs can be downloaded from here . In the “articles” section, we mention two publications by PostgreSQL veteran Tatsuo Ishii about the innovations of the upcoming release - Pgpool-II 4.1.
padnag 1.2.4
In the new version of this tool, the synchronization of PostgreSQL roles with users and Active Directory groups has appeared including:
- Ubuntu 1604 instead of Ubuntu 1804 LTS;
- Switching to AWS CodeCommit and CodeBuild
- automatic testing for proper integration with Windows 2012R2;
- PostgreSQL versions up to 9.4 are no longer tested.
temboard 3.0
The PostgreSQL remote monitoring tool from Dalibo Labs introduced the Maintenance plugin , which monitors databases, charts, tables, and indexes. Helps detect base swelling and effectively manage VACUUM, ANALYZE or REINDEX.
psycopg 2.8
This version of the PostgreSQL adapter for Python (fully implements the Python DB API 2.0) is a remarkable event: the previous version was released 2 years ago. In this version , among other things, appeared
- PostgreSQL error mapping for Python exceptions;
- some improvements enrich the connection status and query results information;
- improved asynchronous interaction and concurrency;
- the rejection of support for obsolete versions of Python (2.6, 3.2, 3.3) allowed us to significantly rework the code.
pg2arrow A
utility that sends a PostgreSQL query and packs the result into Apache Arrow format.
pgMustard
Interface for explain analyse, which can also give recommendations on query acceleration. pgMustard - commercial software, trial period of 7 days. Feedback is welcome, developers say. Version 1.0 works with PostgreSQL 9.6 and later.
pg_snakeoil 1.0 PostgreSQL
antivirus extension . For scanning, it uses ClamAV , which does not slow down PostgreSQL.
vipsql vim
plugin for working in psql.
migra
This tool works with diff PostgreSQL schemas.
dbdot
utilitya command line that generates a DOT description based on the database schema. It is distributed in binary packages for various platforms.
Zedstore - compressed in-core columnar storage
This is a pretty crude patch, but you should pay attention to it. In just a couple of weeks, a column storage was made. In the future, PostgreSQL will have pluggable storages of various types, for which the tableam (table access method) patch, an important part of the storage connectivity API, has now been made and passed. One of the new repositories is already well known: Zheap with UNDO, it is in operation. Now - columnar (and there is still a VOPS extension for vectorized computing).
Articles and Blogs
Benchmarking connection poolers
Postgres Pullers. Comparative testing of the performance of various pullers for Postgres: starting with the popular pgbouncer and up to the newcomer Odyssey and the new development - built-in pullers from Postgres Professional. Tested by Konstantin Knizhnik.
Imperative to Declarative to Imperative
Bruce Momjan is still happy to speak out on the philosophical topics of the DBMS structure: this time he came across the chain “generating declarative code by an imperative code, which [ultimately] will again be executed as an imperative code” ( imperative language that generates declarative output that can be converted into an imperative program and executed).
Bruce unveiled heretheir considerations, bearing in mind the discussion of the corresponding thread in which Peter Geoghegan and Chris Travers spoke.
Uniting SQL and NoSQL for Monitoring: Why PostgreSQL is the ultimate data store for Prometheus
The subtitle of this article (also not too short) explains: "How to use Prometheus, PostgreSQL + TimescaleDB and Grafana to store, analyze and visualize metrics."
What's new in PostgreSQL 11
Markus Winand, author of PostgreSQL Performance Explained, writesnot about the future (PostgreSQL 12), but about the present: "about the main theme of PostgreSQL 11 - window functions." Until 2018, PostgreSQL alone fought with them (if we talk about open source DBMS), but since then some of them have caught up and overtaken. And at 11 again, a jerk forward. Explained by examples, with diagrams and functionality matrices.
Why SQL is beating NoSQL, and what this means for the future of data
The TimescaleDB website has a great article on the history of the battles of SQL and NoSQL over data domination - dating back to the 70s and examples from relational algebra.
Be careful with CTE in PostgreSQL
An article by Haki Benita, an independent developer, on the dangers of CTE. Compares with Oracle, talks about materialization, subqueries as an alternative, CTE inlining, hints and more. The article was published in the fall, and some changes have occurred since then, but many problems are still relevant.
JOIN LATERAL
Elin Mustein, previously a popular author of GeneralBits, recalls the 2ndQuadrant blog post about JOIN LATERAL features. And also about restrictions, about monitoring of relevant requests and about where it makes sense to use JOIN LATERAL, and where not.
Postgres-XL and global MVCC
Suzuki Koichi, who worked at NTT and moved to 2ndQuadrant, asks interesting questions about distributed transactions and is going to discuss them in future articles.
Waiting for PostgreSQL 12 - REINDEX CONCURRENTLY
Depecz, that is, Hubert Lubaszewski, tells and gives examples of the use of this important innovation.
GeoJSON Features from PostGIS
Paul Ramsey talks about how to convert tables into GeoJSON from existing tools in PostgreSQL and your own short script.
Metrics to Monitor in Your PostgreSQL Database
This article continues the theme of another article , "Guide: How to Monitor PostgreSQL Using Telegraf and InfluxDB."
PostgreSQL for a SQL Server DBA: The Tooling Stinks
In this not too loyal Postgres article- “Tools for the PostgreSQL admin are rotten” - observations of a person with an MS SQL background, so his experience and impromptu comparative analysis may be interesting for some postgresession readers. The author considers the main obstacle to the transition MS SQL -> PostgreSQL relative poverty (I have a milder word) of the toolkit, and not the shortcomings of the DBMS itself. Accordingly, we are talking about PgAdmin, but also about DataGrip and Novicat.
Shared Relation Cache and Statement Level Load Balancing
In his blog, Playing with PostgreSQL and Pgpool, Tatsuo Ishii writes about the system-shared cache for the Pgpool-II process that will appear in version 4.1.
In continuation- We are talking about load balancing, which in Pgpool-II 4.1 is possible at the session level. The behavior is determined by the new parameter: statement_level_load_balance in pgpool.conf.
Conferences
Saint HighLoad ++ 2019 (St. Petersburg)
At the last conference, they talked a lot about the future. Here are the slides of the report , where Oleg Bartunov summarizes the innovations of the upcoming version. The conference was held on April 8-9.
German-speaking PostgreSQL Conference 2019
For those who want to practice German and visit Leipzig: this conference will be held on May 10.
PGDay Warsaw
This conference takes place in the context of OpenSource Day on May 14th.
PGDay.IT 2019
Italian PG-Day will be held in Bologna on May 16-17.
The PGCon 2019 Ottawa
Canada Conference will be held May 28-31.
Swiss PGDay 2019
At this conference, which will be held on June 28 in Rappertsville, near Zurich, it is still possible (until April 18) to send applications for reports and register.
PostgresLondon 2019
This conference will be held July 1-3 (the 1st optional day for workshops).
PGConf.Brazil 2019
Sao Paulo is waiting for guests on August 1-3.
Austrian pgDay
New event on September 6th.
PostgresConf South Africa 2019 To
be held in Johannesburg on October 8-9. Applications are accepted until July 30.
And also there is a conference in the corporate style:
Percona Live the Open the Source the Database Conference 2019
Percona holdsthis conference in Texas (Austin, capital) May 28-30.
Postgres Vision 2019
This conference is hosted by EnterpriseDB in Boston June 24-26.
That's all. See you soon!
Subscribe to the postgresso channel !
Send ideas and suggestions to the mail: news_channel@postgrespro.ru
Previous issues: # 14 , # 13 , # 12 , # 11 (special) , # 10 , # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1