PostgreSQL extensions

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:

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 other 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:

  • postgis
  • postgis_tiger_geocoder
  • postgis_topology

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.
cube Implements a data type cube for representing multidimensional cubes.
hstore Implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value.
isn Provides data types for some international product numbering standards.
ltree Implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure.
lo Support for managing Large Objects (also called LOs or BLOBs).
postgresql-hll Introduces a new data type, hll, which is a HyperLogLog data structure. Also see the postgresql-hll section below.

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.

Miscellaneous extensions

Extension Description
dict_int An add-on dictionary template for full-text search that controls the indexing of integers.
dict_xsyn An add-on dictionary template for full-text search that replaces words with groups of their synonyms.
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.
intarray Provides a set of functions and operators for manipulating null-free arrays of integers and performing indexed searches on them.
pageinspect Inspects the contents of database pages at a low level. Also see the pageinspect section below.
pg_buffercache Provides a means for examining what's happening in the shared buffer cache in real time.
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.
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.
pg_prewarm Provides a convenient way to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache.
pg_stat_statements Provides a means for tracking execution statistics of all SQL statements executed.
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.
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.
tablefunc Includes various functions that return tables (multiple rows).
tsm_system_rows Provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command.
tsm_system_time Provides the table sampling method SYSTEM_TIME, which can be used in the TABLESAMPLE clause of a SELECT command.
unaccent A text search dictionary that removes accents (diacritic signs) from lexemes.
uuid-ossp Provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms.

More about some PostgreSQL extensions

This section describes more information about some of the supported PostgreSQL extensions in the table above.

pageinspect

This extension inspects the contents of database pages at a low level. Please review the documentation for version-specific details.

pgfincore

This extension contains functions for managing pages in operating system disk cache memory from PostgreSQL. Please review the documentation for version-specific details.

pg_freespacemap

This extension examines the free space map (FSM). Please 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:

  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. Revoke the grant that you performed in Step 2:

    revoke testuser from csuper1;
    
  5. 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
    

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

Please 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 other 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.

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. Please 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 other 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. Please review the documentation for version-specific details.