[MUSIC PLAYING]
SPEAKER: Google Cloud recently announced the new PostgreSQL
interface for Cloud Spanner.
This combines the power and reliability of Spanner
with the familiarity of Postgres.
Postgres is a well-established, open-source database
with a vibrant and growing ecosystem
that many development and ops teams are already
familiar with.
The Postgres interface allows organizations
to take advantage of Google's managed relational
database using standard tools and skills from the Postgres
ecosystem.
The interface supports Spanner's rich feature set, as well as
common Postgres data types and SQL syntax.
In this video, I will show you how easy
it is to set up a Spanner database with the PostgreSQL
interface, then integrate that into a Python application using
the open source Spanner client library.
And finally, I'll use the standard Postgres psql
command-line tool to explore the database.
First, let's create a new Spanner instance
using the Cloud Console.
I'm creating a small instance for this test with Spanner's
granular instance sizing.
I can easily scale that up later as needed without downtime
or rearchitecture.
A Spanner instance can host multiple databases.
If you already have an existing Spanner instance,
you will need to create a new database with the Postgres
dialect support.
When I create a database, I can choose
between Google Standard SQL and PostgreSQL as the dialect.
I'll select the PostgreSQL option.
Both dialects support the same set of capabilities.
It's important to mention that the Postgres interface does not
implement all of PostgreSQL functionality.
Rather, it provides Postgres syntax for existing Spanner
features to avoid having to learn new semantics when
you begin using Spanner.
So next I will create a users table.
I'll use Postgres DDL and data types.
Because it's just Postgres, this schema change
would run the same in any other Postgres environment.
So this makes applications written against the Postgre
interface more portable.
It's worth mentioning that you can use the gcloud command line
tool to create the database if you prefer.
Just make sure to specify the --database-dialect option.
Leveraging one of the supported Spanner drivers,
you can start writing applications
against the database we just created right away.
Besides the Spanner client library,
no additional software is needed.
For example, we have a Python application here
that connects to our database and leverages the PostgreSQL
interface.
I've downloaded the Spanner Python client library
and installed it via pip into my virtual environment.
In this simple application, I import the library
and define the instance and database.
Then I issue an insert into our users table
using familiar Postgres syntax.
For instance, I'm not having to specify a column list, like I
would with Spanner's dialect.
You'll also notice that I use the Postgres NOW() function
to populate the timestamp columns,
rather than the CURRENT_TIMESTAMP() function
available in Spanner's default SQL dialect.
I just showed how we could use the native Spanner
driver to run Postgres syntax SQL from a Python application.
But what about other tools, like psql, that don't know anything
about Spanner?
For those, we provided a proxy called PGAdapter
that translates the Postgres wire protocol into Spanner's
own gRPC-based protocol.
To set it up, you will need to authenticate
with the gloud command-line tool to generate
the credentials required to connect to your Spanner
instance.
Then download the pgadapter.jar file and run it.
That's it.
With PGAdapter running, the only thing left
is to connect with psql.
I can use psql syntax to list out the tables.
Now I can create a unique index on the email field in our Users
table, again leveraging Postgres syntax.
Next, I want to query the information schema.
You can see that the Postgres data types
are displayed for the columns.
Initially, PGAdapter is only provided for development
scenarios with psql, but we'd like
to support a broader range of Postgres tools eventually.
So that's how easy it is to set up a Spanner
instance with the new PostgreSQL interface
and get started using it in your application and with psql.
If you want Google's highly available and scalable managed
relational database and already know Postgres' SQL syntax,
this is for you.
The PostgreSQL interface for Spanner
is now available for preview.
You can sign up for the preview by filling out
the form in the description below.
Go ahead and try it out and let us know
what you think in the comments.
[MUSIC PLAYING]