• Relational Database Service

rds
  1. Help Center
  2. Relational Database Service
  3. User Guide
  4. Working with RDS for PostgreSQL
  5. Managing a Plugin

Managing a Plugin

RDS provides the PostgreSQL plugin management solution for user root. The auto_explain is automatically created by the system and other plugins need to be manually created.

Creating a Plugin

  1. Connect to the database postgres as user root and use template1 to create a database that can support the plugin.

    # psql --host=<RDS_ADDRESS> --port=<DB_PORT> --dbname=postgres --username=root -c "create database <DB_NAME> template template1;"

    • RDS_ADDRESS indicates the IP address of the RDS DB instance.
    • DB_PORT indicates the RDS DB instance port.
    • DB_NAME indicates the name of the database to be created.

    Enter the password of user root as prompted.

    Create a database named my_extension_db that can support the plugin. Example:

    # psql --host=192.168.6.141 --port=5432 --dbname=postgres --username=root -c "create database my_extension_db template template1;"

    Password for user root:
    CREATE DATABASE

    Note: If you are creating a database as a common user, log in to the created database as the common user and run the following command to grant all rights to user root:

    GRANT ALL ON DATABASE db1 TO root;

  2. Connect to the created database as user root and create a plugin.

    # psql --host=<RDS_ADDRESS> --port=<DB_PORT> --dbname=<DB_NAME> --username=root -c "select control_extension('create','<EXTENSION_NAME>');"

    • RDS_ADDRESS indicates the IP address of the RDS DB instance.
    • DB_PORT indicates the RDS DB instance port.
    • DB_NAME indicates the name of the database to be created.
    • EXTENSION_NAME indicates the plugin name and has the following possible values:
      • postgis. When postgis is created, the following plugins are created at the same time:

        postgis

        postgis_topology

        fuzzystrmatch

        postgis_tiger_geocoder

        address_standardizer

        address_standardizer_data_us

      • btree_gin
      • btree_gist
      • hstore
      • pg_trgm
      • tablefunc
      • unaccent
      • uuid-ossp
      • cube
      • dict_int
      • dict_xsyn
      • earthdistance

        To install the earthdistance plugin, you must install the cube plugin first.

      • intagg
      • intarray
      • ltree
      • pgcrypto
      • timescaledb

        The timescaledb plugin does not support PostgreSQL 9.5.5.

      • hll
      • zhparser, which provides the default dictionary only and does not support user-defined dictionaries. The default values are as follows:

        zhparser.punctuation_ignore = off

        zhparser.punctuation_ignore = off

        zhparser.multi_short = off

        zhparser.multi_duality = off

        zhparser.multi_zmain = off

        zhparser.multi_zall = off

        zhparser.dict_in_memory = off

      • pg_pathman
      • pg_stat_statements
      • pg_hint_plan

    Enter the password of user root as prompted.

    Create plugin postgis in the database my_extension_db. Example:

    # psql --host=192.168.6.141 --port=5432 --dbname=my_extension_db --username=root -c "select control_extension('create','postgis');"

    Password for user root: 
          control_extension       
    ------------------------------
     create postgis successfully.
    (1 row)

Deleting a Plugin

Connect to the database with a plugin created as user root and delete the plugin.

# psql --host=<RDS_ADDRESS> --port=<DB_PORT> --username=root --dbname=<DB_NAME> -c "select control_extension ('drop','<EXTENSION_NAME>');"

  • RDS_ADDRESS indicates the IP address of the RDS DB instance.
  • DB_PORT indicates the RDS DB instance port.
  • DB_NAME indicates the name of the database to be created.
  • EXTENSION_NAME indicates the plugin name and has the following values:
    • postgis
    • btree_gin
    • btree_gist
    • hstore
    • pg_trgm
    • tablefunc
    • unaccent
    • uuid-ossp
    • cube

      If the earthdistance plugin has been installed, deleting the cube plugin will cause the earthdistance plugin to be unavailable.

    • dict_int
    • dict_xsyn
    • earthdistance
    • intagg
    • intarray
    • ltree
    • pgcrypto
    • timescaledb
    • hll
    • zhparser

Enter the password of user root as prompted.

Example:

# psql --host=192.168.6.141 --port=5432 --dbname=my_extension_db --username=root -c "select control_extension('drop','postgis');"

Password for user root: 
     control_extension      
----------------------------
 drop postgis successfully.
(1 row)