Log Insights: Install on Google Cloud SQL
These instructions guide you how to enable pganalyze Log Insights for Google Cloud SQL.
- You are already running the pganalyze collector inside a Google Cloud VM
- You have pganalyze successfully configured for your Google Cloud SQL instance
- You have explicitly configured the GCP project ID (
gcp_project_id) and GCP Cloud SQL instance ID (
gcp_cloudsql_instance_id) in the
In the Google Cloud Console, navigate to "Pub/Sub" and create a new Topic, e.g. calling it "postgres-logs".
Next, create a Pub/Sub subscriber, with the following settings:
- Subscription ID: Name of your choice (e.g. "pganalyze-collector")
- Cloud Pub/Sub topic: Select the topic you just created
- Delivery type: Pull
- Subscription expiration: Leave at default (expire after 31 days)
- Message retention duration: 1 day
- Retry policy: Retry immediately
You now have the Pub/Sub topic and subscriber that can receive the Postgres log messages.
Navigate to your Cloud SQL database instance, click on "View PostgreSQL error logs", and then click on "Logs Router".
Click "Create Sink", and select your Pub/Sub topic. Make sure that the left side is actually showing PostgreSQL logs (as it does in this screenshot), and that you don't have a different type of resource selected:
Now we need to create a service account that can access the logs sent to the Pub/Sub topic.
Go to "IAM & Admin", click on "Service accounts" and then "Create Service Account".
- Pick a name of your choice, e.g. "pganalyze-collector"
- Grant "Pub/Sub Subscriber" permission to the service account
We now have two options here:
- Option 1: Associate a key to the service account, download the private key in JSON format and specify as value for
gcp_credentials_filepganalyze collector setting
- Option 2: Assign the service account to the VM directly
To keep things simple and secure, we recommend assigning the service account to the VM. To do so, navigate to your virtual machine.
If your virtual machine is running, you will need to stop it to change the associated service account. Then, edit the VM instance details, and set the "Service account" setting to the just created service account. Now, start the VM again.
Local processes on the VM, such as the pganalyze collector, can now access APIs through the service account.
Sign in to your virtual machine, and add the following configuration setting:
This is the value of "Subscription name" you can see when clicking on the details for the Pub/Sub subscriber we created earlier.
Now test the collector configuration:
sudo pganalyze-collector --test --reload
This should return something like the following:
I [server1] Testing statistics collection... I [server1] Test submission successful (29.3 KB received, server abc123) I [server1] Testing activity snapshots... I [server1] Test submission successful (2.44 KB received, server abc123) I [server1] Testing log collection (Google Cloud SQL)... I [server1] Log test successful I Successfully reloaded pganalyze collector (PID 123)
You will start seeing log data in pganalyze Log Insights within a few minutes.
We recommend setting up Log-based EXPLAIN as follow-up step, to automatically EXPLAIN slow queries in Postgres.
This error occurs when we could not send a message end-to-end through the database log and Pub/Sub pipeline in 10 seconds.
A common error cause is that the Project ID and/or CloudSQL instance ID do not match the names that show in the log stream. Double check your pganalyze collector configuration settings.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →