Filer Store: postgres backend support pgbouncer (#7077)

support pgbouncer

1 files changed, 26 insertions, 6 deletions

diff --git a/weed/filer/postgres2/postgres2_store.go b/weed/filer/postgres2/postgres2_store.go
index 135bd54c4..23d811816 100644
--- a/weed/filer/postgres2/postgres2_store.go
+++ b/weed/filer/postgres2/postgres2_store.go
@@ -1,3 +1,10 @@
+// Package postgres2 provides PostgreSQL filer store implementation with bucket support
+// 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 postgres2
 
 import (
@@ -7,7 +14,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/filer/postgres"
@@ -44,13 +51,14 @@ func (store *PostgresStore2) 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 *PostgresStore2) initialize(createTable, 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 *PostgresStore2) initialize(createTable, 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 = true
 	if !enableUpsert {
@@ -62,13 +70,23 @@ func (store *PostgresStore2) initialize(createTable, upsertQuery string, enableU
 		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
 	}
@@ -96,16 +114,18 @@ func (store *PostgresStore2) initialize(createTable, upsertQuery string, enableU
 		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)