Monday, February 12, 2024

Review: PostgreSQL 16 Administration Cookbook

This book, published in December 2023 by packt, is the latest incarnation of a book originally principally authored by Simon Riggs, and brought up to date for PostgreSQL 16 by my EDB colleagues Gianni Ciolli,  Boriss Mej√≠as, Jimmy Angelakos and Vibhor Kumar. It's available in both hard copy and electronic format. For review purposes, the publisher provided me with one of each. DBAs will probably find the electronic format more useful because of the ability to cut and paste from it. And in these days of devops, where just about every developer is their own DBA to some extent, the book should appeal to a wide audience of PostgreSQL users.

Books like this don't necessarily aim to be comprehensive guides, but rather to provide a toolbox full of useful tips and recipes. And this book is chock full of them. They cover topics relating to pretty much every aspect of PostgreSQL administration, from installation to maintenance to troubleshooting. And the book is pretty reliable. That's not surprising to me, as I know these authors to be fairly meticulous in their attention to detail.  When I'm given a book to review I usually start by looking at areas where I have pretty good knowledge already. All too often I find errors that are egregious or just plain careless. I can report that I didn't find any errors beyond an insignificant typo or two. This book is pretty solid, and I'm happy to recommend it.

I do have a few relatively minor criticisms. First, the layout could be improved. A horizontal bar between recipes would be useful. Simply using a slightly larger font for recipe headings doesn't make it easy enough to see where one topic ends and another starts. Better visual clues would be good. If that increases the book's length, maybe they could also save some by removing the repetitious "Learn more on Discord" Section at the end of each chapter.

More substantially, some topics are covered in such little detail as to be not very useful. For example, the section of Foreign Data Wrappers is very sparse, as is the section that mentions Prometheus and Grafana. It might be better to have less recipes but with more useful information.

Finally, it is odd to read things stated in the first person singular in a book with multiple authors. I imagine this is a holdover from earlier incarnations of the book, but it still feels strange. If it's going to be kept then the reader should be told whose voice this is.  But in general I think it would be better to use "we" and "our" instead of "I" and "my".

None of this should deter you from buying the book. If you're a PostgreSQL developer or administrator you are highly likely to find useful gems of information in this book.


Friday, August 4, 2023

Announcing Release 17 of the PostgreSQL Buildfarm client

I have pushed Release 17 of the PostgreSQL Buildfarm client.

Release 17 has two main features:

  • Modernize the way we do cross-version upgrade tests. Most of the logic for modifying instances to make them suitable for cross version upgrade testing has now been migrated to the Postgres core code in src/test/perl/PostgreSQL/Test/ The new code simply imports this module and leverages its knowledge.
  • Support of building with meson. This is only supported on version 16 or later of Postgres; older branches will continue to use the older toolsets. To enable building with meson there are several new settings, illustrated in the sample configuration file:
    • using_meson this must be set to a true value
    • meson_jobs this controls the degree of parallelism that meson will use
    • meson_test_timeout this is used to multiply the meson test timeout. The default is 3, 0 turns off timeout
    • meson_config This is an array of settings for passing to meson setup. Note that all options need to be explicitly given here - the client disables all auto options. This includes use of zlib and readline, which do not default to on, unlike autoconf setups.

There are also a number of relatively small bug fixes and tweaks (e.g. some improvements in processing typedefs).

The release is available at or



Friday, January 13, 2023

Announcing Release 16 of the PostgreSQL Buildfarm client


Hot on the heels of Release 15 comes Release 16.

This release deals with some issues that have been discovered with the check for update feature of Release 15 and the force_every and trigger_exclude features, so that it now works correctly with those features.

It also features these items:

  • a new --check-for-work mode of
    This mode doesn't do any work but exits with a zero status if there is work to do and 1 if there is not. It is intended for use as an
    ExecCondition in systemd units
  • up to date filtering now works with an explicit list of branches, as well as with key words like ALL
  • reduce the verbosity of Another process holds the lock messages.
    These are now only emitted if the verbose setting is greater than 1
  • update_personality now has options to change the owner name and owner email
    This was in Release 15 but was accidentally omitted from the release notes. Up to now the only way to change these was by action from
    the administrators.
  • improve collection of logs in cross version upgrade testing

 The release can be downloaded from or 

Saturday, December 31, 2022

Announcing Release 15 of the PostgreSQL Buildfarm client


  • add a new script that lets the user enable or disable alerts for an animal
    This is especially useful in the case of animals that have stopped running for some reason.
  • check if a branch is up to date before trying to run it
    This only applies if the branches_to_build setting is a keyword rather than a list of branches. It reduces the number of useless calls to git pull to almost zero.
  • require Perl version 5.14 or later
    This should not be a problem, as it's more than 10 years old.
  • add --avoid-ts-collisions command line parameter
    This is for specialized uses, and imposes a penalty of a few seconds per run. already does this, so it's not required for normal operations.
  • run TAP tests for src/interfaces subdirectories
  • add amcheck and extension upgrade tests to cross version upgrade testing
  • adjust to changes in postgres code, file locations, etc.
  • assorted minor bug fixes and tweaks

The release can be downloaded from or

Upgrading is highly recommended.


Saturday, November 12, 2022

Goodbye Windows XP

This time for sure (h/t Bullwinkle)

Yesterday I finally turned off my Windows XP instance for good. It's been running for quite a long time, almost 12 years, basically as the host for the buildfarm animals currawong, brolga and frogmouth. However, XP itself went out of support on 2014, and in recent years modern Postgres will not build or run on it due to some platform limitations. The latest version of Postgres that could run on it was release 10, which has now reached end of life, so there is no longer a supported version of Postgres that can build on XP, and there is thus no longer a need for these buildfarm animals.

This means we're also probably saying goodbye to Microsoft Visual C 2008, and Msys version 1.

Wednesday, August 17, 2022

Taming Buildfarm Logs

 The output from running a buildfarm step is the output from running a command such as make check followed by any relevant log files that the step creates. These log files can get quite large, and in some cases there are quite a lot of them. Up to now the buildfarm server has just show all of this in an undifferentiated text blob. That has made finding out what might have gone wrong not impossible but not as easy as it could be, and there have been complaints about it over the years.

A few days ago I implemented a new way of presenting this information. You still see the command output, but instead of showing the log file contents you see a list of the log files. Click on one log file and it will show you the contents of that file. Click on it again and they are hidden again. So if, say, a TAP test fails at script number 12, you can quickly locate the regress_log_012_mytest.log and associated server logs, without having to scroll through thousand of lines that don't interest you.

You can still see the old format if you need to - the page for each stage has a link to the raw log.

I hope this will enhance the usability and usefulness of the buildfarm.

Thanks to Kori Lane for pointing me at the JavaScript gadget that is used to implement this.

Update: All issues now resolved.

Sunday, February 6, 2022

Release 14 of the PostgreSQL Buildfarm client

 This is to announce release 14 of the PostgreSQL Buildfarm client.

Significant changes:

  • don't run TestUpgrade if TAP tests are present in src/bin/pg_upgrade
  • Add proposed new location of pg_upgrade logs in TestUpgrade module
  • Use an HMAC over the whole content as the signature.
  • Quote PROVE_FLAGS in case there are multiple settings
  • tighten failure detection for cross version upgrade
  • be more verbose about git mirror failures
  • Support symlinks on Windows where possible in SCM module
  • Document rm_worktrees setting and make on the default.
  • Add new branches_to_build keywords STABLE and OLD
Because of the changes to how pg_upgrade logging works, owners are strongly urged to upgrade to the new release as soon as possible if they are running the TestUpgrade module. 
The meaning of the branches_to_build keywords are as follows: STABLE means all the live branches other than master/HEAD, while OLD means those branches older than STABLE that we are now supporting limited builds for (currently REL9_2_STABLE through REL9_6_STABLE). 

Wednesday, August 4, 2021

Release 13.1 of the PostgreSQL BuildFarm client


I have just pushed Release 13.1 of the PostgreSQL BuildFarm client.

This update to Release 13 fixes errors that occur from the new default branch name checking code when used with versions of git that are too old. This code is now disabled if the git version is not capable of running it, and in verbose mode a warning is printed. The warning can be disabled by explicitly setting "skip_git_default_check => 1" in the config file. In either case, the owner will need to update their git installation or remove all branch and mirror repositories when the default branch name changes.

Downloads are available at: and

Monday, August 2, 2021

Release 13 of the PostgreSQL BuildFarm client

I have pushed Release 13 of the PostgreSQL BuildFarm client.

Change highlights:

  • add tests for a few cases that were previously missing
  • more fine-grained control over which TAP test sets run
  • --no-keepall can be specified on the command line
  • repair a repo if it crashed during a copy operation
  • make sure the repo is really clean (including free of ignored files)
  • generate stack traces on CYGWIN
  • collect stack traces for TAP tests
  • Extract MSVC settings at runtime rather than had coding them in theconfig file (see below)
  • cross version upgrade tests now run on Windows, both for msys2 and MSVC builds
  • add support for inhibit-runs and force-one-run trigger files( see below)
  • add experimental module for running arbitrary out of tree TAP tests
  • Adjust if an upstream repo changes the default branch name (see below)
  • add use_discard_caches caches setting (see below)

MSVC animals are now very much simpler to set up, and to upgrade to a new compiler. Using the new mechanism, as shown in the sample config file, all that's needed is to specify a location where the standard script vcvarsall.bat can be found. The script will then run that script and extract the settings and apply them. Tha means that upgrading to a new version of Visual Studio would entail just a one line change in the config file.

If you put a file called [animalname].inhibit-runs in the build root, all runs will be stopped until the file is removed. If you put a file called [animalname].force-one-run in the build root, each configured branch will forced to run once, and the file will be removed. These only apply if you use the script.

The client should transparently deal with any change that is made in the upstream repository's default branch name. This avoids the need for a flag day when we eventually change the default branch name for postgresql, as I assume we will do before long. The branch bf_HEAD which the client creates now refers to the upstream default whatever it might be, rather than the hardcoded name 'master'. The code of the SCM module underwent quite a lot of change in order to make this happen; the checkout code had become quite long and convoluted and I had to refactor it somewhat before I was able to make and test this change. The changes have been fairly extensively tested, but I'm still slightly nervous about them. Owners are asked to report any issues promptly.

the use_discard_caches setting reflects a change in the way postgres handles this - it's now a runtime setting rather than a compile time setting. On older branches it sets "-DCLOBBER_CACHE_ALWAYS". If you use this setting don't use that define.

Downloads are available at and

Saturday, June 19, 2021

Running Standalone TAP Tests

Recent discussion set me thinking about what would be involved in setting up standalone TAP tests for Postgres, to do, say, more code coverage than we get from the current core tests. So I started experimenting. And with a tiny bit of Makefile magic it turns out to be absurdly easy.

You need just two things: an entirely boilerplate Makefile and a tiny bit of glue at the top of your TAP test files.

First let's look at the Makefile. Here it is in its entirety:


PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

# install_path for PostgreSQL binaries etc
PG_INSTALL_ROOT := $(dir $(shell $(PG_CONFIG) --bindir))

# where to find PostgresNode and friends
PG_NODE_LOC = $(top_srcdir)/src/test/perl
export PG_NODE_LOC

Then in your TAP test perl file(s) (which should reside in a subdirectory called "t") you put this:

use lib "$ENV{PG_NODE_LOC}";

use PostgresNode;
use Test::More;

local $ENV{PG_REGRESS} = "/bin/true";

my $node_inst = $ENV{PG_INSTALL_ROOT};

# for pre--release-14 releases you would possibly set LD_LIBRARY_PATH
# based on this. For release 14 and up PostgresNode does that for you
# via the install_path parameter.

my $node = PostgresNode->get_new_node('dummy', install_path => $node_inst);


That's all you need. Given that you can run your TAP tests with just a Postgres installation, as in this example:

andrew@emma:tests $ make PG_CONFIG=../inst.head.5701/bin/pg_config installcheck
rm -rf '/home/andrew/pgl/tests'/tmp_check
/usr/bin/mkdir -p '/home/andrew/pgl/tests'/tmp_check
cd ./ && TESTDIR='/home/andrew/pgl/tests' PATH="/home/andrew/pgl/inst.head.5701/bin:$PATH" PGPORT='65701' \
  top_builddir='/home/andrew/pgl/tests//home/andrew/pgl/inst.head.5701/lib/postgresql/pgxs/src/makefiles/../..' \
  PG_REGRESS='/home/andrew/pgl/tests//home/andrew/pgl/inst.head.5701/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress' \
  REGRESS_SHLIB='/src/test/regress/' \
  /usr/bin/prove -I /home/andrew/pgl/inst.head.5701/lib/postgresql/pgxs/src/makefiles/../../src/test/perl/ -I ./  t/*.pl
t/ .. ok   
All tests successful.
Files=1, Tests=1,  3 wallclock secs ( 0.04 usr  0.01 sys +  1.22 cusr  0.36 csys =  1.63 CPU)
Result: PASS

The Makefile and a demo TAP test are at This Gitlab repo

In the next episode, we'll look at how to leverage this so that your standalone tests are run by your buildfarm animal.

Saturday, June 5, 2021

Buildfarm adopts modern git naming

In keeping with modern git practice, I have renamed the default branch on both the client-code and server-code repositories to main. If you have a clone of either of these repositories, you can adapt to the change by doing the following commands in your local repo:

git checkout master
git branch -m main
git fetch
git branch --unset-upstream
git branch -u origin/main
git symbolic-ref refs/remotes/origin/HEAD refs/remotes/origin/main
git fetch -p

There is still a bunch of work to do to ensure that things run smoothly when source repositories such as The PostgreSQL Git repository make this change, as will surely happen at some stage. I will be testing this on a small source repository I control - the plan is that when it happens it will be transparent to buildfarm animals.

 Update (August 4 2021):

I just went to update a couple of machines with very old versions of git. They choked on these lines

git branch --unset-upstream
git branch -u origin/main

The solution is instead to edit the .git/config file directly. In these lines change "master" to "main":

[branch "main"]
    remote = origin
    merge = refs/heads/master

The rest of the recipe worked fine. In some cases you might need a final git pull.

Monday, August 8, 2016

Using EXPLAIN json format output in plpgsql

It's possible to use EXPLAIN output in plpgsql. In the default text format, the result comes back as a set of text values, so you can process them in a loop like this:

   exp text;
   for exp in explain myquery
      raise notice '%', exp;
   end loop;

If you use json format output, however, the result comes back as a single json document rather than a set of lines. You still might need a loop - I haven't found another way yet of getting the output from EXPLAIN into a variable - but the loop will only have one iteration. Here is an example taken from a function I wrote the other day that lets you get the estimated number of rows from the plan:

   exp json;
   for exp in explain (format json) myquery
      raise notice 'rows: %', exp#>>'{0,Plan,Plan Rows}';
   end loop;