How to drop idle connections in CloudSQL(Postgres)

Question

We have an issue with too many open connections in our staging DB Postgresql instance hosted on CloudSQL.

I would like to know if there is any workaround for idle_in_transaction_session_timeout command on CloudSQL. In order to be able to run this command, we would need to have superuser access which is unfortunately not the case for CloudSQL.

I would like to avoid setting up a batch job which would remove idle connections periodically. Can you think of a better alternative?

Answer 1

You can set idle_in_transaction_session_timeout on a db level, but it's going to last one session, so that's not a good approach. Instead of using alter system you can just use set idle_in_transaction_session_timeout.

If you do need this flexibility, what you can do as a workaround is create a Compute Engine instance and use your postgreSQL form there, having full access.