I received an offer recently, to migrate my Azure Postgres database to a Flexible Server that would use less resources and cost me less while delivering the same functionality to my application users. Sounds like a GREAT offer, and I proceeded with the migration. Over the last week, I received a bug report and started scratching my head. What could be causing this error?

Investigation

In my case, the application in question is Fider and it manages the feedback site for KlipTok.  It runs out of a container on Azure App Service.  I popped open the logs and found this entry when users would key in new feedback reports:

Logs are reporting that the function ‘similarity’ does not exist

The logs are indicating that Postgres is looking for a function called ‘similarity’.

That’s strange.. I don’t remember configuring anything for that function in Postgres or Fider.  I did some digging, and according to the Postgres documentation, this is a function that is added through the pg_term Postgres extension.  Ok, so let’s check the Azure documentation to see if it is enabled.

According to the docs, the extension is available, but it needs to be added to the server.  I browsed to the server on the Azure portal and set the Server Parameter “azure.extensions” to include the PG_TRGM option:

Azure Portal showing how to configure the PG_TRGM extension

That’s it!  Right? After saving that configuration, the server restarted and that’s all that’s needed… right?

Nope.

The missing bits in the documentation are that you need to activate the extension for your database.  I connected to the database using the handy ‘Connect’ button in the Azure portal.

Connect to Postgres database using the Azure Portal

Once connected, I executed the following ‘Create Extension’ command to activate the extension:

CREATE EXTENSION pg_trgm;

Done!  No restart needed.  The database is updated and my application now runs properly.  The Azure documentation does show that you need to execute the ‘Create Extension’ statement, but its outside of the numeric list of steps to add extensions to the server. I’ve submitted some feedback about that, and called it a day.