CareerByteCode
ByteBlogby CareerByteCode

Cloud SQL for PostgreSQL: Create, Connect and Back Up

What you build: a Cloud SQL for PostgreSQL 17 instance with private IP only, a database and user, real data, an on-demand backup and a real restore, plus the Auth Proxy. Every step tested.

Cloud SQL for PostgreSQL: Create, Connect and Back Up

✍️ Translate this blog into Hindi, Tamil or Telugu

In this lab you create a managed PostgreSQL 17 database with Cloud SQL, reachable only on a private IP inside your own network. You add a database and a user, load real rows, take a backup, then do the thing every team fears: run a DELETE without WHERE and lose every row. Then you restore the backup and get them all back. Finally you connect the modern way, through the Cloud SQL Auth Proxy.

Who it is for: beginners who know a little SQL and want to run a real database on Google Cloud without managing a server. Time: about 1 to 1.5 hours (most of it is waiting: creating the instance took 9 minutes and the restore 12). Cost: a few cents. The smallest shared-core tier (db-f1-micro) costs about USD 0.01 an hour plus storage, and the free trial credit covers it. Delete the instance at the end: Cloud SQL bills every hour it exists, even when idle.

Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output and screenshot is the real result we got. We typed the commands in a Cloud Shell-like Linux VM in the project's default network (Debian with gcloud and the psql client). Note: a private IP is reachable only from inside your VPC network, so run Steps 3 to 6 on a VM in the same network (Cloud Shell is outside it). Step 6 shows the Auth Proxy, which is how you would connect from elsewhere.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

Part A: Cloud SQL in plain English

Cloud SQL is Google's managed service for PostgreSQL, MySQL and SQL Server. "Managed" means Google runs the server for you: it installs, patches and monitors it, takes backups, and can keep a standby copy for high availability. You still design the tables and write the SQL.

Cloud SQL with private IP
Your VM reaches Postgres over a private IP through private services access; backups and the Auth Proxy around it

Words you will use:

  • Instance: one database server (here shop-pg). It can hold many databases (here shop). Real-life example: an instance is a building, each database a flat in it.
  • Tier: the size of the machine. db-f1-micro is a shared CPU with 0.6 GB memory, fine for learning, not for production.
  • Private IP: an address only reachable inside your VPC network, so the database is never on the internet. It needs private services access: a reserved address range plus a VPC peering (a private link) between your network and Google's.
  • Backup: a full copy of the instance at a point in time. Cloud SQL takes an automatic one every day and you can take on-demand ones. Restoring a backup replaces all data on the target instance.
  • Cloud SQL Auth Proxy: a small program that connects to the instance using your Google identity (IAM) and encrypts the traffic. Your app then talks to localhost as if the database were local.

Before you start

  1. A Google Cloud account (the free trial works) and a project with billing linked.
  2. Open Cloud Shell (the >_ icon at the top right of the Google Cloud console).
  3. Set your project and turn on the APIs. Replace the text in quotes with your own project id:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable sqladmin.googleapis.com servicenetworking.googleapis.com compute.googleapis.com
  1. A small VM in the default network to connect from, with the psql client (sudo apt-get install -y postgresql-client). See Linux for Cloud Beginners for creating one. If you use a service account on that VM instead of your own login, it needs the Cloud SQL Admin and Service Networking Admin roles; our first try failed with a permission error because the second one was missing.

Part B: the lab

Step 1: private services access

Why: before an instance can get a private IP, your VPC must lend Google an address range and be peered with Google's network. You do this once per network. A /20 range is 4,096 addresses, enough for many instances.

# Private IP needs "private services access": a reserved address range in your VPC that Google uses for Cloud SQL
gcloud compute addresses create google-managed-services-default --global --purpose=VPC_PEERING \
  --prefix-length=20 --network=default 2>&1 | tail -1
gcloud services vpc-peerings connect --service=servicenetworking.googleapis.com \
  --ranges=google-managed-services-default --network=default 2>&1 | tail -1
