Good day! I'm setting up a POC with Meltano using system DB for storing the elt state. The db is PostrgeSQL 15. The problem I've run into is that I'm not able to set the system db schema other than

public

. Works (

public

schema)

meltano --environment=dev config meltano set database_uri '<postgresql://CHANGEME:CHANGEME@localhost:5432/meltano__dev>'

The systemdb tables are created in

public

upon first

etl

run however this does not (

systemdb

schema)

meltano --environment=dev config meltano set database_uri '<postgresql://sqlsvc__dpt__meltano_state:meltano@localhost:5432/meltano__dev?options=-csearch_path%3Dsystemdb>'

produces no errors.. but on first elt run, such as

meltano --environment=dev elt tap-CHANGEME target-CHANGEME --transform=skip --state-id=CHANGEME-to-CHANGEME

would produce following error

2023-01-24T09:57:35.507989Z [error    ] (psycopg2.errors.InvalidSchemaName) no schema has been selected to create in
LINE 2: CREATE TABLE alembic_version (
                     ^

[SQL:
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL,
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)

]
(Background on this error at: <https://sqlalche.me/e/14/f405>)
...
[MASSIVE STACK TRACE]

I'm following the official docs Targeting a PostgreSQL Schema

hmm, I just set up the system db with postgres 13.4 recently and it worked fine using

MELTANO_DATABASE_URI=<postgresql://user:pass@my.host:5432/meltano?options=-csearch_path%3Dmeltano>

to use a

meltano

db and

meltano

schema

Thanks @Matt Menzenski! I'll try to reproduce this with 13.4.