You can extend PostgreSQL by bundling SQL objects into a package and using themas a unit. This page contains information about configuring the PostgreSQLextensions that Cloud SQL supports.

Use PostgreSQL extensions

You can install only the extensions that Cloud SQL supports. Formore information, seePostgreSQL extensions supported by Cloud SQL.

• In the psql tool, run theCREATE EXTENSIONcommand.

Requirement for superuser privileges

In Cloud SQL, extensions can only be created by users that are part of thecloudsqlsuperuser role. When you create a new PostgreSQL instance,the defaultpostgres user is created for you (although youmust set the user's password). The defaultpostgres user is part of thecloudsqlsuperuser role. For more information, seeAbout PostgreSQL users.

To request support for an extension, click+1 on the open issue or create anew issue. For a list of open Cloud SQL issues and information aboutcreating new issues, seeSearch 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 inone of the tables below.

Cloud SQL support for PostgreSQL extensions can be divided into thesecategories:

• PostGIS extensions
• Data type extensions
• Language extensions
• Miscellaneous extensions

PostGIS

ThePostGIS extension is supportedfor Cloud SQL for PostgreSQL for all major versions.

The following table contains the PostGIS extension versions for eachCloud SQL for PostgreSQL version:

For a specific PostgreSQL major version, in theCREATE EXTENSION command, you canspecify a PostGIS extension version by using theVERSION 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, seePostGIS Installation.

Additionally, Cloud SQL for PostgreSQL includes version 3.6.2 of thepgRouting extension, which extends PostGIS.ThepgRouting extension enhances geospatial processing through network routingand analysis.

You can upgrade PostGISand its related extensions to their latest version manually.To learn more about upgrading your PostGIS extensions, seeUpgrading PostGIS.

Data type extensions

Language extensions

Miscellaneous extensions

More about some PostgreSQL extensions

This section describes more information about some of the supported PostgreSQLextensions in the tables above.

auto_explain

To begin using this extension on an instance, set thecloudsql.enable_auto_explain flag toon. For information about settingflags, and to review the flags supported for this extension, seeConfigure database flags.

To view logging data and events that are associated with the logs of the execution plans that this extension enables, see theCloud Logging documentation.

Alternatively, for a user that has thecloudsqlsuperuser role (only), you canuse theload command to load this extension for one session.

dblink

From within a database session, you can use this extension to connect toPostgreSQL databases and execute queries.

Currently, this extension works for two Cloud SQL private IP instances withinthe same VPC network, or for cross databases within the same instance.

For additional information, seedblink in the PostgreSQLdocumentation.

Use dblink to connect with a password

To connect to databases, or to connect to the same instance as anotheruser, you must specify a password. For example purposes(but not for production purposes), here is a code snippet:

SELECT*FROMdblink('dbname=name port=1234 host=host user=user password=password','select id, name from table'\)ASt(idint,nametext);

Alternatively, to only set up a connection, here is a code snippet for examplepurposes (not for production purposes):

SELECTdblink_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 apassword. For example:

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

2. Connect without specifying a host, which implies connecting to the sameinstance. Here is an example:

   SELECT*FROMdblink('dbname=finance user=alice','select income from revenue')ASrevenue(incomeinteger);

   The result could be similar to the following:

   income--------1000(1row)

To connect to databases within the same instance,you cannot set host to localhost or to127.0.0.1. Instead,you must use the IP address shown for your instance in the Google Cloud console.

Also seepostgres_fdw andPL/Proxy in thisdocument.

pageinspect

This extension inspects the contents of database pages at a low level.For more information, seepageinspectin the PostgreSQL documentation.

pg_bigm

This extension enables full-text search, and allows a two-gram (bigram) indexfor faster full-text search.

To begin using this extension on an instance, set thecloudsql.enable_pg_bigm flag toon. 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 thisextension, seeConfigure database flags.

pg_cron

To begin usingpg_cron on aninstance, set thecloudsql.enable_pg_cron flag toon. For information aboutsetting flags, and to review the flags supported for this extension, seeConfigure database flags.

The jobs are configured as background workers, so you might need touse standard PostgreSQL techniques(such as themax_worker_processes flag) to adjust the number ofthose background workers.

For this extension, Cloud SQL supports the background worker mode, but notthe libpq interface. Therefore, trust authentication directlyinvolving this extension is not required.

pgfincore

This extension contains functions for managing pages in operating system diskcache memory from PostgreSQL. For more information, see thepgfincore documentation.

pg_freespacemap

This extension examines the free space map (FSM). For more information, seepg_freespacemap in thePostgreSQL documentation.

pg_hint_plan

To begin using this extension on an instance, set thecloudsql.enable_pg_hint_plan flag toon. For information about settingflags, and to review the flags supported for this extension, seeConfigure database flags.

Alternatively, for only a user with thecloudsqlsuperuser role, you canuse theload command to load this extension for one session.

pg_partman

This extension enables you to create and manage time-based and serial-basedtable partition sets.

In Cloud SQL, this extension doesn't include the background worker for automaticpartition maintenance. Instead, you can use, for example,Cloud Scheduler to drive maintenanceby callingmaintenance functionsat a regular interval.

pg_proctab

Here are the steps to use thepg_proctab extensionto enable thepg_top utility:

1. In the psql tool, run theCREATE EXTENSION command forpg_proctab.
2. Download and runpg_top.
3. When connecting to a Cloud SQL for PostgreSQL instance, add the-r option for connecting to a remote database, for gettingmetrics.

The following instance-wide metrics, which are included in the output, includeusage 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 clusterindex). For more information, see thepg_repack documentation. Additionally, foruse of this extension in Cloud SQL, a special procedure is needed foradding privileges to a user.

