Extensions

This page describes the extensions provided by KakaoCloud PostgreSQL and key usage methods.

View and install extensions

KakaoCloud PostgreSQL provides major extensions by default. If you create a new database with the default template, you can use extensions registered in template1 without separate installation.

How extensions are provided by default

• Default extensions other than pg_cron are registered in the template1 database.
• pg_cron is registered in the default postgres database. For usage and restrictions, see pg_cron.
• If you create a database with a template other than template1, you must install the required extensions directly.

If direct installation is required, you can view and install available extensions as follows.

View available extensions

You can view available extensions as follows.

View available extensions

select * from pg_available_extensions ;

Install an extension

To install an extension, use create_extension from the user account that was created first.
create_extension exists in postgres.public, so you must connect to the postgres database before calling it.
If it is not run in the postgres database, a type error may occur.

Install extension using create_extension

# This example installs the orafce extension on a database created with template0.
# The user account in this example is assumed to be admin.

# 1. Create a new database named 'test' based on template0.
postgres=> create database test TEMPLATE template0 ;
CREATE DATABASE

# 2. Call the create_extension procedure in the postgres database. We recommend specifying each field type as shown in this example.
postgres=> \c postgres admin
postgres=> CALL create_extension('test'::text, 'orafce'::text);
NOTICE: Extension "orafce" added to database "test"
CALL

# 3. Verify the extension installed in the 'test' database.
postgres=> \c test admin
You are now connected to database "test" as user "admin".

test=> \dx
                                                  List of installed extensions
  Name   | Version |   Schema   |                                          Description
---------+---------+------------+-----------------------------------------------------------------------------------------------
 orafce  | 4.15    | public     | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Supported extensions

For more detailed information about extensions, see the PostgreSQL official Additional Extensions Documentation.

Name	Description
adminpack	Provides tools for managing and monitoring the server beyond the basic PostgreSQL features. Includes functions like pg_file_settings, pg_logdir_ls, pg_read_file, pg_stat_file, pg_file_write, pg_file_rename, and pg_file_unlink.
amcheck	Checks index consistency, especially useful for B-Tree indexes. Helps maintain data integrity and identify performance issues.
autoinc	Adds auto-increment columns easily, generating unique values for each row insert.
bloom	Implements Bloom filter indexing for faster queries on large datasets with multiple columns. Offers quick existence checks and space-efficient indexing.
intarray	Provides advanced operations and functions for manipulating integer arrays, useful for applications dealing with large sets of integer data.
btree_gin	Allows B-Tree operators to be used with GIN indexes, enabling efficient indexing for complex data types like arrays, JSONB, and Hstore.
btree_gist	Provides B-Tree operator support with GiST indexes, useful for sorting and comparison in array and range types.
citext	Adds case-insensitive text data type, ideal for storing user names, emails, etc., allowing for case-insensitive search and comparison.
cube	Supports multi-dimensional cube data types for geometric and analytical applications. Useful for spatial queries and multi-dimensional indexing.
dblink	Enables connections to other PostgreSQL databases for cross-database queries. Useful in distributed or federated systems.
dict_xsyn	Enhances full-text search by providing synonym dictionaries for consistent search results.
dict_int	Supports integer-based dictionaries for use with full-text search indexing.
earthdistance	Provides functions to calculate distances between geographic coordinates.
file_fdw	Foreign Data Wrapper for reading flat files such as CSV as if they were tables.
fuzzystrmatch	Provides algorithms for fuzzy string matching and comparison. Useful for typo detection and data cleansing.
hstore	Key-value pair storage within a single PostgreSQL column. Ideal for semi-structured data.
hstore_plperl	Enables Perl functions to process hstore data.
hstore_plperlu	Enables untrusted Perl functions to process hstore data.
hypopg	Hypothetical index simulator to test index impact without actual index creation.
insert_username	Automatically logs the username responsible for inserting or updating rows. Useful for audit trails.
intagg	Aggregates integer values into an array format, useful for grouped integer data.
isn	Handles International Standard Numbers like ISBN, ISMN, and ISSN.
jsonb_plperl	Supports manipulating JSONB types within PL/Perl functions.
jsonb_plperlu	Allows JSONB handling in untrusted Perl language environment (plperlu).
jsonb_plpython3u	Enables working with JSONB in untrusted Python 3 functions.
lo	Provides support for large object (LOB) storage and manipulation in the database.
ltree	Allows storage and querying of data organized as a tree-like hierarchy.
ltree_plpython3u	Enables Python 3 functions to process ltree data.
moddatetime	Automatically updates a timestamp column whenever a row is modified.
old_snapshot	Provides status information related to old snapshot usage. It can be used to diagnose issues with long-running queries.
orafce	Adds Oracle compatibility functions, types, and operators to ease migration.
pageinspect	Allows inspection of the contents of database pages at a low level.
pg_buffercache	Provides information about shared buffer cache contents. Useful for performance tuning.
pg_freespacemap	Visualizes free space map information for tables and indexes.
pgaudit	Provides detailed session and object-level audit logging for PostgreSQL.
pgcrypto	Offers cryptographic functions for encryption and hashing of stored data.
pgrowlocks	Displays information about row-level locks for a table.
pgstattuple	Returns table-level statistics including live and dead tuples.
pg_stat_kcache	Collects low-level statistics like CPU usage and I/O operations for queries.
pg_visibility	Provides visibility information for tuples in a table, useful for vacuum analysis.
plperl	Enables writing functions in Perl programming language.
plpgsql	The default procedural language in PostgreSQL. Supports loops, conditions, and complex logic.
plprofiler	Profiles PL/pgSQL functions to identify performance bottlenecks.
plpython3u	Enables use of untrusted Python 3 for writing PostgreSQL functions.
plpythonu	Enables use of untrusted Python 2. Deprecated and not recommended.
postgis	Provides GIS features such as spatial data types, spatial indexes, and distance calculations. Use it for location-based services or spatial data analysis.
postgis_topology	Enables topology-based spatial models in PostGIS. Use it to manage connections, boundaries, and adjacency relationships between spatial objects.
postgres_fdw	Queries tables in external PostgreSQL databases from a local database. It can be used for data integration or migration. For usage, see postgres_fdw.
refint	Implements referential integrity via triggers, simulating foreign key behavior.
seg	Supports segment-based data types, useful for range queries in scientific or financial domains.
sslinfo	Provides SSL connection information between client and server.
tablefunc	Offers a collection of functions for handling crosstabs and other table transformations.
tcn	Provides trigger-based notifications for table changes.
tsm_system_rows	Enables table sampling by specifying number of rows. Useful for approximate queries.
tsm_system_time	Enables time-based table sampling.
unaccent	Removes accents from strings for better search and comparison.
uuid-ossp	Provides functions to generate universally unique identifiers (UUIDs).
pgvector	Stores and queries vector data efficiently. Useful in machine learning and AI applications.
pg_cron	Runs database tasks on a cron schedule. It can be used for regular VACUUM, aggregation, and maintenance tasks. For usage, see pg_cron.
pg_hint_plan	An extension that provides hint syntax, allowing users to manually control PostgreSQL query execution plans.
pg_partman	An extension for automating and managing PostgreSQL table partitioning. It is particularly useful for managing time-series data or large-scale tables.
pg_proctab	An extension that enables access to operating system (OS) process table information within PostgreSQL. It is primarily used for system monitoring and performance analysis.
pg_repack	An extension that reclaims space and increases efficiency by removing bloat from tables or indexes. This task can be performed online without service interruption.
pg_trgm	An extension that measures string similarity and maximizes fuzzy search performance by breaking down text into three-character units (trigrams). Combined with GIN or GiST indexes, it efficiently and accurately processes otherwise inefficient wildcard searches like LIKE '%search_term%', making it essential for text matching and spell correction.
pg_stat_statements	A monitoring tool that collects detailed statistics for all SQL queries executed in the database at the system level. This extension provides key performance metrics such as execution count per query, total elapsed time, and disk I/O, helping database administrators identify the most inefficient or high-load queries and prioritize performance optimization.

Extension details

Detailed instructions for extensions requiring additional setup or usage context.

postgres_fdw

postgres_fdw is a PostgreSQL extension that provides a Foreign Data Wrapper (FDW), enabling access to tables in remote PostgreSQL databases. With this extension, you can query remote tables as if they were local and create foreign tables in your local database that reference remote tables.

In KakaoCloud, the postgres_fdw extension requires the use of the create_fdw_server stored procedure, which is pre-installed in the template1 database. Therefore, all newly created databases will have this procedure available by default.

You can use this procedure to define a connection to the remote server. The procedure automatically maps the user account, allowing you to use foreign tables after server registration.

The steps for using postgres_fdw are as follows:

1. Use an administrator or user account that has permission to create a database.
2. Ensure the outbound rule (port opening) of the security group allows access to the target database.
3. Use the create_fdw_server procedure in the created database to configure the FDW server.
4. Query the remote database.

postgres_fdw usage example

-- Create FDW server and bind user credentials
SELECT * FROM create_fdw_server(
    'fdw_server',                                 -- Server name
    'pg_endpoint',                                -- Remote DB endpoint
    'foreign_database',                           -- Remote database name
    5432,                                         -- Remote database port
    'test_database',                              -- Local DB to map foreign server to
    'admin',                                      -- Local user ID
    'foreign_admin',                              -- Remote DB user ID
    'foreign_pass'                                -- Remote DB user password
);

