FDW and connections
Greetings!
Let say I have a foreign server using the reference postgres_fdw defined
without a port number:
CREATE SERVER /dat_server/ FOREIGN DATA WRAPPER /postgres_fdw/ OPTIONS (
|/host '172.1.1.1', dbname 'dbover_der'/| )
Naturally the tables in question are setup using a foreign table
definition, specifying the foreign server /dat_server/. My understanding
is when the sql is ready for execution whatever is determined to be
pushed down is sent to the foreign server. Taking a look at the code it
appears in postgres_fdw.c a connection is probably made in dat case:
/* for remote query execution */
PGconn *conn; /* connection for the scan */
PgFdwConnState *conn_state; /* extra per-connection state */
and
/*
* Get connection to the foreign server. Connection manager will
* establish new connection if necessary.
*/
fsstate->conn = GetConnection(user, false, &fsstate->conn_state);
My question is - how does the call to GetConnection() know what port to
use? Lets say we're using PGBouncer to connect on the local server at
port 6432, but there is no pgbouncer listening at the foreign server,
what port gets passed? My first thought is whatever the client connects
port is, but I believe pgbouncer ultimately hands of the connection to
whatever port you have defined for the local database...
This gets important when one has an HAProxy instance between the local
and foreign servers which is interrogating the port number to decide
which ip:port to send the request to, ultimately the master or replicant
at the foreign remoter server.
So how does the port number get propagated from local to foreign server???
Much thanks for your help.
Phil Godfrin
From: Phil Godfrin <pgodfrin@comcast.net>
My question is - how does the call to GetConnection() know what port to use? Lets say we're using PGBouncer to connect on the local server at port 6432, but there is no pgbouncer listening at the foreign server, what port gets passed? My first thought is whatever the client connects port is, but I believe pgbouncer ultimately hands of the connection to whatever port you have defined for the local database...
This gets important when one has an HAProxy instance between the local and foreign servers which is interrogating the port number to decide which ip:port to send the request to, ultimately the master or replicant at the foreign remoter server.
So how does the port number get propagated from local to foreign server???
--------------------------------------------------
postgres_fdw uses libpq as a client to connect to the foreign server. So, as the following says, you can specify the libpq's "port" parameter in CREATE SERVER. If it's ommitted as in your case, the default 5432 will be used.
F.35.1.1. Connection Options
https://www.postgresql.org/docs/devel/postgres-fdw.html
"A foreign server using the postgres_fdw foreign data wrapper can have the same options that libpq accepts in connection strings, as described in Section 34.1.2, except that these options are not allowed or have special handling:"
I'm afraid it's better to post user-level questions like this to pgsql-general@lists.postgresql.org.
Regards
Takayuki Tsunakawa
Apologies, in my mind this was an internals to the postgres_fdw code,
which is why I cam here. I checked that part of the docs and nowhere
does it say anything about defaulting to 5432. However in the referred
section, 34.1.2, there it says that libpq defaults to the "port number
established when PostgreSQL was built". I'm not well informed about the
internals of libpq nor the mailing lists, again I'm sorry. Seems to me I
need to learn more about both <grin>. Thanks.
pg
Show quoted text
On 5/16/2021 7:43 PM, tsunakawa.takay@fujitsu.com wrote:
From: Phil Godfrin <pgodfrin@comcast.net>
My question is - how does the call to GetConnection() know what port
to use? Lets say we're using PGBouncer to connect on the local server
at port 6432, but there is no pgbouncer listening at the foreign
server, what port gets passed? My first thought is whatever the client
connects port is, but I believe pgbouncer ultimately hands of the
connection to whatever port you have defined for the local database...This gets important when one has an HAProxy instance between the local
and foreign servers which is interrogating the port number to decide
which ip:port to send the request to, ultimately the master or
replicant at the foreign remoter server.So how does the port number get propagated from local to foreign server???
--------------------------------------------------
postgres_fdw uses libpq as a client to connect to the foreign server.
So, as the following says, you can specify the libpq's "port"
parameter in CREATE SERVER. If it's ommitted as in your case, the
default 5432 will be used.F.35.1.1. Connection Options
https://www.postgresql.org/docs/devel/postgres-fdw.html
"A foreign server using the postgres_fdw foreign data wrapper can have
the same options that libpq accepts in connection strings, as
described in Section 34.1.2, except that these options are not allowed
or have special handling:"I'm afraid it's better to post user-level questions like this to
pgsql-general@lists.postgresql.org.Regards
Takayuki Tsunakawa