If a user outside of thecloudsqlsuperuser role wants to use an extension,you must grant the usercloudsqlsuperuser privileges. For more information, inthis document, seeRequirement for superuser privileges.The following example uses theGRANT command to add the necessary privileges.

Example of adding privileges

For example purposes below,csuper1 is acloudsqlsuperuser andtestdb is adatabase owned bytestuser. To create the pg_repack extension intestdb,initially run the following commands:

1. Connect totestdb as thecloudsqlsuperuser:

   psql-Ucsuper1-dtestdb;

2. Grant the database owner, which istestuser, tocsuper1:

   GRANTtestuserTOcsuper1;

3. Create the extension:

   CREATEEXTENSIONpg_repack;

4. Run thepg_repack command on a table, such ast1 intestdb, ascsuper1:

   pg_repack-h<hostname>-dtestdb-Ucsuper1-k-tt1

5. After the pg_repack job is completed, revoke the grant that you performed in step 2:

   REVOKEtestuserFROMcsuper1;

Thepg_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 runthepg_repack command. For more information, seeConnections 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 thecloudsql.enable_pg_squeeze flag toon.

This extension also supports logical decoding by writing additional information to the write-ahead log (WAL). To enable this functionality for the extension, set thewal_level configuration parameter tological or higher by setting thecloudsql.logical_decoding flag toon.

The extension requires a value to be set for themax_replication_slots flag. If you haven't specified a value for this flag, then set the value to1. 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, seeConfigure database flags.

pgtt

To begin using this extension on an instance, set thepgtt.enabled flag toon. For information about settingflags, and to review the flags supported for this extension, seeConfiguring database flags.

pg_visibility

This extension provides a way to examine the visibility map (VM) andthe page-level visibility information of a table. For more information, seepg_visibility in thePostgreSQL documentation.

PL/Proxy

This extension is a procedural language handler that allows remote procedurecalls among PostgreSQL databases, with optional sharding.

For more information, see thePL/Proxy documentation.

The target instances for connections must be in the same VPC network as theconnecting instance. Additionally, you cannot choose, in theGoogle Cloud console, theAllow only SSL connections button forcluster instances.

To connect to databases within the same instance,you cannot set host to localhost or to127.0.0.1. Instead,you must use the IP address shown for your instance in the Google Cloud console.

Also seepostgres_fdw anddblink in this document.

postgresql_anonymizer

To begin using this extension on an instance, set thecloudsql.enable_anon flag toon. For information about settingflags, and to review the flags supported for this extension, seeConfiguring database flags.

postgres_fdw

This extension allows tables from other ("foreign") PostgreSQL databasesto be exposed as "foreign" tables in the current database. Those tables then areavailable for use, almost as if they were local tables. For more information,seepostgres_fdw in thePostgreSQL documentation.

This extension works for two Cloud SQL private IP instances within thesame VPC network, or for cross databases within the same instance.

To connect to databases within the same instance, you cannot set host tolocalhost or to127.0.0.1. Instead, you must use the IP addressshown for your instance in the Google Cloud console.

Additionally, for an instance where foreign data is stored,you can choose theAllow only SSL connections option in the Google Cloud console.You can't choose theRequire trusted client certificates option.Only thecloudsqlsuperuser can be the ownerof apostgres_fdw foreign data wrapper.

Also seePL/Proxy anddblink in this document.

postgresql-hll

This extension introduces a new data type,hll, which is a HyperLogLog datastructure. For more information, see thepostgresql-hll documentation.