River can use an alternate schema in Postgres using the client's Schema
option or configuring search_path
on connections.
Clusters, databases, and schemas
Postgres clusters may be subdivided in many databases, and each database may be subdivided again into many schemas, each one containing a collection of tables. While it's often practical to keep all an application's tables in a single schema for convenience, some users may find it desirable to put River's tables in an alternate schema. The public
schema is assigned to databases automatically, and by default all tables will be located there.
Configuring an alternate schema explicitly
Configure a schema explicitly on a client using Config.Schema
:
riverClient, err := river.NewClient(riverpgxv5.New(dbPool), &river.Config{ Schema: "alternate_schema", ...})if err != nil { panic(err)}
This option causes River to explicitly prefix all table, function, and enum references with an explicit schema like SELECT * FROM alternate_schema.river_job
.
Migrating with the River CLI
Use an alternate schema explicitly to migrate with the River CLI:
go install github.com/riverqueue/river/cmd/river@latest
river migrate-up --database-url "$DATABASE_URL" --schema alternate_schema
Migrating with the Go API
Use an alternate schema explicitly with the Go rivermigrate
API:
migrator, err := rivermigrate.New(bundle.driver, &rivermigrate.Config{ Schema: "alternate_schema",})if err != nil { panic(err)}
res, err := migrator.Migrate(ctx, rivermigrate.DirectionUp, nil)if err != nil { panic(err)}
Configuring a schema in search path
Alternatively, a non-standard schema can be configured by setting the Postgres search path (SET search_path TO ...
) of the database pool:
dbPoolConfig, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))if err != nil { // handle error}
// Set the schema in search path.dbPoolConfig.ConnConfig.RuntimeParams["search_path"] = "alternate_schema"
dbPool, err := pgxpool.NewWithConfig(ctx, dbPoolConfig)if err != nil { // handle error}defer dbPool.Close()
riverClient, err := river.NewClient(riverpgxv5.New(dbPool), &river.Config{ ...})if err != nil { // handle error}
Search paths are a list of schemas in which to look for database tables in order of preference. Storing River tables in one schema and other tables in another could be accomplished by including them both in search_path
:
SET search_path TO 'my_river_schema, my_other_schema'
A downside of search paths is that they're set on a per connection basis. Database pools like Pgx can set them automatically when procuring a new connection (see PgBouncer and AfterConnect), but it's possible for search_path
to become reconfigured or unset, resulting in River being unable to locate its tables:
ERROR: relation "river_job" does not exist (SQLSTATE 42P01)
Avoid this by preferring the use of an explicit Schema
option as described above.
Prefer the use of an explicit schema
Use of search_path
is generally not recommended due to the possibility that it may become unset or reconfigured on connections, especially in the presence of poolers like PgBouncer. Prefer the use of Config.Schema
instead.
Migrations and search path in database URLs
When running migrations with the River CLI, search path should be set as a parameter on the database URL:
export DATABASE_URL="postgres://host:5432/db?search_path=alternate_schema"
river migrate-up --database-url "$DATABASE_URL"
A schema name in the database URL is also be respected by pgxpool.New
or pgxpool.ParseConfig
, and can act as an alternative way of pointing a pgx connection pool to a different schema.
dbPool, err := pgxpool.New(ctx, "postgres://host:5432/db?search_path=alternate_schema")if err != nil { // handle error}defer dbPool.Close()
PgBouncer and AfterConnect
Depending on configuration and deployment, using an alternate schema with PgBouncer is a little trickier because it may be maintaining connections that weren't initialized with search_path
.
A work around is to use pgx's AfterConnect
hook to always set search_path
when procuring a connection from PgBouncer:
var ( alternateSchema = "alternate_schema" setSearchPath = fmt.Sprintf("SET search_path TO %s, public", pgx.Identifier{alternateSchema}.Sanitize()))
dbPoolConfig, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))if err != nil { return nil, err}
dbPoolConfig.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error { if _, err := conn.Exec(ctx, setSearchPath); err != nil { return fmt.Errorf("failed to set search_path: %w", err) } return nil}
dbPool, err := pgxpool.NewWithConfig(ctx, dbPoolConfig)if err != nil { // handle error}return dbPool, nil
A side effect of this technique is that connections become "tainted" in that even after River's done with them, search_path
stays set in PgBouncer and may affect other applications that subsequently use them. This effect should be benign as long as search_path
contains a fallback schema like alternate_schema, public
so clients can still find relations that aren't found in the River schema.