Category: Azure Database for Postgres Flexible Server

Azure Postgres – Extensions

I was looking around for a list of extensions that can be used with Postgres – didn’t really find a consistent good list.

Just out of curiosity I thought I would look up Azure Postgres documentation which details which ones are available on their platform. Seems quite complete. Importantly it has the foreign data wrapper for Oracle.

Microsoft Azure Documentation – Postgres Extensions

Now I’m not saying you should go out and load too many. I would always be looking to load a minimum number of extensions as I feel you always want to keep complexity to a minimum and extensions are more likely to be prone to dropped support which could be a real long term serious issue.

Azure – Postgres – Restore to Azure Postgres Flexible Server

Have not tested this extensively but I have managed to take a pg_dump file from a version 16 Postgres Local Server and restore it to a Postgres version 16.3 Flexible Server on the Azure platform. I have also briefly tested taking a dump file from azure.

Requirements

  • The computer from which you will restore the database has been IP white listed.
  • Check that you have a higher version of restore than the version of Azure Postgres Database / Overview see version no.
  • Remember to create the database into which you will be placing the dump file
  • host will be endpoint and will be something like myserver.postgres.database.azure.com
  • username will be admin login defined at creation (probably not postgres)
  • Port default is usually 5432
  • These details can be found by going to Settings / Connect –

    • /

    Interestingly the connect screen contains a code generator similar to the one that I created that will assist users in creating their pg_dump and pg_restor commands.



    Add POSTGIS extension to Azure Postgres Database – Worked Example

    Firstly we need to allow for the extension on the server.
    Within the Azure portal navigate to server parameters

    Search for azure.extensions

    Select which extensions to add
    REMEMBER to save before exit!

    Wait a short period while the extension is loaded onto the server..

    Some points
    Azure Database for PostgreSQL supports a subset of key PostgreSQL extensions as listed HERE. This information is also available by running SHOW azure.extensions;. Extensions not listed in this document aren’t supported on Azure Database for PostgreSQL – Flexible Server. You can’t create or load your own extension in Azure Database for PostgreSQL.

    After extensions are allow-listed and loaded, these must be installed in your database before you can use them. To install a particular extension, you should run the CREATE EXTENSION command. This command loads the packaged objects into your database.

    The extension(s) should now be listed against your database.