Thread: pg_service.conf and client support

pg_service.conf and client support

From
Niels Jespersen
Date:

Hello all

 

I just found out about the pg service file. https://www.postgresql.org/docs/current/libpq-pgservice.html

 

I don’t know why it took me so long finding this. I have been looking for ways to abstract physical details of data location away for clients (much like Oracle’s tnsnames).

 

Want to move a cluster to a new host. Sure, move it, edit pg_service.conf and clients will not know the difference.

 

It works great for at least psql and psycopg2.

 

But I cannot find anything on pg_service.conf and the Postgres ODBC driver and Npgsql for .Net

 

I know pg_service.conf support is implemented through libpq and support for rivers not using libpq is not a given thing.

 

But I think the need for the abstraction of connection details is a general one.

 

Can anyone shed som light on the ubiquitousness of support for pg_service.conf?

 

Are there any other mechanisms with broader support, that can be used instead of pg_service.conf (if support is scarce beyond what builds on libpq)?

 

Thank you.

 

Regards Niels Jespersen

Re: pg_service.conf and client support

From
"David G. Johnston"
Date:
On Saturday, June 13, 2020, Niels Jespersen <NJN@dst.dk> wrote:

Can anyone shed som light on the ubiquitousness of support for pg_service.conf?

AFAIK same non-support for JDBC and Node.js 

Are there any other mechanisms with broader support, that can be used instead of pg_service.conf (if support is scarce beyond what builds on libpq)?

Not that I am aware, though programmatically accessed libraries are somewhat able to be adapted using custom wrappers so that the pg_service file contents can be leveraged.

David J.

Re: pg_service.conf and client support

From
Adrian Klaver
Date:
On 6/13/20 10:03 PM, Niels Jespersen wrote:
> Hello all
> 
> I just found out about the pg service file. 
> https://www.postgresql.org/docs/current/libpq-pgservice.html
> 
> I don’t know why it took me so long finding this. I have been looking 
> for ways to abstract physical details of data location away for clients 
> (much like Oracle’s tnsnames).
> 
> Want to move a cluster to a new host. Sure, move it, edit 
> pg_service.conf and clients will not know the difference.
> 
> It works great for at least psql and psycopg2.
> 
> But I cannot find anything on pg_service.conf and the Postgres ODBC 
> driver and Npgsql for .Net
> 
> I know pg_service.conf support is implemented through libpq and support 
> for rivers not using libpq is not a given thing.
> 
> But I think the need for the abstraction of connection details is a 
> general one.
> 
> Can anyone shed som light on the ubiquitousness of support for 
> pg_service.conf?
> 
> Are there any other mechanisms with broader support, that can be used 
> instead of pg_service.conf (if support is scarce beyond what builds on 
> libpq)?

Only thing I know of is something like 
pgbouncer(http://www.pgbouncer.org/) where you keep a constant 
connection setting for the pooler and then change the settings for the 
database(s) it connects to.

> 
> Thank you.
> 
> Regards Niels Jespersen
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: pg_service.conf and client support

From
Laurenz Albe
Date:
On Sun, 2020-06-14 at 05:03 +0000, Niels Jespersen wrote:
> I just found out about the pg service file. https://www.postgresql.org/docs/current/libpq-pgservice.html
> 
> I don’t know why it took me so long finding this. I have been looking for ways to abstract physical details of data
locationaway for clients (much like Oracle’s tnsnames).
 
> 
> Want to move a cluster to a new host. Sure, move it, edit pg_service.conf and clients will not know the difference.
> 
> It works great for at least psql and psycopg2.
> 
> But I cannot find anything on pg_service.conf and the Postgres ODBC driver and Npgsql for .Net
> 
> I know pg_service.conf support is implemented through libpq and support for rivers not using libpq is not a given
thing.
> 
> But I think the need for the abstraction of connection details is a general one.
> 
> Can anyone shed som light on the ubiquitousness of support for pg_service.conf?

You got it right: pg_service.conf is only used by libpq, so all clients that
use libpq automatically support it.  Other clients don't support it.

For your examples that means:
- The PostgreSQL ODBC server can use pg_service.conf
- NpgSQL cannot use the file.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




SV: pg_service.conf and client support

From
Niels Jespersen
Date:

-----Oprindelig meddelelse-----
Fra: Laurenz Albe <laurenz.albe@cybertec.at> 
Sendt: 15. juni 2020 11:24
Til: Niels Jespersen <NJN@dst.dk>; pgsql-general@postgresql.org
Emne: Re: pg_service.conf and client support

On Sun, 2020-06-14 at 05:03 +0000, Niels Jespersen wrote:
> I just found out about the pg service file. 
> https://www.postgresql.org/docs/current/libpq-pgservice.html
> 
> I don’t know why it took me so long finding this. I have been looking for ways to abstract physical details of data
locationaway for clients (much like Oracle’s tnsnames).
 
> 
> Want to move a cluster to a new host. Sure, move it, edit pg_service.conf and clients will not know the difference.
> 
> It works great for at least psql and psycopg2.
> 
> But I cannot find anything on pg_service.conf and the Postgres ODBC 
> driver and Npgsql for .Net
> 
> I know pg_service.conf support is implemented through libpq and support for rivers not using libpq is not a given
thing.
> 
> But I think the need for the abstraction of connection details is a general one.
> 
> Can anyone shed som light on the ubiquitousness of support for pg_service.conf?

You got it right: pg_service.conf is only used by libpq, so all clients that use libpq automatically support it.  Other
clientsdon't support it.
 

For your examples that means:
- The PostgreSQL ODBC server can use pg_service.conf
- NpgSQL cannot use the file.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Thank you Laurenz

You mention that the PostgreSQL ODBC driver can use pg_service.conf. But I cannot find any examples om how to construct
aconnectionstring that PostgreSQL ODBC will accept? 
 

Regards Niels




Re: SV: pg_service.conf and client support

From
Laurenz Albe
Date:
On Mon, 2020-06-15 at 11:58 +0000, Niels Jespersen wrote:
> > For your examples that means:
> > - The PostgreSQL ODBC server can use pg_service.conf
> > - NpgSQL cannot use the file.
> 
> You mention that the PostgreSQL ODBC driver can use pg_service.conf. But I cannot
> find any examples om how to construct a connectionstring that PostgreSQL ODBC will accept? 

After looking at the code, I am no longer sure.

There doesn't seem to be a way to specify a general connection string.

You could try setting the environment variable PGSERVICE to specify
the service.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




SV: SV: pg_service.conf and client support

From
Niels Jespersen
Date:
Fra: Laurenz Albe <laurenz.albe@cybertec.at> 
Sendt: 15. juni 2020 15:36
Til: Niels Jespersen <NJN@dst.dk>; pgsql-general@postgresql.org
Emne: Re: SV: pg_service.conf and client support

On Mon, 2020-06-15 at 11:58 +0000, Niels Jespersen wrote:
> > For your examples that means:
> > - The PostgreSQL ODBC server can use pg_service.conf
> > - NpgSQL cannot use the file.
> 
> You mention that the PostgreSQL ODBC driver can use pg_service.conf. 
> But I cannot find any examples om how to construct a connectionstring that PostgreSQL ODBC will accept?

After looking at the code, I am no longer sure.

There doesn't seem to be a way to specify a general connection string.

You could try setting the environment variable PGSERVICE to specify the service.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

That is the way to do it. It works. Connectionstring 

'Driver={PostgreSQL Unicode(x64)}' 

And environment variables 

PGSERVICEFILE and PGSERVICE

Will connect you. Thank you for helping with this. 

Regards Niels