PostgreSQL FDW over pgBouncer drops connection to PostgreSQL

Question

we have a PostgreSQL setup where we directed FDW (Forward Data Wrapper) connection to other databases over pgBouncer. Once we execute the query to FDW schema, connection from FDW to pgBouncer to another PostgreSQL server is opened correctly but once we close the connection to FDW server, the connection from pgBouncer to PostgreSQL is not returned to the bouncer pool but it's removed from pgBouncer pool and it's not visible (disconnected) on the PostgreSQL side.

If we do the same without the FDW, but we query the database using pgBouncer on the same PostgreSQL server and we disconnect, we see that the connection is returned to pgBouncer pool and it's still available on the PostgreSQL server (dropped once default 10min server_idle_timeout is reached).

Why does pgBouncer drops the connection to PostgreSQL when used by FDW, is this desired behaviour, if not is there any settings that needs to be applied to pgBouncer, FDW or PostgreSQL or this is a bug on either side?

• pgBouncer 1.15.0
• PostgreSQL v12

pgBouncer Config

;;
;;; PgBouncer configuration file
;;;
[databases]
* = host=192.168.210.51 port=5432

[users]

[pgbouncer]
;;;
;;; Administrative settings
;;;
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
;;;
;;; Where to wait for clients
;;;
;; IP address or * which means all IPs
listen_addr = *
listen_port = 6432
;;;
;;; Authentication settings
;;;
;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
;;;
;;; Users allowed into database 'pgbouncer'
;;;
;; comma-separated list of users who are allowed to change settings
admin_users = postgres
;; comma-separated list of users who are just allowed to use SHOW command
stats_users = stats, postgres
;;;
;;; Pooler personality questions
;;;
;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes
pool_mode = session
;;;
;;; Connection limits
;;;
;; Total number of clients that can connect
max_client_conn = 1000
;; Default pool size.  20 is good number when transaction pooling
;; is in use, in session pooling it needs to be the number of
;; max clients you want to handle at any moment
default_pool_size = 50
;; Minimum number of server connections to keep in pool.
min_pool_size = 0
; how many additional connection to allow in case of trouble
;reserve_pool_size = 0

FDW configuration

CREATE SERVER IF NOT EXISTS \"DatabaseName\"
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (dbname 'DatabaseName', host 'DatabaseHost', port 'port',
            fetch_size '50000', use_remote_estimate 'true',
            application_name 'my_reporting_fdw');

CREATE USER MAPPING IF NOT EXISTS
   FOR CURRENT_USER SERVER \"DatabaseName\"
   options (user 'DatabaseUsername', password 'DatabasePassword');

IMPORT FOREIGN SCHEMA \"public\"
   FROM SERVER \"DatabaseName\" INTO \"DatabaseName\";

Answer 1

I'd assume that your min_pool_size setting might be at fault. Try with something higher than 0.

After all, the point of a connection pool is to have persistent connections.