Digest news from the world of PostgreSQL. Issue number 9

We continue to introduce you to the most interesting news on PostgreSQL. Between this release and # 8 more time has passed than usual, so it will be more voluminous.

---

Releases

PostgreSQL 11 Beta 3
This release is different fixed bugs - and common to a series of releases, which are further, and specific to version 11. Details of the fixed (and not only) bugs can be found here . General beta info here .

PostgreSQL 10.5
Fixed serious bugs. For example, a problem was found and fixed in libpq: it did not restore all the connection state variables to the initial state when attempting to reconnect. The variable that specified whether a password was needed was not restored, which could have resulted in illegal access to servers using dblink or postgres_fdw.
In total, there are about 20 fixes and improvements in the release concerning a variety of DBMS mechanisms: VACUUM, WAL, GIN-indices, POSIX-semaphores and much more. Including for work under Windows. They are painted here .

Together with 10.5 and Beta 3 there were corresponding updates 9.6.10, 9.5.14, 9.4.19, 9.3.24 . You can download, as usual, from here .
')
Postgres Pro Standard 9.5.14.1, 9.6.10.1 and 10.5.1
Postgres Pro Standard 9.5.14.1 and 10.5.1 releases are created by Postgres Professional based on the corresponding PostgreSQL versions (without the last digit in the release number, eg PostgreSQL 10.5) and the corresponding previous Postgres Pro Standard versions.
Key improvements to all three (relative to the previous ones):

• The pg_variables module now supports transaction variables. (See Section F.37 of the documentation)
• The auto_explain module auto_explain now display scheduling time.
• Improved version of Postgres Pro Standard for Windows: eliminated the implicit limit on the number of simultaneously opened files in each server subprocess.

In 9.6.10.1 and in 10.5.1 the differences are much larger. For example, the pg_probackup module has pg_probackup updated to version 2.0.19 , in which features such as:

• The restore command can skip copy validation to speed up cluster recovery;
• Improved parallel execution of incremental copying
• Now you can combine incremental copies with the parent full copy to save disk space.

As well as others - see the documentation, sections “Appendix E. Release Notes”, for example, here . Innovations 9.6.10.1 and 10.5.1 overlap strongly, but still be careful: for example, 10.5.2 can (c pg_probackup ) restore a cluster to a given position in the log (LSN).

pgAdmin 4 v3.3, v3.2, v3.3 .
In the latest version ( 3.3 ) only minor amendments. In 3.2 more serious changes: for example, support for SCRAM; JIT when visualizing plans. The biggest changes were in 3.1 , released at the very end of June: it fixed more than 30 bugs, and among the new features:

• Added support for SSH tunnels,
• LISTEN / NOTIFY support in the Query Tool,
• PostgreSQL 11 support for functions and procedures.
• Improved support for Greenplum

PgBouncer 1.9.0
The new version has expanded functionality to improve and facilitate monitoring and monitoring in high-availability architectures. Improved documentation. All changes can be read here , and download from here .

Ora2Pg 19.0
In the new version of this migration tool, there are innovations, for example, export of Oracle hash sections is added with PG_SUPPORTS_PARTITION enabled - and it appeared only in PostgreSQL 11. The whole huge list is here . Fixed flaws that users complained about.
Download from here .

pg_chameleon 2.0.9
This package is able to replicate the MySQL database using MySQL, keeping records in PostgreSQL as JSONB . Written in Python 3 . Next, the function on pl/pgsql parses JSONB , losing changes to PostgreSQL. About the release can be read here.
By the way, here is an article from Percona on how to use pg_chameleon - Replication from Percona Server for MySQL to PostgreSQL using pg_chameleon .

Migrate2Postgres
Another migration tool. You can read and download here . The tool is cross-platform, works through JDBC, an example is built in for migration from MS SQL Server .

Ajqvue v2.0
This is an open source PostgreSQL GUI written in Java. Works with various DBMS, incl. and with PostgreSQL.
In release 2.0, improvements are largely due to the DB_to_FileMemoryDB plugin; DataCharts plugin updated to JFreeChart, 1.5.0. Some information is on the site. Sors here .

PostGIS 2.5.0 beta2
This beta is made for future releases, although it will work with any 9.4 or later. Full functionality will be achieved with PostgreSQL 11beta3 + and GEOS 3.7.0 beta2.

