Digest news from the world of PostgreSQL. Issue number 12
We continue to introduce you to the most interesting news on PostgreSQL.
Releases
PostgreSQL 11.1
10.6, 9.6.11, 9.5.15, 9.4.20 and 9.3.25 were also released with this version (this is the latest release of 9.3). This version has security related changes. Corrected the detected errors. You can read about releases here , and download here .
Postgres Pro Standard 11.1.1, 10.6.1, 9.6.11.1, 9.5.15.1, Postgres Pro Enterprise 10.6.1, 9.6.11.1
These releases are based on the corresponding vanilla versions of PostgreSQL and on previous versions of Standard and Enterprise. In the "Release Notes" section in the appropriate version of the documentation you can read about differences from the previous version of the family. For example, in Enterprise 10.6.1 and 9.6.11.1Added support for Astra Linux operating system Smolensk 1.6 and improved stability of autonomous transactions.
pgAdmin 4 version 3.5.
In this release, not only fixed bugs (11 pieces), but also added new features . Among them:
- Migrating from Bootstrap 3 to Bootstrap 4
- You can stop service processes (Backup / Restore / Maintenance ...)
Binaries for
Windows
and macOS
, as well as for Python Wheel
, Docker
container and tarball with source code can be downloaded from here . RPM
You and we DEB
should appear in the repositories postgresql.org
in the coming days. TimescaleDB 1.0
The announcement says that this version is suitable for use in industrial mode. This time-oriented database is designed as an extension to PostgreSQL. You can run different versions of TimescaleDB with the same PostgreSQL instance. The core of the development team is Princeton University graduates.
PipelineDB 1.0
Released PipelineDB 1.0. This DBMS too, as well as TimescaleDB, is focused on work with time series. But PipelineDB is optimized for aggregation and does not store large amounts of raw data, it is designed for analytics in real time. This DBMS is the successor to TelegraphCQ (streaming DBMS on PostgreSQL), hereinafter StreamDB (its commercial version). Now it works as a PostgreSQL extension under the Apache 2.0 license.
Postgres-XL 10R1
2ndQuadranthas announced the release of Postgres-XL 10R1. It supports most of the major new features from PostgreSQL’s recent past, such as declarative partitioning in PostgreSQL 10 and query parallelization, which appeared in 9.6. In 10R1, a full-text phrasal search appeared. Specific features of XL: The distributed query scheduler has been improved. Details here , and you can download here.
The GDS
2ndQuadrant reports that their Global Database as a Service (GDS) now supports PostgreSQL 11. It is designed for clouds , supports BDR, including in a geographically distributed environment. You can backup from any node to any and from any restore, it works automatically
failover
andswitchover
. You can recover in PITR mode. Some (rather sketchy) description is here . Foreign Data Wrapper for SQLite 1.1.0
In this version, a lot has been completed, started in 1.0.0 released shortly before. As a result, it has:
- PostgreSQL 11 support;
- push-down expressions WHERE;
- push-down aggregation;
- transaction support;
- on external tables, you can perform SELECT, INSERT, UPDATE, and DELETE.
Develop Foreign Data Wrapper for SQLite at Toshiba Software Engineering & Technology Center . Sources can be found in the repository .
pg_probackup 2.0.24
In the new version, bugs were fixed, and among the innovations regarding version 2.0.17:
- Starting with version PG 9.6, when backing up a replica, no connection to the master is required;
merge
now belongs to the category of stable operations (the commandmerge
updates the full parent backup using the specified incremental backups)- in this version, a more conservative approach to file validation: during the validation process,
pg_probackup
checks the checksums of the blocks, validates the block headers and checks whether there are any blocks “from the future”. This is the default behavior and can be undone; - in PAGE mode, you can now create a map of changed pages in parallel;
- Starting from version 10 you can set
LSN
as a restore point.
To see other innovations and fixes, you can look here . You can download from here . The Windows version is now in beta status.
Bucardo 5.5.0
This version adds support for PostgreSQL 11. Bucardo is a daemon written in
Perl
, which does trigger-based replication. You can configure replication at the table level. pg_back 1.7
This is a new version of the backup script . The script can save roles and table spaces.
dbForge Data Compare for PostgreSQL 3.1
In this versionsupport for connections to PostgreSQL 10.x and 11.x. The utility Data Compare allows you to compare databases and individual tables in them, helps to write scripts to synchronize databases.
pgCodeKeeper 5.2.0
This version of the database schema manager adds support for procedures, which appeared in PostgreSQL 11. You can learn about new features and fixes here . You can download from here .
Cybertec PostgreSQL Configurator
In this tuning utility , support for PostgreSQL 11 also appeared.
V1.5 of pgmetrics
In this release, support for SCRAM-SHA-256 authentication has been added . Also added the ability to collect information only about the bases from the specified list. Github release. Details here .
pglogical 2.2.1
2ndQuadrant informs that the new version has full support for PostgreSQL 11.
pgpool-II 4.0.2
In addition to this version, Pgpool-II 4.0.2, 3.7.7, 3.6.14, 3.5.18, 3.4.21 are available, and here versions up to 3.6 are no longer distributed in source codes. About the release here . Sources and RPMs here .
psycopg 2.7.6
This is a popular adapter for Python. It has several extensions to facilitate access to PostgreSQL from Python. In this version, they limited themselves to correcting errors and remapping error codes for PostgreSQL 11. Sources here .
psqlODBC 11.00.0000
About changes in this version (4 zeros - this is not a typo) can be found here .
Conferences and meetings
Past
PGDay Krasnoyarsk The
conference was held on November 12-13. From various reports it was possible to learn, for example, about the experience of working in the PostgreSQL + 1C bundle ( Mikhail Fiskov ); o
pg_probachup
, backup and verification with it ( Grigori Smolkin ); about sharding and sectioning with a billion records ( Alexey Fadeev ). And the famous Bruce Momdjan talked about whether Postgres is eternal. You can read about the conference in the Postgres Professional blog , and here you can read the presentation slides. Egor Rogov and Pavel Luzanov ( Postgres Professional Training Department) held courses on basic PostgreSQL administration skills ( DBA1 )
Mitap iCluster in Nizhny Novgorod
This PostgreSQL mitap was held on November 16 in the Ankudinovka Technopark. Bruce Momdjan again acted as a foreign star . Oleg Bartunov and Ivan Panchenko , who represented Postgres Professional at the highest level, explained the possibilities
JSON/JSONB
and principles of creating high-performance (and not just high-loaded) applications, respectively. Postgres specialists communicated with city authorities and local business representatives. Report cnews journalist can be seen here .Five Cool Things I Learned at the PostgreSQL Conference Europe 2018
Achilles Mantzios about what he saw and heard in Lisbon. Topics that impressed the author (there are not quite 5 of them):
- logical replication;
- zheap;
- parallel queries;
- setting shared_buffers;
- Little by little about everything (including JIT and improved full-text search).
Future
(
Clickable banner, details on the link) PGConf.ASIA 2018
is expected December 10-12 in Tokyo.
PGConf India 2019 An
Indian user group will host PGconf in Bangalore from February 13 to 15. Since quite a few key PG developers are associated with this subcontinent, this can be interesting. Conference site .
Prague PostgreSQL Developer Day 2019
P2D2 2019 will be held simultaneously in Prague (February 13-14). It is expected that the conference will be focused on the problems of users and application developers. Apply here .
PostgreSQL Conference Europe 2019, Milan
This conference will be held October 15-18. It is proposed to follow the news on the sitewhich will still be filled with information as we get closer to the date.
Education
PostgreSQL. The basics of the SQL language The
textbook was published by the BHV-Petersburg publishing house with the assistance of Postgres Professional and is already being sold in bookstores "Biblio-Globus", Ozon.ru, "Labyrinth", "Moscow House of the Book", "Young Guard" and others. The author of the textbook E.P. Morgunov .
Tips Bruce
great and not terrible Bruce Momjian replaced Role soothsayer on the role of an ordinary sensei. Here are tips for those who want to participate as a speaker at international PG conferences. Preferred topics:
- new functionality (features) in PG;
- user experience (case studies);
- (internal device PG (Internals);
- new types of loads (workloads);
- performance
- Application Development
It is better to submit applications for several topics at once: the competition is great, and at least one of them can pass ... And if you have accepted the application, start sculpting the slides a few weeks, and not 30 minutes before the report.
Articles
Care To Know Clauses: All About SELECT , FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT
Joshua Otuel (Joshua Otwell) shows the difference between how and what is written in the request, and what and in what order to actually performed.
When Databases Meet FPGA: Achieving 1 Million TPS With X-DB Heterogeneous Computing
You don’t often hear about GPUs that successfully accelerated PostgreSQL, and FPGA / FPGAs are completely exotic. And here is an article about processing 1 million transactions per second, partly due to FPGA. They have already been assigned acceleration of queries with SELECT and WHERE, on the GROUP BY queue. This solution is more surprising because Alibaba has an OLTP load, not OLAP, and a serious share of pushing transactions. In a distributed DBMS
X-DB
own storage engine X-Engine
. The article describes the specific architecture and modifications necessary for working in such an extreme and exotic environment. Postgres 11 partitioning
Denish Patel (Denish Patel) explains what the problem with sectioning were finally resolved in PostgreSQL 11. There is a matrix functionality of 9.6 / 10/11.
A little about configuring PostgreSQL
This article by Pavel Fomin is remarkable, perhaps not by itself, but by the fact that it appeared on InfoStartwas at least an attempt to seriously talk about configuring PostgreSQL and the technologies of this DBMS. It is written, to put it mildly, not yesterday, but comments continue to arrive to this day.
Express performance review of PostgreSQL 10.5 in the latest cloud services of Yandex. Oblak
In this review there are numbers, performance graphs and prices. There are winners and losers.
fsync () and OS
Viktor Yegorov drew the attention of the members of the PostgreSQL in Russia group to
facebook
the fact that the following minor releases will be with altered behaviors for errorsfsync()
calls. "On a number of OSs (Linux in the first rows), the kernel no longer attempts to write from its buffer to the file system (which the application sees as an fsync error), and when the application is called again by fsync, it simply returns SUCCESS (the kernel simply drops its write -back cache if something went wrong). As a result, it was decided in such cases PANIC, i.e. the base will be folded. The added data_sync_retry torsion, which, when changed, causes the database not to fold, but only to generate an error in the log and continue trying fsync blocks. True, products do not recommend this option. ” Discussed commit and discussion details .Subscribe to the postgresso channel !
Send your ideas and wishes to email: news_channel@postgrespro.ru
Previous issues: # 11 (special) , # 10 ,
# 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1