Digest news from the world of PostgreSQL. Issue number 15

We continue to introduce you to the most interesting news on PostgreSQL.

news

The main event of the month is, of course, Feature Freeze . March kommitfest closed. The main appearance of the PostgreSQL 12 version was determined. Further there will be improvements and corrections, but not changes in functionality. About the most important features of version 12 in the near future we will make a separate publication.

')

Is Vulnerability Vulnerable?

Under the mysterious code CVE-2019-9193 there is a politically important concern for the community. It is about the COPY ... PROGRAM construction, which appeared in 9.3, which makes it possible to execute the OS files in the request and write to the standard input or read from the standard output of the program.



When a vulnerability is not a vulnerability



However, PostgreSQL classic Magnus Hagander (Magnus Hagander) explains in his blog post:

This “vulnerability” is equivalent to the fact that in a typical Unix system you can log in to root and create or edit files and execute commands as root. <...> As a superuser, you can run files in the OS not only with COPY ... PROGRAM. "<...> So, there are no vulnerabilities in PostgreSQL, but there are definitely vulnerable PostgreSQL installations .

Releases

pgAdmin4 V4.5



There are very few changes in this version. Among them, adapter support for Python psycopg2 v2.8 (see below) and support for ESLinter . You can download from this page .

Prior to this, in the pgAdmin4 4.4 version (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 columns support;
• documents can be built in ePub format
• for the data storage directory, the full email is used as the base, and not just the left part.

Details on the PgAdmin page.

Prior to this, version 4.3 added the Commit and Rollback buttons in the Query Tool; It has become possible to run multiple versions of PostgreSQL utilities in containers. The other 5 new features of this version can be found on the PgAdmin page .



repods



The PostgreSQL-based repods cloud platform has appeared. The main purpose is analytics. Interesting visualization of database schemas and other things.



pg_activity 1.5.0



In this version of the monitoring tool, a new one has appeared:

• summary of active compounds;
• app name;
• you can save a list of active requests to a CSV file;
• PGSERVICE support;
• when connection is lost, it tries to connect to the PostgreSQL cluster again.

It is possible to download both with github , and with 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 geo-redundancy mode, cron used to crash if the network was broken during synchronization. Accordingly, the following tasks were not performed. Also fixed a bug in the work of I / O in UTF-8. Details and files for download on sourceforge.



Pgpool-II



Version 4.0.4 has been released, and with it 3.7.9, 3.6.16, 3.5.20 and 3.4.23. From innovations: you can set the client cipher list - ssl_ciphers. Thus, the corresponding PostgreSQL capability is supported in Pgpool-II. About the release here , and RPMs can be downloaded from here . In the "articles" section, we mention two publications by PostgreSQL veteran Tatsuo Ishii (Tatsuo Ishii) about the innovations of the future release - Pgpool-II 4.1.



padnag 1.2.4



In the new version of this tool, the synchronization of PostgreSQL role roles with users and Active Directory groups has appeared, including:

• Ubuntu 1604 instead of Ubuntu 1804 LTS;
• transition to AWS CodeCommit and CodeBuild;
• automatic testing for proper integration with Windows 2012R2;
• there are no more PostgreSQL versions up to 9.4.

Details are here .



temboard 3.0



In the remote monitoring tool PostgreSQL from Dalibo Labs, a Maintenance plugin has appeared that 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 implementing the Python DB API 2.0 interface) 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 information about the connection status and query results;
• improved asynchronous communication and concurrency;
• the refusal to support older versions of Python (2.6, 3.2, 3.3) allowed us to significantly rework the code.

pg2arrow



A utility that sends a PostgreSQL request and packages the result in Apache Arrow format.



pgMustard



The interface for “explain analyze”, which is also capable of making recommendations for speeding up requests. pgMustard - commercial software, a trial period of 7 days. Feedback is welcome - say the developers. Version 1.0 works with PostgreSQL 9.6 and later.



pg_snakeoil 1.0



PostgreSQL anti-virus extension . For scanning, it uses ClamAV , which does not slow down PostgreSQL.



vipsql



Plugin for vim to work in psql.



migra



This tool works with PostgreSQL diff diff schemes.



dbdot



A command line utility that generates a DOT description based on the database schema. Distributed in binary packages for various platforms.



Zedstore - compressed in-core columnar storage



This is a pretty raw patch, but you should pay attention to it. In just a couple of weeks, column storage was done. In the future, PostgreSQL will have plugin storages of different types (pluggable storages), for which the commitfest patch tableam (table access method) is now made and passed - an important part of the API for connecting storages. One of the new repositories is already well known: Zheap with UNDO, it is in operation. Now - columnar (and there is still an extension VOPS for vectorized calculations).

Articles and blogs

Benchmarking connection poolers



Pullers for Postgres. Comparative testing of the performance of various pullers for Postgres: from the popular pgbouncer to the newcomer, Odyssey, and the new development of built-in pullers from Postgres Professional. He tested Konstantin Knizhnik.



Imperative to Declarative to Imperative



Bruce Momdzhan is still happy to speak on the philosophical topics of the DBMS structure: this time, the "generation of declarative code with 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 announced his views here , referring to the discussion of the relevant thread, which expressed Peter Goygan (Peter Geoghegan) and Chris Travers (Chris Travers).



Unlike 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 (Markus Winand), author of the PostgreSQL Performance Explained book, does not write about the future (PostgreSQL 12), but about the present: “about the main theme of PostgreSQL 11 — window functions”. Until 2018, PostgreSQL fought alone with them (if we talk about the open-source DBMS), but since then some of them caught up and overtook. And at 11 again, a dash forward. Explained by examples, with schemas and matrixes of functionality.



Why SQL is beating NoSQL,



TimescaleDB has a large article about the history of SQL and NoSQL battles for domination over data - starting in the 70s and examples from relational algebra.



Be careful with CTE in PostgreSQL



Article by Haki Benita (Haki Benita), independent developer, on the dangers of CTE . Compares with Oracle, talks about materialization, subqueries as an alternative, CTE inlining, hints and so on. The article was published in the fall, and some changes have occurred since then, but many problems are still relevant.



JOIN LATERAL



Elaine Mustaine, previously a popular author of GeneralBits, reminds the 2ndQuadrant blog about the possibilities of JOIN LATERAL. And also about restrictions, about monitoring relevant queries and about where it makes sense to use JOIN LATERAL, and where it doesn't.



Postgres-XL and global MVCC



Koichi Suzuki (Suzuki Koichi), who worked at NTT and switched 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 (Paul Ramsey) tells how to convert tables into GeoJSON from the tools already existing in PostgreSQL and from our 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 article, which is not too loyal to Postgres - “Tools for the PostgreSQL admin is foul” - observations of a person with MS SQL background, so his experience and improvised comparative analysis may be interesting for some post-squeezed readers. The author considers the main obstacle for the transition of MS SQL -> PostgreSQL relative poverty (in my case it is softer) of the toolkit, and not the shortcomings of the DBMS itself. Respectively it is 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 cache of the system catalog, which will appear in version 4.1, shared by the Pgpool-II processes.



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 report slides where Oleg Bartunov summarizes the innovations of the upcoming version. The conference was held April 8-9.



German-speaking PostgreSQL Conference 2019



For those who want to practice in German and visit Leipzig: this conference will take place on May 10th.



PGDay Warsaw



This conference takes place in the context of OpenSource Day on May 14th.



PGDay.IT 2019



An Italian PG-day will be held in Bologna on May 16-17.



PGCon 2019 Ottawa



The Canadian Conference will be held May 28-31.



Swiss PGDay 2019



At this conference, which will take place on June 28 in Rapperstsville, near Zurich, it is still possible (until April 18) to send applications for reports and to register.



PostgresLondon 2019



This conference will be held July 1-3 (1st optional day for master classes).



PGConf.Brazil 2019



Sao Paulo is waiting for guests 1-3 August.



Austrian pgDay



A new event , it will be held on September 6.



PostgresConf South Africa 2019



Will be held in Johannesburg on October 8-9. Applications are accepted until July 30.



And also there are conferences in corporate style:



Percona Live Open Source Database Conference 2019



Percona holds this conference in Texas (Austin, capital) on May 28-30.



Postgres Vision 2019



This conference is held by EnterpriseDB in Boston on June 24-26.

That's all. See you again!

---

Subscribe to the postgresso channel!



Send your ideas and wishes to email: news_channel@postgrespro.ru

Previous issues: # 14 , # 13 , # 12 , # 11 (special) , # 10 , # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1