Thread: client/server versions

client/server versions

From
Dick Visser
Date:
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 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?
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

Re: client/server versions

From
Thiemo Kellner
Date:
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




Re: client/server versions

From
Dick Visser
Date:


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. 

Re: client/server versions

From
Ron Johnson
Date:
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?

Re: client/server versions

From
Dick Visser
Date:
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

 

Re: client/server versions

From
Thiemo Kellner
Date:
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.




Re: client/server versions

From
Daniel Gustafsson
Date:
> 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
olderthan 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




Re: client/server versions

From
Ron Johnson
Date:
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.
 

Re: client/server versions

From
Dick Visser
Date:


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


Re: client/server versions

From
Adrian Klaver
Date:
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> 
> 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