A little permissions problem

So you're on Fedora 22 and you want to run the latest stable PostgreSQL release. You install the PGDG repository package and then install postgresql94-server. You decide to test it out first, so as your normal login user you run initdb and then try to start the server using pg_ctl, something you've done a hundred times before. It should "just work," right? Not this time. You examine the log file and you see it complaining like this:

could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

Ouch! I've never needed that before. These files have always gone in /tmp. OK, so why is it different now we are putting them elsewhere? It turns out that the directory has been created by the RPM with permissions different from /tmp, and only the postgres user can write there.

My solution (which I hope our RPM maintainers adopt) was to change the permissions on the directory like this, making them the same as those of /tmp:

sudo chmod 1777 /var/run/postgresql

After that when I try to start the database everything is as expected.

How to find what's causing that error

Tom Lane just posted a terrific piece of advice about how to find what's causing an error you don't know the source of, which is worth quoting:

What I would recommend is that you get the data onto a non-production
machine where you can play around a bit more. One thing you could do
then is run a build with debug symbols, attach to the backend process
with gdb, and set a breakpoint at "errfinish". Then provoke the error,
and capture a backtrace from the call to errfinish. That would greatly
narrow things down, though it might not be enough to isolate the bug
immediately.

I normally like to have debuginfo packages installed even on production machines so I can do this sort of thing quickly.

Another thing you can try in a non-production environment is to run your test case against a build with assertions enabled and see if it trips an assertion failure. That's often a quick way of finding where problems are occurring.

Testing on Fedora

One of the things that is nice about Fedora is how easy it is to test something very quickly. They provide a considerable number of cloud images suitable for spinning up instances fast. In my case I have a machine with Vagrant and VirtualBox installed, and when we got a complaint recently about PostgreSQL misbehaving with some glibc locales in fedora 23, I wanted to test it. After downloading the image I wanted I was able to get running with this:

vagrant box add --name=f23 Fedora-Cloud-Base-Vagrant-23_Beta-20150915.x86_64.vagrant-virtualbox.box
mkdir f23
cd f23
vagrant init f23
vagrant ssh

It took me about 5 minutes from the time I discovered the image I wanted to test to starting on setting up to build PostgreSQL on it, and most of that was download time.

Redis FDW doesn't suport Redis Cluster

Redis Cluster is a way of sharding data across a group of federated Redis nodes. I was asked recently if this is supported by the Redis Foreign Data Wrapper. Unfortunately, the answer is "No." The Redis site coyly states that "At this stage one of the problems with Redis Cluster is the lack of client libraries implementations." What they don't mention is that their own C client library, hiredis, has no support for it at all, happily reporting back redirects to the client rather than following them. It's rather as if a web client library refused to follow an HTTP redirect. The Redis FDW is based on hiredis, so as the library doesn't support Redis Cluster, neither do we. Of course, at the expense of very large amounts of code we could make the FDW handle it, but this seems quite wrong architecturally. The FDW should not have to know or care that the Redis server it's connected to is sharded.

Keep your configs under version control

A conversation yesterday with a client: "What changes were made in the PostgreSQL configs?" "I don't recall exactly." This is why you should keep all the configs under version control, and have a regular monitoring check that the controlled version is the same as the operating version, so if someone makes a manual change without checking it in the monitoring alarm will go off.

If you keep your config in the data directory, in the default PostgreSQL style, probably the simplest way to do this is to create a git repository with a symbolic link to the data directory and a .gitignore that ignores everything in the data directory but the config files. The add the config files and you're done. If you keep the config files separately, as Debian does, then you can probably just create a git repository right in the config directory if you don't already have one at a higher level.

I have been guilty of not doing this in the past, but I have come to the strong conclusion that this should be a part of standard best practice.

Update

My attention has been drawn to etckeeper, which makes handling this super simple when you're using Debian-style config setups. It's available both for Debian and for RedHat-ish systems, as well as possibly others.