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?

Some of these settings can be set for database for which you do not need superuser. We for example set work_mem on CloudSQL this way. See ALTER DATABASE .... SET config_param - postgresql.org/docs/9.6/static/sql-alterdatabase.html — JosMac
– JosMac, Commented Jun 7, 2018 at 15:56
Thanks for the answer. I am not sure idle_in_transaction_session_timeout could be set on a db level. See example here — user2548047
– user2548047, Commented Jun 8, 2018 at 6:59
@user2548047 Have you found a solution for this? I'm not sure, but cloud sql proxy seems to be creating a lot of idle connections... — d4nielfr4nco
– d4nielfr4nco, Commented Jan 21, 2019 at 13:43

Federico Panunzio · Accepted Answer · 2018-06-08 14:35:23Z

2

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.

answered Jun 8, 2018 at 14:35

Federico Panunzio

9745 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to drop idle connections in CloudSQL(Postgres)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related