gcloud services vpc-peerings list --network=default --format="table(service,reservedPeeringRanges.list())"
Created [https://www.googleapis.com/compute/v1/projects/your-project-id/global/addresses/google-managed-services-default].
Operation "operations/pssn.p24-PROJECT_NUMBER-8bd97836-a8da-4e91-b9a7-cd15b583431c" finished successfully.
SERVICE                                    RESERVED_PEERING_RANGES
services/servicenetworking.googleapis.com  google-managed-services-default

Step 2: create the smallest Postgres instance

Why: --tier=db-f1-micro is the cheapest; --availability-type=zonal means one zone and no standby copy (no HA); --no-assign-ip means no public IP at all; daily backups run at 03:00 UTC and 3 are kept. --edition=ENTERPRISE is needed for the shared-core tiers.

# the smallest tier (shared core), one zone, private IP only, daily backups at 03:00 UTC (takes about 10 minutes)
gcloud sql instances create shop-pg --database-version=POSTGRES_17 --edition=ENTERPRISE \
  --tier=db-f1-micro --region=us-east1 --availability-type=zonal \
  --network=default --no-assign-ip --storage-size=10 \
  --backup-start-time=03:00 --retained-backups-count=3 2>&1 | tail -3
gcloud sql instances describe shop-pg --format="table(name,databaseVersion,settings.tier,state,ipAddresses[0].type)"
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/your-project-id/instances/shop-pg].
NAME     DATABASE_VERSION  LOCATION    TIER         PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
shop-pg  POSTGRES_17       us-east1-c  db-f1-micro  -                172.21.48.3      RUNNABLE
NAME     DATABASE_VERSION  TIER         STATE     TYPE
shop-pg  POSTGRES_17       db-f1-micro  RUNNABLE  PRIVATE
Create the instance
The peering is up and shop-pg is RUNNABLE with only a private address

It took about 9 minutes. PRIMARY_ADDRESS - means there is no public address; the instance is reachable only on its private address.

Step 3: a database and an app user

Why: never use the built-in postgres admin user for an app. We create a database shop and a user app with a random password, saved in a file and never printed.

# a database and an app user; the password is random and kept in a file (never shown)
openssl rand -hex 12 > .pgpass
gcloud sql databases create shop --instance=shop-pg 2>&1 | tail -1
gcloud sql users create app --instance=shop-pg --password="$(cat .pgpass)" 2>&1 | tail -1
gcloud sql databases list --instance=shop-pg --format="value(name)"
project: your-project-id
Created user [app].
postgres
shop

Step 4: connect over the private IP and load data

Why: psql is the PostgreSQL command-line client. sslmode=require makes it encrypt the connection. We create an orders table, insert 6 rows and run a report.

# connect over the PRIVATE IP from a VM in the same VPC, with SSL
IP=$(gcloud sql instances describe shop-pg --format="value(ipAddresses[0].ipAddress)")
export PGPASSWORD=$(cat .pgpass)
psql "host=$IP dbname=shop user=app sslmode=require" <<'SQL'
CREATE TABLE orders (id serial PRIMARY KEY, customer text NOT NULL, item text NOT NULL, qty int NOT NULL, ordered_at date NOT NULL);
INSERT INTO orders (customer, item, qty, ordered_at) VALUES
 ('Asha', 'USB-C cable', 2, '2026-11-01'), ('Ravi', 'Laptop stand', 1, '2026-11-02'),
 ('Meena', 'Keyboard', 1, '2026-11-03'), ('Asha', 'Mouse', 1, '2026-11-05'),
 ('John', 'Monitor', 2, '2026-11-08'), ('Ravi', 'USB-C cable', 3, '2026-11-09');
SELECT customer, count(*) AS orders, sum(qty) AS items FROM orders GROUP BY customer ORDER BY items DESC;
SELECT version();
SQL
CREATE TABLE
INSERT 0 6
 customer | orders | items 
----------+--------+-------
 Ravi     |      2 |     4
 Asha     |      2 |     3
 John     |      1 |     2
 Meena    |      1 |     1
(4 rows)

                                         version                                          
------------------------------------------------------------------------------------------
 PostgreSQL 17.11 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
(1 row)
Connect and load
A database and a user, then real rows and a GROUP BY report over the private IP; PostgreSQL 17.11

Step 5: take a backup before changes

Why: the daily backup might be 23 hours old. Before any risky change (a migration, a bulk update), take an on-demand backup.