pgmetrics 1.4
This application , delivered as a single binary with no dependencies on third-party software, collects information and statistics from a running PostgreSQL server, displays it in text format or exports it to JSON for further processing.

repmgr 4.1.0
Improvements and bug fixes of this version can be found here .

Slony 2.2.7
Minor release of the good old Elephants. fixed bugs. Download here .

PostgreSQL Page Verification tool
Google has opened to the public its tool for checking the integrity of backup pages.
Here is the documentation . Download the code here .
There is an eWeek article on how this works: Google’s company says.
(By the way, Postgres Pro Enterprise already has a similar test: it includes the pg_probackup utility, which can do page-by-page backup validation without cluster data recovery)

Education

Video clips of the DBA1 course read at the end of February in Nizhny Novgorod by Postgres Professional Pavel Luzanov and Egor Rogov are posted.

Articles

Tuning Autovacuum in PostgreSQL and Autovacuum Internals
On always up-to-date topic of avtovacuum settings they write in the blog of the company Percona.

There is also an article about FDW : Foreign Data Wrappers in PostgreSQL and a closer look at postgres_fdw

Orchestrated saga or how to build business transactions in services with the pattern database per service
Detailed article Konstantin Evteev blog Avito .

Understanding And Reading the PostgreSQL System Catalog
Brian Fehrle's article on working with system catalogs (with examples) on the Severalnines blog .

3 ways to detect slow queries in PostgreSQL
CyberTech’s Hans-Jürgen Schönig article (Hans-Jürgen Schönig) discusses tracking of slow transactions with examples:

• log analysis;
• checking plans with auto_explain;
• the use of aggregated information in pg_stat_statements.

Waiting for PostgreSQL 12 - Allow multi-inserts during COPY into a partitioned table
Depesz article (Depesz, that is, Hubert Lubaszewski ) on parallel insertion into a partitioned table when running the COPY command.

At the same time, Alvaro Herrera (Álvaro Herrera) on the 2ndQuadrant blog writes about partitioning in version 11: Talk slides: Partitioning Improvements in PostgreSQL 11 . But these are slides from a conference in Brazil - PGConf.Brazil 2018 . The text should appear later.

How Citus real-time executor parallelizes Postgres queries
Craig Kerstiens' article (Craig Kerstiens) talks about parallelizing SQL queries in their own Postkres fork.
The credo is expressed in the subtitle: Writing in SQL, thinking in MapReduce.

Enabling PostgreSQL applications with Hybrid Transactional / Analytical
The Fujitsu blog explains why their FUJITSU Enterprise Postgres can work effectively both as OLTP and as OLAP DBMS.
For analytics there is some architecture that has some advantages of vertical storage - the Vertical Clustered Index (VCI) .

Conference. Webinars Mitapas.

Krasnodar Dev Days # 3
The annual conference of developers of Krasnodar and the region will be held on September 15.
The conference program has a PostgreSQL Failover Solution report with automatic failover from Igor Kosenkov , Postgres Professional.
Conference site .

Techtrain
This is a conference-festival of developers. It will be held on September 1-2 in St. Petersburg. There will be a PostgreSQL community booth , where a master class and an intellectual quiz will be held, and an elephant path report or career in open source by Oleg Bartunov will open the conference.

Scaling an application to PostgreSQL
Mitap in Avito will take place on September 15 at 12.00 - 16.30. Reports will be devoted to scaling, isolation levels during replication, DBMS Asynchronous Client Library , Clock-SI . speakers from Yandex, Avito, Postgres Professional . Registration will be announced later, stay tuned ..

PostgreOpen Silicon Valley 2018
It will be held September 5-7 in San Francisco.

Will Postgres Live Forever?
Bruce Momdjan's webinar on Postgres? scheduled for August 29 at 17:00 Moscow time. This is not the first appearance of Bruce as a visionary, but few were able to see him.
Register here .

PGDay (Portland PostgreSQL Users Group)
This user group will host a conference on September 10th. Proposals are accepted .

PostgresConf South Africa 2018
Will be held in Johannesburg on October 9th.

PostgreSQL Conference Europe 2018
It will be held in Lisbon on October 23-26.

---

Subscribe to the postgresso channel!

Send your ideas and wishes to email: news_channel@postgrespro.ru
Previous issues: # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1