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 about 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 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
    Releases Postgres Pro Standard 9.5.14.1 and 10.5.1 set Postgres Professional based on the respective versions of PostgreSQL (without the last digit in the release, eg PostgreSQL 10.5) and the corresponding previous versions of Postgres Pro Standard.
    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 module auto_explaincan 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 significantly greater. For example, the module has been pg_probackupupdated to version 2.0.19 , in which features such as:
    • The restore command can skip copy validation to speed up cluster recovery;
    • Improved parallel incremental copying
    • Now you can combine incremental backups 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, but be careful: for example, 10.5.2 can (c pg_probackup) restore the 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: more than 30 bugs were fixed in it, 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 control 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, saving records in PostgreSQL asJSONB. Written on Python 3. Next, the function pl/pgsqldoes not parse 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. Available to download 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, revisions are largely due to the plugin DB_to_FileMemoryDB; 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 A
    minor release of 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 article in eWeek about how it works: the Google's page verification tool CAN help the discover Organizations data loss and corruption earlier in the change cycle, Company About enterprise | says.
    (By the way, Postgres Pro Enterprise already has a similar check: it includes a utility pg_probackupthat can do pager validation of backup without cluster data recovery)

    Education


    Videos 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 the actual topic of setting auto-vacuum is written 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
    A detailed article by Konstantin Evteev in the Avito blog .

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

    3 ways to detect slow queries in PostgreSQL
    In the article Hans-Jürgen Schönig (Hans-Jürgen Schönig) from CyberTech 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
    article Missive (Depesz, ie Hubert Lyubashevskaya ) of concurrent inserts into a partitioned table when 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
    Article Craig Kerstinsa(Craig Kerstiens) talks about parallelizing SQL queries in their own Postgres fork.
    The credo is expressed in the subtitle: Writing in SQL, thinking in MapReduce.

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

    Conference. Webinars Mitapy.


    Krasnodar Dev Days # 3
    The annual conference of the 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. It will feature the booth of the PostgreSQL community , where a master class and an intellectual quiz will be held, and a report on the Elephant Path, or Career in open source by Oleg Bartunov will open the conference .

    Scaling an application on
    Mitap's PostgreSQL in Avitowill be held September 15 at 12.00 - 16.30. Reports will focus on scaling, isolation level replication, DBMS Asynchronous Client Library, Clock-SI. speakers from Yandex, Avito, Postgres Professional . Registration will be announced later, stay tuned ..

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

    Will Postgres Live Forever?
    Webinar Bruce Momjian on the theme: Does Postgres is eternal? 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. Suggestions are accepted .

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

    PostgreSQL Conference Europe 2018 To
    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
    Tags:

    Also popular now: