We were lazy and wrote a simple PostgreSQL monitoring check in shell instead of using some proper language. The code looked about this:

out=$(psql -tAc "SELECT some_stuff, t > now() - '1 day'::interval FROM some_table" some_db 2>&1)
case $out in
    *t) echo "OK: $out" ;;
    *) echo "NOT OK: $out" ;;
esac

If the string ends with 't', all is well, if it ends with 'f' or someting else, something is wrong.

Unfortunately, this didn't go that well:

OK: psql: FATAL: database "some_db" does not exist

We've just put the new PostgreSQL minor releases live on apt.postgresql.org. Building 5 major versions for 10 distributions produces quite a lot of stuff:

• 25 .dsc files (source packages)
• 745 .deb files (360 *_amd64.deb + 360 *_i386.deb + 25 *_all.deb)
• 497 MB in *_amd64.deb files
• 488 MB in *_i386.deb files
• 58 MB in *_all.deb files
• 73 MB in *.orig.tar.bz2 files
• in total 1118 MB

Compiling took a bit more than 10 hours on a 2-cpu VM. Of course that includes running regression tests and the postgresql-common testsuite.

Note: This will be the last update published on pgapt.debian.net. Please update your sources.list entries to point to apt.postgresql.org!

So we finally made it, and sent out an official announcement for apt.postgresql.org.

This new repository hosts packages for all PostgreSQL server versions (at the moment 8.3, 8.4, 9.0, 9.1, 9.2) for several Debian/Ubuntu distributions (squeeze, wheezy, sid, precise) on two architectures (amd64, i386). Now add packages for extension modules on top of all these, and you get a really large amount of binaries from a small number of sources. Right now there's 1670 .deb files and 148 .dsc files, but the .dsc count includes variants that only differ in the version number per distribution (we attach .pgdg60+1 for squeeze packages, .pgdg70+1 for wheezy and so on), so the real number of different sources is rather something like 81, with 38 distinct source package names.

Dimitri Fontaine, Magnus Hagander, and I have been working on this since I first presented the idea at PGconf.EU 2011 in Amsterdam. We now have a Jenkins server building all the packages, an archive server with the master repository, and a feed that syncs the repository to the postgresql.org FTP (well, mostly http) server.

If you were previously using pgapt.debian.net, that's the same archive as on apt.postgresql.org (one rsync away). Please update your sources.list to point to apt.postgresql.org, I'll shut down the archive at that location at the end of January.

Here's the Quickstart instructions from the Wiki page:

Import the repository key from http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc:

wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

Edit /etc/apt/sources.list.d/pgdg.list. The distributions are called codename-pgdg. In the example, replace squeeze with the actual distribution you are using:

deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

Configure apt's package pinning to prefer the PGDG packages over the Debian ones in /etc/apt/preferences.d/pgdg.pref:

Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500

Note: this will replace all your Debian/Ubuntu packages with available packages from the PGDG repository. If you do not want this, skip this step.

Update the package lists, and install the pgdg-keyring package to automatically get repository key updates:

apt-get update
apt-get install pgdg-keyring

We have a PostgreSQL server with 16 cores that was apparently running well below its capacity: load something between 3.0 and 4.0, around 200 active database connections, almost all always being <IDLE>. However, when the tps count reached 7k transactions per second, things started to throttle, and pgbouncer (running on the database server) started listing up to half of the client connections to be in cl_waiting state. Load was still low, but application performance was bad.

The culprit turned out to be the kernel scheduler, fairly distributing CPU time among all running processes. There's one single pgbouncer process, but hundreds of postgres processes.

A simple renice of the pgbouncer process did the trick and gave us another extra 2k tps.

We have this PostgreSQL server with plenty of RAM that is still using some of its swap over the day (up to 600MB). Then suddenly everything is swapped in again.

It turned out the reason is there are two clusters running, and the second one isn't used as heavily as the first one. Disk I/O activity of the first cluster slowly evicts pages from the second shared buffers cache to swap, and then the daily pg_dump run reads them back every evening.

I was not aware of an easy way to get numbers for "amount of SysV shared memory swapped to disk", but some googling led to shmctl(2):

