https://prefect.io logo
Powered by
Title
j

Julio Venegas

09/30/2021, 5:53 PM
Hi everyone! I upgraded to 15.6 and I’m struggling to connect to my external postgres instance. The postgres instance is an Azure managed one, the admin postgres username is automatically generated by azure in the form of INPUT_NAME@AZURE_INSTANCE_NAME which makes it impossible to use the command 
prefect server start --postgres-url postgres://<username>:<password>@hostname:<port>/<dbname>
because the @ in the username makes the parsing process think that AZURE_INSTANCE_NAME is the host. Escaping the @ in the username has not worked. In the past the quick fix someone from the Prefect team recommended to me that actually solved the issue, was to install https://github.com/PrefectHQ/server, pass the postgres connection string to the prefect-server config with export PREFECT_SERVER__DATABASE__CONNECTION_URL=CONNECTION_STRING and then run 
prefect-server database upgrade
. I’m currently doing 1) 
prefect server start --external-postgres --no-upgrade
to start the Docker containers, followed by 
prefect-server database upgrade
and the current error I’m getting from the last command is 
Error: HTTPConnectionPool(host='localhost', port=3000): Max retries exceeded with url: /v1/query (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7f6aaf865730>: Failed to establish a new connection: [Errno 111] Connection refused'))
which based on the port=3000 is related to Hasura. Any advice highly appreciated!!
k

Kevin Kho

09/30/2021, 5:56 PM
Hey, what version did you upgrade from?
j

Julio Venegas

09/30/2021, 5:56 PM
14.01 I think
And hey Kevin 🙂
k

Kevin Kho

09/30/2021, 5:58 PM
Do you think it’s more related to this ? You need to use the 
--expose
flag to open Prefect to outside connections.
This was released in 0.15.5 and is in the changelog here
j

Julio Venegas

09/30/2021, 5:59 PM
I saw one thread related to this, I tried 
prefect server start --expose --external-postgres --np-upgrade
followed by the prefect-server command and also got an error
k

Kevin Kho

09/30/2021, 6:00 PM
what was your error?
j

Julio Venegas

09/30/2021, 6:00 PM
let me see if it was the same one though
Error: HTTPConnectionPool(host='localhost', port=3000): Max retries exceeded with url: /v1/query (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7f5ae52d8730>: Failed to establish a new connection: [Errno 111] Connection refused'))
Just retried the commands 1) 
prefect server start --expose --external-postgres --no-upgrade
and 2) 
prefect-server database upgrade
k

Kevin Kho

09/30/2021, 6:04 PM
Ok will ask the team
j

Julio Venegas

09/30/2021, 6:04 PM
Thanks a bunch Kevin, btw it’s the latest server version from master i.e. current from https://github.com/PrefectHQ/server
k

Kevin Kho

09/30/2021, 6:11 PM
Did your Hasura container start fine?
j

Julio Venegas

09/30/2021, 6:19 PM
Just checked with 
docker ps
and the tmp_hasura_1 container says “Restarting” so I think not!
k

Kevin Kho

09/30/2021, 6:20 PM
Oh that might be the issue? Check the logs of that?
j

Julio Venegas

09/30/2021, 6:20 PM
will do! and yep, the container is not showing any ports haha
{"type":"pg-client","timestamp":"2021-09-30T18:01:30.790+0000","level":"warn","detail":{"message":"postgres connection failed, retrying(0)."}}
{"type":"pg-client","timestamp":"2021-09-30T18:01:30.790+0000","level":"warn","detail":{"message":"postgres connection failed, retrying(1)."}}
{"type":"startup","timestamp":"2021-09-30T18:01:30.790+0000","level":"error","detail":{"kind":"catalog_migrate","info":{"internal":"could not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (127.0.0.1) and accepting\n\tTCP/IP connections on port 5432?\n","path":"$","error":"connection error","code":"postgres-error"}}}
{"internal":"could not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (127.0.0.1) and accepting\n\tTCP/IP connections on port 5432?\n","path":"$","error":"connection error","code":"postgres-error"}
mmmh, maybe I need to first do the command 
prefect-server database upgrade
?
nope, makes no sense since it needs to interface somehow with Hasura
z

Zanie

09/30/2021, 6:36 PM
I think the 
--expose
flag may be necessary to allow connections to external services right now
https://github.com/PrefectHQ/prefect/issues/4963
Ah sorry I did not see you'd tried this --
j

