PostgreSQL database regularly in recovery mode

Question

We are getting this error quite regularly, database is being thrown into recovery mode, application runs out of connections. After a while ~ 3 to 5 minutes it recovers. This is unacceptable.

Application exception:

      at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:692)
      at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
      at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
      at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:677)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1458)
      at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:748)
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
      at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1469)
      at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
      at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
      ... 34 more
Caused by: org.postgresql.util.PSQLException: FATAL: the database system is in recovery mode
      at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:463)
      at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:203)
      at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
      at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
      at org.postgresql.Driver.makeConnection(Driver.java:454)
      at org.postgresql.Driver.connect(Driver.java:256)
      at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
      at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:214)
      at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
      at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
      at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
      at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
      at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

Sad part is, that there are no Errors, Warnings in Stackdriver logging. Checked maintenance windows and backup schedules, none of them coincide with these "database in recovery mode" events.

Answer 1

I had the same issue and contacted Google Cloud Support. It's caused by having Query Insights turned on.

This happens when the Query Insights are active, and after reviewing your instances I notice that it has the Query Insights enabled. To disable this, please follow these steps:

1 - Log in to the GCP Console. 2 - Click on the name of the instance, then go to the Overview page. 3 - On the Configuration section, click Edit Configuration. 4 - Search for Query insights on the Configuration options 5 - Uncheck the Enable Query Insights 6 - Click Save. 7 - Try to run the schema change command.