Friday, June 21, 2024
No menu items!
HomeCloud ComputingHow to use PostgreSQL drivers with Cloud Spanner at scale with Cloud...

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

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 sidecarHow to set up a shared in-memory volume that can be used for Unix domain socketsHow 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.

code_block<ListValue: [StructValue([(‘code’, ‘apiVersion: serving.knative.dev/v1rnkind: Servicernmetadata:rn annotations:rn run.googleapis.com/launch-stage: BETArn name: pgadapter-sidecar-examplernspec:rn template:rn …rn spec:rn containers:rn # This is the main application container.rn – name: apprn image: MY-REGION.pkg.dev/MY-PROJECT/cloud-run-source-deploy/MY-IMAGErn # Definining a containerPort makes this the main container.rn ports:rn – containerPort: 8080rn # This is the PGAdapter sidecar container.rn – name: pgadapterrn image: gcr.io/cloud-spanner-pg-adapter/pgadapterrn args:rn – -x’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5fa4772b50>)])]>

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:

code_block<ListValue: [StructValue([(‘code’, ‘apiVersion: serving.knative.dev/v1rnkind: Servicernmetadata:rn annotations:rn run.googleapis.com/launch-stage: BETArn name: pgadapter-sidecar-examplernspec:rn template:rn …rn spec:rn # Create an in-memory volume that can be used for Unix domain sockets.rn volumes:rn – name: sockets-dirrn emptyDir:rn sizeLimit: 50Mirn medium: Memoryrn containers:rn # This is the main application container.rn – name: apprn image: MY-REGION.pkg.dev/MY-PROJECT/cloud-run-source-deploy/MY-IMAGErn ports:rn – containerPort: 8080rn volumeMounts:rn – mountPath: /socketsrn name: sockets-dirrn # This is the PGAdapter sidecar container.rn – name: pgadapterrn image: gcr.io/cloud-spanner-pg-adapter/pgadapterrn volumeMounts:rn – mountPath: /socketsrn name: sockets-dirrn args:rn – -dir /socketsrn – -x’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5fa4772d00>)])]>

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.

code_block<ListValue: [StructValue([(‘code’, ‘apiVersion: serving.knative.dev/v1rnkind: Servicernmetadata:rn annotations:rn run.googleapis.com/launch-stage: BETArn name: pgadapter-sidecar-examplernspec:rn template:rn metadata:rn annotations:rn run.googleapis.com/execution-environment: gen1rn # This registers ‘pgadapter’ as a dependency of ‘app’ and will ensure thatrn # pgadapter starts before the app container.rn run.googleapis.com/container-dependencies: ‘{“app”:[“pgadapter”]}’rn spec:rn containers:rn # This is the main application container.rn – name: apprn …rn # This is the PGAdapter sidecar container.rn – name: pgadapterrn image: gcr.io/cloud-spanner-pg-adapter/pgadapterrn …rn # Add a startup probe that checks that PGAdapter is listening on port 5432.rn startupProbe:rn initialDelaySeconds: 10rn timeoutSeconds: 10rn periodSeconds: 10rn failureThreshold: 3rn tcpSocket:rn port: 5432’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5fa4772070>)])]>

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.

code_block<ListValue: [StructValue([(‘code’, ‘func main() {rntproject = getenv(“SPANNER_PROJECT”, “my-project”)rntinstance = getenv(“SPANNER_INSTANCE”, “my-instance”)rntdatabase = getenv(“SPANNER_DATABASE”, “my-database”)rntqualifiedDatabaseName = fmt.Sprintf(rntt”projects/%s/instances/%s/databases/%s”,rnttproject, instance, database)rntdatabaseHost = getenv(“PGADAPTER_HOST”, “/sockets”)rntdatabasePort = getenv(“PGADAPTER_PORT”, “5432”)rnrntlog.Printf(“\nConnecting to %s\n\n”, qualifiedDatabaseName)rnrntlog.Print(“starting server…”)rnthttp.HandleFunc(“/”, handler)rnrnt// Determine port for HTTP service.rntport := os.Getenv(“PORT”)rntif port == “” {rnttport = “8080”rnttlog.Printf(“defaulting to port %s”, port)rnt}rnrnt// Start HTTP server.rntlog.Printf(“listening on port %s”, port)rntif err := http.ListenAndServe(“:”+port, nil); err != nil {rnttlog.Fatal(err)rnt}rn}rnrnfunc handler(w http.ResponseWriter, r *http.Request) {rnt// Connect to Cloud Spanner through PGAdapter.rnt// Use a fully qualified database name, as PGAdapterrnt//is started without any project, instance or database options.rntconnString := fmt.Sprintf(“host=%s port=%s database=%s”,rnttdatabaseHost, databasePort, qualifiedDatabaseName)rntctx := context.Background()rntconn, err := pgx.Connect(ctx, connString)rntif err != nil {rnttfmt.Fprintf(w, “failed to connect to PGAdapter: %v\n”, err)rnttreturnrnt}rntdefer conn.Close(ctx)rnrnt// Execute a query on Cloud Spanner.rntvar greeting stringrnterr = conn.QueryRow(ctx, “select ‘Hello world!'”).Scan(&greeting)rntif err != nil {rnttfmt.Fprintf(w, “failed to query Cloud Spanner: %v\n”, err)rnttreturnrnt}rntfmt.Fprintf(w, rntt”\nGreeting from Cloud Spanner PostgreSQL using pgx: %v\n\n”,rnttgreeting)rn}rnrnfunc getenv(key, def string) string {rntvalue := os.Getenv(key)rntif value == “” {rnttreturn defrnt}rntreturn valuern}’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5fa47728b0>)])]>

The full sample code can be found here.

Similar samples for other programming languages can be found here:

C# .NETJavaNode.jsPythonRuby

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.

Cloud BlogRead More

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments