Extensions

PostgreSQL in KakaoCloud offers a range of extensions for server management and monitoring directly from the database.

info

All extensions provided to the template1 database are available by default in KakaoCloud PostgreSQL.
You can use all extensions when creating a new database, and no database restart is required to enable a specific extension.

info

For more detailed information about extensions, please refer to the Additional Extensions Documentation on the PostgreSQL website.

Supported extensions

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.
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.
moddatetime	Automatically updates a timestamp column whenever a row is modified.
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.
postgres_fdw	Foreign Data Wrapper for accessing remote PostgreSQL instances.
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	Enables running cron-style scheduled jobs within PostgreSQL.

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');
```bash title="postgres_fdw usage example"
SELECT * FROM create_fdw_server(
    'fdw_server',
    'pg_endpoint',
    'foreign_database',
    5432,
    'test_database',
    'admin',
    'foreign_admin',
    'foreign_pass'
);

-- 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 external database servers
SELECT srvname, srvtype, srvversion, srvoptions FROM pg_foreign_server;

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

pg_cron

pg_cron is a PostgreSQL extension that enables cron-based job scheduling to improve maintenance and operational efficiency within the database. In KakaoCloud, pg_cron is preinstalled in the default postgres database, and all scheduling follows the UTC±00:00 timezone.

The usage steps are as follows:

1. Connect to the postgres database where pg_cron is enabled.

2. Register a cron-style job schedule.

3. Verify whether the scheduled job was executed.

4. Delete the schedule if necessary.

pg_cron example

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

-- Register a schedule: A schedule named 'weekly-vacuum' that runs the VACUUM command
-- on a specific database every day at 4 AM (UTC)
postgres=> SELECT cron.schedule_in_database('weekly-vacuum', '* 4 * * *', 'VACUUM', 'test_database');

-- View registered schedules
postgres=> select * from cron.job ;
 jobid |  schedule   | command | nodename  | nodeport | database  | username | active |     jobname
-------+-------------+---------+-----------+----------+-----------+----------+--------+------------------
     1 | 5 20 * * *  | VACUUM  | localhost |     5432 | db_name   | admin    | t      | weekly-vacuum

-- View schedule execution history
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 the schedule
postgres=> select cron.unschedule('weekly-vacuum') ;
 unschedule
------------
 t
(1 row)