Working with Postgres Extensions in Azure Cosmos DB for PostgreSQL while Scaling Nodes

Square

Yesterday, I did a presentation for the Azure Cosmos DB Global User Group on Azure Cosmos DB for PostgreSQL. I mentioned in the PostGIS part that I ran into an issue, so I wanted to describe it here.

Problem: I installed PostGIS on my single-node cluster without issues. However, I scaled my cluster to 2 nodes afterwards. When I ran the query that uses ST_X and ST_Y from PostGIS, I got the following error:

ERROR:  type "public.geometry" does not exist
CONTEXT:  while executing command on private-w0.azure-cosmos-db-global-ug-demo.postgres.database.azure.com:5432

When I read the CONTEXT message, I realized by the w# reference that the worker nodes didn’t have PostGIS installed. When you scale the nodes – at least in this case, it doesn’t enable the extensions over there.

My Solution

I looked at the PostgreSQL extensions in Azure Cosmos DB for PostgreSQL page to see if there was guidance there, but I saw nothing there. I did make note, though, that create_extension() works for the PostgreSQL command CREATE EXTENSION.

So then I looked at the PostgreSQL documentation to see if there was a DROP equivalent – because SQL shows patterns of DROP statements that match CREATE statements. I came across DROP EXTENSION. So this works on a single node… will this also work with the distributed cluster?

So I guessed that drop_extension() might also exist in Azure Cosmos DB for PostgreSQL. It does! And it worked! I dropped the extension and then recreated it, which created it on all of my nodes – 1 coordinator node and 2 worker nodes. This is what I had to run:

SELECT drop_extension('postgis');
SELECT create_extension('postgis');
Comment

2 Replies to “Working with Postgres Extensions in Azure Cosmos DB for PostgreSQL while Scaling Nodes”

  1. I’m having a similar issue with pg extensions but not related to multiple nodes. I need privileges to a pg extension with the default “citus” user but this user doesn’t have access to any pg extensions. only the default admin “postgres” user does. However, Azure doesn’t let you log in as the “postgres” user to grant privileges to the “citus” user. How are you able to access the extensions in the first place if you can only access them with a user you can’t log in as.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.