Subject: Update Old Version Public Keys?

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 7 Jun 2020 12:47:21 -0400

Hello Apters,

I noticed while making tests for an extension that supports older versions of PostgreSQL that the public keys for the 8.4 and 9.1 (but not 9.0) repos are out-of-date. Some details here:

  https://github.com/docker-library/postgres/issues/737

Any chance they could be updated? I realize that the community had dropped support for 9.4 and earlier, but as long as there are repos, perhaps the keys should be kept up-to-date?

Thanks,

David

---

From: Christoph Berg <[email-masked]>
Date: Mon, 8 Jun 2020 16:28:13 +0200

Re: David E. Wheeler
> Any chance they could be updated? I realize that the community had dropped support for 9.4 and earlier, but as long as there are repos, perhaps the keys should be kept up-to-date?

Hi,

I left a comment in the github issue.

Christoph

---

From: "David E. Wheeler" <[email-masked]>
Date: Mon, 8 Jun 2020 20:42:49 -0400

> I left a comment in the github issue.

Thanks Christoph!

D

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 13 Jun 2020 20:26:18 -0400

>> I left a comment in the github issue.

Well that was unsatisfying. So I made a new Docker image, pgxn/pgxn-tools:

  https://github.com/pgxn/docker-pgxn-tools

Works very well, except for Postgres 13 (and presumably future pre-releases). Is there some incantation I could put into the Dockerfile to permanently configure it to ready from the prerelease repo, too? Sorry for the newb question, I’m only a superficial apt user.

Thanks,

David

---

From: Christoph Berg <[email-masked]>
Date: Sun, 14 Jun 2020 17:28:51 +0200

Re: David E. Wheeler
> Works very well, except for Postgres 13 (and presumably future pre-releases). Is there some incantation I could put into the Dockerfile to permanently configure it to ready from the prerelease repo…

PG13 requires an extra "13" added to the sources.list entry:

https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release

There's a shell script to automate these bits in postgresql-common 204
or newer:

/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -v 13

https://salsa.debian.org/postgresql/postgresql-common/-/blob/master/pgdg/apt.postgresql.org.sh

(Unfortunately, buster has only version 200, so you'd need to install
postgresql-common from buster-pgdg first, but if you do that, you have
a trust path from buster via buster-pgdg to PG13 without fetching
random things via wget.)

Christoph

---

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 14 Jun 2020 16:06:33 -0400

> https://salsa.debian.org/postgresql/postgresql-common/-/blob/master/pgdg/apt.postgresql.org.sh

Okay. I’m doing this:

ADD  https://www.postgresql.org/media/keys/ACCC4CF8.asc .
RUN apt-key add ACCC4CF8.asc \
    && rm ACCC4CF8.asc \
    && echo deb http://apt.postgresql.org/pub/repos/apt buster-pgdg main > /etc/apt/sources.list.d/pgdg.list

This is so that the container can install any supported version (except 13). Is it possible to have both? Should I grab apt.postgresql.org.sh and use it to set up both the normal repo *and* for 13? Something like:

ADD https://salsa.debian.org/postgresql/postgresql-common/-/blob/master/pgdg/apt.postgresql.org.sh \
    && apt.postgresql.org.sh \
    && /apt.postgresql.org.sh -v 13

And then would it support `apt-get install postgresql-13`?

Thanks,

David

---

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 14 Jun 2020 17:54:56 -0400

> And then would it support `apt-get install postgresql-13`?

Okay, I changed it to:

```
ADD https://salsa.debian.org/postgresql/postgresql-common/-/raw/master/pgdg/apt.postgresql.org.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/apt.postgresql.org.sh \
    && apt-get update \
    && apt-get install -y --no-install-recommends build-essential pgxnclient ca-certificates gnupg2 \
    && apt-get clean
```

And then I added code to my start script to call

    apt.postgresql.org.sh -p -v $PGVERSION

However, when I try to install 13, I get:

```
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 postgresql-13 : Depends: postgresql-client-13 but it is not going to be installed
 postgresql-server-dev-13 : Depends: libpq-dev (>= 13~~) but 11.7-0+deb10u1 is to be installed
                            Depends: postgresql-client-13 but it is not going to be installed
E: Unable to correct problems, you have held broken packages.
```

Is there something else that needs doing?

Thanks,

David

---

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 11 Oct 2020 18:46:11 -0400

> Is there something else that needs doing?

Hello gang. I tried this again today, now that 13.0 is out, and get the same errors. Do I need to do something different than I did for 12 and earlier? Example:

  https://github.com/theory/pg-semver/runs/1239421317

Thanks,

David

---

From: Christoph Berg <[email-masked]>
Date: Mon, 12 Oct 2020 23:28:18 +0200

Re: David E. Wheeler

>   https://github.com/theory/pg-semver/runs/1239421317

| + apt.postgresql.org.sh -p -v 13
| This script will enable the PostgreSQL APT repository on apt.postgresql.org on
| your system. The distribution codename used will be buster-pgdg-testing.

Is that the latest version of the apt.postgresql.org.sh script? It
should only use "-testing" when passed -t.

Christoph

---

From: Aaron Pavely <[email-masked]>
Date: Tue, 13 Oct 2020 09:31:41 -0500

On Mon, Oct 12, 2020 at 4:28 PM Christoph Berg <[email-masked]> wrote:

> Re: David E. Wheeler
> > > Okay, I changed it to:
> > >
> > > ```
> > > ADD
> https://salsa.debian.org/postgresql/postgresql-common/-/raw/master/pgdg/apt.postgresql.org.sh
> /usr/local/bin/
>
> > Hello gang. I tried this again today, now that 13.0 is out, and get the
> same errors. Do I need to do something different than I did for 12 and
> earlier? Example:
> >

I think the issue (in part) is here:
https://salsa.debian.org/postgresql/postgresql-common/-/blob/master/pgdg/apt.postgresql.org.sh#L77-85

Because `-v 13` called in the script above forces the 'testing' release,
installation will fail since the pgxn docker image's 'pg-start' script
doesn't re-pin the release priority to include 'testing':
https://github.com/pgxn/docker-pgxn-tools/blob/main/bin/pg-start#L12

Both should be updated; the former updated to realize the release of
PostgreSQL 13 in non-testing repositories, and the latter to enable pinning
of the testing release when necessary.

-- Aaron

---

From: Christoph Berg <[email-masked]>
Date: Tue, 13 Oct 2020 17:15:35 +0200

Re: Aaron Pavely
> https://salsa.debian.org/postgresql/postgresql-common/-/blob/master/pgdg/apt.postgresql.org.sh#L77-85

Ouch, I knew hard-coding the version in there would backfire some day.
Thanks, I just pushed fix.

I still don't have a smart way to automate that, though :(

Christoph

---

From: "David E. Wheeler" <[email-masked]>
Date: Tue, 13 Oct 2020 13:07:15 -0400

> I still don't have a smart way to automate that, though :(

That fixes it, thanks.

Now, if I wanted to test Postgres 14 (dev), what additional steps would I have to take?

Thanks,

David

---

From: Christoph Berg <[email-masked]>
Date: Tue, 13 Oct 2020 20:44:29 +0200

Re: David E. Wheeler
> Now, if I wanted to test Postgres 14 (dev), what additional steps would I have to take?

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -v 14 -i

The PG14 packages currently have a bug in the "alternatives" handling
for the manpages which prevents installation. I'll try to fix that
now. (Or revert the breaking change.)

Christoph

---

From: Christoph Berg <[email-masked]>
Date: Fri, 16 Oct 2020 17:10:21 +0200

Re: To David E. Wheeler
> now. (Or revert the breaking change.)

Fwiw, the packages have been fixed now.

Christoph

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 17 Oct 2020 19:30:13 -0400

> sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -v 14 -i

For some reason it doesn’t work:

  https://github.com/theory/pg-semver/runs/1269694601?check_suite_focus=true

That’s running

PGVERSION=14
apt.postgresql.org.sh -p -v $PGVERSION
packages="postgresql-$PGVERSION postgresql-server-dev-$PGVERSION postgresql-common"
apt-get -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" install -y $packages

Working fine on v13 and earlier.

Thanks,

David

---

From: Aaron Pavely <[email-masked]>
Date: Sat, 17 Oct 2020 19:58:31 -0500

On Sat, Oct 17, 2020 at 6:30 PM David E. Wheeler <[email-masked]>
wrote:

> apt-get -o Dpkg::Options::="--force-confdef" -o
> Dpkg::Options::="--force-confold" install -y $packages
>

This is back to the release pin priority issue. PG14 is only available in
the testing release, and the testing release's pin priority does not permit
use without explicit enabling, i.e., use of apt-get option `-t
<release-codename>-pgdg-testing`.

Alternatively, skip the `apt-get` and simply use apt.postgresql.org.sh's
`-i` option to install, if you can accept it overwriting any existing
PostgreSQL configuration files, since it would achieve the same package set
installation goal.

-- Aaron

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 17 Oct 2020 20:59:58 -0400

On Oct 17, 2020, at 20:58, Aaron Pavely <[email-masked]> wrote:

> Alternatively, skip the `apt-get` and simply use apt.postgresql.org.sh's `-i` option to install, if you can accept it overwriting any existing PostgreSQL configuration files, since it would achieve the same package set installation goal.

Would that get me the server-dev package, too?

Thanks,

David

---

From: Aaron Pavely <[email-masked]>
Date: Sat, 17 Oct 2020 20:14:53 -0500

> Would that get me the server-dev package, too?

Yes, because the repository component '14' is specified as part of the
value for $COMPONENTS when the repository installation is done by the
apt.postgresql.org.sh script, here:
https://salsa.debian.org/postgresql/postgresql-common/-/blob/32702b6cd4c50b8da9484f5f0a189a6db3ef79a4/pgdg/apt.postgresql.org.sh#L78-85

If, instead, the `-i` option is used, the $PIN value assigned is used later
by `apt-get`, here:
https://salsa.debian.org/postgresql/postgresql-common/-/blob/32702b6cd4c50b8da9484f5f0a189a6db3ef79a4/pgdg/apt.postgresql.org.sh#L247-249

-- Aaron