Back in 2015, when PostgreSQL 9.5 alpha 1 was released, I had posted the PostgreSQL data from Debian’s popularity contest.

8 years and 8 PostgreSQL releases later, the graph now looks like this:

Currently, the most popular PostgreSQL on Debian systems is still PostgreSQL 13 (shipped in Bullseye), followed by PostgreSQL 11 (Buster). At the time of writing, PostgreSQL 9.6 (Stretch) and PostgreSQL 15 (Bookworm) share the third place, with 15 rising quickly.

pg_dirtyread

Earlier this week, I updated pg_dirtyread to work with PostgreSQL 14. pg_dirtyread is a PostgreSQL extension that allows reading “dead” rows from tables, i.e. rows that have already been deleted, or updated. Of course that works only if the table has not been cleaned-up yet by a VACUUM command or autovacuum, which is PostgreSQL’s garbage collection machinery.

Here’s an example of pg_dirtyread in action:

# create table foo (id int, t text);
CREATE TABLE
# insert into foo values (1, 'Doc1');
INSERT 0 1
# insert into foo values (2, 'Doc2');
INSERT 0 1
# insert into foo values (3, 'Doc3');
INSERT 0 1

# select * from foo;
 id │  t
────┼──────
  1 │ Doc1
  2 │ Doc2
  3 │ Doc3
(3 rows)

# delete from foo where id < 3;
DELETE 2

# select * from foo;
 id │  t
────┼──────
  3 │ Doc3
(1 row)

Oops! The first two documents have disappeared.

Now let’s use pg_dirtyread to look at the table:

# create extension pg_dirtyread;
CREATE EXTENSION

# select * from pg_dirtyread('foo') t(id int, t text);
 id │  t
────┼──────
  1 │ Doc1
  2 │ Doc2
  3 │ Doc3

All three documents are still there, but only one of them is visible.

pg_dirtyread can also show PostgreSQL’s system colums with the row location and visibility information. For the first two documents, xmax is set, which means the row has been deleted:

# select * from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text);
 ctid  │ xmin │ xmax │ id │  t
───────┼──────┼──────┼────┼──────
 (0,1) │ 1577 │ 1580 │  1 │ Doc1
 (0,2) │ 1578 │ 1580 │  2 │ Doc2
 (0,3) │ 1579 │    0 │  3 │ Doc3
(3 rows)

Undelete

Caveat: I’m not promising any of the ideas quoted below will actually work in practice. There are a few caveats and a good portion of intricate knowledge about the PostgreSQL internals might be required to succeed properly. Consider consulting your favorite PostgreSQL support channel for advice if you need to recover data on any production system. Don’t try this at work.

I always had plans to extend pg_dirtyread to include some “undelete” command to make deleted rows reappear, but never got around to trying that. But rows can already be restored by using the output of pg_dirtyread itself:

# insert into foo select * from pg_dirtyread('foo') t(id int, t text) where id = 1;

This is not a true “undelete”, though - it just inserts new rows from the data read from the table.

pg_surgery

Enter pg_surgery, which is a new PostgreSQL extension supplied with PostgreSQL 14. It contains two functions to “perform surgery on a damaged relation”. As a side-effect, they can also make delete tuples reappear.

As I discovered now, one of the functions, heap_force_freeze(), works nicely with pg_dirtyread. It takes a list of ctids (row locations) that it marks “frozen”, but at the same time as “not deleted”.

Let’s apply it to our test table, using the ctids that pg_dirtyread can read:

# create extension pg_surgery;
CREATE EXTENSION

# select heap_force_freeze('foo', array_agg(ctid))
    from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text) where id = 1;
 heap_force_freeze
───────────────────

(1 row)

Et voilà, our deleted document is back:

The apt.postgresql.org repository has been extended to cover the arm64 architecture.

We had occasionally received user request to add “arm” in the past, but it was never really clear which kind of “arm” made sense to target for PostgreSQL. In terms of Debian architectures, there’s (at least) armel, armhf, and arm64. Furthermore, Raspberry Pis are very popular (and indeed what most users seemed to were asking about), but the raspbian “armhf” port is incompatible with the Debian “armhf” port.

Now that most hardware has moved to 64-bit, it was becoming clear that “arm64” was the way to go. Amit Khandekar made it happen that HUAWEI Cloud Services donated a arm64 build host with enough resources to build the arm64 packages at the same speed as the existing amd64, i386, and ppc64el architectures. A few days later, all the build jobs were done, including passing all test-suites. Very few arm-specific issues were encountered which makes me confident that arm64 is a solid architecture to run PostgreSQL on.

We are targeting Debian buster (stable), bullseye (testing), and sid (unstable), and Ubuntu bionic (18.04) and focal (20.04). To use the arm64 archive, just add the normal sources.list entry:

deb https://apt.postgresql.org/pub/repos/apt buster-pgdg main

Ubuntu focal

At the same time, I’ve added the next Ubuntu LTS release to apt.postgresql.org: focal (20.04). It ships amd64, arm64, and ppc64el binaries.

deb https://apt.postgresql.org/pub/repos/apt focal-pgdg main

Old PostgreSQL versions

Many PostgreSQL extensions are still supporting older server versions that are EOL. For testing these extension, server packages need to be available. I’ve built packages for PostgreSQL 9.2+ on all Debian distributions, and all Ubuntu LTS distributions. 9.1 will follow shortly.

This means people can move to newer base distributions in their .travis.yml, .gitlab-ci.yml, and other CI files.

Users had often asked where they could find older versions of packages from apt.postgresql.org. I had been collecting these since about April 2013, and in July 2016, I made the packages available via an ad-hoc URL on the repository master host, called “the morgue”. There was little repository structure, all files belonging to a source package were stuffed into a single directory, no matter what distribution they belonged to. Besides this not being particularly accessible for users, the main problem was the ever-increasing need for more disk space on the repository host. We are now at 175 GB for the archive, of which 152 GB is for the morgue.

Our friends from yum.postgresql.org have had a proper archive host (yum-archive.postgresql.org) for some time already, so it was about time to follow suit and implement a proper archive for apt.postgresql.org as well, usable from apt.

So here it is: apt-archive.postgresql.org

The archive covers all past and current Debian and Ubuntu distributions. The apt sources.lists entries are similar to the main repository, just with “-archive” appended to the host name and the distribution:

deb https://apt-archive.postgresql.org/pub/repos/apt DIST-pgdg-archive main
deb-src https://apt-archive.postgresql.org/pub/repos/apt DIST-pgdg-archive main

The oldest PostgreSQL server versions covered there are 8.2.23, 8.3.23, 8.4.17, 9.0.13, 9.1.9, 9.2.4, 9.3beta1, and everything newer.

Some example:

$ apt-cache policy postgresql-12
postgresql-12:
  Installed: 12.2-2.pgdg+1+b1
  Candidate: 12.2-2.pgdg+1+b1
  Version table:
 *** 12.2-2.pgdg+1+b1 900
        500 http://apt.postgresql.org/pub/repos/apt sid-pgdg/main amd64 Packages
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
        100 /var/lib/dpkg/status
     12.2-2.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12.2-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12.1-2.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12.1-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12.0-2.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12.0-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12~rc1-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12~beta4-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12~beta3-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12~beta2-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12~beta1-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages

Because this is hosted on S3, browsing directories is only supported indirectly by static index.html files, so if you want to look at some specific URL, append “/index.html” to see it.

The archive is powered by a PostgreSQL database and a bunch of python/shell scripts, from which the apt index files are built.

Archiving old distributions

I’m also using the opportunity to remove some long-retired distributions from the main repository host. The following distributions have been moved over:

• Debian etch (4.0)
• Debian lenny (5.0)
• Debian squeeze (6.0)
• Ubuntu lucid (10.04)
• Ubuntu saucy (13.10)
• Ubuntu utopic (14.10)
• Ubuntu wily (15.10)
• Ubuntu zesty (17.04)
• Ubuntu cosmic (18.10)

They are available as “DIST-pgdg” from the archive, e.g. squeeze:

deb https://apt-archive.postgresql.org/pub/repos/apt squeeze-pgdg main
deb-src https://apt-archive.postgresql.org/pub/repos/apt squeeze-pgdg main

I knew it was about this time of the year 10 years ago when my Debian account was created, but I couldn’t remember the exact date until I looked it up earlier this evening: today :). Rene Engelhard had been my advocate, and Marc Brockschmidt my AM. Thanks guys!

A lot of time has passed since then, and I’ve worked in various parts of the project. I became an application manager almost immediately, and quickly got into the NM front desk as well, revamping parts of the NM process which had become pretty bureaucratic (I think we are now, 10 years later, back where we should be, thanks to almost all of the paperwork being automated, thanks Enrico!). I’ve processed 37 NMs, most of them between 2005 and 2008, later I was only active as front desk and eventually Debian account manager. I’ve recently picked up AMing again, which I still find quite refreshing as the AM will always also learn new things.

Quality Assurance was and is the other big field. Starting by doing QA uploads of orphaned packages, I attended some QA meetings around Germany, and picked up maintenance of the DDPO pages, which I still maintain. The link between QA and NM is the MIA team where I was active for some years until they kindly kicked me out because I was MIA there myself. I’m glad they are still using some of the scripts I was writing to automate some things.

My favorite MUA is mutt, of which I became co-maintainer in 2007, and later maintainer. I’m still listed in the uploaders field, but admittedly I haven’t really done anything there lately.

Also in 2007 I started working at credativ, after having been a research assistant at the university, which meant making my Debian work professional. Of course it also meant more real work and less time for the hobby part, but I was still very active around that time. Later in 2010 I was marrying, and we got two kids, at which point family was of course much more important, so my Debian involvement dropped to a minimum. (Mostly lurking on IRC ;)

Being a PostgreSQL consultant at work, it was natural to start looking into the packaging, so I started submitting patches to postgresql-common in 2011, and became a co-maintainer in 2012. Since then, I’ve mostly been working on PostgreSQL-related packages, of which far too many have my (co-)maintainer stamp on them. To link the Debian and PostgreSQL worlds together, we started an external repository (apt.postgresql.org) that contains packages for the PostgreSQL major releases that Debian doesn’t ship. Most of my open source time at the moment is spent on getting all PostgreSQL packages in shape for Debian and this repository.

