Digest news from the world of PostgreSQL. Issue number 14
We continue to introduce you to the most interesting news on PostgreSQL.
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.
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:
- Ivan Frolkov , Postgres Professional
Common application errors when working with Postgres
- Ivan Muratov , “First Monitoring Company”
PostgreSQL + PostGIS + TimescaleDB - storage for transport monitoring systems
- Nikolay Samokhvalov , Nombox LLC Industrial Approach to PostgreSQL Tuning
: Database Experiments
We are planning to write more about some reports. While you can watch the presentation .
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.
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 .
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 .
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
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.
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
checknow supports ANSI color codes;
- fixed bugs.
All changes and bugfixes at this address . Sources here .
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 .
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
You can read about the changes here . Documentation on
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 type
tsvectorwhich 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).
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.
This is a plugin for Nagios . In the new version, you can, for example, to filter results
oldest_idlexact. Details here . BSD license.
pgCenter 0.6.0 and 0.6.1
This is Alexey Lesovsky's
Go- utility from Data Egret . In version 0.6.0 there were :
- A new profiler that works with a
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
- pgCenter received the status of A + Go Report Card - a step to the recognition of pgCenter loyalty to the language style
- other changes.
Release 0.6.1. minor and not considered important changes. Github pgCenter here .
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
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 out
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
standalone- blog Michelle talks about:
- Functions for partitions;
- SKIP_LOCKED for VACUUM and ANALYZE;
- 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:
|Contribution to the %
|Number of patches
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:
|Contribution to the %
|Number of commits
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:
|Number of letters
That's all. See you again!
Subscribe to the postgresso channel !
Send your ideas and wishes to email: firstname.lastname@example.org
Previous issues: # 13 , # 12 , # 11 (special) , # 10 , # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1