pg_service.conf and client support
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
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.
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.htmlI 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 .NetI 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
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 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?
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
-----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.htmlI 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 .NetI 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
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 a connectionstring that PostgreSQL ODBC will accept?
Regards Niels
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
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