Can't drop postgresql flexible server database with logical replication

Question

Help please resolve the issue with broken database. The problem:

1. Have not responding database on server
2. In pg_catalog.pg_subscription old subscription record to this database

And now I can't delete subscription from pg_subscription table(permission denied) and can't drop database (There is 1 subscription.database "" is being used by logical replication subscription). How it could be resolved? Thanks in advance

Answer 1

Hi Yury •,

Welcome to Microsoft Q&A forum.

As I understand, you Can't drop postgresql flexible server database with logical replication.

Could you please let us know how are you trying to perform the operation? Via Azure Portal, Azure CLI etc

Does az postgres db show shows the database details in Azure CLI?

Refer to the similar issue stackexchange post here:

https://dba.stackexchange.com/questions/120411/unable-to-force-drop-postgres-database

Let us know if this helps. If not, please share more details with screenshot so that we can assist you.

Thanks

Answer 2

Hi Yury •,

Thanks for providing the details.

Please try if below works in your case:

DB="my_database_name" psql <<EOF
-- Making sure the database exists
SELECT * from pg_database where datname = '${DB}';

-- Disallow new connections
UPDATE pg_database SET datallowconn = false WHERE datname = '${DB}';
ALTER DATABASE ${DB} CONNECTION LIMIT 1;

-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '${DB}';

-- Drop database
DROP DATABASE ${DB};
EOF

Or by drop with Force:

DROP DATABASE database_to_drop WITH (FORCE) or dropdb [-f/--force] database_to_drop

Refer to the below links for similar issues if useful:

https://stackoverflow.com/questions/50752894/postgresql-logical-replication-unable-to-drop-subscription

https://dba.stackexchange.com/questions/207653/cannot-drop-database-with-logical-replication

https://stackoverflow.com/questions/31147581/psql-fatal-database-db-name-is-not-currently-accepting-connections

https://fongyang.medium.com/postgres-connections-to-all-databases-refused-c769dd433e46

Let us know if these helped or still facing issue.

Thanks