Google Cloud SQL

PostgreSQL Max Connections Not As Desired

Ensures that max_connections flag is enabled for PostgreSQL instances. 

Risk Level: Low

Description

This plugin ensures that all PostgreSQL database instances have the max_connections flag enabled and set to the desired value. The max_connections flag specifies the maximum number of concurrent database server connections.

About the Service

Google Cloud SQL:

Google Cloud SQL is a relational database for MySQL, PostgreSQL, and SQL Server that is fully managed. It automates database provisioning, storage capacity management, replication, and backups while lowering maintenance costs. It can be set up easily using the built-in migration tools and lets you scale your instances effortlessly. To know more about Cloud SQL, read here

Impact

If the max_connections flag is not set to the desired value, it could severely impact the health and performance of your SQL instances. If the value is set too low, it may result in issues while running some queries. However, if the value is set too high, your database may get overburdened.

Steps to Reproduce

Using GCP Console-

  1. Log In to your GCP Console.
  2. From the top navigation bar, select the GCP project you want to investigate.
  3. From the navigation panel on the left side of the console, go to SQL. You can use this link here to navigate directly if you’re already logged in.
  4. Set Type to PostgreSQL in the Filter box to only see PostgreSQL database instances.
  5. Select the ID of the SQL instance you want to investigate from the list of instances available and click on the OVERVIEW tab to check the configuration settings of the selected instance.
  6. In the Database flags section under Configuration, check the configuration of max_connections. If it is not set to the desired value or if there is no max_connections flag set then the max_connections flag is not configured properly for the selected SQL instance.
  7. Repeat steps 5 and 6 for all the SQL instances you want to investigate in the selected project.
  8. If you have multiple projects, repeat steps 2 to 7 for each project in your GCP Console. 

Steps for Remediation

Make the necessary changes to correctly configure the max_connections flag for your database instances using the steps below.


Using GCP Console-

  1. Log In to your GCP Console.
  2. From the top navigation bar, select the GCP project you want to investigate.
  3. From the navigation panel on the left side of the console, go to SQL. You can use this link here to navigate directly if you’re already logged in.
  4. Set Type to PostgreSQL in the Filter box to only see PostgreSQL database instances.
  5. Select the ID of the SQL instance you want to reconfigure in the list of instances available. (In case you aren’t sure which SQL instance needs to be configured, follow the steps to reproduce listed above to determine which to choose.)
  6. Go to the OVERVIEW tab and click on the Edit button found on the top navigation bar.

  7. Under the Configuration section, click on Flags and set the value of max_connections to the desired amount. Click the SAVE button to save all the changes.
    Note: If you do not find the max_connections flag, click on the Add item button, choose max_connections from the dropdown list provided, and set the desired value.
  8. In the confirmation box, click on SAVE AND RESTART to apply the configuration changes.
  9. Repeat steps 5 to 7 for all the SQL instances you want to reconfigure in the selected project.
  10. If you have multiple projects, repeat steps 2 to 8 for each project in your GCP console.