PostgreSQL drivers with Google Cloud Spanner at scale with Google Cloud Run

Overview

Innovators in various industries, including financial services, gaming, and retail, rely on Google Cloud Spanner to power their demanding relational database workloads. Google Cloud Spanner, built on Google’s distributed infrastructure, offers a fully managed solution with exceptional consistency and availability, even at scale. Moreover, Google Cloud Spanner features a PostgreSQL interface that seamlessly integrates with the open-source PostgreSQL ecosystem.

Introduction

Google Cloud Run, a fully managed runtime environment, seamlessly scales code within containers to handle incoming requests efficiently. Combined with Google Cloud Spanner, it forms a robust scaling solution and ensures high availability in operational applications.

A pivotal element in this synergy is the use of sidecar containers. Sidecars enhance functionality alongside the main container without impacting the core container’s operation. Cloud Run now supports sidecar containers, enabling the deployment of independent sidecars that run alongside the primary container and streamlining web request processing.

To facilitate the connection between PostgreSQL-based applications and Cloud Spanner, Cloud Spanner PGAdapter acts as a lightweight proxy. It translates PostgreSQL network protocol into Cloud Spanner gRPC protocol, allowing applications to seamlessly connect with a Cloud Spanner PostgreSQL database using standard PostgreSQL drivers and frameworks.

Discover seamless PGAdapter configuration as a sidecar in Cloud Run for efficient communication, automatic instance adjustments, and heightened security exclusive to the Cloud Run environment.

gcp

Your application connects to PGAdapter using either of the following options:

TCP connection on localhost:5432 (or any other port you choose for PGAdapter)
Unix domain socket using an in-memory shared volume

This example shows how to use Unix domain sockets, which gives you the lowest possible latency between your application container and PGAdapter.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Configuration

Cloud Run sidecars can currently only be configured using service.yaml files. This section shows you.

How to configure PGAdapter as a sidecar
How to set up a shared in-memory volume that can be used for Unix domain sockets
How to set up a startup probe for PGAdapter Configure PGAdapter as a Sidecar

Step-by-Step Guide

Step 1 – Configure PGAdapter as a Sidecar

In Cloud Run, you make a sidecar container by adding several containers to the list of containers. The container that specifies a container port is known as the primary container. This can only be done by one container. The rest of the containers are sidecar containers.

apiVersion: serving.knative.dev/v1
kind: Service
metadata:
  annotations:
    run.googleapis.com/launch-stage: BETA
  name: pgadapter-sidecar-example
spec:
  template:
  ...
    spec:
      containers:
        # This is the main application container.
        - name: app
          image: MY-REGION.pkg.dev/MY-PROJECT/cloud-run-source-deploy/MY-IMAGE
     # Defining a container Port makes this the main container.
          ports:
            - containerPort: 8080
        # This is the PGAdapter sidecar container.
        - name: pgadapter
          image: gcr.io/cloud-spanner-pg-adapter/pgadapter
          args:
            - -x

apiVersion: serving.knative.dev/v1

kind: Service

metadata:

annotations:

run.googleapis.com/launch-stage: BETA

spec:

template:

...

spec:

containers:

# This is the main application container.

- name: app

image: MY-REGION.pkg.dev/MY-PROJECT/cloud-run-source-deploy/MY-IMAGE

# Defining a container Port makes this the main container.

ports:

- containerPort: 8080

# This is the PGAdapter sidecar container.

- name: pgadapter

image: gcr.io/cloud-spanner-pg-adapter/pgadapter

args:

- -x

A common loopback network interface connects the two containers. This implies that the application container may use a regular PostgreSQL driver to connect to the default PGAdapter port at localhost:5432.

Step 2 – Configure a Shared Volume for Unix Domain Sockets

The PGAdapter container is registered as a dependent of the main application container in this configuration. This guarantees that the PGAdapter runs before the application container. Before launching the application container, Cloud Run will utilize the startup probe to ensure that PGAdapter has successfully begun. The startup probe verifies that it can establish a TCP connection on port 5432, the default port on which PGAdapter listens for incoming connections.

apiVersion: serving.knative.dev/v1
kind: Service
metadata:
  annotations:
    run.googleapis.com/launch-stage: BETA
  name: pgadapter-sidecar-example
spec:
  template:
  ...
    spec:
      # Create an in-memory volume that can be used for Unix domain sockets.
      volumes:
        - name: sockets-dir
          emptyDir:
            sizeLimit: 50Mi
            medium: Memory
      containers:
        # This is the main application container.
        - name: app
          image: MY-REGION.pkg.dev/MY-PROJECT/cloud-run-source-deploy/MY-IMAGE
          ports:
            - containerPort: 8080
          volumeMounts:
            - mountPath: /sockets
              name: sockets-dir
        # This is the PGAdapter sidecar container.
        - name: pgadapter
          image: gcr.io/cloud-spanner-pg-adapter/pgadapter
          volumeMounts:
            - mountPath: /sockets
              name: sockets-dir
          args:
            - -dir /sockets
            - -x