According to minechangelogs, currently 844 changelog entries in Debian mention my name, or were authored by me. Scrolling back yields memories of packages that are long gone again from unstable, or I passed on to other maintainers. There are way too many people in Debian that I enjoy(ed) working with to list them here, and many of them are my friends. Debian is really the extended family on the internet. My last DebConf before this year had been in Mar del Plata - I had met some people at other conferences like FOSDEM, but meeting (almost) everyone again in Heidelberg was very nice. I even remembered all basic Mao rules :D.

Today saw the release of PostgreSQL 9.5 Alpha 1. Packages for all supported Debian and Ubuntu releases are available on apt.postgresql.org:

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

The package is also waiting in NEW to be accepted for Debian experimental.

Being curious which PostgreSQL releases have been in use over time, I pulled some graphics from Debian’s popularity contest data:

Before we included the PostgreSQL major version in the package name, “postgresql” contained the server, so that line represents the installation count of the pre-7.4 releases at the left end of the graph.

Interestingly, 7.4 reached its installation peak well past 8.1’s. Does anyone have an idea why that happened?

At this year’s FOSDEM I gave a talk in the PostgreSQL devroom about Large Scale Quality Assurance in the PostgreSQL Ecosystem. The talk included a graph about the growth of the apt.postgresql.org repository that I want to share here as well:

The yellow line at the very bottom is the number of different source package names, currently 71. From that, a somewhat larger number of actual source packages that include the “pgdgXX” version suffixes targeting the various distributions we have is built (blue). The number of different binary package names (green) is in about the same range. The dimension explosion then happens for the actual number of binary packages (black, almost 8000) targeting all distributions and architectures.

The red line is the total size of the pool/ directory, currently a bit less than 6GB.

(The graphs sometimes decrease when packages in the -testing distributions are promoted to the live distributions and the old live packages get removed.)

Yesterday saw the first beta release of the new PostgreSQL version 9.4. Along with the sources, we uploaded binary packages to Debian experimental and apt.postgresql.org, so there’s now packages ready to be tested on Debian wheezy, squeeze, testing/unstable, and Ubuntu trusty, saucy, precise, and lucid.

If you are using one of the release distributions of Debian or Ubuntu, add this to your /etc/apt/sources.list.d/pgdg.list to have 9.4 available:

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

On Debian jessie and sid, install the packages from experimental.

Happy testing!

Over the past few weeks, new distributions have been added on apt.postgresql.org: Ubuntu 13.10 codenamed “saucy” and the upcoming Ubuntu LTS release 14.04 codenamed “trusty”.

Adding non-LTS releases for the benefit of developers using PostgreSQL on their notebooks and desktop machines has been a frequently requested item since we created the repository. I had some qualms about targeting a new Ubuntu release every 6 months, but with having automated more and more parts of the repository infrastructure, and the bootstrapping process now being painless, the distributions are now available for use. Technically, trusty started as empty, so it hasn’t all packages yet, but of course all the PostgreSQL server packages are there, along with pgAdmin. Saucy started as a copy of precise (12.04) so it has all packages. Not all packages have been rebuilt for saucy, but the precise packages included (you can tell by the version number ending in .pgdg12.4+12 or .pgdg13.10+1) will work, unless apt complains about dependency problems. I have rebuilt the packages needing it I was aware about (most notably the postgresql-plperl packages) - if you spot problems, please let us know on the mailing list.

Needless to say, last week’s PostgreSQL server updates are already included in the repository.

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

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! :)

At work, I’m dealing with lots of different database setups, luckily mostly PostgreSQL running on Debian. At the same time, a fair amount of the tools in the PostgreSQL ecosystem (not the PostgreSQL server packages itself) are not in the best shape in the Debian archive.

I’m trying to change that by adopting some of the packages. So far, I have fixed a few RC bugs where packages where suddenly trying to build against PostgreSQL 9.0 while expecting 8.4. To my surprise, there are no packages yet in the archive that support multiple PostgreSQL versions in parallel. There is even a package ready to help doing this - postgresql-server-dev-all, but apparently nobody has used it yet. It turned out that after working around a few trivial problems and adding just a few lines of sh code, it was pretty straightforward to port skytools and postgresql-pllua to 9.0 while keeping 8.4 support. The latter has no version-specific code left in debian/ except for a list of supported versions in debian/pgversions, so a future port to 9.1 will be trivial. (Fun fact: the old postgresql-pllua version 0.8.1 was actually a typoed 0.3.1 version.)

Most PostgreSQL tools use a common subversion repository on Alioth, but there is no common mailing list address that is put into the Uploaders fields, so it is hard to get an overview over the state of all packages. I’ve compiled a list of all packages in svn, git, bzr (the server packages), and a few others in DDPO to fix that for now.

Other packages I’ve updated so far are pgtune, pgbouncer, and pgpool2.