You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In postgresql managed by pg_auto_failover when a database is attempted to drop, the statement never returns and the postgresql log starts getting flooded with messages reporting deadlocks as shown below.
postgres=# create database amod;
CREATE DATABASE
postgres=# drop database amod;
--> HANGS..
2025-01-24 07:52:24.845 GMT [954540]: [19-1]db=,user=,app=,client= LOG: 00000: started worker for pg_auto_failover health checks in "amod"
2025-01-24 07:52:24.845 GMT [954540]: [20-1]db=,user=,app=,client= LOCATION: HealthCheckWorkerLauncherMain, health_check_worker.c:347
2025-01-24 07:52:25.847 GMT [954591]: [1-1]db=,user=,app=,client= LOG: 00000: process 954591 still waiting for RowExclusiveLock on object 24610 of class 1262 of database 0 after 1000.140 ms
2025-01-24 07:52:25.847 GMT [954591]: [2-1]db=,user=,app=,client= DETAIL: Process holding the lock: 954584. Wait queue: 954591.
2025-01-24 07:52:25.847 GMT [954591]: [3-1]db=,user=,app=,client= LOCATION: ProcSleep, proc.c:1508
2025-01-24 07:52:29.862 GMT [954584]: [1-1]db=postgres,user=postgres,app=psql,client=[local] LOG: 00000: still waiting for backend with PID 954543 to accept ProcSignalBarrier
2025-01-24 07:52:29.862 GMT [954584]: [2-1]db=postgres,user=postgres,app=psql,client=[local] LOCATION: WaitForProcSignalBarrier, procsignal.c:421
2025-01-24 07:52:29.862 GMT [954584]: [3-1]db=postgres,user=postgres,app=psql,client=[local] STATEMENT: drop database amod (force);
Soon the sessions starts accumulating due to deadlock and it causes the database go inresponsive due to too many sessions error. Only way to resolve is to cancel the drop database statement by ctrl+c. This leads to database to be dropped go in invalid state and it is reported continuously in the postgresql log.
2025-01-28 07:47:34.975 GMT [1848700]: [1-1]db=amod,user=pmm,app=[unknown],client=10.8.99.136 FATAL: 55000: cannot connect to invalid database "amod"
2025-01-28 07:47:34.975 GMT [1848700]: [2-1]db=amod,user=pmm,app=[unknown],client=10.8.99.136 HINT: Use DROP DATABASE to drop invalid databases.
2025-01-28 07:47:34.975 GMT [1848700]: [3-1]db=amod,user=pmm,app=[unknown],client=10.8.99.136 LOCATION: InitPostgres, postinit.c:1117
Has anyone seen this behavior? How to circuvent this issue? Any ideas/known best methods sharing would help greatly.
Regards
Amod
The text was updated successfully, but these errors were encountered:
Hi
In postgresql managed by pg_auto_failover when a database is attempted to drop, the statement never returns and the postgresql log starts getting flooded with messages reporting deadlocks as shown below.
postgres=# create database amod;
CREATE DATABASE
postgres=# drop database amod;
--> HANGS..
2025-01-24 07:52:24.845 GMT [954540]: [19-1]db=,user=,app=,client= LOG: 00000: started worker for pg_auto_failover health checks in "amod"
2025-01-24 07:52:24.845 GMT [954540]: [20-1]db=,user=,app=,client= LOCATION: HealthCheckWorkerLauncherMain, health_check_worker.c:347
2025-01-24 07:52:25.847 GMT [954591]: [1-1]db=,user=,app=,client= LOG: 00000: process 954591 still waiting for RowExclusiveLock on object 24610 of class 1262 of database 0 after 1000.140 ms
2025-01-24 07:52:25.847 GMT [954591]: [2-1]db=,user=,app=,client= DETAIL: Process holding the lock: 954584. Wait queue: 954591.
2025-01-24 07:52:25.847 GMT [954591]: [3-1]db=,user=,app=,client= LOCATION: ProcSleep, proc.c:1508
2025-01-24 07:52:29.862 GMT [954584]: [1-1]db=postgres,user=postgres,app=psql,client=[local] LOG: 00000: still waiting for backend with PID 954543 to accept ProcSignalBarrier
2025-01-24 07:52:29.862 GMT [954584]: [2-1]db=postgres,user=postgres,app=psql,client=[local] LOCATION: WaitForProcSignalBarrier, procsignal.c:421
2025-01-24 07:52:29.862 GMT [954584]: [3-1]db=postgres,user=postgres,app=psql,client=[local] STATEMENT: drop database amod (force);
Soon the sessions starts accumulating due to deadlock and it causes the database go inresponsive due to too many sessions error. Only way to resolve is to cancel the drop database statement by ctrl+c. This leads to database to be dropped go in invalid state and it is reported continuously in the postgresql log.
2025-01-28 07:47:34.975 GMT [1848700]: [1-1]db=amod,user=pmm,app=[unknown],client=10.8.99.136 FATAL: 55000: cannot connect to invalid database "amod"
2025-01-28 07:47:34.975 GMT [1848700]: [2-1]db=amod,user=pmm,app=[unknown],client=10.8.99.136 HINT: Use DROP DATABASE to drop invalid databases.
2025-01-28 07:47:34.975 GMT [1848700]: [3-1]db=amod,user=pmm,app=[unknown],client=10.8.99.136 LOCATION: InitPostgres, postinit.c:1117
Has anyone seen this behavior? How to circuvent this issue? Any ideas/known best methods sharing would help greatly.
Regards
Amod
The text was updated successfully, but these errors were encountered: