https://prefect.io logo
Join Slack
Powered by
Hi everyone, trying to get an external postgres in...
# prefect-server
j
Hi everyone, trying to get an external postgres instance running for prefect 0.14.19. Postgres instance is running on Azure, it’s postgresql 11. Long story short, all Azure postgres usernames are in the format 
username@server-name
. In order to get hasura running I have to escape the 
@
in the username with 
%40
but that leads to the following graphql error
Copy code
graphql_1  | Could not upgrade the database!
graphql_1  | Error: invalid interpolation syntax in '<postgres://prefectpgadmin>%40sql-dev-prefect:REDACTED_PSWD@sql-dev-prefect.postgres.database.azure.com:5432/sqldb-dev-prefect' at position 25
Any suggestions about what I can to avoid the graphql interpolation issue?
relevant github thread, similar issue that mlflow users have had in Azure https://github.com/mlflow/mlflow/issues/1487
c
Following the guidance here: https://github.com/PrefectHQ/server/pull/139 I think you need to keep the 
@
for running the migrations, but escape it for hasura
j
Hi Chris, do I need to use a specific env variable for the migrations? How can I keep the 
@
for the migrations intact?
c
I believe you need to set 
PREFECT_SERVER__DATABASE__CONNECTION_URL
with the 
@
in the process that you intend to run the upgrade CLI command from, and all your other services should run with the escaped env var set
j
Maybe I missed sharing more details. I first did the export 
PREFECT_SERVER__DATABASE__CONNECTION_URL
 with the 
@
in the username, followed by 
prefect server start --external-postgres
. In this case, Hasura parsed the connection string wrong, throwing an error. Then I exported 
PREFECT_SERVER__DATABASE__CONNECTION_URL
changing the @ for 
%40
In this case graphql raised the error I originally mentioned. I have not ran any upgrade commands in the CLI, I’ve only ran 
prefect server start --external-postgres
c
Ah! OK so here’s what you can try: • using the escaped username / connection with 
%40
, run 
prefect server start --external-postgres --no-upgrade
• install Prefect Server and then set the environment variable with the 
@
sign; now you can run 
prefect-server database upgrade
to upgrade the DB separately
j
ok! Thank you, will give this a try now!
👍 1
sorry, having issues with the npm install
c
you shouldn’t need npm for this command
j
ok, was just following the install steps from https://github.com/PrefectHQ/server (no. 3), I’ll skip it
👍 1
Ok, towel is not working 😕
Copy code
towel_1    | {"severity": "ERROR", "name": "prefect-server.ZombieKiller", "message": "Unexpected error: ValueError([{'extensions': {'path': '$.selectionSet.task_run', 'code': 'validation-failed'}, 'message': 'field \"task_run\" not found in type: \\'query_root\\''}])"}
c
Yup that’s expected - the services won’t work until the migrations are run
j
Copy code
(py38) adminuser@vmdataorchestration:~$ prefect-server database upgrade
Are you sure you want to upgrade the database? [y/N]: y

Running Alembic migrations...
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
Applied Hasura metadata from /home/adminuser/Code/server/services/hasura/migrations/metadata.yaml

Database upgraded!
that’s the command I ran in a separate shell
c
and can you confirm that the database looks up to date? You see all the prefect tables? It’s possible that you need to restart your services by cancelling and rerunning the 
prefect-server start
command
🙌 1
j
yep, re running 
prefect server start
did it ❤️
c
success kid great!
j
just having some checks now, but the UI is up! so I think it’s just the regular setup steps now!! Thanks so much!
👍 1
🎉 1
Can’t understand why azure had to have 
@
in user names, their support team must hate that decision
c
haha yea
j
all good!! connections showing in the Azure monitor, ran a couple of flows and the logs are displaying without issue 😄
💯 1
Thanks a lot Chris, always amazing support!
Hi Chris, having some issues today. I had to relaunch a new postgres database, following the same steps you recommended, today when I run 
prefect-server database upgrade
I get the following error even though I’ve changed the config.toml with the export and I can see the external host in the config. 
Error: (psycopg2.OperationalError) could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
I guess it’s just a matter of changing the right config but any help is welcome
ok, I had retyped 
export PREFECT__SERVER
instead of 
export PREFECT_SERVER
and I see that in the server repo, there’s a config.toml that is accessed under 
perfect_server
i.e single underscore not double.
all clear now
2 Views
Open in Slack
PreviousNext
Powered by