Postresso 22

Releases

There is a feeling that the month-long release stream between this and the previous Postgresso release has noticeably thinned out.

PgBouncer 1.14.0

In this version Support for Unix-domain sockets on Windows appeared – keeping up with PostgreSQL 13.
The server can now be entered using encrypted SCRAM secrets stored in userlist.txt or received in auth_query.

There is support for creating sockets using systemd.

Pgpool-II 4.1.2, 4.0.9, 3.7.14, 3.6.21 and 3.5.25

Minor releases. Nothing really remarkable correction a large number of minor bugs. You can download sorts and RPM from here.

SQLancer

He is Synthesized query lancer. This interesting Java program is designed to find logical errors in the DBMS (the topic is relevant, see the Jepsen article below). It first creates a database, and then generates queries that create, modify, and kill data. In addition, indexes and views are created.

There are three approaches to generating queries. For example, in the first –
Pivoted Query Synthesis (Pqs) – a record is selected and queries are generated for it, which obviously must return this record. The absence indicates an error. Another is testing the optimization bugs of Non-optimizing Reference Engine Construction (NoREC), the third is logical errors again, but using Ternary Logic Partitioning (TLP)

pgsodium 1.1.1

This is written by Michel Pelletier expansion (it laid out on PGXN, registration is needed), which adapts the functions of the cryptographic library libsodium to SQL. The author admits that he drew inspiration from the extension pgcryptokey Bruce Momjan. When a key is loaded, new keys can be obtained by generating key pairs with deterministic functions, for example crpypto_box_seed_new_keypair(). Upload from here.

veil2, pgbitmap

vail2 – a framework for quickly building systems with RLS (Virtual Private Databases, VPD) give a chance simplify access control, user authentication – github, documentation.

pgbitmap is a curious thing, while in alpha stage. This is an extension for working with bitmaps in PostgreSQL, you can use them much to dofor example, subtract from each other, watch intersections, convert to textual representations. It was developed for access control tasks for RLS (Virtual Private Databases, VPD). Both are written by Marc Munro.

Citus 9.3

IN this version basically the capabilities that were already in 9.2, and which work on the concept of HTAP (hybrid transactional analytic processing), that is, to make the database suitable for both transactional tasks and analytics (especially for analytics), are developing and improving. Accordingly, optimization of sharding and window functions in the spotlight.

Previously, only queries involving different shards were supported in the case when PARTITION BY refers to the column by which the table was divided into shards – the distribution column. Now you can not think about it.

The choice of shards (shard pruning) now works more universally: earlier there were problems with queries with OR in WHERE clauses, now with all Boolean logical expressions in WHERE only the necessary shards are selected.

Expressions of the form INSERT … SELECT have worked in distributed transactions before, but not with sequences. Now with them too.

In Citus, you can create 3 types of tables: reference (dictionary), distributed (distributed)
and local (“regular” Postgres tables). There were problems of JOIN dictionary tables with ordinary ones, and now the dictionary lives on the cluster coordinator, there are no problems, and such joins work faster.

And another thing: adaptive connection management has appeared – so that with many parallel requests it is possible to do without external pullers (without PgBouncer).

wal2mongo v1.0.6

The Chinese company HighGo Software, which was also mentioned in Postgresso 21, with the help of this plugin Provides the ability to arrange logical replication from PostgreSQL to MongoDB. This is practical if raw data is accumulated in PG, which is further analyzed in Mongo. Tested and built with PostgreSQL 12.3. Are available sorts and binaries for CentOS6, 7, for Windows 7, 10, 2019.

Pitrery 3.1

This is a set of bash scripts for PITR backup. The release of several bug fixes. Sway from here.

psqlODBC 12.02.0000

IN this release some set of not too fundamental changes and improvements: the appearance of an option IgnoreTimeout, eg.

Articles

Multi-Master Replication Solutions for PostgreSQL

In this article from Percona are considered

• BDR (2ndQuadrant);
• xDB (EnterpriseDB);
• PostgreSQL XC / XC2 (NTT in conjunction with EnterpriseDB);
• PostgreSQL XL (fork of PostgreSQL-XC, now supported by 2ndQuadrant);
• Rubyrep (author Arnt Lehmann – Arndt Lehmann);
• Bucardo (authors End Point Corporation).

Strange that is not visible in this summary multimaster in Postgres Pro Enterprise.

Jepsen: PostgreSQL 12.3

Company analysts Jepsen.io set their utility on Postgres Elle, which tests the validity of declared isolation levels. And they found a bug. No tragedy: they found them in a dozen respected DBMSs; the bug is already fixed, and the patch will appear in the next minor release 12.4, scheduled for August 13th. Kyle Kingsbury writes about it in the blog of his company. Actually the SERIALIZABLE level in PostgreSQL is SSI (Serializable Snapshot Isolation), which, however, was known – as well as about the fact that REPEATABLE READ is Snapshot Isolation. But this was considered a sufficient approximation to the corresponding standard ANSI SQL levels.

Elle found that PEREATABLE READ in PostgreSQL is weaker than the standard (but, as Jepsen says, the definition itself for this level is rather ambiguous – so okay) and that the SERIALIZABLE level in PostgreSQL allows anomaly G-2, and this is not comme il faut. This event has already been discussed on Tuesday. # Ru.Postgres # 23 0:01:57 (see Webinars and Mitapas section). We decided that the situation is far from extreme.

PostgreSQL Transparent Data Encryption

Cybertec enlightens postgresists on the topic of TDE, good their patch accessible and working (many are currently developing TDE: EDB, eg)

TDE encrypts everything except the pg_stat_statements extension data and transaction metadata into a 128-bit AES-CTR. Encryption is supported by processor instructions to speed up I / O, overhead on the encryption itself against the background of I / O is invisible. TDE works from PostgreSQL 9.6.12 up to 12.3.

Safety Systems Can Reduce Safety

What is the purpose? – rhetorically sharpens Bruce Momjan – make the system safer, or make it looked safe? Meet some requirements from the outside? But even in the first case, this is not always successful, says Bruce, recalling Chernobyl. Security systems complicate the system and introduce crash scenarios that were not possible without it. Answer 4 questions

1. How serious is the incident that the security system wants to prevent?
2. how likely is it?
3. How likely is a security failure?
4. How can a security failure affect the entire system?

Now do some complicated calculations with these unknowns. For DBMS, such components as backup, error logging, replication, connection pool will give different contributions to these issues – which is what Bruce discusses in a note.

When Does a Commit Happen?

IN this note Bruce digs deeper into the mechanism of commits: they do not fit into the yes / no logic, but break up into 6 phases. As a result of a process crash, for example, it may turn out that the commit is already flushed to disk, but is not yet visible to clients or will be already replicated and visible to requests on replicas, but not visible to requests on the wizard – sorry: on primary. Bruce also recommends reading this thread Andrei Borodin from Yandex.

10 Things Postgres Could Improve – Part 1, 2

On my blog PG-Pyatnets (PG Phriday, cf. with Tuesdays #ruPostgres) Sean Thomas (Shaun Thomas, 2ndQuadrant) announced 4 parts of the series 10 things to improve with Postgres:

1. problems with transaction IDs;
2. unpleasant replication surprises;
3. MVCC and why you need to be careful with storage;
4. kernel functionality.

The first 2 of them have already been released.

In the first we are, of course, talking about wraparound, 32-bit failure per identifier (and two billion transactions) and vacuum. I’ll add from myself that so far only Postgres Pro Enterprise has 64-bit transaction countersbut movement in that direction little by little occur in PostgreSQL. Sean, however, hopes that the new pluggable storage engines will solve the problem. In the meantime, she advises playing with partitioning, monitoring dropped prepared transactions, and makes a list of recommendations.

Second part, about replication, brighter: at least the names of the heads fascinate: “ebb and flow”, “drink from a fire hose”, “plugging holes in a dam”. This series, perhaps, can not be attributed to in depth, but Sean places emphasis and reminds that on a sinful earth developers have something to do.

By the way, the format is becoming fashionable: in Postgresso 21 was About 7 Things You Can Improve In PG – The author of Cybertech Kaarel Moppela – can be compared. In addition, Sean himself refers to an article in an adjacent genre: 10 things i hate in postgres Rick Bronson, working with petabyte databases. It is clear that such things as vacuum will not be bypassed by any author of this genre, while other points do not intersect everywhere. Stay tuned, lovers of the genre.

EXPLAIN ANALYZE May Be Lying to You

Is it really lying? Варlvaro Hernández calls this is a (quantum-mechanical) effect of the observer, and he himself observes a 1.5 times increase in the query time with EXPLAIN ANALYZE compared to the real query. The answer to the system clock – Alvaro explains what happens in both cases. This article was discussed at # ru.Postgres # 22.

Home education

Certification

Starting July 1st Postgres Professional resumes process certification PostgreSQL DBA. There are currently 10 free time slots for recording. Certification will be carried out in full compliance with sanitary and epidemiological measures – masks, gloves, distance.

Webinars and Mitapas

Tuesdays #ruPostgres: # 22, # 23

The main dock of Postgres Tuesdays, where there is also a new link for active participation in Zoom, here.

On the # 23 started with a DBA survey: do you have a beard. 49.4% answered Yes. Since the guest of the issue was Vladimir Borodin (Borodin just does not have a beard), the head of the DBaaS development team in Yandex.Cloud, we talked a lot about cloud solutions. The list of affected (sometimes quite fluently) topics is on the video signature, it is huge: there are bugs in MySQL, and about the migration of multi-terabyte databases to the clouds, about sharding in PG (relatively many), about Odyssey, pgbouncer, about multiprimary and much more friend.

# 22 was dedicated to visualizing plans about “lies“EXPLAIN ANALYZE, about pgsentinel, datasentinel.io and more. The highlight of the program were the PASH Viewer utilities (wait events analysis for active sessions) and the young plan-exporter (exporting EXPLAIN data to visualization tools directly from psql).

Online @Databases Meetup # 2

Mitap in Mail.ru (more precisely: Mail.ru Cloud Solutions & Tarantool) will be held on July 25. Program:

• How to build a hybrid cloud using Kubernetes, which can replace DBaaS. Peter Zaitsev, general director Percona;
• from the hosts: S3 architecture: 3 years of Mail.ru Cloud Storage evolution. Vladimir Perepelitsaplatform architect Mail.ru Cloud Solutions;
• Json[b] in Postgres: It’s time for a great unification. Oleg Bartunov, general director Postgres professional;
• The evolution of Postgres Pro over the years and strategic development plans. Ivan PanchenkoDeputy Director General Postgres professional.

Theory & Practice of PostgreSQL Migration

The FORS meeting will take place on July 2. IN the program:

• How to upgrade to PostgreSQL in 10 steps. Ivan PanchenkoDeputy Director General Postgres professional;
• We pass. PostgreSQL Migration: Expectations and Reality. Alexey Beresnevlead teacher UKC FORS;
• Practical experience of migration on the example of Zabbix monitoring server on PostgreSQL. Oleg Konstantinov, head of the group of engineers of the implementation and support service, FORS Development Center.;
• Ample opportunities for switching to PostgreSQL: application development, migration tool, cluster on OC “Elbrus. Alexander Lyubushkin, Technical Director FORS Telecom.

Postgres Vision 2020

Should pass the online June 23-24. Bruce Momjan with Marc Linster talk about PostgreSQL’s future cloudy world. Oleg Bartunov will deploy in 25 minutes encyclopedia full text search.

Conferences

The PGCon 2020

On May 26-29, the first major conference entirely online was held. In peacetime, PGCon takes place in Ottawa. So far, reviews from “excellent” to “expecting the worst.” The big minus is that it is much easier to get distracted by work and household chores than offline, therefore, regularly attending scheduled performances is noticeably less. Many expect to see the records later. The slides of the reports are laid out (in various places, but there are links in the program), no video yet, but promised (except for master classes that are only live).

Ilya Kosmodemyansky made a speech from the Russian-speaking community (slides) and Nikolai Samokhvalov (slides) in the master classes, Alexander Korotkov spoke about the community plans sharding, Andrei Borodin about indexes as extensions, Oleg Bartunov about JSONB-roadmap, Alexey Kondratov about Ptrack 2.0.

PG Day Russia

Moved for a year – on July 9, 2021.

FOSS4G 2020
Will pass in Calgary, Canada, August 24-29. Applications accepted.

PGDay Ukraine

One-day conference in 2 streams should take place September 5 in Lviv. Among speakers Magnus Hagander, Ilya Kosmodemyansky, Devrim Gündüz, Tomas Vondra, Oleksiy Vasilyev and Oleksiy Klyukin, Pavlo Golub. Program here.

pgDay Israel 2020
Outlined on September 10th in Tel Aviv. Speakers will include Ivan Panchenko, Pavlo Golub, Dorofei Proleskovsky, Andrei Borodin.

PGDay Austria

In the summer residence of the Habsburg castle SchönbrunnVienna September 18th must pass the conference PGDay Austria.

subscribe to the channel postgresso!

Send ideas and suggestions to the mail: news_channel@postgrespro.ru
Previous issues: # 21, # 20, # 19, # 18, # 17, # 16, # 15, # 14, # 13, # 12, # 11 (special), # 10, # 9, # 8, # 7 , # 6, # 5, #4, # 3, # 2, #1