Configure PostgreSQL extensions

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 are supported by Cloud SQL. For more information, see PostgreSQL extensions supported by Cloud SQL.

Install an extension

Before using an extension, install it:

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.

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.

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 3.0 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 2.3.11
PostgreSQL 10 2.4.9, 3.1.4
PostgreSQL 11 2.5.5, 3.1.4
PostgreSQL 12 3.1.4
PostgreSQL 13 3.1.4
PostgreSQL 14 3.1.4

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:

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 manually upgrade PostGIS and its related extensions to their latest version. 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.
Cloud SQL for 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.
Cloud SQL for 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.
Cloud SQL for PostgreSQL 9.6 and 10 use version 1.0. Not supported for other versions.
citext Provides a case-insensitive character string type citext.
Cloud SQL for 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.
Cloud SQL for 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.
Cloud SQL for 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.
Cloud SQL for 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.
Cloud SQL for PostgreSQL uses version 2.4.
ltree Implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure.
Cloud SQL for 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.1.2, which in turn uses V8 JavaScript engine version 9.9.

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.
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.
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.
Cloud SQL for PostgreSQL 9.6, 10, 11, and 12 use version 1.2. PostgreSQL 13 uses version 1.3. PostgreSQL 14 uses version 1.5.
pageinspect Inspects the contents of database pages at a low level. Also see pageinspect in this document.
Cloud SQL for 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 READ, WRITE, FUNCTION, ROLE, DDL, MISC, and ALL. For versions 1.4.2 through 1.6.1, you can also set the value of MISC_SET.

For more information about using this extension with Cloud SQL, see Audit for PostgreSQL using pgAudit.
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.
Cloud SQL for 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.
Cloud SQL for PostgreSQL uses version 10 (or higher).
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.1.
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.2.
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_partman Enables you to create and manage time-based and serial-based table partition sets. Also see pg_partman in this document.
Cloud SQL for PostgreSQL uses version 4.5.1.
pg_prewarm Provides a convenient way to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache.
Cloud SQL for 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.4.7.
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.
Cloud SQL for 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_stat_statements Provides a means for tracking execution statistics of all SQL statements executed.
Cloud SQL for 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.1.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.
Cloud SQL for 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. For more information, see the pgtt section.

Cloud SQL for PostgreSQL uses version 2.9.0.
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.
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.10.0.
postgres_fdw Allows foreign data wrappers to be created within instances or across instances. Also see postgres_fdw in this document.
Cloud SQL for 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.
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.
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.

Alternatively, for a user that has the cloudsqlsuperuser role (only), you can use the load command to load this extension for one session.

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.

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');

To connect to the same instance as the same user, you can connect without a password. For example:

  1. Set the following database flag to enable local connections without a password
    cloudsql.allow_passwordless_local_connections

  2. 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:

  1. In the psql tool, run the CREATE EXTENSION command for pg_proctab.
  2. Download and run pg_top.
  3. 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:

  1. Connect to testdb as the cloudsqlsuperuser:

    psql -U csuper1 -d testdb;

  2. Grant the database owner, which is testuser, to csuper1:

    GRANT testuser TO csuper1;

  3. Create the extension:

    CREATE EXTENSION pg_repack;

  4. Run the pg_repack command on a table, such as t1 in testdb, as csuper1:

    pg_repack -h <hostname> -d testdb -U csuper1 -k -t t1

  5. 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).

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, 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.

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.