psycopg2-binary 2.9.2 bundles wrong libpq version on aarch64

[oar-spease]

• OS: Docker container python:3.9-slim (Linux face723b8883 5.10.47-linuxkit 1 SMP PREEMPT Sat Jul 3 21:50:16 UTC 2021 aarch64 GNU/Linux)
• Psycopg version: 2.9.2
• Python version: 3.9
• PostgreSQL version: Debian 14.0-1.pgdg110+1
• pip version: 21.3.1

As the title says, psycopg2-binary is including the wrong libpq version. This causes the error "SCRAM authentication requires libpq version 10 or above"

The file appears to be /usr/local/lib/python3.9/site-packages/psycopg2_binary.libs/libpq-4fa63d26.so.5.9

The installation was pip install psycopg2-binary on a python:3.9-slim docker container, which to my knowledge is debian based.

Python 3.9.9 (main, Nov 17 2021, 12:25:33)
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> psycopg2.__version__
'2.9.2 (dt dec pq3 ext lo64)'
>>> psycopg2.__libpq_version__
90623

[dvarrazzo]

Yes, the problem is aarch64. We don't build the libpq from scratch on manylinux_2_24, we use the packaged version, and postgres doesn't have a repository where to fetch a more modern libpq version, so you get what debian gives.

• Build on aarch64
• Build on ppc

Did we find in the wrong place?

[oar-spease]

Looking at https://packages.debian.org/sid/libpq-dev, it appears as though debian's repos should be able to provide libpq 14.1

[dvarrazzo]

Have you got any way to install it on your system? If you manage we would be happy to upgrade the packages.

[oar-spease]

I can have the docker container manually apt-get update && apt-get install -y libpq-dev, which gives libpq-dev 13.5-0+deb11u1 and libpq5:arm64 13.5-0+dev11u1, but psycopg2 does not use it.

[dvarrazzo]

If you install psycopg2 instead of psycopg2-binary it will use them.

[oar-spease]

I assume that's meant to be a temp fix? Just double checking, since we want deployments to be fast, which means we generally try to limit compilation from source as much as possible. It does, however, work.

[dvarrazzo]

If PostgreSQL Debian packagers will package a more modern version we will use it. I don't know their reason behind not having an aarch64 distribution.

If you want to save the compilation time, and you said you can install a libpq in other ways, you can make a package to keep on a private pypi instance.

[Borketh]

Here's my solution, this may or may not help some of you, @oar-spease especially.

Dockerfile

FROM python:3.10-slim as runtime

VOLUME /config
WORKDIR /app

COPY runtime-deps.sh .
RUN bash runtime-deps.sh 1> /dev/null && rm runtime-deps.sh

FROM python:3.10-slim as build

WORKDIR /app

RUN apt-get -qq update && apt-get -qq install curl libpq-dev gcc 1> /dev/null

RUN curl -sSL https://install.python-poetry.org | POETRY_HOME=/usr/local python3 -

COPY pyproject.toml .
RUN poetry install -nvvv --no-dev && mv $(poetry env info --path) /app/venv

FROM runtime

COPY --from=build /app/venv /app/venv
COPY fred *.env ./fred/

CMD ./venv/bin/python3 -m fred

runtime-deps.sh

#!/bin/bash
# This file is meant to be run in the Dockerfile to get the runtime dependencies
# if you run it outside, good luck

echo "Getting initial setup dependencies..." 1>&2
apt-get update
apt-get install software-properties-common ca-certificates gnupg curl -y

# add repo that adds libpq properly
echo "Adding repository for libpq..." 1>&2
curl -sS https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt bullseye-pgdg main"
apt-get update

# install runtime dependencies
echo "Installing runtime dependencies..." 1>&2
apt-get install -y libpq5 tesseract-ocr

# we don't need this crap anymore
echo "Cleaning up..." 1>&2
apt-get remove -y software-properties-common gnupg curl
apt-get autopurge -y
rm -rf /var/lib/apt/lists/*
echo "All runtime dependencies handled!"

[ptman]

https://github.com/psycopg/psycopg2/blob/master/scripts/build/build_manylinux_2_24.sh#L31= looks like http://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/ has newer version of libpq available, why isn't it being used?

[ptman]

Right, the error message seems to be:

N: Skipping acquire of configured file 'main/binary-arm64/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease' doesn't support architecture 'arm64'

[ptman]

build-manylinux seems to run on ubuntu20.04, i.e. focal. why use stretch packages on arm64 when pgdg doesn't supply arm64 packages for stretch, but does for focal?

[Borketh]

As you can see in my example it also works on debian bullseye

[dvarrazzo]

manylinux_2_24 images are build on Debian 9 (Stretch). pgdg packages for aarch64 are only available from Debian 10 on.

Is it possible to install Debian 10 or Ubuntu packages on Debian 9? If so let me know, possibly with a MR.

[mbanck]

@dvarrazzo why are you (or is manylinux) still using Debian stretch? Is there some roadmap to move to supported version of Debian?

[df7cb]

apt.postgresql.org has been supporting arm64 (=aarch64) for some time now, but only for buster and newer. (I forgot why, but there was some reason that made stretch unsuitable for arm64 when I added that architecture.)

[dvarrazzo]

@df7cb do you think it would be possible to backport at least the libpq5/libpq-dev packages to stretch?

I am aware that stretch is getting old; however Python binary packages are built "by design" on old-ish architectures in order to give the produced artifacts the maximum ABI compatibility.

Even if those packages didn't make it to the public apt.postgresql.org, but there was a way to generate them using our CI, it would be extremely useful. I'm not very experience with .deb packaging but it would be nice to have some help on that front 🙂

[mbanck]

But the non-arm64 binary packages appear to be fine, at least that is what I've been told, maybe they are built in a totally different way?

The problem with being API compatible is that things like SCRAM or target_session_attrs won't work with the bundled libpq and honestly, this is not about skipping major (library) versions - bullseye still has libpq.so.5, but shipping a recent minor version.

[df7cb]

Stretch is 6 weeks away from the LTS EOL, so I don't think it makes sense to fix that now, only to tear it down again at the end of June.

https://wiki.debian.org/LTS/

[ptman]

There's ELTS: https://wiki.debian.org/LTS/Extended if one really wants to support it.

[jesseinit]

I'm facing an issue where when I install psycopg2, then build and run the container with docker run, I notice that libpq_version == 130009 but when I run the same image via docker-compose then access the shell for the container, I notice libpq_version == 90624.

How do I fix this?

[dvarrazzo]

Fixed by #1545. To be released in 2.9.6.