📜 ⬆️ ⬇️

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. This is a message on the Citus website , as well as on the MS website .

Postgres Pro Enterprise Certified

Postgres Pro Enterprise DBMS has received the FSTEC certificate, and now in the Postgres Pro kit 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 , previously known in Europe and Asia, acquired OmniTI to enter the American market. credativ specializes in deploying and supporting open source projects. 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 for 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 OTEP “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

The conference will be held on February 24 in Ankara.

PostgreSQL @ SCaLE

The 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

The 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

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

pgAdmin4 4.2

PgAdmin 4 v4.2 released:


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

pgBadger 10.2

Just 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 has moved from the Dalibo githab to the github.com/darold/pgbadger github and the website pgbadger.darold.net of the developer Gilles Darold. From the changelog it follows that in the new version:


And fixed bugs, of course.

barman 2.6

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


All changes and bugfixes at this address . Sources here .

Ora2g

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


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

pg_probackup 2.0.26

New version of the utility for backup and recovery from Postgres Professional . In it, except for the corrected bugs:


You can read about the changes here . The pg_probackup documentation is here .

datasketches 1.0.0 and other new PGXN

A new extension has been posted on PGXN - datasketches 1.0.0 Alexander Saydakov . This extension Datasketches libraries that have approximate estimation algorithms 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 tsvector2 1.0.0 extension - this is an alternative to the tsvector type, which is used for full-text search (compression is improved and there is no limit to 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. Simultaneously with the pg_pathman , the latest version of pg_partman is posted - 4.0.0 by Keith Fiske .

PAF v2.2.1

A new version of PostgreSQL Automatic Failover (PAF) has been released. Fixed bugs, including those that caused swelling of 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, for example, you can filter the results by application_name in longest_query and oldest_idlexact . Details here . BSD license.

pgCenter 0.6.0 and 0.6.1

This is the Go- utility of Alexey Lesovsky (Alexey Lesovsky) from Data Egret . In version 0.6.0 there were :


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

Education


Tutorial "Fundamentals of database technology"

Following the tutorial «PostgreSQL. The Basics of the SQL Language ”the first part of the textbook B.A. Novikova and Ye.A.Gorshkova "The 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 course "Basics of database technology"

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

Book-baby "Postgres: first acquaintance"

A new, now 5th edition of the little Postgres: first acquaintance book has been released . 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 complemented and deepened 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 about various types of locks.

New QPT course

Query optimization, previously reviewed in the DBA2 course by PostgreSQL 9.5, is now highlighted in a separate course , equally needed by 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 Joseph M. Hellerstein, the chapter “Looking Back at Postgres,” from the collection of Michael Stounbraker. The original of this chapter in PDF can be read free on arXiv.org.

Articles and blogs


Three Factors of Authentication

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


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 Pooling and PostgreSQL - The Case for LDAP
It discusses the configuration of a corporate system with
PostgreSQL 10, Wildfly Application Server, LDAP, pgbouncer. Examples with configuration files and code are given.

Migration from Mongo to Postgres: The Guardian newspaper experience

This is a translation 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 full and differential backup on schedule. He writes about backup policies, how to create the schedule itself, gives examples of commands.

An Overview of JSON Capabilities Within PostgreSQL

Venkata Nagothi writes about full-text search and gives examples of the correct and incorrect use of the @> and #> operators

Lessons learned scaling PostgreSQL database to 1.2bn records / month

The article by Gajus Kuizinas (Gajus Kuizinas) is the story of a large project with a Kubernetes cluster. 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 made Google abandon it; Amazon RDS does not support the TimescaleDB extension that you decided to use for partitioning; Aiven.io did not show decisive flaws, but also with weak advantages. As a result, the project was launched on its own. For backup, this project uses barman , the brainchild of the 2ndQuadrant . We remind you that barman 2.6 just been released.

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 , but it contains a lot of information about cloud solutions with PostgreSQL 10 from five cloud providers.

pg_sampletolog: Analog to log a sample of statements

The article by Adrien Nayrat from the French company Doctolib tells why the author made the pg_sampletolog extension and how it samples transactions into logs (choosing, for example, 10% of transactions).

The most useful Postgres extension: pg_stat_statements

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

Postgres 12 highlight series by Michel Paquier

In his standalone blog, Michelle talks about:


Featured gits

Peter Eizentraut of 2ndQuadrant shares his experience of using git , advises how to work in your own branches, how to merge and commit.

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

The developers of TimescaleDB, the PostgreSQL extensions for working with time series, claim that their brainchild works at 5 nodes faster than Cassandra by 30, that the insertion works 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 theoretical article talks about the Japanese development of DudeTx. The authors sought to combine the best in UNDO and REDO approaches. In this new concept is based on the capabilities of NVM (non-volatile memory).

Who contributed to PostgreSQL Development in 2018?

Robert Haas
published calculations - his own and Stephen Frost (Stephen Frost) - contributions 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 score 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

Source: https://habr.com/ru/post/436352/

More articles: