Hello! The past year for Postgres users and developers has been very busy. And the 2018th promises to be no less interesting and bright. Under the cut, I will talk about one of the first high-profile events for the community - PGConf.Russia 2018, and about what I remember the past year for the Avito DBA team.

PGConf.Russia 2018: announcement

From 5 to 8 February at the site of the Faculty of Economics of Moscow State University. Mv Lomonosov will be held PGConf.Russia 2018 . Let me remind you that this is an international technical conference on an open PostgreSQL DBMS, which annually gathers more than 500 developers, database administrators and IT managers for the exchange of experience and professional communication. The program includes master classes from world leading experts, reports in three thematic streams, examples of best practices and error analysis, the developers' living room and blitz reports from the audience.

Program

In general, I urge everyone to carefully study the program , since you will have to choose from three streams of thematic reports. Here is a list of reports that I will definitely try to get.

What should we expect in PG 11?
In 2017, the new PostgreSQL 10 was released, in which there are many new interesting features. It is very important to understand how users perceived them, how they began to use, what to expect in the new 11th version. Oleg Bartunov promised to consider these and many other issues at the upcoming conference during his report.

Credereum - Postgres with blockchain support
Alexander Korotkov will tell how to combine the proof and immutability of the blockchain with the performance and efficiency of traditional DBMS.

Securing PostgreSQL From External Attack
Bruce Momgian - how intruders can steal passwords, see requests and their results, and even replace sessions and return false data

Master Class: Managing High Availability PostgreSQL Clusters with Patroni
Alexey Klyukin and Alexander Kukushkin will tell in detail how Patroni works, demonstrate the configuration of highly accessible clusters based on it, and introduce you to various additional features and diagnostics of problems.

Modern PostgreSQL High Availability
If you are thinking about implementing PostgreSQL's automatic resolution of crashes and do not want to be called at night due to the fall of the master server and the need to switch to the reserve, do not miss the Álvaro Hernández report, which will provide a comparative review of PostgreSQL HA solutions.

Connected storages
The topic of connected storage for PostgreSQL has already become the talk of the town. In this report, Alexander Korotkov will address the following questions: a review of the resulting interface for plug-in storages; PostgreSQL kernel changes needed to implement this interface current and potential applications of this interface, including heap with undo-log and in-memory OLTP engine; the current status of patches and the prospect of their adoption in the core; further development of the interface in order to expand the capabilities of the connected storages (columnar, index-organized, LSM, etc.).

In addition, we are waiting for the session of the blitz-speeches from the audience. If you did not have time to apply for a report, you have a chance to share your experience. And, of course, the program has many other very interesting speeches.

Avito on PGConf.Russia 2018

DBA Avito since the founding of the conference constantly take part in it. This time we want to share the results of our research: “ Cases of using logical replication for data recovery in PostgreSQL 10 ”. This report is based on a study that I, Mikhail Tyurin and Sergey Burladyan began to conduct when the alpha-version of PostgreSQL 10 was released and continues to this day.

In the report, Mikhail Tyurin and I will show how our cases of data recovery and reconciliation after accidents for Londiste (PGQ, in particular) in distributed data processing systems can be implemented on a new logical replication subsystem in PostgreSQL 10. In the current implementation of out-of-box replication, this nontrivial solutions.
We can open a number of issues in the community, in order to make it easier to implement recovery mechanisms - as easy as setting up logical replication in 10-ke. The implementation of our solutions is related to playing replication queue events, changing the position of receiving subscriber events relative to the replication source, “rewinding” the subscriber from the unreachable “future” to the position of the source that is in the “past” relative to the recipients after the accident:

• re-initializing the subscriber from another subscriber;
• REDO mechanism at the source - implementation of the "rewind" replication queue;
• UNDO mechanism - implementation of consumer-side replication recovery.

Avito and PostgreSQL: Retro 2017

While we are all waiting for the upcoming event - I will tell you about 2017, the life of the Postgres community and our (Avito) participation in it.

In March 2017 on PGConf.Russia 2017 Dmitry Vagin told about how Avito collects and monitors the load on the database. From the report you can find out how we send metrics from stored procedures to Grafana, collect pg_stat * metrics as they are displayed in Grafana. Examples of life - in this video .

In May 2017, I was able to realize a long-held dream and visit PGCon 2017 in Canadian Ottawa. This is the very first annual PostgreSQL user and developer conference that has been running since 2007.
A very atmospheric place to meet with colleagues, discuss, build relationships, study valuable information and, as a rule, talk about the work that happens with PostgreSQL. The “Unconference” day left the most vivid impressions of myself, and in general, the whole conference is more like a large planning meeting of colleagues and friends.

One of the most memorable reports: Corruption War Stories Real-Life Corruption Issues and Recovery . And in the end - a charity charity-auction from Dan Langille.

If you attend this conference, stay for this event - believe me, there will be a lot of impressions.

Our colleague Viktor Yagofarov made a report “PgBouncer and 20,000 transactions per second on one machine: fine tuning, hacks, solving common and unusual problems” on PG DAY Russia 17 , PostgresOpen 2017 and Swiss PGDay 2017 .

Swiss PGDay 2017

... and noted that our conferences PG DAY RUSSIA and PGConf.Russia have a very high level of organization and reports, in many respects even surpassing Europe and America.

In September, as some blog readers remember, we organized PGHACK.

This competition is a bit like a competition for information security specialists, CTF. The difference is that PGHACK was developed by us, taking into account the tasks faced by specialists working with databases. And this is not only a competition, but also professional communication. We have not heard that someone conducted such thematic events in this format, it was the first competition of this kind. From the feedback from participants: 100% of them would like to visit #PGHACK again.

How it was:

• many postgres;
• excitement of competition;
• fire in the eyes of participants;
• cool discussions on the sidelines;
• bright emotions and great mood.

The story about our own platform, which collected Pkorobeinikov , can be found here . Photo report posted on Facebook . But a review of the competition and analysis of tasks from our winner Envek .
After that, we repeated this event on HL 2017.

Pending the release of Postgres 10, Peter Korobeinikov translated the Release Notes .

At Highload 2017, we conducted a master class “Logical Replication and Avito” with Sergey Burladyan and Mikhail Tyurin. You can watch the video from it.

Hot discussion of our research after the master class HL 2017

Nikolai Vorobiev shared our best practice of code deployment and a tool for collecting metrics from plpgsql:

• Versioning and warming PostgreSQL code. A story about how to write, test, and deploy hundreds of databases and thousands of stored procedures to multiple servers with the ability to quickly roll back in a 24x7 download and not die.
• PG Metricus. Collecting metrics from plpgsql code or as three lines of code has simplified life. Links: blog , pg_metricus_python , pg_metricus_c , https://pgxn.org/dist/pg_metricus .

They shared our development - PostgreSQL RPC built on top of pgq .

They told about Avito's “internal kitchen” at RuPostgres Live with Nikolay Samokhvalov. Text transcript can be read here .

We diagnosed a number of errors and described them in bug reports:

• BUG # 15006: "make check" error if current user is "user" ;
• Why standby restores some WALs many times from archive? ;
• BUG # 14879: Bug connected with table structure and query function query plan invalidation ;
• BUG # 14699: Statement trigger and logical replication ;
• BUG # 14710: Function now () and logical replication ;
• Re: Broken hint bits (freeze) ;
• setReadOnly does not work with pgbouncer and transaction pooling mode # 848 ;
• DBE-5693 Respect the default search_path again ;
• Debian Bug report logs - # 868367. postgresql-common: standby cannot start with "recovery.conf file found in config directory" ;
• Invalidation pg catalog cache in trigger functions .

Found an error of Listen / Notify via pgbouncer, and Sergey Burladyan made a patch for the bouncer .

The year 2017 was very interesting and fruitful. 2018 promises to be even more eventful! As they say, to be continued. See you at PGConf.Russia 2018!