PostgreSQL Network Filter for EnvoyProxy

How do you monitor Postgres? What information can you get out of it, and to what degree does this information help to troubleshoot operational issues? What if you want/need to log all the queries? That may bring heavy trafficked databases down.

At OnGres we’re obsessed with improving PostgreSQL’s observability. So we worked together with Tetrate folks on an Envoy’s Network Filter extension for PostgreSQL, to provide and extend observability of the traffic inout a cluster infrastructure. This extension is public and open source. You can use it anywhere you use Envoy. It allows you to capture automated metrics and to debug network traffic. This talk will be a technical deep-dive into PostgreSQL’s protocol decoding, Envoy proxy filters and will cover all the capabilities of the tool and its usage and deployment in any environment.

Resources:

• https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/other_protocols/postgres#arch-overview-postgres
• https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new-postgres-extension-with-monitoring-support/

pg_upgrade, Advanced Techniques

When it comes to performing postgres upgrades between major versions the command line utility pg_upgrade is the most popular method used today. But as with all things wonderful, there are caveats. One of the more critical issues is what to do when there's a failure. The purpose of this talk is to present those little covered "tricks" of the trade that allows one to improve and enhance the upgrade experience.

We'll start out by reviewing its basic mode operation. We'll then learn what makes it so darn fast when upgrading multi-terabyte datacluster in a matter of minutes. Finally, you will be introduced to those feared failures and how to deal with them with confidence and certainty.

Herein is a summary of the topics in this talk:

• How pg_upgrade works: the big picture
• About pgupgrade (comand line invocation)
  • arguments and options

• The steps performing an upgrade
• About the replication ROLE
  • with replication
  • with login

• Upgrade options: COPY vs Hard Link
• Post-upgrade
  • about performance
  • aboutanalyze
  • repack
  • reindexing

• When something goes wrong at the point of no return (or is it?)
• Upgrading the REPLICA
  • default method: pg_basebackup
  • the cool method:
    • - leveraging rsync
    caveat; about vacuum wraparound

New features of pg_profile/pgpro_pwr - historical workload profiler

This talk is about postgres extension pg_profile - simple historic database workload profiler. I'll describe it's architecture, features and use cases. There is a new branch of pg_profile called pgpro_pwr, designed to run in PostgresPro Enterprise Edition and PostgresPro Standard Edition databases. It is using extended performance statistics of those databases providing some valuable benefits.

SQL as data

Almost every business app is essentially just a SQL generator. How to easily build and compose SQL queries? I will explain the "Clojure way" of representing SQL as data (data DSL) and show how it may help you to dynamically build and compose SQL queries up to macros and query analysis.