[Package Request] - PostgreSQL 16

[sylr]

trafficstars

What package is missing from Amazon Linux 2023? Please describe and include package name.

PostgreSQL 16

Is this an update to existing package or new package request?

New

Is this package available in Amazon Linux 2? If it is available via external sources such as EPEL, please specify.

Any additional information you'd like to include. (use-cases, etc)

[GrahamCampbell]

Would have to be the 2023.3 I think?

[syampillai]

When will this be available? I have migrated to RDS PostgreSQL 16.1 and now I can't do a pg_dump from my EC2 instance.

[andrzej-talarek]

Workaround for this would be temporarly using dockerized version of postgresql client to make a dump. Luckily it's available also for arm (including arm64).

[syampillai]

Yea, but I already installed Ubuntu on the EC2 for the time being.

[julrodriguez]

Is there an ETA on this? we have migrated some RDS database to PG16 and we cannot use pg_dump anymore; is there a workaround we can use in the mean time avaliable in AWS?

[andrzej-talarek]

I've wrote the workaround two comments earlier, did you miss this?

[norbertbede]

Hello @andrzej-talarek

i'm facing this issue after each upgrade of RDS. Since possible to apply workaround, but looks me really not practical to not taking care about this at AWS.

if i would take your suggestion then it will be temp from year 2013 since we are using RDS. :)

norbert

[andrzej-talarek]

Well, I don't argue with this - obviously it should be supported out-of-the-box by Amazon. But sine it's not... I didn't find any simpler solution to workaround this.

[dmorehouse]

It sucks that there's no compatible RPM package available for PostgreSQL 16. That said, you can easily build the PostgreSQL client binaries from source. This is what I'm doing as it performs much better than docker especially to just run psql, pg_dump, pg_restore on a small EC2 instance.

# Install the needed packages to build the client libraries from source
sudo yum install -y gcc readline-devel libicu-devel zlib-devel openssl-devel

# Download the source, you can browse the source code for other PostgreSQL versions (e.g. 16.2)
wget https://ftp.postgresql.org/pub/source/v16.1/postgresql-16.1.tar.gz  # PostgreSQL 16.1
tar -xvzf postgresql-16.1.tar.gz

cd postgresql-16.1

# Set bin dir so that executables are put in /usr/bin where psql and the others are installed by RPM
./configure --bindir=/usr/bin --with-openssl

sudo make -C src/bin install
sudo make -C src/include install
sudo make -C src/interfaces install

[LimmaPaulus]

Thanks @dmorehouse! Just to note that you have to run ./configure with --with-openssl flag if you want to use SSL-connections. That requires openssl-devel package to be installed.

`./configure --bindir=/usr/bin --with-openssl'

[dmorehouse]

Thanks @dmorehouse! Just to note that you have to run ./configure with --with-openssl flag if you want to use SSL-connections. That requires openssl-devel package to be installed.

`./configure --bindir=/usr/bin --with-openssl'

Thanks for pointing that out. I wasn't using SSL for my connections (everything is within a private VPC) but that does not mean other people won't want secure connections. I've updated my instructions to include SSL support.

[FrederikNygaardSvendsen]

It sucks that there's no compatible RPM package available for PostgreSQL 16. That said, you can easily build the PostgreSQL client binaries from source. This is what I'm doing as it performs much better than docker especially to just run psql, pg_dump, pg_restore on a small EC2 instance.

# Install the needed packages to build the client libraries from source
sudo yum install -y gcc readline-devel libicu-devel zlib-devel openssl-devel

# Download the source, you can browse the source code for other PostgreSQL versions (e.g. 16.2)
wget https://ftp.postgresql.org/pub/source/v16.1/postgresql-16.1.tar.gz  # PostgreSQL 16.1
tar -xvzf postgresql-16.1.tar.gz

cd postgresql-16.1

# Set bin dir so that executables are put in /usr/bin where psql and the others are installed by RPM
./configure --bindir=/usr/bin --with-openssl

sudo make -C src/bin install
sudo make -C src/include install
sudo make -C src/interfaces install

@dmorehouse You just saved my day :-)

[sylr]

I'd suggest to add systemd to the configure config:

yum install systemd-devel
./configure --with-systemd --with-openssl

[ritzk]

I'd suggest to add systemd to the configure config:

yum install systemd-devel
./configure --with-systemd --with-openssl