Julio Venegas

09/30/2021, 6:39 PM
No problem, thanks for checking on this!
z

Zanie

09/30/2021, 6:39 PM
This connection failure is a bit perplexing 😕 is the connection url wrong? localhost:5432 doesn't seem to match your top-level comment
j

Julio Venegas

09/30/2021, 6:43 PM
Yep, that’s confusing to me too, its does not match what I pass with 
export PREFECT_SERVER__DATABASE__CONNECTION_URL=CONNECTION_STRING
z

Zanie

09/30/2021, 6:44 PM
I think you are missing a 
_
PREFECT__SERVER
j

Julio Venegas

09/30/2021, 6:44 PM
damniiiit
z

Zanie

09/30/2021, 6:45 PM
It's a bit tricky, setting things for prefect server via the prefect cli requires 
PREFECT__SERVER
-- 
PREFECT_SERVER
targets the prefect server config directly
j

Julio Venegas

09/30/2021, 6:45 PM
But as far as I understand, the export command is to be used by prefect-server not prefect
z

Zanie

09/30/2021, 6:45 PM
Unless it's somehow getting injected into the docker-compose, it's not going to be used though
j

Julio Venegas

09/30/2021, 6:46 PM
Ok, will try with the double underscore!
I think it does have to be 
PREFECT_SERVER__DATABASE
otherwiser prefect-server does not get the right connection and tries to connect with local postgres (which does not exist)
z

Zanie

09/30/2021, 6:56 PM
If you run 
prefect server config --expose --external-postgres
then just hardcode your connection url does it work?
There are a few too many points where this could go wrong so far
j

Julio Venegas

09/30/2021, 7:05 PM
Sorry, it’s a bit complex to explain the learnings so far haha
prefect server config --expose --external-postgres
shows the right configuration, is this config prefect or prefect-server?
z

Zanie

09/30/2021, 7:10 PM
That outputs the 
docker-compose.yml
that we'd run when you do 
prefect server start
So you should be able to 
> docker-compose.yml
that to a file and then 
docker-compose up
This way you can modify the env in that file without dealing with the CLI
j

Julio Venegas

09/30/2021, 7:14 PM
I checked the docker logs for hasura, and we are back to the original problem of it parsing the connection string incorrectly. Azure creates the postgres user name as INPUT_NAME@AZURE_INSTANCE_NAME and when I check the docker logs for hasura I see, that it is using AZURE_INSTANCE_NAME as host
will run everything again from start and check back, thanks again for all the help!
having an “@” character in the username is causing plenty of issues haha
z

Zanie

09/30/2021, 7:15 PM
So if you modify the env for the hasura container to have 
PREFECT_SERVER__DATABASE__CONNECTION_URL
you should be able to override any parsing (I think?)
We had to do this for the helm chart https://github.com/PrefectHQ/server/pull/139/files
j

Julio Venegas

09/30/2021, 7:21 PM
Yes, that fix used to work, but currently when doing 
prefect-server database upgrade
after exporting the connection string to PREFECT_SERVER__DATABASE__CONNECTION_URL is throwing this error 
Error: HTTPConnectionPool(host='localhost', port=3000): Max retries exceeded with url: /v1/query (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7f6aaf865730>: Failed to establish a new connection: [Errno 111] Connection refused'))
because Hasura is not running correctly
1. 
prefect server start --external-postgres --no-upgrade
Hasura container fails to start because it’s looking for local postgres 2. 
export PREFECT_SERVER__DATABASE__CONNECTION_URL=CONNECTION_STRING
3. 
prefect-server database upgrade
fails to connect to Hasura
z

Zanie

09/30/2021, 7:23 PM
I see
1. should have --postgres-url with the connection string with a 
%40
instead of 
@
j

Julio Venegas

09/30/2021, 7:24 PM
🙏 will try that right now!
BOOOOM!!!! 🎆
Back in business!! @Zanie and @Kevin Kho thanks so much
z

Zanie

09/30/2021, 7:28 PM
Wonderful haha
j

Julio Venegas

09/30/2021, 7:28 PM
I cannot complaint more to Azure about how much of a bad idea their user name conventions are
Thanks a bunch 🙂
2 Views
#prefect-serverJoin Slack
Powered by