Databases

How to use PostgreSQL drivers with Cloud Spanner at scale with Cloud Run

October 13, 2023

Knut Olav Løite

Software Engineer

Innovators in financial services, gaming, retail, and many other industries rely on Cloud Spanner to power demanding relational database workloads that need to scale without downtime. Built on Google’s distributed infrastructure, Spanner provides a fully managed experience with the highest levels of consistency and availability at any scale. Cloud Spanner’s PostgreSQL interface makes the capabilities of Spanner accessible from the open-source PostgreSQL ecosystem.

Cloud Run is a fully-managed runtime that automatically scales your code, in a container, from zero to as many instances as needed to handle all incoming requests. Cloud Spanner and Cloud Run together provide end-to-end scaling and availability for operational applications. Sidecar containers are containers that run alongside the main container and add additional functionality without changing the existing container. Cloud Run now also supports sidecars, allowing you to start independent sidecar containers that run alongside the main container serving web requests.

Cloud Spanner PGAdapter is a lightweight proxy that translates the PostgreSQL network protocol to the Cloud Spanner gRPC protocol. This allows your application to use standard PostgreSQL drivers and frameworks to connect with a Cloud Spanner PostgreSQL database.

This blog shows how you can configure PGAdapter as a Cloud Run sidecar. This setup ensures the lowest possible latency between your application and PGAdapter, and automatically scales the number of PGAdapter instances with the number of application instances.

Overview

All containers within a Cloud Run instance share the same network namespace and can communicate with each other over localhost:port. The containers can also share files via shared volumes. Your application container is the container that receives traffic from the Internet. PGAdapter is set up as a sidecar container. A sidecar is an additional container that runs alongside your main container. It is only accessible from within the Cloud Run instance.

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.

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

You create a sidecar container in Cloud Run by adding more than one container to the list of containers. The main container is the container that specifies a container port. Only one container may do this. All other containers are sidecar containers.

The two containers have a shared loopback network interface. That means that the application container can connect to the default PGAdapter port at localhost:5432 using a standard PostgreSQL driver.

Configure a Shared Volume for Unix Domain Sockets

Most PostgreSQL drivers support connections using Unix domain sockets. A Unix domain socket is a communication endpoint for processes running on the same host machine, typically using a file as the communication channel. Unix domain sockets offer lower latency than TCP connections.

PGAdapter also supports Unix domain sockets. For this, we need a volume that is accessible for both containers. Cloud Run supports this in the form of shared in-memory volumes. The following example adds a shared volume to our setup:

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 /sockets’ for PGAdapter. This instructs PGAdapter to listen for incoming Unix domain sockets on this path.

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 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

This configuration registers the PGAdapter container as a dependency of the main application container. This ensures that PGAdapter is started before the application container. Cloud Run will also use the startup probe to verify that PGAdapter has successfully started before starting the application container. The startup probe checks that it can successfully make a TCP connection on port 5432, which is the default port where PGAdapter listens for incoming connections.

Application Code

The main application connects to PGAdapter using a standard PostgreSQL driver. This example uses Go and the pgx driver. See this directory for a full list of samples using other programming languages and drivers.

The application connects to PGAdapter using a Unix domain socket on the shared in-memory volume mounted at ‘/sockets’. The pgx driver automatically knows that it should use a Unix domain socket instead of a TCP socket, because the host begins with a slash.

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

This sample application starts a simple HTTP server that listens on port 8080. Whenever it receives a request, it will create a connection to PGAdapter and execute a query that returns ‘Hello World!’. This message is then printed to the response stream of the HTTP server.

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
}

The full sample code can be found here.

Similar samples for other programming languages can be found here:

Conclusion

The introduction of Cloud Run sidecars significantly simplifies the deployment of scalable applications with Cloud Spanner.

Cloud Spanner’s PostgreSQL interface provides developers with access to Spanner’s consistency and availability at scale using the SQL they already know. PGAdapter adds the ability to use PostgreSQL off-the-shelf drivers and frameworks with Cloud Spanner—the same ones in their PostgreSQL applications today. Cloud Run complements Spanner with elastic application scaling. The addition of sidecar containers provide additional flexibility to automatically scale related processes with the application. Using Unix domain sockets with in-memory volumes from your sidecar provides the lowest possible latency for communication between your application and PGAdapter.

Learn more about what makes Spanner unique and how it’s being used today. Or try it yourself for free for 90-days or for as little as $65 USD/month for a production-ready instance that grows with your business without downtime or disruptive re-architecture.

Posted in