https://prefect.io
Join Slack
Hi everyone! I upgraded to 15.6 and I’m struggling to connect to my external postgres instance. The ...
# prefect-server
j
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
Hey, what version did you upgrade from?
j
14.01 I think
And hey Kevin 🙂
k
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
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
what was your error?
j
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
Ok will ask the team
j
Thanks a bunch Kevin, btw it’s the latest server version from master i.e. current from https://github.com/PrefectHQ/server
k
Did your Hasura container start fine?
j
Just checked with 
docker ps
and the tmp_hasura_1 container says “Restarting” so I think not!
k
Oh that might be the issue? Check the logs of that?
j
will do! and yep, the container is not showing any ports haha
Copy code
{"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
I think the 
--expose
flag may be necessary to allow connections to external services right now
Ah sorry I did not see you'd tried this --
j
No problem, thanks for checking on this!
z
This connection failure is a bit perplexing 😕 is the connection url wrong? localhost:5432 doesn't seem to match your top-level comment
j
Yep, that’s confusing to me too, its does not match what I pass with 
export PREFECT_SERVER__DATABASE__CONNECTION_URL=CONNECTION_STRING
z
I think you are missing a 
_
PREFECT__SERVER
j
damniiiit
z
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
But as far as I understand, the export command is to be used by prefect-server not prefect
z
Unless it's somehow getting injected into the docker-compose, it's not going to be used though
j
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
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
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
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
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
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
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
I see
1. should have --postgres-url with the connection string with a 
%40
instead of 
@
j
🙏 will try that right now!
BOOOOM!!!! 🎆
Back in business!! @Zanie and @Kevin Kho thanks so much
z
Wonderful haha
j
I cannot complaint more to Azure about how much of a bad idea their user name conventions are
Thanks a bunch 🙂
4 Views
Open in Slack
PreviousNext
Powered by

Prefect Community

Bring your towel and join one of the fastest growing data communities. Welcome to our second-generation open source orchestration platform, a completely rethought approach to dataflow automation.

Powered by