#393 — February 17, 2021

Web Version

Postgres Weekly

Babelfish: The Elephant in The Room? — Last year, AWS announced Babelfish, a project to provide a SQL Server compatible endpoint for PostgreSQL and that will be open sourced this year. Will an open source version easily integrate with Postgres though? Not exactly, and that’s the problem examined here.

Álvaro Hernández

PostgreSQL 13.2, 12.6, 11.11, 10.16, 9.6.21, and 9.5.25 Released — A suite of updates to Postgres to fix two security vulnerabilities. This is also expected to be the final 9.5 branch release ever, so upgrading if you’re on 9.5 now becomes even more important.

PostgreSQL Global Development Group

5 Ways to Get More from PostgreSQL — PostgreSQL is a top open source database management system (DBMS) choice for business today. This eBook explores 5 important tactics to help IT Managers and DBAs better manage PostgreSQL deployments and ultimately get the most out of Postgres.

EDB sponsor

Implementing a Generic REDUCE Aggregate Function with SQL — A fun bit of SQL spelunking here for arbitrarily reducing a collection of data. If you want to learn something about SQL today, this is the article for you.

Lukas Eder

Reconnecting Your Application After a Postgres Failover — A deep dive into client-side HA to learn what happens when a Postgres failover occurs.

Dimitri Fontaine

On ANALYZE and Optimizer Statistics — A (very) quick, practical look at how Postgres handles queries, tries to find the best execution plan for them, and the statistics that help the optimizer do this.

Hans-Jürgen Schönig

Where Are The Postgres Config Files? — Rather than poking around your filesystem, you can just ask Postgres itself to tell you where various config files are.

Hubert depesz Lubaczewski

A Few SQL Command Changes Coming in Postgres 14CREATE TRIGGER gets an OR REPLACE option to create a trigger even if one exists with the same name already. You’ll also be able to specify column aliases without AS in many situations.

Ahsan Hadi

Speed Up a Search Query from Seconds to Milliseconds — Download our free eBook and learn when and how to best use exact matches, trigrams, ILIKE, and full-text search.

pganalyze sponsor

Querying JSON Data in Postgres — I’ve been working directly with querying JSON directly in Postgres a lot lately and it’s been.. rather pleasant! Nothing mindblowing in this post but it covers the basics if you haven’t dipped a toe into the JSONB column world yet.

Aaron Bos

How To Set Up Continuous Archiving and Perform Point-In-Time-Recovery with Postgres 12 on Ubuntu 20.04 — Another straightforward ‘what it says in the title’ tutorial published by DigitalOcean.

Nathan McCulloch

🔧 Tools and Code

pgCenter: Command Line Tool for Observing and Troubleshooting Postgres — Imagine something like top but for Postgres. You can keep your eye on vital statistics and running queries in real time. Linux only and binaries are amd64 only.

Lesovsky Alexey

pgFormatter 5.0: A Postgres SQL Syntax Beautifier — We linked this last year, but version 5 has just dropped with a variety of fixes and improvements. There’s an online demo here and it can work with SQL generally, not just Postgres's dialect.

Gilles Darold

pg_probackup: Backup and Recovery Manager for Postgres — A tool to manage backup and recovery (including incremental backup and restore) of Postgres clusters across Postgres 9.5 through 13. v2.4.10 was just released.

Postgres Professional

PostgreSQL-OCaml 5.0: OCaml Bindings for Postgres — v5 adds support for parameter types when preparing or executing queries. OCaml is quite a neat language if you’ve never played with it, by the way.

Markus Mottl

Adminer: Database Management in a Single PHP File — A mature project that’s an alternative to phpMyAdmin for managing Postgres, MySQL, SQL Server, and other databases via a Web interface. The main selling point is it’s distributed as a single PHP file. v4.8.0 just dropped. GitHub repo.

Jakub Vrána

How to Install pgBadger on a Remote ServerpgBadger is a Postgres log analysis reporting tool (i.e. it takes Postgres logs and generates HTML reports - complete with graphs - of server behavior, numbers of queries, vacuuming, etc.)

Pablo en Roshka