Google Cloud Spanner PGAdapter

PGAdapter is a proxy that translates the PostgreSQL wire-protocol into the equivalent for Spanner databases that use the PostgreSQL interface. It enables you to use standard PostgreSQL drivers and tools with Cloud Spanner and is designed for the lowest possible latency.

Note: JVM-based applications can add PGAdapter as a compile-time dependency and run the proxy in the same process as the main application. See samples/java/jdbc for a small sample application that shows how to do this.

Drivers and Clients

PGAdapter can be used with the following drivers and clients:

1. psql: Versions 11, 12, 13 and 14 are supported. See psql support for more details.
2. IntelliJ, DataGrip and other JetBrains IDEs. See Connect Cloud Spanner PostgreSQL to JetBrains for more details.
3. JDBC: Versions 42.x and higher are supported. See JDBC support for more details.
4. R2DBC: Versions 1.0.7 and higher are supported. See R2DBC sample for a small sample application.
5. pgx: Version 4.15 and higher are supported. See pgx support for more details.
6. psycopg2: Version 2.9.3 and higher are supported. See psycopg2 for more details.
7. psycopg3: Version 3.1.x and higher are supported. See psycopg3 support for more details.
8. connectorx: Version 0.3.3 and higher have experimental support. See connectorx sample for more details.
9. node-postgres: Version 8.8.0 and higher are supported. See node-postgres support for more details.
10. npgsql: Version 6.0.x and higher have experimental support. See npgsql support for more details.
11. PDO_PGSQL: The PHP PDO driver has experimental support. See PHP PDO for more details.
12. postgres_fdw: The PostgreSQL foreign data wrapper has experimental support. See Foreign Data Wrapper sample for more details.

ORMs, Frameworks and Tools

PGAdapter can be used with the following frameworks and tools:

1. Hibernate: Version 5.3.20.Final and higher are supported. See hibernate support for more details.
2. Spring Data JPA: Spring Data JPA in combination with Hibernate is also supported. See the Spring Data JPA Sample Application for a full example.
3. Liquibase: Version 4.12.0 and higher are supported. See Liquibase support for more details. See also this directory for a sample application using Liquibase.
4. gorm: Version 1.23.8 and higher are supported. See gorm support for more details. See also this directory for a sample application using gorm.
5. SQLAlchemy 2.x: Version 2.0.1 and higher are supported. See also this directory for a sample application using SQLAlchemy 2.x.
6. SQLAlchemy 1.x: Version 1.4.45 and higher has experimental support. It is recommended to use SQLAlchemy 2.x instead of SQLAlchemy 1.4.x for the best possible performance. See also this directory for a sample application using SQLAlchemy 1.x.
7. pgbench can be used with PGAdapter, but with some limitations. See pgbench.md for more details.
8. Ruby ActiveRecord: Version 7.x has experimental support and with limitations. Please read the instructions in PGAdapter - Ruby ActiveRecord Connection Options carefully for how to set up ActiveRecord to work with PGAdapter.
9. Knex.js query builder can be used with PGAdapter. See Knex.js sample application for a sample application.
10. Sequelize.js ORM can be used with PGAdapter. See Sequelize.js sample application for a sample application.
11. Prisma ORM can be used with PGAdapter. See Prisma sample application for a sample application.

FAQ

See Frequently Asked Questions for answers to frequently asked questions.

Performance

See Latency Comparisons for benchmark comparisons between using PostgreSQL drivers with PGAdapter and using native Cloud Spanner drivers and client libraries.

Insights

See OpenTelemetry in PGAdapter for how to use OpenTelemetry to collect and export traces to Google Cloud Trace.

Usage

PGAdapter can be started both as a Docker container, a standalone process as well as an in-process server (the latter is only supported for Java and other JVM-based applications).

Docker

• See running PGAdapter using Docker for more examples for running PGAdapter in Docker.
• See running PGAdapter as a sidecar proxy for how to run PGAdapter as a sidecar proxy in a Kubernetes cluster.

Replace the project, instance and database names and the credentials file in the example below to run PGAdapter from a pre-built Docker image.