apiVersion: serving.knative.dev/v1

kind: Service

metadata:

annotations:

run.googleapis.com/launch-stage: BETA

spec:

template:

...

spec:

# Create an in-memory volume that can be used for Unix domain sockets.

volumes:

- name: sockets-dir

emptyDir:

sizeLimit: 50Mi

medium: Memory

containers:

# This is the main application container.

- name: app

image: MY-REGION.pkg.dev/MY-PROJECT/cloud-run-source-deploy/MY-IMAGE

ports:

- containerPort: 8080

volumeMounts:

- mountPath: /sockets

# This is the PGAdapter sidecar container.

- name: pgadapter

image: gcr.io/cloud-spanner-pg-adapter/pgadapter

volumeMounts:

- mountPath: /sockets

args:

- -dir /sockets

- -x

This configuration adds the following:

A shared in-memory volume with the name ‘sockets-dir’.
A mount path ‘/sockets’ for the shared volume to the application container.
A mount path ‘/sockets’ for the shared volume to the PGAdapter container.
A command line argument ‘-dir /socket’s for PGAdapter. This instructs PGAdapter to listen for incoming Unix domain sockets on this path.

Step 3 – Configure a Startup Probe for PGAdapter

Containers can be configured to start up in a fixed order, and you can also add startup probes to ensure that a container has been started before another container starts. We’ll use this to ensure that the PGAdapter has started before the main application container is started.

apiVersion: serving.knative.dev/v1
kind: Service
metadata:
  annotations:
    run.googleapis.com/launch-stage: BETA
  name: pgadapter-sidecar-example
spec:
  template:
    metadata:
      annotations:
        run.googleapis.com/execution-environment: gen1
        # This registers 'pgadapter' as a dependency of 'app' and will ensure that
        # pgadapter starts before the app container.
        run.googleapis.com/container-dependencies: '{"app”: ["pgadapter"]}'
    spec:
      containers:
        # This is the main application container.
        - name: app
    ...
        # This is the PGAdapter sidecar container.
        - name: pgadapter
          image: gcr.io/cloud-spanner-pg-adapter/pgadapter
    ...
          # Add a startup probe that checks that PGAdapter is listening on port 5432.
          startupProbe:
            initialDelaySeconds: 10
            timeoutSeconds: 10
            periodSeconds: 10
            failureThreshold: 3
            tcpSocket:
              port: 5432

apiVersion: serving.knative.dev/v1

kind: Service

metadata:

annotations:

run.googleapis.com/launch-stage: BETA

spec:

template:

metadata:

annotations:

run.googleapis.com/execution-environment: gen1

# This registers 'pgadapter' as a dependency of 'app' and will ensure that

# pgadapter starts before the app container.

run.googleapis.com/container-dependencies: '{"app”: ["pgadapter"]}'

spec:

containers:

# This is the main application container.

- name: app

...

# This is the PGAdapter sidecar container.

- name: pgadapter

image: gcr.io/cloud-spanner-pg-adapter/pgadapter

...

# Add a startup probe that checks that PGAdapter is listening on port 5432.

startupProbe:

initialDelaySeconds: 10

timeoutSeconds: 10

periodSeconds: 10

failureThreshold: 3

tcpSocket:

port: 5432

Application Code

The primary application communicates with PGAdapter using a regular PostgreSQL driver. Go and the pgx driver is used in this example. A complete set of samples utilizing additional programming languages and drivers may be found in this directory.

The program communicates with PGAdapter via a Unix domain socket on the shared in-memory volume ‘/sockets’. Because the host begins with a slash, the pgx driver understands it should utilize a Unix domain connection rather than a TCP socket.

The fully qualified database name in the form projects/my-project/instances/my-instance/databases/my-database must be used in the Cloud Spanner connection string.

This sample program launches a basic HTTP server on port 8080. When it gets a request, it establishes a connection to PGAdapter.

func main() {
	project = getenv("SPANNER_PROJECT", "my-project")
	instance = getenv("SPANNER_INSTANCE", "my-instance")
	database = getenv("SPANNER_DATABASE", "my-database")
	qualifiedDatabaseName = fmt.Sprintf(
		"projects/%s/instances/%s/databases/%s",
		project, instance, database)
	databaseHost = getenv("PGADAPTER_HOST", "/sockets")
	databasePort = getenv("PGADAPTER_PORT", "5432")

	log.Printf("\nConnecting to %s\n\n", qualifiedDatabaseName)

	log.Print("starting server...")
	http.HandleFunc("/", handler)

	// Determine port for HTTP service.
	port := os.Getenv("PORT")
	if port == "" {
		port = "8080"
		log.Printf("defaulting to port %s", port)
	}

	// Start HTTP server.
	log.Printf("listening on port %s", port)
	if err := http.ListenAndServe(":"+port, nil); err != nil {
		log.Fatal(err)
	}
}

func handler(w http.ResponseWriter, r *http.Request) {
	// Connect to Cloud Spanner through PGAdapter.
	// Use a fully qualified database name, as PGAdapter
	//is started without any project, instance or database options.
	connString := fmt.Sprintf("host=%s port=%s database=%s",
		databaseHost, databasePort, qualifiedDatabaseName)
	ctx: = context.Background()
	conn, err: = pgx.Connect(ctx, connString)
	if err! = nil {
		fmt.Fprintf(w, "failed to connect to PGAdapter: %v\n", err)
		return
	}
	defer conn.Close(ctx)

	// Execute a query on Cloud Spanner.
	var greeting string
	err = conn.QueryRow(ctx, "select 'Hello world!'").Scan(&greeting)
	if err! = nil {
		fmt.Fprintf(w, "failed to query Cloud Spanner: %v\n", err)
		return
	}
	fmt.Fprintf(w, 
		"\nGreeting from Cloud Spanner PostgreSQL using pgx: %v\n\n",
		greeting)
}

func getenv(key, def string) string {
	value := os.Getenv(key)
	if value == "" {
		return def
	}
	return value
}

func main() {

project = getenv("SPANNER_PROJECT", "my-project")

instance = getenv("SPANNER_INSTANCE", "my-instance")

database = getenv("SPANNER_DATABASE", "my-database")

qualifiedDatabaseName = fmt.Sprintf(

"projects/%s/instances/%s/databases/%s",

project, instance, database)

databaseHost = getenv("PGADAPTER_HOST", "/sockets")

databasePort = getenv("PGADAPTER_PORT", "5432")

log.Printf("\nConnecting to %s\n\n", qualifiedDatabaseName)

log.Print("starting server...")

http.HandleFunc("/", handler)

// Determine port for HTTP service.

port := os.Getenv("PORT")

if port == "" {

port = "8080"

log.Printf("defaulting to port %s", port)

}

// Start HTTP server.

log.Printf("listening on port %s", port)

if err := http.ListenAndServe(":"+port, nil); err != nil {

log.Fatal(err)

}

func handler(w http.ResponseWriter, r *http.Request) {

// Connect to Cloud Spanner through PGAdapter.

// Use a fully qualified database name, as PGAdapter

//is started without any project, instance or database options.

connString := fmt.Sprintf("host=%s port=%s database=%s",

databaseHost, databasePort, qualifiedDatabaseName)

ctx: = context.Background()

conn, err: = pgx.Connect(ctx, connString)

if err! = nil {

fmt.Fprintf(w, "failed to connect to PGAdapter: %v\n", err)

return

}

defer conn.Close(ctx)

// Execute a query on Cloud Spanner.

var greeting string

err = conn.QueryRow(ctx, "select 'Hello world!'").Scan(&greeting)

if err! = nil {

fmt.Fprintf(w, "failed to query Cloud Spanner: %v\n", err)

return

}

fmt.Fprintf(w,

"\nGreeting from Cloud Spanner PostgreSQL using pgx: %v\n\n",

greeting)

}

func getenv(key, def string) string {

value := os.Getenv(key)

if value == "" {

return def

}

return value

}

Conclusion

Adding Cloud Run sidecars facilitates the development of scalable apps using Cloud Spanner.

Leverage Cloud Spanner’s PostgreSQL interface for seamless integration with familiar SQL, ensuring scalability and availability. With PGAdapter, utilize off-the-shelf PostgreSQL drivers and frameworks in Cloud Spanner. Cloud Run enhances application scalability, and sidecar containers automate scaling tasks. Employ Unix domain sockets and in-memory volumes for minimal latency between your application and PGAdapter.

Drop a query if you have any questions regarding Cloud Run and we will get back to you quickly.

Making IT Networks Enterprise-ready – Cloud Management Services

Accelerated cloud migration
End-to-end view of the cloud environment

Get Started

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, Microsoft Gold Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, and many more.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.

FAQs

1. What is Cloud Spanner, and why would I want to use PostgreSQL drivers with it on Cloud Run?

ANS: – Cloud Spanner is a globally distributed, horizontally scalable, and strongly consistent database service by Google Cloud. Using PostgreSQL drivers with Cloud Spanner on Cloud Run allows you to leverage existing PostgreSQL tools and libraries while benefiting from the scalability and global distribution features of Cloud Spanner.

2. Which PostgreSQL drivers are compatible with Cloud Spanner on Cloud Run?

ANS: – Cloud Spanner supports the PostgreSQL wire protocol, so any PostgreSQL driver that adheres to this protocol can be used. Popular choices include psycopg2 for Python, pg-promise for Node.js, and pg for Node.js.

WRITTEN BY Ayush Agarwal

Ayush Agarwal works as a Research Associate at CloudThat. He has excellent analytical thinking and carries an optimistic approach toward his life. He is having sound Knowledge of AWS Cloud Services, Infra setup, Security, WAR, and Migration. He is always keen to learn and adopt new technologies.