Could we support CockroachDB, so we get clustering and region clustering in order to support the privacy laws.

[gedw99]

support CockroachDB. Its got multi region clustering and so conforms to the various privacy laws. A lot easier.

explanation: https://www.cockroachlabs.com/blog/introducing-super-regions/

Nature of bug

The Issue is the migrate command and pg_advisory_lock: https://github.com/golang-migrate/migrate/issues/703

stencil server migrate fails:

db migrator: try lock failed in line 0: SELECT pg_advisory_lock($1) (details: pq: unknown function: pg_advisory_lock())

The driver ( github.com/jackc/pgx/v4 ) is fine and works great with cockroach.

source of bug:

package postgres

import (
	"context"
	"embed"
	"fmt"
	"log"
	"net/http"

	"github.com/golang-migrate/migrate/v4"
	_ "github.com/golang-migrate/migrate/v4/database/postgres"
	"github.com/golang-migrate/migrate/v4/source/httpfs"
	"github.com/jackc/pgconn"
	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/log/zapadapter"
	"github.com/jackc/pgx/v4/pgxpool"
	"github.com/pkg/errors"
	"github.com/raystack/stencil/internal/store"
	"github.com/raystack/stencil/pkg/logger"
)
``

## Reproduction of bug

here is single server.

.env
```sh
COCKROACH_SRC_DATA_FSPATH=$(PWD)/.data/cockroach
COCKROACH_SRC_LISTEN_ADDRESS=localhost:26257
COCKROACH_SRC_HTTP_ADDRESS=localhost:8080
# cluster ( i you want it )
COCKROACH_SRC_JOIN_ADDRESS=localhost:26257,localhost:26258,localhost:26259

start ( single cockroach node ): cockroach runs on all desktops btw. you don't have to use docker.

cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259 --background

config:

# Raystack Stencil Configuration
#
#
# !!WARNING!!
# This configuration file is for documentation purposes only. Do not use it in production.
#
# Stencil can be configured using a configuration file and passing the file location using `--config path/to/config.yaml`.
# Per default, Stencil will look up and load file ~/.stencil.yaml. All configuration keys can be set using environment
# variables as well.
#

# server controls the configuration for the GRPC server.
# The port to listen on. Defaults to 8080
port: 8080
# Timeout represents graceful shutdown period. Defaults to 60 sec
timeout: 5s
# Configuration for profiling application with new relic
newrelic:
  appname: example
  enabled: false
  license: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
# Database configurations for stencil backend
db:
  # Connection string for postgres database
  #connectionstring: "postgres://postgres@localhost:5432/db"
  connectionstring: "postgres://root@localhost:26257/defaultdb?sslmode=disable"

[ravisuhag]

@gedw99 Thanks for the issue. We can certainly support it. Would you be able to pick it up?

[gedw99]

@ravisuhag

Not sure what to do : The bug is with the migrate command which i believe is their bug your yours ?