#297 — March 20, 2019

Read on the Web

Postgres Weekly

Azure Data Studio: Now an Open Source GUI Tool for Postgres Too — Postgres’s own psql command line client is fantastic, but some people much prefer a graphical tool and Microsoft’s Azure Data Studio now has preview-level support for Postgres. They’ve also released similar tools in an extension for VS Code.

Rachel Agyemang (Microsoft)

Sign Up for the Postgres-BDR 60-Day Trial — As the most powerful tool for multi-master replication, Postgres-BDR significantly reduces the impact of a master failure and minimizes risk of data loss. BDR provides up to five 9s of availability, Point-In-Time Recovery, Rolling Upgrades, and more.

2ndQuadrant PostgreSQL Products sponsor

Vertically Scaling PostgreSQL — Postgres is very receptive to making use of extra resources (CPU, memory, disk, etc.) but it’s not all automatic and sometimes tweaks are necessary. Of course, if you want some horizontal scaling for Postgres, check out Citus.

RapidLoop

postgresqltuner.pl: A Simple Script to Get Tuning Advice — A script that analyzes your Postgres database configuration and provides tuning advice.

Julien Francoz

Performance Tuning Foreign Data Wrappers — postgres_fdw has some hidden tuning options that are not widely known by users.

Hans-Jürgen Schönig

How to Replicate Postgres Data to Remote Sites — Compares Slony, Bucardo, logical replication, and FDWs.

Venkata Nagothi

▶  How to Manage & Automate PostgreSQL — Watch the free replay of our recent webinar on the power of automation for PostgreSQL.

Severalnines sponsor

Fun with SQL: Text and System Functions — Mostly a look at Postgres’s position function along with some other helpful, related functions.

Craig Kerstiens

Performing a PostgreSQL Upgrade using pg_dumpall — A look at one of several approaches for when upgrading Postgres. pg_dumpall is well suited for clusters and is relatively straightforward, although it’s not ideal for particularly large databases.

Avinash Vallarapu

supported by

💡 Tip of the Week

Using \watch in psql
(This is truly one of those "you either know it, or you don't" tips!)

Postgres's psql client comes with a lot of meta-commands to help you navigate your databases and perform various administrative functions. A particularly interesting one is \watch.

At its simplest, \watch runs the last run query (from psql's query buffer) every 2 seconds. If you specific an argument, in seconds, it'll run the query every X seconds instead: \watch 1 for every second, say. Use CTRL+C to break the loop.

One place this could be useful is if you want to always see what the last added user to your app is. For example:

SELECT * FROM users ORDER BY created_at DESC LIMIT 1; \watch 30

Another interesting use for this could be to monitor all of the currently running queries on your Postgres server (naturally only those running at 1 second intervals would be tracked by this):

SELECT datname, usename, query FROM pg_stat_activity; \watch

This week's tip is sponsored by the database team at Citus Data, now part of the Microsoft family. Learn more about how Citus scales out Postgres on the Citus Data blog.

🗓 Upcoming Postgres Events