Digest news from the world of PostgreSQL. Issue number 10

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

Releases

PostgreSQL 11 Beta 4
In this release, they fixed the bugs revealed after the release of Beta 3. Including:
')

• JIT now disabled by default.
• constraint names must be unique.
• removed a memory leak when accessing XMLTABLE
• fixed errors in stored procedures
• finalized the partitioning, including the selection of sections at the time of execution (runtime partition pruning)

Read more here .

PostgreSQL 10.5
In this release, several dozen fixes related to WAL, libpq, VACUUM and FREEZE, GIN indices, query parallelization, OpenSSL. Here is a list of them .

Postgres Pro Enterprise 10.5.2.
In this version there are the following innovations in relation to Postgres Pro Enterprise 10.5.1, they concern pgbench :

• pgbench now supports composite commands;
• With the help of the --latency-limit parameter, you can now limit the time --latency-limit for the repetition of transactions. If, when using this parameter, the value --max-tries=0 , transactions can be repeated an unlimited number of times, until the time specified by the --latency-limit parameter --latency-limit ;
• when calculating the number of processed transactions and the speed of execution (TPS), skipped and unsuccessful transactions are no longer taken into account.

Recall that during the time between our releases, Postgres Pro Enterprise 10.5.1 was released. . There are significant changes, you can read about them here .

Postgres-XL 10r1beta1
2ndQuadrant announced the release of the release, which can be downloaded here .
In the vanilla version based on PostgreSQL 10, the following improvements were picked up:

• declarative partitioning;
• improved query concurrency;
• overall performance significantly improved;
• improved monitoring and administration.

From the version based on PostgreSQL 9.6 taken:

• parallel execution of sequential scanning, JOINs and aggregations;
• during VACUUM FREEZE, pages are not scanned unnecessarily;
• significantly improved performance, especially in the case of multi-socket servers;
• full-text search is now able to do a phrasal search.

TimescaleDB 1.0.0 rc1
There was a release candidate of this DBMS, which is based on PostgreSQL, refined for storing and processing time series ( time-series ), made as an extension to PostgreSQL. With a modest release order number, the DBMS already has such customers as Bloomberg and Cray , millions of daunloods .

PostGIS 2.5.0 released
About a month after the beta came out and the official release. List of changes here
The official ChangeLog is here , download the weights from here .

repmgr 4.1.1
In the version announced by 2ndQuadrant there are improvements for more convenient work. Details here , and you can download here.

pgAdmin 4 v 3.3
In this release there are useful improvements:

• viewing geometric shapes;
• Added new options in the backup/restore version of PostgreSQL 11 and new options in pg_dumpall .

Packages are available for Windows, macOS, for Python Wheel, Docker, there are also tarballs. RPM and DEB will appear a bit later on postgresql.org

pgCenter
The new version of the monitoring utility, written by Alexey Lesovsky (Data Egret) , has been rewritten to Go . Now pgCenter can save statistics in files, take into account background processes, filter by regular expressions. The version is accompanied by an article where the innovations are described in detail. There is a demo. Download here .

temboard v2.0
In the new version of the means for monitoring and control of Postgres, which is offered in Dalibo , alerting has appeared. Download here .

pgBadger 10.0
This is a major release, in addition to fixing bugs, there are new, significant features:

• pgbouncer file format support (syslog log) ;
• support for all formats auto_explain format (text, xml, json and yaml) ;
• %q placeholder log_line_prefix support %q placeholder log_line_prefix ;
• jsonlog format jsonlog in Michel jsonlog extension, with option -f jsonlog pgbadger can parse the log;
• instead of SQL formatter/beautify now pgFormatter v3.0

Added a lot of options.
Source codes here .

pitrery 2.2
This is a set of bash scripts for working with PITR backups in PostgreSQL. In the new version, which can be found on the Dalibo githaba , it can, for example, save the configuration file and saved commands inside the backup itself.

pg_terminator
Depesz (i.e. Hubert Lyubashevsky) reports pg_terminator output . This monitoring utility can clean problem queries and / or connections.

Ora2Pg 19.1
Added:

• PostgreSQL 11 hash partitioning support
• export of the default section;
• support for stored procedure objects

As well as some other improvements for less painful exports and more meaningful debugging.
See all changes on github ;
download ;
documentation

Training

New training courses from Postgres Professional "SQL Language" and "Basics of database technology" started on September 10
From September 10 to October 19, 2018, two new training courses with the support of Postgres Professional are held at the UMC MSU and the PCF HSE.

