PgConf.Russia 2019

As we all know, PostgreSQL is a classic vertically scalable database for OLTP loads. In parallel with PostgreSQL for many years there is its alternative horizontal-scalable MPP version of PostgreSQL, that is called Greenplum, sharpened for big data and OLAP workload. In my pitch I will show the internal architecture of Greenplum (distributed transactions, data sharding, partitioning with hybrid storage in external systems, column storage engines with compression, and much more), a comparison with the internal structure of PostgreSQL and the application areas of each solution are shown.

Virtualization in companies has become an alternative to the conservative "one task-one server" approach, which allows efficient use of hardware resources, centralized management of server infrastructure, saving energy and cooling resources. The report explains how to configure the VMware environment for intensive input / output PostgreSQL and profiling tools virtual infrastructure to monitor performance and resolve identified problems.

Sometimes problems arise during Postgres operation, and the faster they are identified and resolved, the happier users eventually will be. pgCenter is a set of CLI powerful utilities for troubleshooting in the "here and now" mode. In the talk I will show how to use pgCenter for efficient troubleshooting, the directions in which to search, and how to respond to certain problems, in particular how to:

• check if Postgres is in the normal state;
• identify promptly the faulty clients and stop them;
• reveal too heavy queries;
• and other tips and tricks of pgCenter.

In this master class, I will take you step-by step through a major upgrade of PostgreSQL. Through our practice we see a lot of different PostgreSQL servers in production environment and often, almost too often teams who once made PostgreSQL their database of choice never update it following the initial installation. The are many different reasons for it, the result, however, is the same - they all miss out on the new useful features of the newer releases and reduced database performance.
The goal of my masterclass is to equip attendees with necessary tools for performing PostgreSQL upgrade. I will take you through each step of the major upgrade and will dive into each executed command. I will also explain the particular order in which I perform an upgrade and explain the consequences of not following this order or missing a particular step. We will perform an upgrade of PostgreSQL 9.0 to 11. My hope is that following this masterclass number of outdated PostgreSQL database will reduce since participants will then go back to their databases and make sure that they are running the most recent version.