Quickstart for Cloud SQL for PostgreSQL command-line client using private IP

This page shows you how to create and connect to a PostgreSQL instance using a private IP. The resources created in this quickstart typically cost less than a dollar, assuming you complete the steps, including the clean up, in a timely manner.

Set up your project

Before performing the steps in this quickstart, complete the following tasks:

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  4. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  6. Make sure you have the Cloud SQL Admin role on your user account.
    • Go to the IAM page. Go to the IAM page
    • Click Add.
    • In New Principles, add your user account name.
    • In Select a role, filter for Cloud SQL Admin and select it from the list.
    • Click Save.
  7. Enable the Cloud SQL Admin API.

    Enable the API

Overview

There are many ways to connect to a Cloud SQL instance with a private IP address, depending on where the source is located. The key factor is that the source machine must be in the same VPC network as the Cloud SQL instance. If the source is not in Google Cloud or not in the same Google Cloud project, then you have to configure connectivity differently than we show here.

In this quickstart, we configure connectivity through the most direct path. The source and target are in the same Google Cloud project and in the same VPC network. We create a Cloud SQL instance with a private IP address (the target) and a Compute Engine VM (the source). We use the VM to install and use the tools required to connect from the VM to the Cloud SQL instance.

Perform the following actions:

  1. Create a Cloud SQL instance with a private IP address.

    Find and save the instance's connection name for later user.

  2. Create a Compute Engine VM.
  3. Open two SSH windows into the VM.

    You'll use the first window to install the psql and install and start the Cloud SQL Auth proxy. Then you'll use the second window to connect to the Cloud SQL instance by connecting to the Cloud SQL Auth proxy.

  4. Install a psql client in SSH window #1.
  5. Install the Cloud SQL Auth proxy in SSH window #1.

    The Cloud SQL Auth proxy acts as a connector between the psql client and the Cloud SQL instance.

  6. Start the Cloud SQL Auth proxy in SSH window #1.

    On success, the Cloud SQL Auth proxy listens for connection requests.

  7. In SSH window #2, connect to the Cloud SQL instance by having the psql client connect to the Cloud SQL Auth proxy.

    On success, you see your psql prompt in this window, and a successful connection message in SSH window #1, where the Cloud SQL Auth proxy is running.

  8. Clean up.

    Delete both the Cloud SQL instance and the Compute Engine VM.

Create a Cloud SQL instance with a private IP address

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click Create Instance.
  3. Click Choose PostgreSQL.
  4. If you're prompted to enable the Compute API, click the Enable API button.
  5. In the Instance info section, enter a name for the Instance ID.
  6. Enter a password for the postgres user. Take note of the password you create, because you need it later.
  7. In the Choose region and zonal availability section, select the Single zone option.
  8. Expand Show configuration options.
  9. Expand Connections.
  10. Clear Public IP.
  11. Select Private IP.
  12. From the Network dropdown, select default.
  13. If you're using a new project, you're prompted by the message: Private service connection required. Then follow these steps:
    1. Click Set up connection.
    2. Click Enable service networking API.
    3. In theAllocate an IP range section, select Use an automatically allocated IP range.
    4. Click Continue.
    5. Click Create connection and wait for connection creation to complete.
  14. Click Create instance.

You're taken to the instance Overview page. Click into the new instance view the details including its private IP address.

In the Connect to this instance section, copy and save the instance's Connection name. The connection name is in the format projectID:region:instanceID.

You'll use this connection name later when starting the Cloud SQL Auth proxy.

Create a Compute Engine VM

  1. In the Google Cloud Console, go to the VM instances page.

    Go to VM instances

  2. Click Create instance.
  3. Enter a Name for the instance.
  4. In Access scopes, select Allow full access to all Cloud APIs.
  5. Click Create and wait for the VM to finish being created.

Open two SSH connections to the Compute Engine VM

We use two windows in the VM. The first window is used to install the psql client and the Cloud SQL Auth proxy, get the instance connection name, and use this name to start the proxy. The second window is used to connect to the Cloud SQL instance through the proxy.

  1. Expand the SSH menu in the Connect column for your Compute Engine VM instance.
  2. Select Open in browser window to open SSH window #1.

    It might take a few seconds for the prompt in the window to become available for you.

  3. When the prompt appears, enter pwd to verify that you're in the /home/$USER directory.

    You'll install the psql client and the Cloud SQL Auth proxy, and also start the Cloud SQL Auth proxy, in this window.

  4. Select Open in browser window again to open SSH window #2.

    You'll use this window to connect to your Cloud SQL instance.

Install the psql client

Use SSH window #1 for this step.

Install the psql client from the package manager:

sudo apt-get update
sudo apt-get install postgresql-client
  

Install the Cloud SQL Auth proxy

Use SSH window #1 for this step.

  1. Install wget:
    sudo apt-get install wget
        
  2. Download the Cloud SQL Auth proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
      
  3. Make the Cloud SQL Auth proxy executable:
    chmod +x cloud_sql_proxy
      

Start the Cloud SQL Auth proxy

Use SSH window #1 for this step.

Start the Cloud SQL Auth proxy so you can monitor its output. Replace INSTANCE_CONNECTION_NAME with the connection name you copied when you created the Cloud SQL instance.

./cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:5432

When the Cloud SQL Auth proxy starts successfully, a message similar to the following appears in the SSH window:

Listening on 127.0.0.1:5432 for myInstance
Ready for new connections

Connect to your Cloud SQL instance

Use SSH window #2 for this step.

Run the following command:

psql "host=127.0.0.1 port=5432 sslmode=disable dbname=DB_NAME user=USERNAME"

At the Enter password: prompt, enter the password of your PostgreSQL account.

Verify that the PostgreSQL prompt appears. You have connected to your database using the psql client.

Return to the terminal window where you started the Cloud SQL Auth proxy. You should see a message similar to the following:

New connection for myInstance

Cleanup

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Select the your instance's name to open the Overview page.
  3. In the icon bar at the top of the page, click Delete.
  4. In the Delete instance window, type your instance's name, then click Delete to delete the instance.

    You cannot reuse an instance name for about 7 days after the instance is deleted.

  5. In the Google Cloud Console, go to the VM instances page.

    Go to VM instances

  6. Select your instance's name.

  7. Select Delete from the More actions menu.

What's next

Based on your needs, you can learn more about creating Cloud SQL instances.

You also can learn about creating PostgreSQL users and databases for your Cloud SQL instance.

Additionally, you can learn about connecting to a Cloud SQL instance from other Google Cloud applications: