Connect psycopg3 to a PostgreSQL-dialect database

This page explains how to connect the PostgreSQL psycopg3 driver to a PostgreSQL-dialect database in Spanner. psycopg3 is a Python driver for PostgreSQL.

  1. Verify that PGAdapter is running on the same machine as the application that is connecting using the PostgreSQL psycopg3 driver.

    export GOOGLE_APPLICATION_CREDENTIALS=/CREDENTIALS_FILE_PATH/credentials.json
docker pull gcr.io/cloud-spanner-pg-adapter/pgadapter
docker run \
  -d -p 5432:5432 \
  -v ${GOOGLE_APPLICATION_CREDENTIALS}:${GOOGLE_APPLICATION_CREDENTIALS}:ro \
  -e GOOGLE_APPLICATION_CREDENTIALS \
  gcr.io/cloud-spanner-pg-adapter/pgadapter \
  -p PROJECT_NAME -i INSTANCE_NAME \
  -x

    For more information, see Start PGAdapter.

  2. Connect to PGAdapter using TCP.

    import psycopg

with psycopg.connect("host=APPLICATION_HOST port=PORT dbname=DATABASE_NAME sslmode=disable") as conn:
  conn.autocommit = True
  with conn.cursor() as cur:
    cur.execute("select 'Hello world!' as hello")
    print("Greeting from Cloud Spanner PostgreSQL:", cur.fetchone()[0])

    Replace the following:

    • APPLICATION_HOST: the hostname or IP address of the machine where PGAdapter is running. If running locally, use localhost.
    • PORT: the port number where PGAdapter is running. Change this in the connection string if PGAdapter is running on a custom port. Otherwise, use the default port, 5432.

Unix ___domain sockets

This section explains how to use Unix ___domain sockets to connect to a PostgreSQL-dialect database. Use Unix ___domain sockets for the lowest possible latency.

To use Unix ___domain sockets, PGAdapter must be running on the same host as the client application.

Verify the PostgreSQL JDBC driver is loaded.

import psycopg

with psycopg.connect("host=/tmp
                      port=PORT
                      dbname=DATABASE_NAME") as conn:
conn.autocommit = True
with conn.cursor() as cur:
  cur.execute("select 'Hello world!' as hello")
  print("Greetings from Cloud Spanner PostgreSQL:", cur.fetchone()[0])

Replace the following:

  • /tmp: the default ___domain socket directory for PGAdapter. This can be changed using the -dir command-line argument.
  • PORT: the port number where PGAdapter is running. Change this in the connection string if PGAdapter is running on a custom port. Otherwise, use the default port, 5432.

What's next