You can extend PostgreSQL by bundling SQL objects into a package and using them as a unit. This page contains information about configuring the PostgreSQL extensions that Cloud SQL supports.
Use PostgreSQL extensions
You can install only the extensions that Cloud SQL supports. For more information, see PostgreSQL extensions supported by Cloud SQL.
Install 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
About PostgreSQL users.
Inter-database connections
The target instances for connections must be in the same VPC network as the connecting instance.
For cluster instances, you can't choose the
Require trusted client certificates option in the
Google Cloud console.
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.
Request support for a new extension
You cannot create your own extensions in Cloud SQL.
To request support for an extension, click +1 on the open issue or create a new issue. For a list of open Cloud SQL issues and information about creating new issues, see Search for or create issues and feature requests, by product.
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 extension is supported for Cloud SQL for PostgreSQL for all major versions.
The following table contains the PostGIS extension versions for each Cloud SQL for PostgreSQL version:
Cloud SQL for PostgreSQL version | PostGIS extension |
PostgreSQL 9.6 | 3.2.5 |
PostgreSQL 10 | 3.2.5 |
PostgreSQL 11 | 3.2.5 |
PostgreSQL 12 | 3.2.5 |
PostgreSQL 13 | 3.4.0 |
PostgreSQL 14 | 3.4.0 |
PostgreSQL 15 | 3.4.0 |
PostgreSQL 16 | 3.4.0 |
PostgreSQL 17 | 3.4.3 |
For a specific PostgreSQL major version, in the
CREATE EXTENSION command, you can
specify a PostGIS extension version by using the VERSION
clause.
The PostGIS extension includes the following:
postgis
postgis_raster
postgis_sfcgal
postgis_tiger_geocoder
postgis_topology
address_standardizer
address_standardizer_data_us
For more information, see PostGIS Installation.
Additionally, Cloud SQL for PostgreSQL includes version 3.3.0 of the
pgRouting
extension, which extends PostGIS.
The pgRouting
extension enhances geospatial processing through network routing
and analysis.
You can upgrade PostGIS and its related extensions to their latest version manually. To learn more about upgrading your PostGIS extensions, see Upgrading PostGIS.
Data type extensions
Extension | Description |
btree_gin | Provides sample GIN operator classes that implement B-tree equivalent
behavior. PostgreSQL 9.6 uses version 1.0. PostgreSQL 10 uses version 1.2. All other versions use version 1.3. |
btree_gist | Provides GiST index operator classes that implement B-tree equivalent
behavior. PostgreSQL 9.6 uses version 1.2. PostgreSQL 10, 11, 12, and 13 use version 1.5. PostgreSQL 14 uses version 1.6. |
chkpass | Implements a data type chkpass that is designed
for storing encrypted passwords. PostgreSQL 9.6 and 10 use version 1.0. Not supported for other versions. |
citext | Provides a case-insensitive character string type citext .PostgreSQL 9.6 uses version 1.3. PostgreSQL 10 uses version 1.4. PostgreSQL 11 uses version 1.5. PostgreSQL 12, 13, and 14 use version 1.6. |
cube | Implements a data type cube for representing multidimensional cubes.PostgreSQL 9.6 and 10 use version 1.2. PostgreSQL 11 uses version 1.3. PostgreSQL 12 and 13 use version 1.4. PostgreSQL 14 uses version 1.5. |
hstore | Implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value.PostgreSQL 9.6 and 10 use version 1.4. PostgreSQL 11 uses version 1.5. PostgreSQL 12 uses version 1.6. PostgreSQL 13 uses version 1.7. PostgreSQL 14 uses version 1.8. |
isn | Provides data types for some international product numbering standards. PostgreSQL 9.6 and 10 use version 1.1. All other versions use version 1.2. |
ip4r | Provides data types for IPv4/v6 addresses, and address ranges, plus
index support. PostgreSQL uses version 2.4.2. |
ltree | Implements a data type ltree for representing labels of data
stored in a hierarchical tree-like structure. PostgreSQL 9.6, 10, 11, and 12 use version 1.1. PostgreSQL 13 and 14 use version 1.2. |
lo | Support for managing Large Objects (also called LOs or BLOBs). Cloud SQL for PostgreSQL uses version 1.1. |
postgresql-hll | Introduces a new data type, hll , which is a HyperLogLog
data structure. Also see postgresql-hll in
this document.Cloud SQL for PostgreSQL uses version 2.16. |
prefix | Provides prefix-matching, plus index support. Cloud SQL for PostgreSQL uses version 1.2.0. |
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. Cloud SQL for PostgreSQL uses version 1.0. |
plv8 | Provides a procedural language for enabling JavaScript. Cloud SQL for PostgreSQL uses version 3.2.2, which in turn uses V8 JavaScript engine version 11.5. |
Miscellaneous extensions
Extension | Description |
amcheck | Provides functions for verifying the logical consistency of the structure of relations, enabling the pg_amcheck application to check for corruption. |
auto_explain | Enables automatic logging of execution plans of slow statements, for troubleshooting and more. Provides an automated way to perform the functionality of the EXPLAIN command. Also see auto_explain in this document. |
autoinc | Provides functions for incrementing fields automatically. This trigger stores the next value of a sequence into an integer field. Cloud SQL for PostgreSQL uses version 1.0. |
bloom | Provides a method to access indexes based on bloom filters. A bloom filter is a space-efficient data structure that you can use to test whether an element is a member of a set. Cloud SQL for PostgreSQL uses version 1.0. |
dblink | Provides functions to connect to PostgreSQL databases from within
a database session. Also see
dblink in this document. Cloud SQL for PostgreSQL uses version 1.2. |
decoderbufs | A PostgreSQL logical decoder that delivers output data as Protocol Buffers, adapted for Debezium. |
dict_int | An add-on dictionary template for full-text search that controls the
indexing of integers. Cloud SQL for PostgreSQL uses version 1.0. |
earthdistance | Provides two approaches to calculating great circle distances on the
surface of the Earth. Cloud SQL for PostgreSQL uses version 1.1. |
fuzzystrmatch | Provides several functions to determine similarities and distance between
strings. Cloud SQL for PostgreSQL uses version 1.1. |
google_ml_integration | Generates vector embeddings to translate text prompts into numerical vectors using a large language model (LLM). Cloud SQL for PostgreSQL uses version 1.2. |
insert_username | Provides functions for storing the current user's name into a text field. You can use this to track who last modified a row in a database table. Cloud SQL for PostgreSQL uses version 1.0. |
intagg | Provides an integer aggregator and an enumerator. Cloud SQL for PostgreSQL uses 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, 10, 11, and 12 use version 1.2. PostgreSQL 13 uses version 1.3. PostgreSQL 14 uses version 1.5. |
moddatetime | Provides functions for storing the current time into a timestamp field. You can use this to track the last time that a row in a database table is modified.Cloud SQL for PostgreSQL uses version 1.0. |
oracle_fdw | Provides a foreign data wrapper (fdw) for accessing Oracle databases easily and efficiently. Cloud SQL for PostgreSQL uses version 1.2. |
orafce | Provides functions and operators that emulate a subset of functions and packages from an Oracle database. Use these functions to port Oracle applications to PostgreSQL. PostgreSQL 9.6 and 10 use version 4.6.1. PostgreSQL 11 and later use version 4.7.0. |
pageinspect | Inspects the contents of database pages at a low level. Also see
pageinspect in this document. PostgreSQL 9.6 uses version 1.5. PostgreSQL 10 uses version 1.6. PostgreSQL 11 and 12 use version 1.7. PostgreSQL 13 and 14 use version 1.8. |
pgAudit | Collects audit data.
Possible values to set for the audit log files for all versions of
pgAudit are For more information about using this extension with Cloud SQL, see Audit for PostgreSQL using pgAudit. |
pg_background | Lets you run arbitrary commands in a background worker. Cloud SQL for PostgreSQL uses version 1.2. |
pg_bigm | Enables full-text search, and allows a two-gram (bigram) index for faster full-text search. Also see pg_bigm in this document. |
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, 13, and 14 use version 1.3. |
pg_cron | Provides a cron-based job scheduler. This extension enables cron syntax
to schedule PostgreSQL commands directly from the database. For more
information about the extension, see pg_cron
in this document. PostgreSQL versions 10 and later use version 1.6.0. |
pgcrypto | Provides cryptographic functions for PostgreSQL. Cloud SQL for PostgreSQL uses version 1.3. |
pglogical | Provides logical replication for PostgreSQL. See
Setting up logical replication and decoding. Cloud SQL for PostgreSQL uses version 2.4.3. |
pgfincore | A set of functions to manage pages in operating system disk cache memory
from PostgreSQL. Also see
pgfincore in this document. Cloud SQL for PostgreSQL uses version 1.3.1. |
pg_freespacemap | Examines the free space map (FSM).
Also see pg_freespacemap in this document. Cloud SQL for PostgreSQL uses version 1.2. |
pg_hint_plan | Enables you to improve PostgreSQL execution plans using hints, which are simple descriptions in SQL comments. Also see pg_hint_plan in this document. |
pgoutput | This plugin is used by logical replication for change data capture.
pgoutput is a default plugin and is therefore supported on
all versions of Cloud SQL for PostgreSQL.
|
pg_ivm | Enables you to make materialized views up-to-date in which only
incremental changes are computed and applied on views rather than
recomputing the contents from scratch. PostgreSQL versions 13 and later support version 1.9. |
pg_partman | Enables you to create and manage time-based and serial-based table
partition sets. Also see
pg_partman in this document. PostgreSQL versions 14 and later support version 5.0.1 while PostgreSQL versions that are earlier than 14 support only up to version 4.7.4. |
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 and 10 use version 1.1. PostgreSQL 11, 12, 13, and 14 use version 1.2. |
pg_proctab | Enables you to use pg_top with Cloud SQL for PostgreSQL, and generate reports from the operating system process table. Also see pg_proctab in this document. |
pg_repack | Lets you remove bloat from tables and indexes.
Optionally, lets you perform an online CLUSTER (order tables by cluster
index). Also see pg_repack in this document. Cloud SQL for PostgreSQL uses version 1.5.0. |
pgrowlocks | Provides row locking information for the specified table. Cloud SQL for PostgreSQL uses version 1.2. |
pgstattuple | Provides various functions to obtain tuple-level statistics. PostgreSQL 9.6 uses version 1.4. All other versions use version 1.5. |
pg_similarity | Provides support for similarity queries on PostgreSQL. Cloud SQL for PostgreSQL uses version 1.0. |
pg_squeeze | Removes unused space from a table and optionally uses an index to sort records or rows (tuples) of the table. For more information about this extension, see pg_squeeze in this document. PostgreSQL versions 11 and later use version 1.5. |
pg_stat_statements | Provides a means for tracking execution statistics of all SQL
statements executed. PostgreSQL 9.6 uses version 1.4. PostgreSQL 10 and 11 use version 1.6. PostgreSQL 12 uses 1.7. PostgreSQL 13 uses 1.8. PostgreSQL 14 uses 1.9. |
pgTAP | Provides a unit testing framework for PostgreSQL, written in PL/pgSQL
and PL/SQL. Cloud SQL for PostgreSQL uses version 1.3.0. |
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 and 10 use version 1.3. PostgreSQL 11 and 12 use version 1.4. PostgreSQL 13 uses 1.5. PostgreSQL 14 uses version 1.6. |
pgtt | Creates and manages DB2 or Oracle-style global temporary tables in a
PostgreSQL database. |
pgvector |
An open-source extension for storing and searching vector embeddings in PostgreSQL databases. |
pg_visibility | Provides a way to examine the visibility map (VM) and the page-level
visibility information of a table. Also see
pg_visibility in this document. Cloud SQL for PostgreSQL uses version 1.2. |
pg_wait_sampling | Collects sampling statistics of wait events, providing wait event data for processes on servers. Cloud SQL for PostgreSQL uses version 1.1.5. |
PL/Proxy | Procedural language handler that allows remote procedure calls among
PostgreSQL databases, with optional sharding.
Also see
PL/Proxy in this document. Cloud SQL for PostgreSQL uses version 2.11.0. |
postgres_fdw | Allows foreign data wrappers to be created within instances or across
instances. Also see
postgres_fdw in this document. PostgreSQL 9.6, 10, 11, 12, and 13 use version 1.0. PostgreSQL 14 uses version 1.1. |
postgresql_anonymizer | Masks or replaces personally identifiable information (PII) or sensitive data from a PostgreSQL database. For more information, see the postgresql_anonymizer section. Cloud SQL for PostgreSQL uses version 1.0.0. |
postgresql_hll | Provides a HyperLogLog ( Cloud SQL for PostgreSQL uses version 2.18. |
rdkit | A collection of cheminformatics and machine-learning software. You can use
this extension for comparing, manipulating, and identifying molecular structures.
Cloud SQL for PostgreSQL uses version 4.2.0. |
refint | Includes functions to check foreign key restraints, the referencing table, and the referenced table. This extension also demonstrates the Server Programming Interface (SPI). Cloud SQL for PostgreSQL uses version 1.0. |
sslinfo | Provides information about the SSL certificate that the current client
provided when it connected to the instance. Cloud SQL for PostgreSQL uses version 1.2. |
tablefunc | Includes various functions that return tables (multiple rows). Cloud SQL for PostgreSQL uses version 1.0. |
tcn | Provides a trigger function that notifies listeners of changes to the content of database tables. Cloud SQL for PostgreSQL uses version 1.0. |
temporal_tables | Provides support for temporal tables. A temporal table records the period of time when a row is valid from a database perspective. Cloud SQL for PostgreSQL uses version 1.2.2. |
tsm_system_rows | Provides the table sampling method SYSTEM_ROWS, which can be used in the
TABLESAMPLE clause of a SELECT command. Cloud SQL for PostgreSQL uses version 1.0. |
tsm_system_time | Provides the table sampling method SYSTEM_TIME, which can be used in the
TABLESAMPLE clause of a SELECT command. Cloud SQL for PostgreSQL uses version 1.0. |
unaccent | A text search dictionary that removes accents (diacritic signs) from
lexemes. Cloud SQL for PostgreSQL uses version 1.1. |
uuid-ossp | Provides functions to generate universally unique identifiers (UUIDs)
using one of several standard algorithms. Cloud SQL for PostgreSQL uses version 1.1. |
More about some PostgreSQL extensions
This section describes more information about some of the supported PostgreSQL extensions in the tables above.
auto_explain
To begin using this extension on an instance, set the
cloudsql.enable_auto_explain
flag to on
. For information about setting
flags, and to review the flags supported for this extension, see
Configure database flags.
To view logging data and events that are associated with the logs of the execution plans that this extension enables, see the Cloud Logging documentation.
Alternatively, for a user that has the cloudsqlsuperuser
role (only), you can
use the load
command to load this extension for one session.
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 dblink in the PostgreSQL documentation.
Use 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');
Use 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 in this document.
pageinspect
This extension inspects the contents of database pages at a low level. For more information, see pageinspect in the PostgreSQL documentation.
pg_bigm
This extension enables full-text search, and allows a two-gram (bigram) index for faster full-text search.
To begin using this extension on an instance, set the
cloudsql.enable_pg_bigm
flag to on
. The following flags also are supported:
pg_bigm.enable_recheck
pg_bigm.gin_key_limit
pg_bigm.similarity_limit
For information about setting flags, and to review the flags supported for this extension, see Configure database flags.
pg_cron
To begin using pg_cron on an
instance, set the cloudsql.enable_pg_cron
flag to on
. For information about
setting flags, and to review the flags supported for this extension, see
Configure database flags.
The jobs are configured as background workers, so you might need to
use standard PostgreSQL techniques
(such as the max_worker_processes
flag) to adjust the number of
those background workers.
For this extension, Cloud SQL supports the background worker mode, but not the libpq interface. Therefore, trust authentication directly involving this extension is not required.
pgfincore
This extension contains functions for managing pages in operating system disk cache memory from PostgreSQL. For more information, see the pgfincore documentation.
pg_freespacemap
This extension examines the free space map (FSM). For more information, see pg_freespacemap in the PostgreSQL documentation.
pg_hint_plan
To begin using this extension on an instance, set the
cloudsql.enable_pg_hint_plan
flag to on
. For information about setting
flags, and to review the flags supported for this extension, see
Configure database flags.
Alternatively, for only a user with the cloudsqlsuperuser
role, you can
use the load
command to load this extension for one session.
pg_partman
This extension enables you to create and manage time-based and serial-based table partition sets.
In Cloud SQL, this extension doesn't include the background worker for automatic partition maintenance. Instead, you can use, for example, Cloud Scheduler to drive maintenance by calling maintenance functions at a regular interval.
pg_proctab
Here are the steps to use the pg_proctab extension to enable the pg_top utility:
- In the psql tool, run the CREATE EXTENSION command for pg_proctab.
- Download and run pg_top.
- When connecting to a Cloud SQL for PostgreSQL instance, add the
-r
option for connecting to a remote database, for getting metrics.
The following instance-wide metrics, which are included in the output, include usage by other supporting agents and services in the instance:
- Load average
- CPU states (% user, nice, system, idle, and iowait)
- Memory (used, free, and cached)
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). For more information, see the pg_repack documentation. Additionally, for use of this extension in Cloud SQL, a special procedure is needed for adding privileges to a user.
If a user outside of the cloudsqlsuperuser
role wants to use an extension,
you must grant the user cloudsqlsuperuser
privileges. For more information, in
this document, see Requirement for superuser 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;
Run the
pg_repack
command on a table, such ast1
intestdb
, ascsuper1
:pg_repack -h <hostname> -d testdb -U csuper1 -k -t t1
After the pg_repack job is completed, revoke the grant that you performed in step 2:
REVOKE testuser FROM csuper1;
The pg_repack
command might 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 timeout (from Compute Engine).
pg_squeeze
This extension removes unused space from a table and optionally uses an index to sort tuples of the table. To begin using the extension on an instance, set the cloudsql.enable_pg_squeeze
flag to on
.
This extension also supports logical decoding by writing additional information to the write-ahead log (WAL). To enable this functionality for the extension, set the wal_level
configuration parameter to logical
or higher by setting the cloudsql.logical_decoding
flag to on
.
The extension requires a value to be set for the max_replication_slots
flag. If you
haven't specified a value for this flag, then set the value to 1
. Otherwise, add 1 to the value that's set for the flag.
For information about setting flags, and to review the flags supported for this extension, see Configure database flags.
pgtt
To begin using this extension on an instance, set the
pgtt.enabled
flag to on
. For information about setting
flags, and to review the flags supported for this extension, see
Configuring database flags.
pg_visibility
This extension provides a way to examine the visibility map (VM) and the page-level visibility information of a table. For more information, see pg_visibility in the PostgreSQL documentation.
PL/Proxy
This extension is a procedural language handler that allows remote procedure calls among PostgreSQL databases, with optional sharding.
For more information, see the PL/Proxy documentation.
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 in this document.
postgresql_anonymizer
To begin using this extension on an instance, set the
cloudsql.enable_anon
flag to on
. For information about setting
flags, and to review the flags supported for this extension, see
Configuring database flags.
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. For more information, see postgres_fdw in the PostgreSQL documentation.
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, for an instance where foreign data is stored,
you can choose the
Allow only SSL connections option in the Google Cloud console.
You can't choose the
Require trusted client certificates option.
Only the cloudsqlsuperuser
can be the owner
of a postgres_fdw
foreign data wrapper.
Also see PL/Proxy and dblink in this document.
postgresql-hll
This extension introduces a new data type, hll
, which is a HyperLogLog data
structure. For more information, see the
postgresql-hll documentation.