docker pull gcr.io/cloud-spanner-pg-adapter/pgadapter
docker run \
  -d -p 5432:5432 \
  -v /path/to/credentials.json:/credentials.json:ro \
  gcr.io/cloud-spanner-pg-adapter/pgadapter \
  -p my-project -i my-instance -d my-database \
  -c /credentials.json -x

The -x argument turns off the requirement that all TCP connections must come from localhost. This is required when running PGAdapter in a Docker container.

See Options for an explanation of all further options.

Distroless Docker Image

We also publish a distroless Docker image for PGAdapter under the tag gcr.io/cloud-spanner-pg-adapter/pgadapter-distroless. This Docker image runs PGAdapter as a non-root user.

docker pull gcr.io/cloud-spanner-pg-adapter/pgadapter-distroless
docker run \
  -d -p 5432:5432 \
  -v /path/to/credentials.json:/credentials.json:ro \
  gcr.io/cloud-spanner-pg-adapter/pgadapter-distroless \
  -p my-project -i my-instance -d my-database \
  -c /credentials.json -x

Standalone with pre-built jar

A pre-built jar and all dependencies can be downloaded from https://storage.googleapis.com/pgadapter-jar-releases/pgadapter.tar.gz

wget https://storage.googleapis.com/pgadapter-jar-releases/pgadapter.tar.gz \
  && tar -xzvf pgadapter.tar.gz
java -jar pgadapter.jar -p my-project -i my-instance -d my-database

Use the -s option to specify a different local port than the default 5432 if you already have PostgreSQL running on your local system.

<!--- {x-version-update-start:google-cloud-spanner-pgadapter:released} -->

You can also download a specific version of the jar. Example (replace v0.53.3 with the version you want to download):

VERSION=v0.53.3
wget https://storage.googleapis.com/pgadapter-jar-releases/pgadapter-${VERSION}.tar.gz \
  && tar -xzvf pgadapter-${VERSION}.tar.gz
java -jar pgadapter.jar -p my-project -i my-instance -d my-database

<!--- {x-version-update-end} -->

See Options for an explanation of all further options.

Standalone with locally built jar

1. Build a jar file and assemble all dependencies by running

mvn package -P assembly

2. Execute (the binaries are in the target/pgadapter folder)

cd target/pgadapter
java -jar pgadapter.jar -p my-project -i my-instance -d my-database

See Options for an explanation of all further options.

In-process

This option is only available for Java/JVM-based applications.

1. Add google-cloud-spanner-pgadapter as a dependency to your project by adding this to your pom.xml file:

<!--- {x-version-update-start:google-cloud-spanner-pgadapter:released} -->

<!-- [START pgadapter_dependency] -->
<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-spanner-pgadapter</artifactId>
  <version>0.53.3</version>
</dependency>
<!-- [END pgadapter_dependency] -->

<!--- {x-version-update-end} -->

2. Build a server using the com.google.cloud.spanner.pgadapter.ProxyServer class:

class PGProxyRunner {
  public static void main(String[] args) {
      OptionsMetadata.Builder builder =
          OptionsMetadata.newBuilder()
              .setProject("my-project")
              .setInstance("my-instance")
              .setDatabase("my-database")
              .setCredentialsFile("/path/to/credentials.json")
              // Start PGAdapter on any available port.
              .setPort(0);
      ProxyServer server = new ProxyServer(builder.build());
      server.startServer();
      server.awaitRunning();
    }
}

See samples/java/jdbc for a small sample application that adds PGAdapter as a compile-time dependency and runs it together with the main application.

Emulator

A pre-built Docker image that contains both PGAdapter and the Spanner Emulator can be started with these commands:

docker pull gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator
docker run \
  -d -p 5432:5432 \
  gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator
sleep 2
psql -h localhost -p 5432 -d test-database

This Docker container configures PGAdapter to connect to a Cloud Spanner Emulator running inside the same container. You do not need to first create a Spanner instance or database on the Emulator before connecting to them. Instead, the instance and database are automatically created on the Emulator when you connect to PGAdapter.

Additional Information

See this document for more information on how to connect PGAdapter to the Cloud Spanner Emul