Managed Postgres databases can be an excellent choice if you are self-hosting Prefect Orion in the cloud. On Azure, Postgres Flexible Server lets you start small and scale up as your needs grow.
There’s one catch: Orion needs the pg_tgrm extension enabled, but Azure does not enable it by default on Postgres Flexible Server, and the command to enable it does not work.
Fortunately, there’s an easy way to enable the extension and use the database with Orion!
If you haven’t created a Postgres Flexible Server on Azure yet, you can find it using the Azure portal’s search bar:
Once you’ve created your server, load its dashboard and make sure everything looks correct:
Then, in the menu on the left, find and click Server Parameters:
And use the search box to find azure.extensions:
Next, click the VALUE dropdown and select PG_TRGM:
Finally, click Save. Your database is now capable of using the pg_trgm extension! First, though, you’ll need to sign into the database and enable the extension.
Use pgAdmin or another database tool of your choice to sign into your Postgres instance using the credentials you created when setting up the database. You can find your database’s address on its dashboard page in the Azure portal.
Once you are connected, run the following:
CREATE EXTENSION pg_trgm;
And with that, you’re ready to use your Azure Postgres Flexible Server with Prefect Orion!
You can set up Orion to use your database by running:
prefect config set PREFECT_ORION_DATABASE_CONNECTION_URL="postgresql+asyncpg://<your-db-username>:<your-db-pasword>@<your-postgres-flexible-server-address>:5432/postgres"
If you want to use a schema other than the default of postgres, update /postgres to /<your schema name>.
Happy engineering!