client/server versions

Hi Dick

Out of curiosity, what is the reason there is this zoo of versions. Is
it impossible to align them to one version?

Cheers

Thiemo

On Tue, 21 Nov 2023 at 12:40, Thiemo Kellner <thiemo@gelassene-pferde.biz>
wrote:

Hi Dick

Out of curiosity, what is the reason there is this zoo of versions. Is
it impossible to align them to one

It is possible, it's just that there is no real need to at the moment, ao
we have not spent that much effort on the topic.
At any time there will always be newer upstream versions.

On Tue, Nov 21, 2023 at 6:29 AM Dick Visser <dnmvisser@gmail.com> wrote:

Hi

I'm working with AWS RDS PG instances that have been created over time,
and that by now are a mix of several major/minor versions ranging from 12
to 15.
The initial configuration and management is done from an EC2 instance
running Debian 11, which has Postrgres 13.13.
I already found out that there are compatibility issues if I use the
client programs from the Debian VM when they're older than the server
version.

Like what?

psql can *sometimes* be wonky when running against newer servers, as can
PgAdmin4, but that's because they won't understand changed catalog tables.
Do the other client programs use new SQL features?

On Tue, 21 Nov 2023 at 14:07, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

On Tue, Nov 21, 2023 at 6:29 AM Dick Visser <dnmvisser@gmail.com> wrote:

Hi

I'm working with AWS RDS PG instances that have been created over time,
and that by now are a mix of several major/minor versions ranging from 12
to 15.
The initial configuration and management is done from an EC2 instance
running Debian 11, which has Postrgres 13.13.
I already found out that there are compatibility issues if I use the
client programs from the Debian VM when they're older than the server
version.

Like what?

For starters:

pg_restore: error: unsupported version (1.15) in file header

Am 21.11.2023 um 13:18 schrieb Dick Visser:

It is possible, it's just that there is no real need to at the moment,
ao we have not spent that much effort on the topic.
At any time there will always be newer upstream versions.

Ok, fair enough, but it puzzles me that the effort shall be put into
making a multitude of client verions working flawlessly with a multitude
of server versions instead of holding server (and client) versions the same.

On 21 Nov 2023, at 14:31, Dick Visser <dnmvisser@gmail.com> wrote:
On Tue, 21 Nov 2023 at 14:07, Ron Johnson <ronljohnsonjr@gmail.com <mailto:ronljohnsonjr@gmail.com>> wrote:
On Tue, Nov 21, 2023 at 6:29 AM Dick Visser <dnmvisser@gmail.com <mailto:dnmvisser@gmail.com>> wrote:

I already found out that there are compatibility issues if I use the client programs from the Debian VM when they're older than the server version.

Like what?

For starters:

pg_restore: error: unsupported version (1.15) in file header

Older versions of tools are not guaranteed to be compatible with newer versions
of the server. You should use the dump/restore tools of the version you are
targeting. For other clients like psql it's usually fine to use the latest
version against older servers.

If you have a mix of server versions installed you need to put in the work of
ensuring you are using the right version of the clients as well.

--
Daniel Gustafsson

On Tue, Nov 21, 2023 at 8:31 AM Dick Visser <dnmvisser@gmail.com> wrote:

On Tue, 21 Nov 2023 at 14:07, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

On Tue, Nov 21, 2023 at 6:29 AM Dick Visser <dnmvisser@gmail.com> wrote:

Hi

I'm working with AWS RDS PG instances that have been created over time,
and that by now are a mix of several major/minor versions ranging from 12
to 15.
The initial configuration and management is done from an EC2 instance
running Debian 11, which has Postrgres 13.13.
I already found out that there are compatibility issues if I use the
client programs from the Debian VM when they're older than the server
version.

Like what?

For starters:

pg_restore: error: unsupported version (1.15) in file header

Upgrade Postgresql on the Debian 11 EC2 instance from 13.11 to 15.latest.

Or install Postgresql 12, 14 and 15 alongside Pg 13. They all go in
separate directories.

On Tue, 21 Nov 2023 at 15:00, Thiemo Kellner <thiemo@gelassene-pferde.biz>
wrote:

Ok, fair enough, but it puzzles me that the effort shall be put into
making a multitude of client verions working flawlessly with a multitude
of server versions instead of holding server (and client) versions the
same.

Yes, I know, but the method I described is a rather trivial way to make
things work in this situation.
My own deduction is that using latest version clients is probably better
than using same version clients.

Keep in mind that there are situations where users of clients programs
aren't in control of server bits, so this answer would be helpful for them
as well.

Dick

On 11/21/23 03:28, Dick Visser wrote:

Hi

I'm working with AWS RDS PG instances that have been created over time,
and that by now are a mix of several major/minor versions ranging from
12 to 15.
The initial configuration and management is done from an EC2 instance
running Debian 11, which has Postrgres 13.13.
I already found out that there are compatibility issues if I use the
client programs from the Debian VM when they're older than the server
version.
To avoid this I tried to run the client programs from a dedicated PG
container image with the same version as the server.
So instead of running:

PGSERVICE=foobar psql

I would now run:

podman run -e PGSERVICE=foobar -v
$(pwd)/.pg_service.conf:/root/.pg_service.conf --rm -it
docker.io/library/postgres:15.3 <http://docker.io/library/postgres:15.3&gt;
psql

This is reasonably straightforward and seems to just work.
Now that I have a way to pick whatever client version I need, I wonder
what the best strategy is wrt versions...
Keeping them exactly the same, or is it better to always use the latest
client version?

Not sure how you installed the Postgres versions and what the OS'es are
for all the instances? If you used the Debian/Ubuntu repos or Postgres
community repo Debian/Ubuntu packages then you got the program
pg_wrapper installed.

Per man pg_wrapper or postgresql-common(they point to the same thing) file:

"""
This program is run only as a link to names which correspond to
PostgreSQL programs in /usr/lib/postgresql/version/bin. It determines
the configured cluster and database for the user and calls the
appropriate version of the desired program to connect to that cluster
and database, supplying any specified options to thatcommand.

...

For psql, pg_archivecleanup, and pg_isready, pg_wrapper will always use
the binary from the newest PostgreSQL version installed, as these are
downwards compatible.
"""

I know that the latter is a bit easier as one can just pick an untagged
container image, because that corresponds to the latest version.
Also I'm thinking that using the latest version of pg_dump/pg_restore
allows you to have more options.

thx

Dick Visser

--
Adrian Klaver
adrian.klaver@aklaver.com