Filer Store: postgres backend support pgbouncer (#7077)

support pgbouncer

1 files changed, 26 insertions, 6 deletions

diff --git a/weed/filer/postgres/postgres_store.go b/weed/filer/postgres/postgres_store.go
index 568096b0b..1c60575ee 100644
--- a/weed/filer/postgres/postgres_store.go
+++ b/weed/filer/postgres/postgres_store.go
@@ -1,3 +1,10 @@
+// Package postgres provides PostgreSQL filer store implementation
+// Migrated from github.com/lib/pq to github.com/jackc/pgx for:
+// - Active development and support
+// - Better performance and PostgreSQL-specific features
+// - Improved error handling (no more panics)
+// - Built-in logging capabilities
+// - Superior SSL certificate support
 package postgres
 
 import (
@@ -6,7 +13,7 @@ import (
 	"strconv"
 	"time"
 
-	_ "github.com/lib/pq"
+	_ "github.com/jackc/pgx/v5/stdlib"
 	"github.com/seaweedfs/seaweedfs/weed/filer"
 	"github.com/seaweedfs/seaweedfs/weed/filer/abstract_sql"
 	"github.com/seaweedfs/seaweedfs/weed/util"
@@ -39,13 +46,14 @@ func (store *PostgresStore) Initialize(configuration util.Configuration, prefix
 		configuration.GetString(prefix+"sslkey"),
 		configuration.GetString(prefix+"sslrootcert"),
 		configuration.GetString(prefix+"sslcrl"),
+		configuration.GetBool(prefix+"pgbouncer_compatible"),
 		configuration.GetInt(prefix+"connection_max_idle"),
 		configuration.GetInt(prefix+"connection_max_open"),
 		configuration.GetInt(prefix+"connection_max_lifetime_seconds"),
 	)
 }
 
-func (store *PostgresStore) initialize(upsertQuery string, enableUpsert bool, user, password, hostname string, port int, database, schema, sslmode, sslcert, sslkey, sslrootcert, sslcrl string, maxIdle, maxOpen, maxLifetimeSeconds int) (err error) {
+func (store *PostgresStore) initialize(upsertQuery string, enableUpsert bool, user, password, hostname string, port int, database, schema, sslmode, sslcert, sslkey, sslrootcert, sslcrl string, pgbouncerCompatible bool, maxIdle, maxOpen, maxLifetimeSeconds int) (err error) {
 
 	store.SupportBucketTable = false
 	if !enableUpsert {
@@ -57,13 +65,23 @@ func (store *PostgresStore) initialize(upsertQuery string, enableUpsert bool, us
 		UpsertQueryTemplate:    upsertQuery,
 	}
 
+	// pgx-optimized connection string with better timeouts and connection handling
 	sqlUrl := "connect_timeout=30"
+
+	// PgBouncer compatibility: add prefer_simple_protocol=true when needed
+	// This avoids prepared statement issues with PgBouncer's transaction pooling mode
+	if pgbouncerCompatible {
+		sqlUrl += " prefer_simple_protocol=true"
+	}
+
 	if hostname != "" {
 		sqlUrl += " host=" + hostname
 	}
 	if port != 0 {
 		sqlUrl += " port=" + strconv.Itoa(port)
 	}
+
+	// SSL configuration - pgx provides better SSL support than lib/pq
 	if sslmode != "" {
 		sqlUrl += " sslmode=" + sslmode
 	}
@@ -91,16 +109,18 @@ func (store *PostgresStore) initialize(upsertQuery string, enableUpsert bool, us
 		sqlUrl += " dbname=" + database
 		adaptedSqlUrl += " dbname=" + database
 	}
-	if schema != "" {
+	if schema != "" && !pgbouncerCompatible {
 		sqlUrl += " search_path=" + schema
 		adaptedSqlUrl += " search_path=" + schema
 	}
 	var dbErr error
-	store.DB, dbErr = sql.Open("postgres", sqlUrl)
+	store.DB, dbErr = sql.Open("pgx", sqlUrl)
 	if dbErr != nil {
-		store.DB.Close()
+		if store.DB != nil {
+			store.DB.Close()
+		}
 		store.DB = nil
-		return fmt.Errorf("can not connect to %s error:%v", adaptedSqlUrl, err)
+		return fmt.Errorf("can not connect to %s error:%v", adaptedSqlUrl, dbErr)
 	}
 
 	store.DB.SetMaxIdleConns(maxIdle)