• SQL language (read by SIBGU professor E.P. Morgunov );
• The basics of database technology (read by Professor B. A. Novikov of SPSU).

You can apply for the course “Basics of Database Technologies” by writing to the email address: m.abubekerov@postgrespro.ru , stating the full name of the letter in the letter.

Enterprise-Grade PostgreSQL: Built on Open Source Tools
Everyone is invited to the Percona webinar on October 10th. Register here . Topics will be discussed:

• how to secure a database cluster;
• high availability;
• How to choose a backup strategy and what tools to use;
• PostgreSQL scaling with pullers and load balancers;
• utilities / modules (extensions) for every day work of DBA, detailed logging;
• real-time monitoring and analysis.

Using Postgres to watch Star Wars!
In this video report, Will Leinweber tells and shows how to make a vintage cartoon using DBMS.

PgCodeKeeper PgCodeKeeper
20-minute video.

Articles

Preventing SQL Injections
Dimitri Fontaine 's short, intelligible article explains how to protect PostgreSQL from SQL injections. Not ad-free Python adapter psycopg.

Securing PostgreSQL as an Enterprise-Grade Environment
Percona employees describe the PostgreSQL security configuration process in enterprise information systems. We are talking about

• authentication
• authorization
• Rls
• encryption
• logging and auditing
• bug tracking

PostgreSQL Backup Strategy for an Enterprise-Grade Environment
The second article in this perkonovskoy series tells about the backup enterprise level. Both articles are not too detailed problem, but this is really very concise.

USE, RED, PgBouncer, its settings and monitoring
Great article from okmeter with many examples and illustrations.

PostgreSQL 11 and Just In Time Compilation of Queries
This article from Citus Data covers , among other things, the TPC-H Q1 benchmark, compared to the Pricing Summary Report in the real world. Benchmark is a request with a large number of units. It JIT how the JIT version of PostgreSQL 11 speeds up queries.

Setting up Streaming Replication in PostgreSQL
Avinash Vallarapu (Avinash Vallarapu) in the Percona blog tells how to prepare the master and slave for stream replication.

Upgrading to PostgreSQL 11 with Logical Replication
Peter Eizentraut (Peter Eisentraut) on the 2ndQuadrant blog discusses the pros and cons of upgrading PostgreSQL installation: using pg_dump and restore , or pg_upgrade , or using logical replication (in the kernel). It then details how to set up logical replication.

Parallelism in PostgreSQL: not spherical, not a horse, not in a vacuum
An article based on a real case with one of Postgres Professional 's customers. About diagnostics and patches, including queue managers of LWLock .

Parallel PostGIS and PgSQL 11
In his blog, Paul Ramsey (Paul Ramsey) shares PostGIS 2.5 tests on PostgreSQL 11, which show that some progress in parallelization has been made.

Partitioning Improvements in PostgreSQL 11
Alvaro Herrera reviews the 11th version patches related to partitioning.

Be careful with CTE in PostgreSQL
This article warns against mindlessly using CTE: a side effect could be a dramatic drop in performance.

Better PostgreSQL testing with Python: announcing pytest-pgsql and pgmock
Users, Clover Health employees present 2 utilities: pytest-pgsql and pgmock .

Conferences and meetings

past

Materials from the meeting #RuPostgres - videos, presentations, quiz analysis and photo report
Mitap was organized by Avito , the materials are posted here . Reports:

• Stas Kelvich (Postgres Professional) spoke about distributed transactions and time travel.
• Konstantin Evteev (Avito) made a presentation on scaling the application on PostgreSQL in Avito and shared tips and tricks.
• Mikhail Tyurin has prepared a report on logical replication and PostgreSQL transaction isolation levels.
• Sergey Khandrikov (Yandex) told the audience about the structure of OZO, the asynchronous type-safe header-only PostgreSQL client library for C ++ 17.

future

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

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

PGConf.Sibir 2018
It will be held on November 12-13 in Krasnoyarsk. Conference topics:

• Experience using PostgreSQL in various areas
• Experience in the implementation of transfer to PostgreSQL information systems that work with other DBMS
• Experience in developing system tools for PostgreSQL
• Highly loaded projects based on PostgreSQL
• Using PostgreSQL in web projects
• Big Data and PostgreSQL
• PostgreSQL Administration
• Using PostgreSQL in teaching in universities, colleges and schools.

PGConf.ASIA 2018
expected December 10-12 in Tokyo,

---

Subscribe to the postgresso channel!

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