https://prefect.io logo
Title
j

Julio Venegas

05/23/2021, 7:30 PM
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
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

Chris White

05/23/2021, 7:43 PM
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

Julio Venegas

05/23/2021, 7:47 PM
Hi Chris, do I need to use a specific env variable for the migrations? How can I keep the
@
for the migrations intact?
c

Chris White

05/23/2021, 7:49 PM
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

Julio Venegas

05/23/2021, 8:03 PM
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

Chris White

05/23/2021, 8:07 PM
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

Julio Venegas

05/23/2021, 8:08 PM
ok! Thank you, will give this a try now!
👍 1
sorry, having issues with the npm install
c

Chris White

05/23/2021, 8:25 PM
you shouldn’t need npm for this command
j

Julio Venegas

05/23/2021, 8:27 PM
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 😕
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

Chris White

05/23/2021, 8:41 PM
Yup that’s expected - the services won’t work until the migrations are run
j

Julio Venegas

05/23/2021, 8:42 PM
(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

Chris White

05/23/2021, 8:44 PM
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

Julio Venegas

05/23/2021, 8:47 PM
yep, re running
prefect server start
did it ❤️
c

Chris White

05/23/2021, 8:47 PM
😒uccess-kid: great!
j

Julio Venegas

05/23/2021, 8:48 PM
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

Chris White

05/23/2021, 8:49 PM
haha yea
j

Julio Venegas

05/23/2021, 8:54 PM
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