Connect to Cloud SQL for PostgreSQL from your local computer
Learn how to deploy a sample app on your Linux, macOS, or Windows-based local computer connected to a PostgreSQL instance by using the Google Cloud console and a client application.
Assuming that you complete all the steps in a timely manner, the resources created in this quickstart typically cost less than one dollar (USD).
Before you begin
- 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.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the Cloud APIs necessary to run a Cloud SQL sample app on your local computer.
Click the Enable APIs button to enable the APIs required for this quickstart.
This enables the following APIs:
- Cloud SQL Admin API
- IAM API
Install the gcloud CLI which provides command-line access to your Google Cloud resources. The gcloud CLI is used to run the
gcloud CLI
commands presented throughout this quickstart. All the commands are formatted to be run in a Linux/macOS terminal or in Windows Powershell.Open the terminal and run the following
gcloud
command:gcloud services enable sqladmin.googleapis.com iam.googleapis.com
This command enables the following APIs:
- Cloud SQL Admin API
- IAM API
Set up Cloud SQL
Create a Cloud SQL instance
Create an instance with a public IP address
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Click Create Instance.
- Click Choose PostgreSQL.
- Enter
quickstart-instance
for Instance ID. - Enter a password for the postgres user. Save this password for future use.
- Click the Single zone option for Choose region and zonal availability.
- Click and expand the Show Configuration Options section.
- In the Machine Type drop-down menu, select Lightweight.
Click Create Instance and wait until the instance initializes and starts.
Create an instance with a public IP address
Before running the gcloud
sql instances create
command as follows, replace DB_ROOT_PASSWORD with the password of your database user.
Optionally, modify the values for the following parameters:
- --database_version: The database engine type and version. If left unspecified, the API default is used. See the gcloud database versions documentation to see the currently available versions.
- --cpu: The number of cores to set in the machine.
- --memory: Whole number value indicating how much memory to set in the machine. A size unit should be provided (for example, 3072MB or 9GB). If no units are specified, GB is assumed.
- --region: Regional location of the instance
(for example asia-east1, us-east1). If left unspecified, the default
us-central
is used. See the full list of regions.
Run the gcloud
sql instances create
command to create a Cloud SQL instance.
gcloud sql instances createquickstart-instance --database-version=POSTGRES_13 --cpu=1 --memory=4GB --region=us-central --root-password=DB_ROOT_PASSWORD
Create a database
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select
quickstart-instance
. - Open the Databases tab.
- Click Create database.
- In the New database dialog box, enter
quickstart_db
as the name of the database.
- Click Create.
Run the gcloud
sql databases create
command to create a database.
gcloud sql databases createquickstart_db --instance=quickstart-instance
Create a user
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
- Click Add user account.
- On the Add a user account to instance instance_name page,
add the following information:
- In the Username field, enter
quickstart-user
- In the Password field, specify a password for your database user. Make a note of this for use in a later step of this quickstart.
- In the Username field, enter
- Click Add.
Replace the following:
- PASSWORD with a password for your database user. Make a note of this for use in a later step of this quickstart.
Run the gcloud sql users create
command to create the user.
gcloud sql users createquickstart-user --instance=quickstart-instance --password=PASSWORD
User name length limits are the same for Cloud SQL as for on-premises PostgreSQL.
Set up a service account
Create a service account
- In the Google Cloud console, go to the Create service account page.
- Select a Google Cloud project.
- Enter a
quickstart-service-account
as the service account name. - Optional: Enter a description of the service account.
- Click Create and continue and continue to the next step.
- Choose the Cloud SQL Client role to grant to the service account on the project.
- Click Continue.
- Click Done to finish creating the service account.
Create and download the service account key file
- In the Google Cloud console, go to the Service accounts page.
- Select a project.
- Click the email address of the
quickstart-service-account
service account that you want to create a key for. - Click the Keys tab.
- Click the Add key drop-down menu, then select Create new key.
- Select JSON as the Key type and click Create.
Clicking Create downloads a service account key file. After you download the key file, you cannot download it again.
Make sure to store the key file securely, because it can be used to authenticate as your service account. You can move and rename this file however you would like.
Create a service account
- To create the service account, run the
gcloud iam service-accounts create
command: DESCRIPTION
: An optional description of the service account- To grant your service account the Cloud SQL Client role on your project, run the
gcloud projects add-iam-policy-binding
command. Replace PROJECT_ID with your Google Cloud project ID:gcloud projects add-iam-policy-binding
PROJECT_ID --member="serviceAccount:quickstart-service-account@PROJECT_ID .iam.gserviceaccount.com" --role="roles/cloudsql.client"
gcloud iam service-accounts create quickstart-service-account --description="DESCRIPTION " --display-name="quickstart-service-account "
Replace the following value:
Create and download the service account key file
To use the service account you just created from your local computer you need a service account key file to
authenticate the sample application as the service account. To create and download the service account key file, run the
gcloud iam service-accounts keys create
command:
gcloud iam service-accounts keys createKEY_FILE --iam-account=quickstart-service-account@PROJECT_ID .iam.gserviceaccount.com
Replace the following values:
-
KEY_FILE
: The path to a new output file for the private key — for example,~/sa-private-key.json
. -
PROJECT_ID
: Your Google Cloud project ID.
The service account key file is now downloaded to your local computer. After you download the key file, you cannot download it again.
Make sure to store the key file securely, because anyone can use it to authenticate as your service account. You can move and rename this file however you would like.
Setup development environment for programming language
Set up your local computer's development environment for your preferred programming language.
Complete the following steps to set up your development environment to run the Go sample app.
- Go to the setup guide for a Go development environment.
- Complete the instructions in the Install Go section.
Complete the following steps to set up your development environment to run the Java sample app.
- Go to the setup guide for a Java development environment.
- Complete the instructions in the Install a JDK (Java Development Kit) section.
- Complete the instructions in the Install a build automation tool to set up Apache Maven.
Complete the following steps to set up your development environment to run the Node.js sample app.
- Go to setup guide for a Node.js development environment.
- Complete the instructions in the Installing Node.js and npm section.
Complete the following steps to set up your development environment to run the Python sample app.
- Go to setup guide for a Python development environment.
- Complete the instructions in the Installing Python section.
Install Git
Install Git, an open source version control system.
Linux
Follow the official Git installation documentation for Linux.
macOS
Follow the official Git installation documentation for macOS.
Windows
Follow the official Git installation documentation for Windows.
Clone a sample app
Clone a sample app to your local computer using the git clone
command.
Run the following commands to clone the Go sample app to your local computer and change directory to the directory containing the sample app.
-
Clone the sample app.
git clone https://github.com/GoogleCloudPlatform/golang-samples
-
Change directory to the directory containing the sample app.
cd golang-samples/cloudsql/postgres/database-sql
Run the following commands to clone the Java sample app to your local computer and change directory to the directory containing the sample app.
-
Clone the sample app.
git clone https://github.com/GoogleCloudPlatform/java-docs-samples
-
Change directory to the directory containing the sample app.
cd java-docs-samples/cloud-sql/postgres/servlet
Run the following commands to clone the Node.js sample app to your local computer and change directory to the directory containing the sample app.
-
Clone the sample app.
git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples
-
Change directory to the directory containing the sample app.
cd nodejs-docs-samples/cloud-sql/postgres/knex
Run the following commands to clone the Python sample app to your local computer and change directory to the directory containing the sample app.
-
Clone the sample app.
git clone https://github.com/GoogleCloudPlatform/python-docs-samples
-
Change directory to the directory containing the sample app.
cd python-docs-samples/cloud-sql/postgres/sqlalchemy
Configure and run sample app
Configure and run the sample app.
Linux/macOS
Open the terminal and run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
/path/to/service/account/key.json
. - INSTANCE_CONNECTION_NAME with your instance's Connection name that appears on the Cloud SQL instance Overview page.
- YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
export GOOGLE_APPLICATION_CREDENTIALS=CREDENTIALS_JSON_FILE export INSTANCE_CONNECTION_NAME='INSTANCE_CONNECTION_NAME ' export DB_PORT='5432' export DB_NAME='quickstart_db ' export DB_USER='quickstart-user ' export DB_PASS='YOUR_DB_PASSWORD '
Run the following commands to get the Go sample app's dependencies on your local computer and run the sample app.
-
Get the dependencies required to run the sample app.
go get ./...
-
Run the sample app.
go run cmd/app/main.go
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the terminal window where you started the sample app.
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
/path/to/service/account/key.json
. - INSTANCE_CONNECTION_NAME with your instance's Connection name that appears on the Cloud SQL instance Overview page.
- YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
export GOOGLE_APPLICATION_CREDENTIALS=CREDENTIALS_JSON_FILE export INSTANCE_CONNECTION_NAME='INSTANCE_CONNECTION_NAME ' export DB_PORT='5432' export DB_NAME='quickstart_db ' export DB_USER='quickstart-user ' export DB_PASS='YOUR_DB_PASSWORD '
-
Run the following command to get the Java sample app's dependencies on your local computer and run the sample app.
mvn jetty:run
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the terminal window where you started the sample app.
-
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
/path/to/service/account/key.json
. - YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
export GOOGLE_APPLICATION_CREDENTIALS=
CREDENTIALS_JSON_FILE export INSTANCE_HOST='127.0.0.1' export DB_PORT='5432' export DB_NAME='quickstart_db ' export DB_USER='quickstart-user ' export DB_PASS='YOUR_DB_PASSWORD ' - CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
- Download the Cloud SQL Auth Proxy.
curl -o cloud-sql-proxy \ https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.0.0/cloud-sql-proxy.linux.amd64
- Run the Cloud SQL Auth Proxy as a background process. Replace INSTANCE_CONNECTION_NAME with your
instance's Connection name that appears on the Cloud SQL instance Overview page.
./cloud-sql-proxy \ --credentials-file $GOOGLE_APPLICATION_CREDENTIALS \
INSTANCE_CONNECTION_NAME &
Run the following commands to get the required Node.js packages on to your local computer and run the sample app.
-
Install the Node.js packages necessary to run the app locally.
npm install
-
Run the sample app.
npm start
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the terminal window where you started the sample app.
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
/path/to/service/account/key.json
. - INSTANCE_CONNECTION_NAME with your instance's Connection name that appears on the Cloud SQL instance Overview page.
- YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
export GOOGLE_APPLICATION_CREDENTIALS=CREDENTIALS_JSON_FILE export INSTANCE_CONNECTION_NAME='INSTANCE_CONNECTION_NAME ' export DB_PORT='5432' export DB_NAME='quickstart_db ' export DB_USER='quickstart-user ' export DB_PASS='YOUR_DB_PASSWORD '
Run the following commands to get the Python sample app's requirements on to your local computer and run the sample app.
-
Initialize a virtual environment and install the requirements to run the sample app.
python3 -m venv env source env/bin/activate pip install -r requirements.txt
-
Run the sample app.
python app.py
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the terminal window where you started the sample app.
Windows
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
C:\Users\Your-Username\Downloads\key.json
. - INSTANCE_CONNECTION_NAME with your instance's Connection name that appears on the Cloud SQL instance Overview page.
- YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
$env:GOOGLE_APPLICATION_CREDENTIALS="CREDENTIALS_JSON_FILE " $env:INSTANCE_CONNECTION_NAME="INSTANCE_CONNECTION_NAME " $env:DB_PORT="5432" $env:DB_NAME="quickstart_db " $env:DB_USER="quickstart-user " $env:DB_PASS="YOUR_DB_PASSWORD "
Run the following commands to get the Go sample app's dependencies on your local computer and run the sample app.
-
Get the dependencies required to run the sample app.
go get ./...
-
Run the sample app.
go run cmd\app\main.go
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the Powershell window where you started the sample app.
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
C:\Users\Your-Username\Downloads\key.json
. - INSTANCE_CONNECTION_NAME with your instance's Connection name that appears on the Cloud SQL instance Overview page.
- YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
$env:GOOGLE_APPLICATION_CREDENTIALS="CREDENTIALS_JSON_FILE " $env:INSTANCE_CONNECTION_NAME="INSTANCE_CONNECTION_NAME " $env:DB_PORT="5432" $env:DB_NAME="quickstart_db " $env:DB_USER="quickstart-user " $env:DB_PASS="YOUR_DB_PASSWORD "
-
Run the following command to get the Java sample app's dependencies on your local computer and run the sample app.
mvn jetty:run
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the Powershell window where you started the sample app.
-
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
C:\Users\Your-Username\Downloads\key.json
. - YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
$env:GOOGLE_APPLICATION_CREDENTIALS="
CREDENTIALS_JSON_FILE " $env:INSTANCE_HOST="127.0.0.1" $env:DB_PORT="5432" $env:DB_NAME="quickstart_db " $env:DB_USER="quickstart-user " $env:DB_PASS="YOUR_DB_PASSWORD " - CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
- Download the Cloud SQL Auth Proxy.
wget https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.0.0/cloud-sql-proxy.x64.exe -O cloud-sql-proxy.exe
- Run the Cloud SQL Auth Proxy as a background process. Replace INSTANCE_CONNECTION_NAME with your
instance's Connection name that appears on the Cloud SQL instance Overview page.
Start-Process -filepath ".\cloud-sql-proxy.exe" -ArgumentList ` "--credentials-file $env:GOOGLE_APPLICATION_CREDENTIALS"
INSTANCE_CONNECTION_NAME
Run the following commands to get the required Node.js packages on your local computer and run the sample app.
-
Install the Node.js packages necessary to run the app locally.
npm install
-
Run the sample app.
npm start
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the Powershell window where you started the sample app.
Run the following commands to initialize environment variables required to run the sample app. Before running the commands, make the following replacements:
- CREDENTIALS_JSON_FILE with the file path of
your service account's JSON credentials key file using a format like
C:\Users\Your-Username\Downloads\key.json
. - INSTANCE_CONNECTION_NAME with your instance's Connection name that appears on the Cloud SQL instance Overview page.
- YOUR_DB_PASSWORD with the password of the
quickstart-user
that you created in the previous Create a user quickstart step.
$env:GOOGLE_APPLICATION_CREDENTIALS="CREDENTIALS_JSON_FILE " $env:INSTANCE_CONNECTION_NAME="INSTANCE_CONNECTION_NAME " $env:DB_PORT="5432" $env:DB_NAME="quickstart_db " $env:DB_USER="quickstart-user " $env:DB_PASS="YOUR_DB_PASSWORD "
Run the following commands to get the Python sample app's requirements on your local computer and run the sample app.
-
Initialize a virtual environment and install the requirements to run the sample app.
virtualenv --python python3 env .\env\Scripts\activate pip install -r requirements.txt
-
Run the sample app.
python app.py
-
View the running sample app. Open a browser window and go to
http://127.0.0.1:8080
. - To stop the sample app, press Control+C in the Powershell window where you started the sample app.
Clean up
To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select the
quickstart-instance
instance to open the Instance details page. - In the icon bar at the top of the page, click Delete.
- In the Delete instance dialog box, type
quickstart-instance
, and then click Delete to delete the instance.
Optional cleanup steps
If you're not using the Cloud SQL client role that you assigned to the
quickstart-service-account
service account, you can remove it.
-
In the Google Cloud console, go to the IAM page.
- Click the edit icon (which looks like a pencil) for the IAM account named quickstart-service-account.
- Delete the Cloud SQL client role.
- Click Save.
If you're not using the APIs that were enabled as part of this quickstart, you can disable them.
- APIs that were enabled within this quickstart:
- Cloud SQL Admin API
- Identity and Access Management API
In the Google Cloud console, go to the APIs page.
Select any API that you would like to disable and then click the Disable API button.
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.
For more information about pricing, see Cloud SQL for PostgreSQL pricing.
Learn more about:
- Configuring your Cloud SQL instance with a public IP address.
- Configuring your Cloud SQL instance with a private IP address.
Additionally, you can learn about connecting to a Cloud SQL instance from other Google Cloud applications: