pgfallback

README

pgfallback

A Go package for pgx, database/sql and GORM applications on a PostgreSQL cluster with a primary and streaming standbys. The application keeps its connection string with the rw addresses, which reach the primary. The package adds read addresses, which reach a standby, and uses them only while no rw address takes a new connection. Once one does again, new connections go back to rw.

So while there is no primary, reads keep working and writes fail with an error the application can recognise.

go get github.com/djosh34/pgfallback@latest

It needs Go 1.25 and adds pgx v5. It does not import GORM.

What it does

• A new connection tries the rw hosts in order, then the read addresses in the order given. The first that lets it in serves it. Nothing asks the server for its role, so a read address that reaches the primary serves writes too.
• A connection on a read address is a read connection. While any are in use, the package tries a full connect to rw in the background, at most once a second, and closes it at once.
• Once an attempt works, read connections are closed instead of reused. pgxpool closes one when it is handed out or released, after the running query ends. database/sql and GORM close one the next time it is handed out. An idle one stays open, unused, until the pool's idle limit.
• After more than 2 s without an attempt, a read connection that is handed out is closed, so the next query tries rw first.

It does not retry anything, change any error, send reads to the read addresses while rw works, or reorder the hosts.

Connection string

host=node1.example.com,node2.example.com,node3.example.com port=5432 dbname=orders user=orders
sslmode=verify-full sslrootcert=ca.crt sslcert=orders-app.crt sslkey=orders-app.key connect_timeout=2

• TLS on every host: sslmode require, verify-ca or verify-full.
• connect_timeout set; 2 works.
• target_session_attrs unset or any. Any other value makes pgx check each server's role.

Pool returns an error for a config that breaks one of these. With Stdlib, the error comes from the first connect: db.PingContext, or gorm.Open.

Set up

The examples reach the primary on port 5432 of each node and a standby on port 5433. TestDocExamples checks that each Go block here is in example_test.go, where it compiles.

pgx

// connectPool opens a pgxpool.Pool that falls back to the read port.
func connectPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}
	// Set the pool's own hooks and limits on cfg before this call.
	err = pgfallback.Pool(cfg, "node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		return nil, err
	}
	return pgxpool.NewWithConfig(ctx, cfg)
}

Pool keeps the config's hooks and calls them too. A change to the config after the call reaches neither.

database/sql

// connectDB opens a *sql.DB that falls back to the read port.
func connectDB(dsn string) (*sql.DB, error) {
	cfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}
	opts, err := pgfallback.Stdlib("node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		return nil, err
	}
	return stdlib.OpenDB(*cfg, opts...), nil
}

GORM

// connectGORM opens a *gorm.DB that falls back to the read port.
func connectGORM(dsn string) (*gorm.DB, error) {
	opts, err := pgfallback.Stdlib("node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		return nil, err
	}
	return gorm.Open(postgres.New(postgres.Config{DSN: dsn, OptionOpenDB: opts}), &gorm.Config{})
}

Stdlib returns stdlib.OptionBeforeConnect and stdlib.OptionResetSession. The last option of each kind wins, so pass none of your own of those two kinds.

A short dial timeout

A host that is down and does not refuse makes each new connection wait connect_timeout for it. A dialer with a shorter timeout gets past such hosts sooner. Set it before pgfallback.Pool, which copies the config for its attempts:

cfg.ConnConfig.DialFunc = (&net.Dialer{Timeout: 500 * time.Millisecond}).DialContext

Pick a value above the network's normal connect time.

Errors while there is no primary

The package passes every error on as it is.

• A write on a standby fails with SQLSTATE 25006. pgfallback.NoPrimary(err) is true for it. It checks only the SQLSTATE, so a read-only transaction on the primary gives true too.
• A pgx transaction with AccessMode: pgx.ReadWrite fails on a standby at BEGIN with SQLSTATE 0A000; NoPrimary is false for it.
• Whether a commit under way when its connection broke went through is not known.
• Retries are the application's, a whole transaction at a time.

How far behind a read can be

A read on a read address sees what that standby has applied, and nothing bounds how far behind that is. A standby that comes back can serve reads before it has caught up. An application that must read its own last commit has that only on the primary.

Tests

go test -race ./... needs docker and openssl. testdata/cluster.sh starts PostgreSQL 18 in docker: a primary, two streaming standbys and a standby with hot_standby off. Lint: go run github.com/golangci/golangci-lint/v2/cmd/golangci-lint@v2.15.0 run.

Licence

Apache-2.0, see LICENSE.

Documentation

Overview

Package pgfallback lets a pgx or GORM application use the read addresses of a database only while none of its rw addresses takes a connection, and go back to rw as soon as one does.

The application keeps its own connection string with the rw hosts. Pool adds the read addresses to a pgxpool.Config, Stdlib to database/sql and GORM through stdlib.OpenDB options:

cfg, err := pgxpool.ParseConfig(dsn)
...
err = pgfallback.Pool(cfg, "vm1:5433", "vm2:5433", "vm3:5433")

For each new connection pgx tries the rw hosts first, as it always does, and then the read addresses in the order given. A connection that landed on a read address is a read connection. While read connections are in use, the package tries a full connect to the rw hosts in the background, at most once a second. Once one works, read connections are closed when they are handed out or released, so new connections go to rw again. Nothing asks the server for its role.

The config must use TLS on every host, set connect_timeout, and leave target_session_attrs at any.

How to set it up, which errors an application sees while there is no primary, and how far behind a read on the read port can be: https://github.com/djosh34/pgfallback/blob/main/README.md

Index

• func NoPrimary(err error) bool
• func Pool(cfg *pgxpool.Config, read ...string) error
• func Stdlib(read ...string) ([]stdlib.OptionOpenDB, error)

Examples

• Pool
• Pool (DialTimeout)
• Stdlib
• Stdlib (Gorm)

Functions

func NoPrimary

func NoPrimary(err error) bool

NoPrimary reports whether err is or wraps a PostgreSQL error with SQLSTATE 25006, read_only_sql_transaction, which a write gets on a standby.

func Pool

func Pool(cfg *pgxpool.Config, read ...string) error

Pool adds the read addresses, each "host:port", to cfg. It keeps every hook cfg already has (BeforeConnect, AfterConnect, PrepareConn, BeforeAcquire, AfterRelease, and the AfterConnect of its ConnConfig) and calls it too. Call it once cfg is complete, right before pgxpool.NewWithConfig: a later change to cfg reaches neither the wrapped hooks nor the attempts at rw.

Example

package main

import (
	"context"
	"log"

	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/djosh34/pgfallback"
)

const exampleDSN = "host=node1.example.com,node2.example.com,node3.example.com port=5432 dbname=orders user=orders " +
	"sslmode=verify-full sslrootcert=ca.crt sslcert=orders-app.crt sslkey=orders-app.key connect_timeout=2"

// connectPool opens a pgxpool.Pool that falls back to the read port.
func connectPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}

	err = pgfallback.Pool(cfg, "node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		return nil, err
	}
	return pgxpool.NewWithConfig(ctx, cfg)
}

func main() {
	pool, err := connectPool(context.Background(), exampleDSN)
	if err != nil {
		log.Print(err)
		return
	}
	defer pool.Close()
}

Example (DialTimeout)

A pool whose TCP connects give up after 500 ms (README.md, A short dial timeout).

package main

import (
	"context"
	"log"
	"net"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/djosh34/pgfallback"
)

const exampleDSN = "host=node1.example.com,node2.example.com,node3.example.com port=5432 dbname=orders user=orders " +
	"sslmode=verify-full sslrootcert=ca.crt sslcert=orders-app.crt sslkey=orders-app.key connect_timeout=2"

func main() {
	cfg, err := pgxpool.ParseConfig(exampleDSN)
	if err != nil {
		log.Print(err)
		return
	}
	cfg.ConnConfig.DialFunc = (&net.Dialer{Timeout: 500 * time.Millisecond}).DialContext
	err = pgfallback.Pool(cfg, "node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		log.Print(err)
		return
	}
	pool, err := pgxpool.NewWithConfig(context.Background(), cfg)
	if err != nil {
		log.Print(err)
		return
	}
	defer pool.Close()
}

func Stdlib

func Stdlib(read ...string) ([]stdlib.OptionOpenDB, error)

Stdlib returns the stdlib.OpenDB options that add the read addresses, each "host:port", for database/sql, or for GORM through postgres.Config.OptionOpenDB. They set OptionBeforeConnect and OptionResetSession. The config is checked when a connection is opened.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"

	"github.com/djosh34/pgfallback"
)

const exampleDSN = "host=node1.example.com,node2.example.com,node3.example.com port=5432 dbname=orders user=orders " +
	"sslmode=verify-full sslrootcert=ca.crt sslcert=orders-app.crt sslkey=orders-app.key connect_timeout=2"

// connectDB opens a *sql.DB that falls back to the read port.
func connectDB(dsn string) (*sql.DB, error) {
	cfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}
	opts, err := pgfallback.Stdlib("node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		return nil, err
	}
	return stdlib.OpenDB(*cfg, opts...), nil
}

func main() {
	db, err := connectDB(exampleDSN)
	if err != nil {
		log.Print(err)
		return
	}
	if err := db.Close(); err != nil {
		log.Print(err)
	}
}

Example (Gorm)

package main

import (
	"log"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"

	"github.com/djosh34/pgfallback"
)

const exampleDSN = "host=node1.example.com,node2.example.com,node3.example.com port=5432 dbname=orders user=orders " +
	"sslmode=verify-full sslrootcert=ca.crt sslcert=orders-app.crt sslkey=orders-app.key connect_timeout=2"

// connectGORM opens a *gorm.DB that falls back to the read port.
func connectGORM(dsn string) (*gorm.DB, error) {
	opts, err := pgfallback.Stdlib("node1.example.com:5433", "node2.example.com:5433", "node3.example.com:5433")
	if err != nil {
		return nil, err
	}
	return gorm.Open(postgres.New(postgres.Config{DSN: dsn, OptionOpenDB: opts}), &gorm.Config{})
}

func main() {
	db, err := connectGORM(exampleDSN)
	if err != nil {
		log.Print(err)
		return
	}
	sqlDB, err := db.DB()
	if err != nil {
		log.Print(err)
		return
	}
	if err := sqlDB.Close(); err != nil {
		log.Print(err)
	}
}