I would recommend grabbing srpm from https://kojipkgs.fedoraproject.org/packages/postgresql16/, and building it inside amazon linux 2023 container as a user.

rpm -ivh <package>
sudo dnf install -y rpm-build
sudo dnf builddep -y rpmbuild/SPECS/postgresql16.spec
sed  's/%patch /%patch/g' rpmbuild/SPECS/postgresql16.spec
rpmbuild -bb  rpmbuild/SPECS/postgresql.spec
 ```
 

[terranova-joseph]

@dmorehouse THANK YOU!!!

[shorn]

I just needed a container that I could do a few pg client commands from to work with RDS.
I thought AL2023 would be the best distro choice since it's the most modern Amazon-supplied distro. I figured it might not have great support for other non-amazon things, but would be well-supported for AWS stuff.

Eight months later, my workaround is to mix in the binaries from the official postgres image during the build.

---

Here are the relevant commands from my multi-stage docker build:

• installs the libpq libraries
• copies the postgres16 libraries and binaries from the official postgres 16 docker image
• adds the copied files to the LD_LIBRARY_PATH and PATH

FROM public.ecr.aws/docker/library/postgres:16 as pg-client
FROM public.ecr.aws/amazonlinux/amazonlinux:2023

RUN dnf update -y \
  && dnf install -y libpq \
  && dnf clean all

COPY --from=pg-client /usr/lib/postgresql/16 /usr/lib/postgresql/16
ENV LD_LIBRARY_PATH="/usr/lib/postgresql/16/lib:${LD_LIBRARY_PATH}"
ENV PATH="/usr/lib/postgresql/16/bin:${PATH}"

---

Note that this assumes you're using these tools via the PATH. i.e. psql blah blah blah

If your usage of the tools has hardcoded absolute paths like /usr/bin/psql, you either need to alter those references to use the path, or use /usr/lib/postgresql/16/bin/psql blah blah blah, or you can add the symbolic links to the /usr/bin directory or something.

[jensenbox]

Seems a bit odd that this would not make it in to AL2023 rapidly. Is AL2023 even maintained? I am scared to think that AL2024 will have all these problems all over again.

[ashkulz]

Building PostgreSQL 16 packages for Amazon Linux 2023 via Docker

docker run -v$PWD:/dl --rm amazonlinux:2023 sh -c "dnf install -y shadow-utils util-linux rpm-build 'dnf-command(builddep)' && useradd -ms /bin/bash build && su build -c 'cd && curl --remote-name-all https://kojipkgs.fedoraproject.org/packages/postgresql16/16.3/3.fc41/src/postgresql16-16.3-3.fc41.src.rpm https://kojipkgs.fedoraproject.org/packages/libpq/16.3/3.fc41/src/libpq-16.3-3.fc41.src.rpm && rpm -ivh *.src.rpm' && dnf builddep -y ~build/rpmbuild/SPECS/postgresql16.spec && su build -c 'cd && rpmbuild -bb rpmbuild/SPECS/libpq.spec' && sed -i 's/%patch /%patch/g' ~build/rpmbuild/SPECS/postgresql16.spec && su build -c 'cd && rpmbuild -bb rpmbuild/SPECS/postgresql16.spec' && cp ~build/rpmbuild/RPMS/x86_64/*.rpm /dl"

This uses a specific package version (16.3-3) from Fedora and creates a lot of RPMs in the current directory -- to install the normal set of packages used, you'll need to run:

dnf install -y libpq-16*.rpm libpq-devel-16*.rpm postgresql-16*.rpm postgresql-private-libs-16*.rpm

NOTE: I'm not sure why the package prefix is postgresql-* instead of postgresql16-*, so you may need to ensure that the postgresql15-* packages are not installed.

[shorn]

Is AL2023 even maintained? I am scared to think that AL2024 will have all these problems all over again.

It's certainly got me re-thinking what to use as the base for my images.

[ozbenh]

AL2023 is most definitely actively maintained, I invite you to look at all the CVE fixes released by the team regularly. Adding new packages, especially large ones such as pgsql16, with appropriate support and long term CVE fixing, isn't a completely trivial matter, but rest assured that we are still looking into it. I also would like to point out that some other major enterprise distributions only ship one version of pgsql for their entire lifetime, and EPEL is .... unsupported.

[ozbenh]

As for @ashkulz message above, you might want to see the changes we did to the pgsql15 SPEC file in order to namespace it, specifically the statement:

Name: postgresql%{majorversion}

Which changes the package name, along with the various Conflicts: statement to ensure exclusion with older versions.

[rawandahmad698]

If you build from source and unable to get the pg_dump command working, try:

1. Locate your PostgreSQL installation: If you followed the default installation, it's likely in /usr/local/pgsql. Add the PostgreSQL bin directory to your PATH:
2. Edit your shell's configuration file (e.g., ~/.bashrc or ~/.bash_profile) and add the following line:

export PATH=/usr/local/pgsql/bin:$PATH

Source your updated configuration file or restart your shell session:

source ~/.bashrc

(or source ~/.bash_profileif you edited that file)

3. Ensure the PostgreSQL binaries have the correct permissions:

sudo chmod +x /usr/local/pgsql/bin/*

3. Try running pg_dump --version to check if it's now recognized. Output:

pg_dump (PostgreSQL) 16.3

[daveisfera]

AL2023 is most definitely actively maintained, I invite you to look at all the CVE fixes released by the team regularly. Adding new packages, especially large ones such as pgsql16, with appropriate support and long term CVE fixing, isn't a completely trivial matter, but rest assured that we are still looking into it. I also would like to point out that some other major enterprise distributions only ship one version of pgsql for their entire lifetime, and EPEL is .... unsupported.

Older versions of Amazon Linux had the extras for managing multiple versions like this and since Software Collections have fallen out of favor for DNF Modules, then couldn't those be used to provide multiple versions of tools, like Postgres 16 and 17?

[RavianXReaver]

AL2023 is most definitely actively maintained, I invite you to look at all the CVE fixes released by the team regularly. Adding new packages, especially large ones such as pgsql16, with appropriate support and long term CVE fixing, isn't a completely trivial matter, but rest assured that we are still looking into it. I also would like to point out that some other major enterprise distributions only ship one version of pgsql for their entire lifetime, and EPEL is .... unsupported.

Yeah fixing CVE are important. But it doesnt mean we have to stick to older versions of applications. It took 7 months for them to add PHP 8.3 to the repository.

Not gonna use AL for my next EC2.

[ozbenh]

postgresql16 is added to an upcoming version of AL2023

[ashkulz]

@ozbenh can you update this ticket when this shows up in the release notes?

[ozbenh]

The release notes are lagging a bit behind but AL2023.6 is out and contains

Name         : postgresql16
Version      : 16.4
Release      : 1.amzn2023.0.1
Architecture : aarch64
Size         : 1.8 M
Source       : postgresql16-16.4-1.amzn2023.0.1.src.rpm
Repository   : amazonlinux
Summary      : PostgreSQL client programs
URL          : http://www.postgresql.org/
License      : PostgreSQL
Description  : PostgreSQL is an advanced Object-Relational database management system (DBMS).
             : The base postgresql package contains the client programs that you'll need to
             : access a PostgreSQL DBMS server, as well as HTML documentation for the whole
             : system.  These client programs can be located on the same machine as the
             : PostgreSQL server, or on a remote machine that accesses a PostgreSQL server
             : over a network connection.  The PostgreSQL server can be found in the
             : postgresql-server sub-package.

[ashkulz]

I guess it's still propagating everywhere, still not available from the docker image?

$ docker rmi amazonlinux:2023; docker run --rm amazonlinux:2023 sh -c "yum search postgresql16"
Error response from daemon: No such image: amazonlinux:2023
Unable to find image 'amazonlinux:2023' locally
2023: Pulling from library/amazonlinux
5acaf245b957: Pull complete 
Digest: sha256:019cf20c5b98efcd548952d31df1768377916bf8d57d70d979c6c92bfd5e7446
Status: Downloaded newer image for amazonlinux:2023
Amazon Linux 2023 repository                     11 MB/s |  28 MB     00:02    
Last metadata expiration check: 0:00:04 ago on Tue Oct 15 03:44:55 2024.
No matches found.

but awesome to see it happen :tada:

[ozbenh]

The new images might still be propagating. In the meantime you can try to update your existing one. Check the instructions from sudo dnf check-release-update

[douglascodes]

The new images might still be propagating. In the meantime you can try to update your existing one. Check the instructions from sudo dnf check-release-update

Thanks. That is helpful. Using the current latest as the base: amazonlinux:2023.5.20241001.1 And running dnf upgrade -y --releasever=2023.6.20241010 allows me to do dnf install -y postgresql16 which is what I needed.