# take an on-demand backup now (daily automatic backups also run at 03:00 UTC)
gcloud sql backups create --instance=shop-pg --description="before-changes" 2>&1 | tail -1
gcloud sql backups list --instance=shop-pg --format="table(id,type,status,description)"
[https://sqladmin.googleapis.com/sql/v1beta4/projects/your-project-id/instances/shop-pg] backed up.
ID             TYPE       STATUS      DESCRIPTION
1795028373912  ON_DEMAND  SUCCESSFUL  before-changes
Backup
An ON_DEMAND backup named before-changes, status SUCCESSFUL

Break it: DELETE without WHERE

Someone meant to delete one order but forgot the WHERE:

# Break it: a DELETE without WHERE removes every order
IP=$(gcloud sql instances describe shop-pg --format="value(ipAddresses[0].ipAddress)")
export PGPASSWORD=$(cat .pgpass)
psql "host=$IP dbname=shop user=app sslmode=require" -c "DELETE FROM orders;" -c "SELECT count(*) AS orders_left FROM orders;"
DELETE 6
 orders_left 
-------------
           0
(1 row)
DELETE without WHERE
DELETE 6: every order is gone, 0 left

DELETE 6 and orders_left 0. PostgreSQL did exactly what it was told. There is no undo button in SQL.

Fix it: restore the backup

Why: gcloud sql backups restore copies the backup back onto the instance. It replaces all data on the instance, including anything written after the backup, so in real life you often restore to a new instance instead and copy back only the lost rows.

# Fix it: restore the "before-changes" backup onto the instance (this replaces ALL data on it; takes a few minutes)
BACKUP_ID=$(gcloud sql backups list --instance=shop-pg --filter="description=before-changes" --format="value(id)")
gcloud sql backups restore $BACKUP_ID --restore-instance=shop-pg --quiet 2>&1 | tail -1
IP=$(gcloud sql instances describe shop-pg --format="value(ipAddresses[0].ipAddress)")
export PGPASSWORD=$(cat .pgpass)
psql "host=$IP dbname=shop user=app sslmode=require" -c "SELECT count(*) AS orders_back FROM orders;" -c "SELECT * FROM orders ORDER BY id LIMIT 3;"
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →
Restored [https://sqladmin.googleapis.com/sql/v1beta4/projects/your-project-id/instances/shop-pg].
 orders_back 
-------------
           6
(1 row)

 id | customer |     item     | qty | ordered_at 
----+----------+--------------+-----+------------
  1 | Asha     | USB-C cable  |   2 | 2026-11-01
  2 | Ravi     | Laptop stand |   1 | 2026-11-02
  3 | Meena    | Keyboard     |   1 | 2026-11-03
(3 rows)
Restore
After a 12-minute restore all 6 orders are back

All 6 orders are back. The restore took about 12 minutes, during which the instance was unavailable. For finer recovery, turn on point-in-time recovery (--enable-point-in-time-recovery), which lets you restore to any second in the last days, for example one second before the bad DELETE.

Step 6: connect through the Cloud SQL Auth Proxy

Why: the proxy signs in with your Google identity and encrypts the connection, so you do not need to manage SSL certificates or open the database to IP addresses. Your app connects to 127.0.0.1. We download the newest version 2 release of the proxy, start it on port 6432 and run a query through it.

# The Cloud SQL Auth Proxy: connects with your Google identity (IAM) and encrypts for you; the app talks to localhost
VER=$(curl -s "https://api.github.com/repos/GoogleCloudPlatform/cloud-sql-proxy/releases?per_page=30" | jq -r '[.[] | select(.tag_name | startswith("v2"))][0].tag_name')
echo "proxy version $VER"
curl -s -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/$VER/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy && ./cloud-sql-proxy --version
CONN=$(gcloud sql instances describe shop-pg --format="value(connectionName)")
nohup ./cloud-sql-proxy --private-ip --port 6432 "$CONN" > proxy.log 2>&1 &
sleep 5
export PGPASSWORD=$(cat .pgpass)
psql "host=127.0.0.1 port=6432 dbname=shop user=app" -c "SELECT item, sum(qty) AS sold FROM orders GROUP BY item ORDER BY sold DESC LIMIT 3;"
pkill -f "[c]loud-sql-proxy --private-ip"
proxy version v2.28.0
cloud-sql-proxy version 2.28.0+linux.amd64
    item     | sold 
-------------+------
 USB-C cable |    5
 Monitor     |    2
 Mouse       |    1
(3 rows)
Auth Proxy
Cloud SQL Auth Proxy 2.28.0 on localhost:6432, and a query through it

Watch out: the GitHub "latest release" of this project can be a version 1 release (both lines are maintained), and version 1 files are not at the version 2 download address. Our first try downloaded an error page instead of the program for exactly that reason; the command above picks the newest v2 tag.

POC: prove it works

  • A PostgreSQL 17 instance with only a private IP, on the smallest tier, with daily backups.
  • An app user, a database and real rows queried over an encrypted connection.
  • An on-demand backup taken, every row deleted by mistake, and all 6 rows restored from the backup.
  • The same data read through the Cloud SQL Auth Proxy on localhost.

Clean up (delete everything)

Why: a Cloud SQL instance bills every hour, even with no traffic. Deleting the instance also deletes its automatic and on-demand backups. Then remove the private services access.

# delete the instance (its backups go with it), then the private services access
gcloud sql instances delete shop-pg --quiet 2>&1 | tail -1
# Google keeps using the connection for a while after the delete; if it refuses, remove the peering from the VPC side
gcloud services vpc-peerings delete --service=servicenetworking.googleapis.com --network=default --quiet > /dev/null 2>&1 \
  && echo "peering deleted" \
  || { echo "still in use by Cloud SQL: delete it from the VPC side"; gcloud compute networks peerings delete servicenetworking-googleapis-com --network=default --quiet 2>&1 | tail -1; }
gcloud compute addresses delete google-managed-services-default --global --quiet 2>&1 | tail -1
rm -f .pgpass cloud-sql-proxy proxy.log
echo "Cloud SQL instances left: $(gcloud sql instances list --format="value(name)" | wc -l)"
echo "reserved ranges left: $(gcloud compute addresses list --global --format="value(name)" | wc -l)"
echo "VPC peerings left: $(gcloud compute networks peerings list --network=default --format="value(peerings[].name)" | wc -w)"
Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/your-project-id/instances/shop-pg].
still in use by Cloud SQL: delete it from the VPC side
Updated [https://www.googleapis.com/compute/v1/projects/your-project-id/global/networks/default].
Deleted [https://www.googleapis.com/compute/v1/projects/your-project-id/global/addresses/google-managed-services-default].
Cloud SQL instances left: 0
reserved ranges left: 0
VPC peerings left: 0
Clean up
0 instances, 0 reserved ranges, 0 VPC peerings left

A real snag: right after the instance was deleted, gcloud services vpc-peerings delete was refused because Google still saw the connection as in use. Deleting the peering from the VPC side (gcloud compute networks peerings delete) worked at once. Also note that a deleted instance's name cannot be reused for up to a week, so pick a new name if you run the lab again.

FAQ

Public IP or private IP? Private IP keeps the database off the internet and is the usual choice. If you need a public IP, never open it to 0.0.0.0/0; use the Auth Proxy, which needs no open firewall rule.

How do I get high availability? Create the instance with --availability-type=regional. Cloud SQL keeps a standby in another zone and fails over automatically. It roughly doubles the cost and needs a dedicated-core tier.

How long are backups kept? We kept 3 automatic backups (--retained-backups-count=3); the default is 7. On-demand backups stay until you delete them or the instance.

Can Cloud Run or GKE connect to a private IP? Yes: Cloud Run through Direct VPC egress or a connector, GKE when the cluster is in the same VPC. Both can also use the Cloud SQL connectors or the Auth Proxy.

Where should the database password live? Not in code. Use Secret Manager, or IAM database authentication so there is no password at all. See Secret Manager: Store Passwords and API Keys Safely.

Cloud SQL, AlloyDB or Firestore? Cloud SQL for classic PostgreSQL or MySQL apps; AlloyDB for heavy PostgreSQL workloads that need more speed; Firestore for a serverless document database. See Firestore Hands-On.

Troubleshooting

You seeWhyFix
PERMISSION_DENIED creating the peeringThe account lacks Service Networking AdminGrant roles/servicenetworking.networksAdmin (or use a project Owner)
psql: ... timeout to the private IPYou are outside the VPC (for example in Cloud Shell)Connect from a VM in the same network, or use the Auth Proxy with a public IP
password authentication failed for user "app"Wrong password or usergcloud sql users set-password app --instance=shop-pg --prompt-for-password
./cloud-sql-proxy: syntax error near unexpected tokenThe download was an error page, not the programUse a real v2.x.y version in the download address
Producer services ... are still using this connectionThe deleted instance still holds the peeringgcloud compute networks peerings delete servicenetworking-googleapis-com --network=default
instance name already usedNames stay reserved after a deleteUse a new instance name

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →Cloud Storage Deep Dive: Classes, Lifecycle and Signed URLs⏱ 12 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →