Avoiding, Detecting, and Recovering From Corruption
PostgreSQL databases can become corrupted for a variety of reasons, including hardware failure, software failure, and user error. In this talk, I’ll talk about some of my experiences with database corruption. In particular, I’ll mention some of the things which seem to be common causes of database corruption, such as procedural errors taking or restoring backups; some of the ways that database corruption most often manifests when it does occur, such as errors indicating inconsistencies between a table and its indexes or a table and its toast table; and a little bit about techniques that I have seen used to repair databases or recover from corruption, including some experiences with pg_resetxlog. This talk will be based mostly on my experiences working with EnterpriseDB customers; I hope that it will be useful to hackers from the point of view of thinking about possible improvements to PostgreSQL, and to end users from the point of view of helping them avoid, diagnose, and cope with corruption.
Ibrar Ahmed Percona LLC
PostgreSQL provides different levels of security. This talk will cover all the available security techniques used in PostgreSQL 13. We’ll look at client-side security (LibPq, JDBC) through to server-side security. It will cover all supported authentication methods and the pros and cons of all these methods. Some of the key features of the talk are:
- Introduction to Cryptography
- SSL, TLS, GSSAPI, and OpenSSL
- Client-Side Encryption
- Securing Authentication
- Securing Data on the disk
- Securing Backup & Basebackup
- Securing Replication
- Database Roles and Privileges
It’s important to be familiar with all the security levels such as (1)network-level security (2) on-disk security (3) row-level, (4), and column level security. The talk will cover all the aspects with some real-life use cases and examples.
Christopher Travers DeliveryHero SE
Once at Adjust we faced a problem of impending xid wraparound in a very central database due to a long-stalled autovacuum run. Because we spotted warnings 5 hours before impending disaster we were able to minimize the customer impact. Come learn how we used the time to prepare, and what lessons this has for others facing unusual problems in large databases.
Anton Doroshkevich ИнфоСофт
Postgres Pro Enterprise has a great compression engine. The year 2020 was devoted to the study of this mechanism in the real work of 1C. We have accumulated some statistical data and of course the subtleties of the use and behavior of 1C compared to other popular DBMS, which I want to share.
Nikolay Samokhvalov Nombox LLC
There are two types of SQL query analysis:
"Macro": analyzing the workload as a whole (three major approaches: using metrics provided by pg_stat_statements or similar, log analysis with pgBadger or similar, and sampling of pg_stat_activity)
"Micro": diving into details of single query execution (EXPLAIN command being the central tool here)
And there are huge gaps between them that become noticeable at scale. The main challenges:
- Switching between "macro" and "micro" without a huge overhead
- Verifying optimization ideas reliably
- Deploying changes risk-free
Solving these tasks at a scale requires advanced DBA experience and–sometimes–intuition. Or better tools that (fortunately!) very recently started to appear.
In this tutorial, we will learn how to establish a smooth and seamless SQL optimization process in your organization: * what tools should you choose in your particular case? * how to close the gaps mentioned above?