-- Example
SELECT * FROM create_fdw_server(
    'db_nameserver',
    'db_name-pg-001.csnzphcqgi72.ap-northeast-2.rds.amazonaws.com',
    'postgres',
    5432,
    'grey',
    'admin',
    'remote_user',
    'remote_password'
);

-- View configured foreign servers
SELECT srvname, srvtype, srvversion, srvoptions FROM pg_foreign_server;

-- Map a foreign table
CREATE FOREIGN TABLE remote_table (
    id SERIAL,
    name TEXT,
    value NUMERIC
)
SERVER fdw_server_example  -- FDW server name
OPTIONS (schema_name 'public', table_name 'remote_table_name');

pg_cron

pg_cron is an extension that enables cron-based job scheduling in PostgreSQL to support database maintenance and operational efficiency.
In KakaoCloud, pg_cron is pre-installed in the default database postgres, and it operates based on the UTC±00:00 time zone.

Unsupported pg_cron operations

To operate the service stably, operations that directly create or modify cron.job, such as cron.schedule, cron.alter_job, and UPDATE cron.job, are not supported.

The usage steps are as follows:

1. Connect to the postgres database where pg_cron is configured.
2. Register a cron-style schedule.
3. View whether cron ran at the registered time.
4. Delete the schedule.

pg_cron usage example

postgres=# \c postgres {admin/user ID}
You are now connected to database "postgres" as user "{admin/user ID}".

[Register schedule]
# Registers a schedule named 'weekly-vacuum' that runs VACUUM on a specific database every day at 4 AM (UTC)
# postgres=> CALL create_cronjob('{job name}', '{schedule}', '{command}', '{DB name}', {active});

postgres=> CALL create_cronjob('weekly-vacuum', '* 4 * * *', 'VACUUM', 'test', true);

[View schedules]
postgres=> select * from cron.job ;
 jobid |  schedule   | command | nodename  | nodeport | database | username | active |     jobname
-------+-------------+---------+-----------+----------+----------+----------+--------+------------------
     1 |  * 4 * * *  | VACUUM  |           |     5432 | test     | admin    | t      | weekly-vacuum

[View schedule executions]
postgres=> select * from cron.job_run_details ;
 jobid | runid | job_pid | database | username | command |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+---------+-----------+----------------+-------------------------------+---------------------
     8 |     1 |  379928 | db_name  | postgres | VACUUM  | succeeded | VACUUM         | 2024-11-11 11:01:00.066025+00 | 2024-11-11 11:01:05.821539+00
(1 row)

[Delete schedule]
# Delete an existing schedule
# postgres=> select cron.unschedule('{Job name or Job ID}') ;

postgres=> select cron.unschedule('weekly-vacuum') ;
 unschedule
------------
 t
(1 row)

Modifying a job

To modify a job, delete the existing job and register a new one with the updated settings.

pgaudit

pgaudit is an extension for auditing various activities that occur in a database.
You can use the set_pgaudit_param procedure to change settings by ROLE or DATABASE. This procedure can be run in the postgres database.

Caution when using the set_pgaudit_param procedure

• Changed settings apply to new sessions. If you connect to a ROLE and DATABASE where the same parameter is both configured, the ROLE setting takes precedence.
• Only pgaudit.* parameters can be changed. Parameters cannot be changed for internally managed ROLEs and DATABASEs.
• Settings changed through this procedure take precedence over values set in the parameter group. Therefore, values shown in parameter group lookup results may differ from values queried in an actual session.

1. Connect to a database where you can run the pgaudit setting change procedure.
2. Call the set_pgaudit_param procedure to change settings.

pgaudit usage example

postgres=# \c postgres {admin/user ID}
You are now connected to database "postgres" as user "{admin/user ID}".

[Procedure call format]
# public.set_pgaudit_param('{scope}', '{target name}', '{parameter name}', '{setting value}');
# Scope: Select ROLE or DATABASE
# Target name: Select the ROLE or DATABASE to configure
# Parameter name: Name of the pgaudit parameter to change
# Setting value: Parameter setting value to apply

postgres=> CALL public.set_pgaudit_param(
    '{role|database}',
    '{target_name}',
    '{parameter_name}',
    '{parameter_value}'
);

[ROLE-level setting example]
# Configure read and write logs for a ROLE named 'audit_user'
postgres=> CALL public.set_pgaudit_param(
    'role',
    'audit_user',
    'pgaudit.log',
    'read,write'
);

[DATABASE-level setting example]
# Configure all logs for a DATABASE named 'audit_db'
postgres=> CALL public.set_pgaudit_param(
    'database',
    'audit_db',
    'pgaudit.log',
    'all'
);