Thread
#prefect-server
    j

    Julio Venegas

    11 months ago
    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!!
    Kevin Kho

    Kevin Kho

    11 months ago
    Hey, what version did you upgrade from?
    j

    Julio Venegas

    11 months ago
    14.01 I think
    And hey Kevin 🙂
    Kevin Kho

    Kevin Kho

    11 months ago
    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

    11 months ago
    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
    Kevin Kho

    Kevin Kho

    11 months ago
    what was your error?
    j

    Julio Venegas

    11 months ago
    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
    Kevin Kho

    Kevin Kho

    11 months ago
    Ok will ask the team
    j

    Julio Venegas

    11 months ago
    Thanks a bunch Kevin, btw it’s the latest server version from master i.e. current from https://github.com/PrefectHQ/server
    Kevin Kho

    Kevin Kho

    11 months ago
    Did your Hasura container start fine?
    j

    Julio Venegas

    11 months ago
    Just checked with
    docker ps
    and the tmp_hasura_1 container says “Restarting” so I think not!
    Kevin Kho

    Kevin Kho

    11 months ago
    Oh that might be the issue? Check the logs of that?
    j

    Julio Venegas

    11 months ago
    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
    Michael Adkins

    Michael Adkins

    11 months ago
    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

    Julio Venegas

    11 months ago
    No problem, thanks for checking on this!
    Michael Adkins

    Michael Adkins

    11 months ago
    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

    11 months ago
    Yep, that’s confusing to me too, its does not match what I pass with
    export PREFECT_SERVER__DATABASE__CONNECTION_URL=CONNECTION_STRING
    Michael Adkins

    Michael Adkins

    11 months ago
    I think you are missing a
    _
    PREFECT__SERVER
    j

    Julio Venegas

    11 months ago
    damniiiit
    Michael Adkins

    Michael Adkins

    11 months ago
    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

    11 months ago
    But as far as I understand, the export command is to be used by prefect-server not prefect
    Michael Adkins

    Michael Adkins

    11 months ago
    Unless it's somehow getting injected into the docker-compose, it's not going to be used though
    j

    Julio Venegas

    11 months ago
    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)
    Michael Adkins

    Michael Adkins

    11 months ago
    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

    11 months ago
    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?
    Michael Adkins

    Michael Adkins

    11 months ago
    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

    11 months ago
    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
    Michael Adkins

    Michael Adkins

    11 months ago
    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

    11 months ago
    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
    Michael Adkins

    Michael Adkins

    11 months ago
    I see
    1. should have --postgres-url with the connection string with a
    %40
    instead of
    @
    j

    Julio Venegas

    11 months ago
    🙏 will try that right now!
    BOOOOM!!!! 🎆
    Back in business!! @Michael Adkins and @Kevin Kho thanks so much
    Michael Adkins

    Michael Adkins

    11 months ago
    Wonderful haha
    j

    Julio Venegas

    11 months ago
    I cannot complaint more to Azure about how much of a bad idea their user name conventions are
    Thanks a bunch 🙂