PostgreSQL provides a way to extend the functionality of a database by bundling SQL objects into a package and using them as a unit. This page describes the PostgreSQL extensions and describes the extensions supported by Cloud SQL.
Using PostgreSQL extensions
You can install only the extensions that are supported by Cloud SQL.
Installing an extension
Before using an extension, install it:
- In the psql tool, run the CREATE EXTENSION command.
Requirement for superuser privileges
In Cloud SQL, extensions can only be created by users that are part of the
cloudsqlsuperuser
role. When you create a new PostgreSQL instance,
the default postgres
user is created for you (although you
must set the user's password). The default postgres
user is part of the
cloudsqlsuperuser
role. For more information, see
PostgreSQL users.
Inter-database connections
The target instances for connections must be in the same VPC network as the connecting instance.
You cannot choose, in the Google Cloud Console, the
Allow only SSL connections button for cluster
instances. Additionally, to connect to databases within the same instance,
you cannot set host to
localhost or to 127.0.0.1
. Instead, you must use the IP address
shown for your instance in the Google Cloud Console.
Requesting support for a new extension
You cannot create your own extensions in Cloud SQL.
To request support for an extension, add a Me, too! vote for its issue in
the Issue Tracker
(under Cloud SQL
), or create a new issue there.
PostgreSQL extensions supported by Cloud SQL
For information about using a specific extension, see the documentation link in one of the tables below.
Cloud SQL support for PostgreSQL extensions can be divided into these categories:
PostGIS
The PostGIS 2.3 extension family and the PostGIS 3.0 extension are supported for Cloud SQL, including JSON-C support. These include the following extensions:
PostgreSQL 9.6 does not support postgis
. PostgreSQL 11 uses version 2.5.1.
PostgreSQL 12 and 13 use version 3.0.0.
The postgis_sfcgal
extension is not supported.
For more information, see PostGIS Installation.
Data type extensions
Extension | Description |
btree_gin | Provides sample GIN operator classes that implement B-tree equivalent behavior. |
btree_gist | Provides GiST index operator classes that implement B-tree equivalent behavior. |
chkpass | Implements a data type chkpass that is designed
for storing encrypted passwords. Not supported for PostgreSQL 12 for
Cloud SQL.
|
citext | Provides a case-insensitive character string type citext .PostgreSQL 9.6 uses version 1.3. PostgreSQL 11 uses version 1.5. PostgreSQL 12 and 13 use version 1.6. |
cube | Implements a data type cube for representing multidimensional cubes. PostgreSQL 9.6 uses version 1.2. PostgreSQL 11 uses version 1.3. PostgreSQL 12 and 13 use
version 1.4.
|
hstore | Implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. PostgreSQL 9.6 uses version 1.4. PostgreSQL 11 uses version 1.5. PostgreSQL 12 and 13 uses version 1.6.
|
isn | Provides data types for some international product numbering standards. PostgreSQL 9.6 uses version 1.1. PostgreSQL 11, 12, and 13 use version 1.2. |
ip4r | Provides data types for IPv4/v6 addresses, and address ranges, plus index support. |
ltree | Implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. PostgreSQL 9.6, 11, and 12 use version 1.1. PostgreSQL 13 uses version 1.2.
|
lo | Support for managing Large Objects (also called LOs or BLOBs). PostgreSQL 9.6, 11, 12, and 13 use version 1.1. |
postgresql-hll | Introduces a new data type, hll , which is a HyperLogLog
data structure. Also see the
postgresql-hll section below. |
prefix | Provides prefix-matching, plus index support. |
Language extensions
Extension | Description |
plpgsql | A loadable procedural language for creating functions, procedures and triggers. You also can use this language for direct execution of code in DO blocks. PostgreSQL 9.6, 11, 12, and 13 use version 1. |
Miscellaneous extensions
Extension | Description |
dblink | Provides functions to connect to PostgreSQL databases from within a database session. Also see the dblink section below. |
dict_int | An add-on dictionary template for full-text search that controls the indexing of integers. |
earthdistance | Provides two approaches to calculating great circle distances on the surface of the Earth. |
fuzzystrmatch | Provides several functions to determine similarities and distance between strings. |
intagg | Provides an integer aggregator and an enumerator. PostgreSQL 9.6, 11, 12, and 13 use version 1.1. |
intarray | Provides a set of functions and operators for manipulating null-free arrays of integers and performing indexed searches on them. PostgreSQL 9.6, 11, and 12 use version 1.2. PostgreSQL 13 uses version 1.3. |
pageinspect | Inspects the contents of database pages at a low level. Also see the pageinspect section below. |
pgaudit | Collects audit data. For information about using this extension with Cloud SQL, see Auditing for PostgreSQL using pgAudit. |
pg_buffercache | Provides a means for examining what's happening in the shared buffer cache in real time. PostgreSQL 9.6 uses version 1.2. PostgreSQL 10, 11, 12, and 13 use version 1.3. |
pgfincore | A set of functions to manage pages in operating system disk cache memory from PostgreSQL. Also see the pgfincore section below. |
pg_freespacemap | Examines the free space map (FSM). Also see the pg_freespacemap section below. |
pgcrypto | Provides cryptographic functions for PostgreSQL. PostgreSQL 9.6, 11, 12, and 13 use version 1.3. |
pgstattuple | Provides various functions to obtain tuple-level statistics. Not supported in Cloud SQL for PostgreSQL 9.6. |
pg_repack | Lets you remove bloat from tables and indexes. Optionally, lets you perform an online CLUSTER (order tables by cluster index). Also see the pg_repack section below. |
pgrowlocks | Provides row locking information for the specified table. PostgreSQL 9.6, 11, 12, and 13 use version 1.2. |
pg_prewarm | Provides a convenient way to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache. PostgreSQL 9.6 uses version 1.1. PostgreSQL 10, 11, 12, and 13 use version 1.2. |
pg_stat_statements | Provides a means for tracking execution statistics of all SQL statements executed. PostgreSQL 9.6 uses version 1.4. PostgreSQL 11 uses version 1.6. PostgreSQL 12 uses 1.7. PostgreSQL 13 uses 1.8. |
pg_trgm | Provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings. PostgreSQL 9.6 uses version 1.3. PostgreSQL 11 and 12 use version 1.4. PostgreSQL 13 uses 1.5. |
pg_visibility | Provides a way to examine the visibility map (VM) and the page-level visibility information of a table. Also see the pg_visibility section below. |
PL/Proxy | Procedural language handler that allows remote procedure calls among PostgreSQL databases, with optional sharding. Also see the PL/Proxy section below. |
postgres_fdw | Allows foreign data wrappers to be created within instances or across instances. Also see the postgres_fdw section below. |
sslinfo | Provides information about the SSL certificate that the current client provided when it connected to the instance. PostgreSQL 9.6, 11, 12, and 13 use version 1.2. |
tablefunc | Includes various functions that return tables (multiple rows). PostgreSQL 9.6, 11, 12, and 13 use version 1. |
tsm_system_rows | Provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command. PostgreSQL 9.6, 11, 12, and 13 use version 1. |
tsm_system_time | Provides the table sampling method SYSTEM_TIME, which can be used in the TABLESAMPLE clause of a SELECT command. PostgreSQL 9.6, 11, 12, and 13 use version 1. |
unaccent | A text search dictionary that removes accents (diacritic signs) from lexemes. PostgreSQL 9.6, 11, 12, and 13 use version 1.1. |
uuid-ossp | Provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. PostgreSQL 9.6, 11, 12, and 13 use version 1.1. |
More about some PostgreSQL extensions
This section describes more information about some of the supported PostgreSQL extensions in the tables above.
dblink
From within a database session, you can use this extension to connect to PostgreSQL databases and execute queries.
Currently, this extension works for two Cloud SQL private IP instances within the same VPC network, or for cross databases within the same instance.
For additional information, see the documentation.
Using dblink to connect with a password
To connect to databases, or to connect to the same instance as another user, you must specify a password. For example purposes (but not for production purposes), here is a code snippet:
SELECT * FROM dblink (
'dbname=name port=1234 host=host user=user password=password',
'select id, name from table' \
) AS t(id int, name text);
Alternatively, to only set up a connection, here is a code snippet for example purposes (not for production purposes):
SELECT dblink_connect('dbname=dblinktest user=postgres host=name_or_ip password=xxx');
Using dblink to connect without a password
To connect to the same instance as the same user, you can connect without a password. For example:
Set the following database flag to enable local connections without a password
cloudsql.allow_passwordless_local_connections
Connect without specifying a host, which implies connecting to the same instance. Here is an example:
SELECT * FROM dblink('dbname=finance user=alice', 'select income from revenue') AS revenue(income integer);
The result could be similar to the following:
income -------- 1000 (1 row)
To connect to databases within the same instance,
you cannot set host to localhost or to 127.0.0.1
. Instead,
you must use the IP address shown for your instance in the Google Cloud Console.
Also see postgres_fdw and PL/Proxy.
pageinspect
This extension inspects the contents of database pages at a low level. Review the documentation for version-specific details.
pgfincore
This extension contains functions for managing pages in operating system disk cache memory from PostgreSQL. Review the documentation for version-specific details.
pg_freespacemap
This extension examines the free space map (FSM). Review the documentation for version-specific details.
pg_repack
This extension enables you to remove bloat from tables and indexes. Optionally, you can use this extension to perform an online CLUSTER (order tables by cluster index). Version-specific details are in the documentation. Additionally, for use of this extension in Cloud SQL, a special procedure is needed for adding privileges to a user.
As described above, when you create a
new PostgreSQL instance, the default postgres
user is created for you (but you
must set the password). That default postgres
user is part of the
cloudsqlsuperuser
role. If a user outside of cloudsqlsuperuser
wants to use
an extension, you must add the privileges. The following example uses the
grant
command to add the necessary privileges.
Example of adding privileges
For example purposes below, csuper1
is a cloudsqlsuperuser
and testdb
is a
database owned by testuser
. To create the pg_repack extension in testdb
,
initially run the following commands:
Connect to
testdb
as thecloudsqlsuperuser
:psql -U csuper1 -d testdb;
Grant the database owner, which is
testuser,
tocsuper1
:GRANT testuser TO csuper1;
Create the extension:
CREATE EXTENSION pg_repack;
Revoke the grant that you performed in Step 2:
REVOKE testuser FROM csuper1;
Run the
pg_repack
command on a table, such ast1
intestdb
, ascsuper1
:pg_repack -h <hostname> -d testdb -U csuper1 -k -t t1
The pg_repack
command may fail with the following error:
"ERROR: query failed: SSL SYSCALL error: EOF detected"
If that error occurs, try setting TCP keepalives to a smaller value and then run
the pg_repack
command. For more information, see
Connections from Compute Engine.
pg_visibility
This extension provides a way to examine the visibility map (VM) and the page-level visibility information of a table. Review the documentation for version-specific details.
PL/Proxy
This extension is a procedural language handler that allows remote procedure calls among PostgreSQL databases, with optional sharding.
Review the documentation for version-specific details. PL/Proxy 2.9 is supported in PostgreSQL 11 and 12.
The target instances for connections must be in the same VPC network as the connecting instance. Additionally, you cannot choose, in the Google Cloud Console, the Allow only SSL connections button for cluster instances.
To connect to databases within the same instance,
you cannot set host to localhost or to 127.0.0.1
. Instead,
you must use the IP address shown for your instance in the Google Cloud Console.
Also see postgres_fdw and dblink.
postgres_fdw
This extension allows tables from other ("foreign") PostgreSQL databases to be exposed as "foreign" tables in the current database. Those tables then are available for use, almost as if they were local tables. Review the documentation for version-specific details.
Currently, this extension works for two Cloud SQL private IP instances within the same VPC network, or for cross databases within the same instance.
To connect to databases within the same instance, you cannot set host to
localhost or to 127.0.0.1
. Instead, you must use the IP address
shown for your instance in the Google Cloud Console.
Additionally, you cannot choose, in the Google Cloud Console, the
Allow only SSL connections button for an
instance where foreign data is stored. Only cloudsqlsuperuser
can be the owner
of a postgres_fdw foreign data wrapper.
postgresql-hll
This extension introduces a new data type, hll
, which is a HyperLogLog data
structure. Review the
documentation for
version-specific details.