Hi, I have an issue with the database, I configured the external postgres database but, when I run the jobs, I see that the database is empty. No tables. When I check the meltano configuration I see the postgres uri for the store.?

Can you please show me how you've configured the database URI? Is it showing up when you run

meltano config meltano list

?

Sorry didn’t mean to hijack the thread, @douwe_maan, so I did an export

meltano_database_uri

but it doesn’t seem to get updated when I invoke meltano command. I guess I’d have to add it manually doing config

@keat Hmm,

export MELTANO_DATABASE_URI=foo

is definitely supposed to work and it does for me locally! Can you share some more details of your setup where you're not seeing this working as expected?

Do I have to run at least one

meltano elt

because I ran a

meltano invoke

hoping that it will migrate. So I did an

export MELTANO_DATABASE_URI=foo

then ran a

meltano invoke tap

and hoped that it will do a db migrate. But when I checked

meltano config meltano list

, it’s still listed as sqllite. Then again its not a big issue, I could just

meltano config set

it. was also referring to this. https://meltano.slack.com/archives/CFG3C3C66/p1590695845231600?thread_ts=1590694764.222300&cid=CFG3C3C66

meltano config meltano list

itself will also perform the system database migration, so when I run

export MELTANO_DATABASE_URI=<postgres://foo>

(with an obviously invalid URL),

meltano config meltano list

errors out, indicating that the env var was picked up correctly:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "foo" to address: nodename nor servname provided, or not known

ok, I am deleting this for now

I'm not quite sure yet 😕 Database migrations to create tables are run as part of

meltano config

, so you should definitely see a few tables even if they'll be empty...

ok, thanks.

When I set

MELTANO_DATABASE_URI

to an empty database,

meltano config meltano list

immediately runs migrations as seen in the logs:

$ export MELTANO_DATABASE_URI=<postgresql://meltano:meltano@localhost:5432/system-database>
$ meltano config meltano list
[2020-07-20 09:53:09,922] [73560|MainThread|alembic.runtime.migration] [INFO] Context impl PostgresqlImpl.
[2020-07-20 09:53:09,922] [73560|MainThread|alembic.runtime.migration] [INFO] Will assume transactional DDL.
[2020-07-20 09:53:09,930] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade  -> b4c05e463b53, Create meltano.core base tables
[2020-07-20 09:53:09,959] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade b4c05e463b53 -> 6ef30ab7b8e5, Create meltano.api tables
[2020-07-20 09:53:09,982] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade 6ef30ab7b8e5 -> 53e97221d99f, Add run_id to Job
[2020-07-20 09:53:10,014] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade 53e97221d99f -> a3e2b0a4937d, add_login_audit_columns
[2020-07-20 09:53:10,016] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade a3e2b0a4937d -> 990c0665f3ce, ensure user.login_count default value
[2020-07-20 09:53:10,017] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade 990c0665f3ce -> ceb00d7ff3bd, Create the EmbedToken table
[2020-07-20 09:53:10,021] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade ceb00d7ff3bd -> 23ea52e6d784, add resource type to embed token
[2020-07-20 09:53:10,030] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade 23ea52e6d784 -> 87d9638f6ac6, Add Subscription
[2020-07-20 09:53:10,035] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade 87d9638f6ac6 -> 367228df6a43, Add 'trigger' to Job
[2020-07-20 09:53:10,036] [73560|MainThread|alembic.runtime.migration] [INFO] Running upgrade 367228df6a43 -> e4fbabc3fed6, add last activity at column
<snip>
database_uri [env: MELTANO_DATABASE_URI] (default: 'sqlite:///$MELTANO_PROJECT_ROOT/.meltano/meltano.db') current value: '<postgresql://meltano:meltano@localhost:5432/system-database>' (from the environment)

yes

@meir.shamay I assume you're on the latest version of Meltano (not that that should make a difference since I'm not aware of a bug in this area, but just to be sure)

yes, latest