Digest news from the world of PostgreSQL. Issue number 14



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

    news


    Microsoft acquired Citus Data

    Of course, the main news in the world of PostgreSQL. There is a message on the Citus website , as well as on the MS website .

    Postgres Pro Enterprise Certified

    DBMS Postgres Pro Enterprise has received the FSTEC certificate, and now in the Postgres Pro suite there is also a “Certified Postgres Pro Enterprise Version”. Prior to this, the certificate necessary for working with personal data was available only to Postgres Pro Standard (“Certified Postgres Pro Version”). Details on the site .

    credativ: PostgreSQL Competence Center

    The German firm credativ , before well-known in Europe and Asia, has acquired a firm OmniTIto enter the American market. credativ specializes in project deployment and support open source. PostgreSQL Competence Center will now open in the US, which will deal with highly critical projects and support the database throughout their life cycle.

    Conferences


    Postgres On The Beach (Ibiza) The

    beaches of Ibiza are waiting for speakers and listeners on June 21-22, and even better to fly on the 20th and stay until the evening of the 24th because of the rich entertainment program over the weekend.

    PGConf.Russia 2019 The

    conference was held on February 4-6 at the Faculty of Economics of Moscow State University, gathering a record number of participants - more than 700. There was a lot of new things . In a separate stream highlighted reports on the topic of Postgres and 1C . In total, 55 reports, 9 master classes and 3 round tables were held in three streams. The most vigorous discussion was caused by the round table “Import Substitution of System and Infrastructure Software” , organized with the support of the OTF “Otechestvenniy Soft”.

    Prizes for the best reports at the conference received:


    We are planning to write more about some reports. While you can watch the presentation .

    image

    Turkish PostgreSQL

    Conference will be held on February 24 in Ankara.

    PostgreSQL @ SCaLE A

    two-day two-line conference will be held March 7-8 in the city of Pasadena (USA) as part of SCaLE 17X.

    Postgres Conference 2019

    Conference will be held in New York on March 18-22.

    Prague PostgreSQL Developer Day 2019

    The P2D2 2019 conference in Prague is already on February 13-14.

    Releases


    PostgreSQL 11.2

    Released all updates of the supported PostgreSQL versions: 11.2, 10.7, 9.6.12, 9.5.16 and 9.4.21. In these versions there are important corrections: first of all, the behavior of PostgreSQL when calling is changed fsync(). Also fixed some shortcomings of sectioning and 70 other errors that have accumulated over the past 3 months. Details here .

    pgAdmin4 4.2

    Released pgAdmin 4 v4.2:

    • the statistics panel does not freeze even when there are more than a thousand tables;
    • the browser warns you before closing or refreshing the page;
    • the result of the query will be shown even if the results window (Data Output) is disconnected from the query window (Query Tool);
    • improved navigation and optimized widgets.

    40 bugs fixed (and in v4.1 - 7, v4.0 - 26). The list of bug fixes is here .
    Download from here .

    pgBadger 10.2

    Immediately two news about pgBadger (Perl graphical log analyzer). Version 10.2 released. But if you follow the link (for example, in David Fetter's news feed), you will not go anywhere. Because the project is moved to githaba Dalibo on githab github.com/darold/pgbadger and site pgbadger.darold.net Developer - Gilles Darolya (Gilles Darold). It follows from the changelog that in the new version:

    • option added exclude-db, that is, you can skip information related to the specified database;
    • added ability to work with logs via ftpand http. In this case, the log is analyzed at boot and is not saved to disk. You can set log addresses on different servers (remote and local, for example);
    • on a sshlog understands on a place. You can specify files for analysis using * . The option -rfor remote servers is still retained for compatibility, but will be eliminated.

    And fixed bugs, of course.

    barman 2.6

    2ndQuadrant announced the release of a new release. What is in it:

    • geographical redundancy for disaster recovery;
    • A command has been added put-walso that you can run the archive_commandDBMS using the script barman-wal-archivethat is in the package barman-cli;
    • operation checknow supports ANSI color codes;
    • fixed bugs.

    All changes and bugfixes at this address . Sources here .

    Ora2Pg The

    jubilee version is released - 20.0 - version of the Ora2Pg utility. This is a work by the same author - Gilles Darol . From the new:

    • BFILE export to the type is byteanow done using the PL / SQL function, which extracts the data from the BFILE and converts it to a form suitable for INSERT or COPY in PostgreSQL;
    • the directive DATA_EXPORT_ORDER appeared, now it is possible to export not in alphabetical order, but considering the size of the tables;
    • The directive NO_BLOB_EXPORT was added (the name speaks for itself).

    There are other changes, many bugs fixed. Changer is here . About the developer github release here .

    pg_probackup 2.0.26

    A new version of the Postgres Professional backup and restore utility . In it, except for the corrected bugs:

    • during validation, the current version pg_probackupand the version created by the backup are now checked . Working pg_probackupwith a copy of a more recent version is explicitly prohibited (i.e. no direct compatibility);
    • improved Windows support;
    • improved support for tablespaces within PGDATA.

    You can read about the changes here . Documentation on pg_probackuphere .

    datasketches 1.0.0 and other new PGXN

    A new extension is posted on PGXN - datasketches 1.0.0authorship by Alexander Saydakov . This extension connects the library Datasketches, where there are algorithms approximate estimates for Big Data. On the PGXN website created by David Wheeler, many more extensions can be found. For example, Citus 8.1.1 is a DBMS that works as a PostgreSQL extension. There you can also see the extension tsvector2 1.0.0 - this is an alternative to the typetsvectorwhich is used for full-text search (compression is improved and there is no limit of 1 MB). tsvector2 is laid out on PGXN in December, and two months earlier the latest version of the pg_pathman extension , 1.5.2, also developed by Postgres Professional, appeared there. At the same time pg_pathman, the latest version of pg_partman is 4.0.0 Keith Fiske (Keith Fiske).

    PAF v2.2.1

    A new version of PostgreSQL Automatic Failover (PAF) has been released. Fixed bugs, including swelling logs. You can download source codes, RPMs or debbs from the project github . The documentation is here . There is a demo . PostgreSQL license.

    check_pgactivity v2.4

    This is a plugin for Nagios . In the new version, you can, for example, to filter results application_namein longest_queryand oldest_idlexact. Details here . BSD license.

    pgCenter 0.6.0 and 0.6.1

    This is Alexey Lesovsky'sGo- utility from Data Egret . In version 0.6.0 there were :

    • A new profiler that works with a wait_eventview attribute pg_stat_activitythat helps you explore long-playing queries and figure out where they spend their time.
    • support for the GoReleaser utility that helps to build binary packages .rpmand .deb;
    • pgCenter received the status of A + Go Report Card - a step to the recognition of pgCenter loyalty to the language style Go;
    • other changes.

    Release 0.6.1. minor and not considered important changes. Github pgCenter here .

    Education


    Tutorial "Fundamentals of database technology"

    Following the textbook «PostgreSQL. The Basics of the SQL Language ”the first part of the textbook B.A. Novikova and Ye.A.Gorshkova "Basics of database technology . " The textbook can be purchased at the publishing house DMK Press , online stores ozon.ru , Labirint.ru and retail book networks. In PDF format, the first part of the book can be downloaded freely from the Postgres Professional website . The second part of the book is being prepared for publication. The textbook covers database theory, methods and algorithms used in the implementation of the DBMS, as well as their features in the PostgreSQL system.

    Video of the course "Basics of database technology"

    Lined slides and videos of the first two parts of the course B.A. Novikova .

    Book-baby “Postgres: first acquaintance”

    A new, already 5th edition of the book-baby “Postgres: first acquaintance” has been published . Starting from the 4th edition this book is also published in English.

    DBA2 Course Update

    PostgreSQL 9.5's previous DBA2 course was called “advanced” and included topics that complement and deepen DBA1 material. The new DBA2 course, according to PostgreSQL 10, turned out to be more focused due to the separation of a part of the material into independent courses and was called “Setup and Monitoring”. If DBA1 discusses the server configuration mechanism, then this course explains exactly which parameters need to be configured and how to do it meaningfully, with an understanding of PostgreSQL's internal structure and based on feedback from monitoring. The course material has been significantly updated , a module has been added for various types of locks.

    New QPT Course Query

    Optimization, previously reviewed in the DBA2 course for PostgreSQL 9.5, is now highlighted in a separate course.equally necessary for both administrators and application developers. The course has been updated to reflect the new version of PostgreSQL, in particular, the material on parallel query execution has been added. All course examples now use a demo database .

    Postgres in retrospect

    Elena Indrupskaya translated the chapter “Looking Back at Postgres” by Joseph Hellerstein from the collection about Michael Stonebreaker. The original of this chapter in PDF can be read free on arXiv.org.

    Articles and blogs


    Three Factors of Authentication

    Bruce Momdjan made a short but conceptual note on PostgreSQL's authentication principles on his blog. According to him, of three factors:

    • what you know (password, PIN);
    • what you have (phone, device for authentication);
    • who you are (fingerprint, iris, voice);

    in PostgreSQL, only variations of the first are directly used. With some tricks, however, you can use PIV devices, such as the YubiKey . It is proposed to consider whether to build in PostgreSQL a direct support for authentication types 2 and 3, or simply to clarify in detail those who wish with the help of documentation. Please note that before this, Bruce wrote about 15 authentication methods .

    One Security System for Connection, Connection Pooling and PostgreSQL - The Case for LDAP
    This article discusses the configuration of a corporate system with
    PostgreSQL 10, Wildfly, LDAP, pgbouncer application server. Examples with configuration files and code are given.

    Migrating from Mongo to Postgres: The Guardian

    It Experiencetranslation of the article Bye bye Mongo, Hello Postgres from the Guardian itself .

    Scheduling Backups En Masse with the Postgres Operator

    Jonathan Katz (Jonathan S. Katz) tells how to make a scheduled full and differential backups. He writes about backup policies, how to create the schedule itself, gives examples of commands.

    An Overview of JSON Capabilities Within PostgreSQL

    Venkata nudity (Venkata Nagothi) writes, including full text search and provides examples of proper and improper use of operators @>and #>

    Lessons learned scaling PostgreSQL database to 1.2bn records / month

    ArticleGajus Kuizinas (Gajus Kuizinas) - is the story of the embodiment of a large project with a Kubernetescluster. Investigated four options for placing data: Google, Amazon, Aiven.io and at home. The bug in Cloud SQL for PostgreSQL and the sluggish response of the support service forced us to abandon Google ; Amazon RDS does not support the TimescaleDB extension that you decided to use for partitioning; in Aiven.io not found critical flaws, but with the advantages of obscenities. As a result, the project was launched on its own. For backup in this project, use barman , the brainchild of the 2ndQuadrant . We remind you that just came outbarman 2.6.

    PostgreSQL Performance in AWS, GCP, Azure

    , DO and UpCloud This article is , of course, biased: it was written by the CEO of Aiven.io Oskari Saarenmaa (Oskari Saarenmaa), but there is a lot of information about cloud solutions with PostgreSQL 10 from five cloud providers.

    pg_sampletolog: An extension to log a sample of statements

    Article Adrien Naira (Adrien Nayrat) from the French company Doctolib explains why the author has made the expansion pg_sampletolog, and it is sampled in the transaction log files (by choosing, for example, 10% of transactions).

    The most useful Postgres extension: pg_stat_statements

    Craig Kerstiens (Craig Kerstiens)made a squeeze out of his report on PostgreSQL extensions at the FOSDEM conference in Brussels, concerning pg_stat_statements, as it was this extension that caused the most interest.

    A series of articles Postgres 12 highlight Michel Paquier

    In his standalone- blog Michelle talks about:

    • Functions for partitions;
    • SKIP_LOCKED for VACUUM and ANALYZE;
    • pg_promote;
    • wal_sender_timeout now user-settable;
    • Controlling SSL protocol;
    • New PGXS options for isolation and TAP tests;
    • Dos prevention.

    Maintaining feature branches and submitting patches with Git

    Peter Ayzentraut (Peter Eisentraut) of 2ndQuadrant shares his experience of using git, advises how to work in their own branches as Marj and commit.

    Eye or the Tiger: Benchmarking Cassandra vs. TimescaleDB for time-series data

    The developers of TimescaleDB, extensions of PostgreSQL to work with time series, claim that their brainchild works on 5 nodes faster than Cassandra by 30, that insertion is faster, and queries have accelerated up to 5800 times. And this is at 10 times lower cost of renting Azur nodes, a more flexible data model and full SQL support.

    DudeTx: Durable Transactions Made Decoupled

    This TheoreticalThis article is about the Japanese development of DudeTx. The authors sought to combine the best in the approaches of UNDO and REDO . In this new concept is based on the capabilities of NVM (non-volatile memory).

    Who contributed to PostgreSQL Development in 2018?

    Robert Haas (Robert Haas)
    published calculations - his own and Stephen Frost (Stephen Frost) - contribution to PostgreSQL for 2018. In the nomination “authorship of patches,” the laureates are ordered by the number of lines of code:
    RatingDeveloperTotal linesContribution to the %Number of patches
    18Alexander Korotkov26711.0724
    20Anastasia Lubennikova23450.942
    22Anton Bykov21180.852
    24Nikita Glukhov18900.76five

    About a quarter of all new lines of code are written by Tom Lane .

    In the nomination “committers' activity” from Russia there are only two committers, and both from Postgres Professional:
    RatingDeveloperTotal linesContribution to the %Number of commits
    3Fedor Sigaev1364611.2844
    9Alexander Korotkov23451.9415

    It is worth noting that Alexander Korotkov became a committer only in the middle of 2018, so he managed to gain these results in six months.

    The third nomination is “ hackers correspondence activity ” (threshold> 100 letters). The top masters of the epistolary genre included:
    DeveloperNumber of letters
    Alexander Korotkov301
    Dmitry Dolgov226
    Konstantin Knizhnik223
    Andrey Borodin159
    Fedor Sigaev152
    Artur Zakirov111
    Sergey Kornilov106

    That's all. See you again!



    Subscribe to the postgresso channel !

    Send your ideas and wishes to email: news_channel@postgrespro.ru
    Previous issues: # 13 , # 12 , # 11 (special) , # 10 , # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1
    Tags:

    Also popular now: