Cannot drop an azure postgres database with logical subscription

Jordi Urios 0

Hi there,

I've been playing with logical replication with a postgres database in Azure. I created two database (main and replica) on the same postgres service and then I created a publication on the main database and a subscription on the replica. Unfortunately, I wasn't able to make the replication works (that's another problem ^^). Then, I removed, using the Azure panel, both databases. The main database has been dropped successfully. However, the replica failed. I tried to drop the database using a SQL client (dbeaver) afterwards and I see the problem is that there is an active subscription.

The problem is that I cannot drop that subscription. With drop subscription sub1 I get the error that sub1 does not exist (but is listed when I run select * from pg_subscription;). After a bit of google research, I saw that I have to connect to the replica database in order to remove the subscription but... I cannot connect to the replica database (the error is is not currently accepting connections). Looks like the replica database is in an unstable state as I see it in some places (like Azure panel or when I select * frompg_stat_database`) but in other places is hidden (like en Dbeaver navigation list).

I'm not sure how to fix this problem. Can someone help me?

Thanks.

Sai Raghunadh M 1,295 Reputation points Microsoft Vendor

2024-12-04T18:48:48.1366667+00:00
Hi @Jordi Urios

Thanks for the question and using MS Q&A platform.

It sounds like the issue you're facing is due to the active replication subscription that was left behind after you dropped the main database. Here are some steps you can follow to try and resolve the issue:

Sometimes, a simple restart can help resolve connection issues. You can do this in the Azure portal by navigating to your PostgreSQL server and selecting the option to restart the database.

If the database is in a state that allows connections, try connecting using a different SQL client or command-line tool. If successful, you can run the DROP SUBSCRIPTION command to remove the subscription.

If you can connect but still face issues, you can attempt to forcefully drop the subscription with the following command:

DROP SUBSCRIPTION sub1 WITH (FORCE);

If the above steps do not resolve the issue, I recommend reaching out to Azure support. They can provide specialized assistance and may be able to help you remove the subscription or address any underlying database issues.

I hope this information helps, please do let us know if you have any Queries.

Share via

Cannot drop an azure postgres database with logical subscription

Your answer