https://linen.dev logo
Join Slack
Powered by
Hello, I'm experiencing issues with setting up Po...
# troubleshooting
a
Hello, I'm experiencing issues with setting up Postgres as state backend. What I did: 1. I have Meltano project (3.5.1) that works with Default and Local File System state backends. 2. Created virtual environment in project's folder. 3. Activated virtual environment. 4. Installed extras: 
pip install meltano[postgres]==3.5.1
5. Set corresponding env variable in 
.env
file: 
MELTANO_STATE_BACKEND_URI='postgresql+psycopg://<username>:<password>@<host>:<port>/<database>'
6. Executed 
meltano install
Having the following output after running "run" command 
meltano run tap-mockrestapi--node1 target-jsonl
:
Copy code
(venv) vscode ➜ /workspaces/Meltano $ meltano run tap-mockrestapi--node1 target-jsonl
2024-09-06T05:32:21.225314Z [info     ] Environment 'dev' is active   
2024-09-06T05:32:22.253005Z [info     ] Installing extractor 'tap-mockrestapi--node1'
2024-09-06T05:32:49.881601Z [info     ] Installed extractor 'tap-mockrestapi--node1'
2024-09-06T05:32:49.882191Z [info     ] Installed 1/2 plugins         
2024-09-06T05:32:49.882405Z [info     ] Skipped installing 1/2 plugins
2024-09-06T05:32:50.106098Z [error    ] Cannot start plugin tap-mockrestapi--node1: 'postgresql+psycopg' is not a valid StateBackend
2024-09-06T05:32:50.111278Z [error    ] Block run completed.           block_type=ExtractLoadBlocks err=RunnerError("Cannot start plugin tap-mockrestapi--node1: 'postgresql+psycopg' is not a valid StateBackend") exit_codes={} set_number=0 success=False
Has anyone seen similar issue?
If I run command to list states:
Copy code
meltano state --database-uri postgresql+psycopg://<username>:<password>@<host>:<port>/<database> list
I see message regarding import failure for "psycopg_c"
Copy code
2024-09-06T06:56:31.931720Z [debug    ] meltano 3.5.1, Linux          
2024-09-06T06:56:31.934940Z [debug    ] /etc/timezone found, contents:
 Etc/UTC

2024-09-06T06:56:31.935512Z [debug    ] /etc/localtime found          
2024-09-06T06:56:31.944184Z [debug    ] 2 found:
 {'/etc/timezone': 'Etc/UTC', '/etc/localtime is a symlink to': 'Etc/UTC'}
2024-09-06T06:56:32.005874Z [info     ] Environment 'dev' is active   
2024-09-06T06:56:32.115164Z [debug    ] Creating DB engine for project at '/workspaces/Meltano' with DB URI '<postgresql+psycopg://postgres>:********@host.docker.internal/meltano'
2024-09-06T06:56:32.195054Z [debug    ] couldn't import psycopg 'c' implementation: No module named 'psycopg_c'
2024-09-06T06:56:33.879346Z [debug    ] Need help fixing this problem? Visit <http://melta.no/> for troubleshooting steps, or to
join our friendly Slack community.

'postgresql+psycopg' is not a valid StateBackend
But installing psycopg[c] didn't help. The only difference that this line disappeared from the logs but overall result is the same.
a
I think that you can't set postgres as a separate state store independently of your systemdb. If you want to use postgres as a state store then it also has to be your systemdb too. State Backend options: https://github.com/meltano/meltano/blob/fb52ef60fe96d7aca0c8b160dcdbb13398342fea/src/meltano/core/state_store/__init__.py#L27 https://docs.meltano.com/guide/v3-migration#using-postgres-as-a-backend-now-requires-installing-meltano-with-extra-components The postgres message you're seeing is a fairly standard debug message, doesn't relate to your exact issue. https://github.com/psycopg/psycopg/issues/807
a
Interesting, with "database_uri" set in yaml file it works... Moreover there are two environment variables: MELTANO_STATE_BACKEND_URI MELTANO_DATABASE_URI With 
MELTANO_STATE_BACKEND_URI
I'm facing this issue, but when I switched to 
MELTANO_DATABASE_URI
everything works fine. It is weird.
@Andy Carter yes, at some point I realized that it is generic message and I enabled debug logs and switched over meltano state command for simplicity. But anyway, even when I installed all dependencies manually 
pip install "psycopg[binary,pool,c]"
it didn't help, 
MELTANO_STATE_BACKEND_URI
still doesn't work, but 
MELTANO_DATABASE_URI
works as expected.
And one more interesting observation. With 
MELTANO_DATABASE_URI
even if you don't install 
psycopg[c]
dependency it works but in logs you still can see message:
Copy code
couldn't import psycopg 'c' implementation: No module named 'psycopg_c'
a
I think that log message isn't really an error, just something surfacing from psycopg that looks bad but is part of normal operation. @Edgar Ramírez (Arch.dev) can confirm but I think you cannot use postgres as just a state backend. But you can use it as a systemdb, and if you set a systemdb without explicitly setting a state backend, then your systemdb is used as your state backend too. Most folks in production want to use a transactional db like mysql or postgres for the run information, then some use a s3-like system just for state store.
c
can confirm the psycopg warning is just that, a warning - I went down the path of trying to diagnose it thinking it was the root of another problem and it was not
👍 2
a
Yes there is some 'robust' discussion here 🤣 https://github.com/psycopg/psycopg/issues/807
c
haha oh yes indeed
e
Hi @Alexander Shabunevich! 👋 @Andy Carter is correct, you need to set 
MELTANO_STATE_BACKEND_URI=systemdb
or just unset it completely since that's the default value anyway, and set 
MELTANO_DATABASE_URI=postgresql+psycopg//...
. We can probably make the documentation clearer as to how to use Postgres or any supported database as the state backend, and also add details to that error message, like which backends are available.
👍 2
33 Views
Open in Slack
PreviousNext
Powered by