#define _GNU_SOURCE 1
#include <sys/ipc.h>
#include <sys/shm.h>
#include <stdio.h>
#include <unistd.h>

int main ()
{
        struct shm_info info;
        int max;
        long PAGE_SIZE = sysconf(_SC_PAGESIZE);

        max = shmctl(0, SHM_INFO, (struct shmid_ds *) &info);
        printf ("max: %d\nshm_tot: %ld\nshm_rss: %ld\nshm_swp: %ld\n",
                        max,
                        info.shm_tot * PAGE_SIZE,
                        info.shm_rss * PAGE_SIZE,
                        info.shm_swp * PAGE_SIZE);

        return 0;
}

The output looks like this:

max: 13
shm_tot: 13232308224
shm_rss: 12626661376
shm_swp: 601616384

Update: Mark points out that ipcs -mu shows the same information. Thanks for the hint!

# ipcs -mu

------ Shared Memory Status --------
segments allocated 2
pages allocated 3230544
pages resident  3177975
pages swapped   51585
Swap performance: 0 attempts     0 successes

In Wheezy's grep version [1], you can omit the "." in

$ grep -r foobar .

and just write

$ grep -r foobar

[1] actually since 2.11

If you think you are smart and create an ext filesystem with 1024 bytes blocksize because there will be zillions of very small files, and then run into ENOSPC errors while there's both space and inodes left, you will probably see ext3_dx_add_entry: Directory index full! in the kernel log.

Turns out that there's a limit of approximately 300,000 files per directory with 1k blocks, after which some hash tables are full. Recreate the filesystem with 2k blocks and the limit will be MUCH higher.

Almost a year has passed since my talk at pgconf.eu 2011 in Amsterdam on Connecting the Debian and PostgreSQL worlds, and unfortunately little has happened on that front, mostly due to my limited spare time between family and job. pgapt.debian.net is up and running, but got few updates and is lagging behind on PostgreSQL releases.

Luckily, we got the project moving. Dimitri Fontaine and Magnus Hagander suggested to do a face-to-face meeting, so we got together at my house for two days last week and discussed ideas, repository layouts, build scripts, and whatnot to get all of us aligned for pushing the project ahead. My employer sponsored my time off work for that. We almost finished moving the repository to postgresql.org infrastructure, barring some questions of how to hook the repository into the existing mirror infrastructure; this should get resolved this week.

The build server running Jenkins is still located on my laptop, but moving this to a proper host will also happen really soon now. We are using Mika Prokop's jenkins-debian-glue scripts for driving the package build from Jenkins. The big plus point about Jenkins is that it makes executing jobs on different distributions and architectures in parallel much easier than a bunch of homemade shell scripts could get us with reasonable effort.

Here's a list of random points we discussed:

• We decided to go for "pgdg" in version numbers and distribution names, i.e. packages will have version numbers like 9.1.5-1.pgdg+1, with distributions wheezy-pgdg, squeeze-pgdg, and so on.
• There will be Debian-testing-style distributions called like wheezy-pgdg-testing that packages go into for some time before they get promoted to the "live" distributions.
• PostgreSQL versions out of support (8.2 and below) will not be removed from the repository, but will be moved to distributions called like wheezy-pgdg-deprecated. People will still be able to use them, but the naming should make it clear that they should really be upgrading.
• We have a slightly modified (compared to Debian unstable) postgresql-common package that sets the "supported-versions" to all versions supported by the PostgreSQL project. That will make the postgresql-server-dev-all package pull in build-dependencies for all server versions, and make extension module packages compile for all of them automatically. (Provided they are using pg_buildext.)
• There's no Ubuntu support in there yet, but that's mostly only a matter of adding more cowbuilder chroots to the build jobs. TBD soon.

We really aim at using unmodified packages from Debian as much as possible, and in fact this project doesn't mean to replace Debian's PostgreSQL packaging work, but to extend it beyond the number of server versions (and Debian and Ubuntu versions covered) supported. The people behind the Debian and Ubuntu packages, and this repository are mostly the same, so we will claim that "our" packages will be the same quality as the "original" ones. Big thanks go to Martin Pitt for maintaining the postgresql-common testsuite that really covers every aspect of running PostgreSQL servers on Debian/Ubuntu systems.

Stay tuned for updates! :)