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:

    • now disabled by default is JITcompilation.
    • names in constraints 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;
    • Using the parameter, --latency-limityou can now limit the time allotted for the repetition of transactions. If using this parameter value --max-tries=0, transactions can be repeated an unlimited number of times until the time specified by the parameter has expired --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;
    • performance improved overall;
    • 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 without the need;
    • 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 The
    release candidate of this DBMS, which is based on PostgreSQL, has appeared, has been improved for storing and processing time series ( time-series), made as an extension to PostgreSQL. With a modest release order number , the DBMS already has clients like Bloomberg and Cray , millions of daunloods .

    I came PostGIS 2.5.0
    Approximately one month after the beta was released and the official release. List of changes here
    Official ChangeLog here , download the weights from here .

    repmgr 4.1.1
    in the announced 2ndQuadrant-th version there are improvements for more convenient work. Details are here , and you can download it here

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

    • viewing geometric shapes;
    • Added new options in backup/restorePostgreSQL 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 it pgCentercan 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 tools for monitoring and managing Postgres, which is offered in Dalibo , there were notifications ( alerting). 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);
    • support %q placeholder в log_line_prefix;
    • Added a format jsonlogin the extension by Michel Paquier, with the option -f jsonlog pgbadger can parse the log;
    • instead SQL formatter/beautifynowpgFormatter v3.0

    Added a lot of options.
    Source codes here .

    pitrery 2.2
    This is a set of bashscripts for working with PITRbackup copies 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) announces -a exitpg_terminator . 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.
    View all changes on the githaba ;
    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 are held with the support of Postgres Professional

    • SQL language (read by SIBGU associate 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.ruspecifying the full name of the letter in the letter.

    Enterprise-Grade PostgreSQL: Built on Open Source Tools
    At the Percona webinar, everyone is invited 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.

    DemonstrationPgCodeKeeper
    20-minute video.

    Articles


    Preventing SQL Injections
    Dimitri Fontaine 's short, intelligible article explains how to protect PostgreSQL from SQL injections. Not an ad-free Pythonadapter Securing PostgreSQL as an Enterprise-Grade Environment Percona employees describe the process of configuring PostgreSQL security in enterprise information systems. We are talking aboutpsycopg.




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

    PostgreSQL Backup Strategy for an Enterprise-Grade Environment
    The second article in this perkonovskaya series talks about enterprise-level backup. Both articles do not detail the problem too much, but this one is quite laconic.

    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 tells about 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 shows how JITPostgreSQL 11 speeds up queries.

    Setting up Streaming Replication in PostgreSQL
    Avinash Vallarapu (Avinash Vallarapu) blog Percona tells you how to prepare masterand slavefor streaming replication.

    Upgrading to PostgreSQL 11 with Logical Replication
    Peter Yazentraut (Peter Eisentraut) on the 2ndQuadrant blog discusses the pros and cons of upgrading PostgreSQL installation: using pg_dumpand restore, orpg_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 from one of Postgres Professional 's customers . About diagnostics and patches, including block queue managers 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 has been made in parallelizing.

    Partitioning Improvements in PostgreSQL 11
    Alvaro Herrera (Alvaro Herrera) reviews11th version patches related to partitioning.

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

    Better PostgreSQL testing with Python: announcing pytest-pgsql and pgmock
    Users, employees of Clover Health present 2 utilities: pytest-pgsqland 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 how OZO works, 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
    will be held November 12-13 in Krasnoyarsk. Conference topics:

    • Experience using PostgreSQL in various areas
    • Experience in the implementation of the 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
    is 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
    Tags:

    Also popular now: