How to Install extensions in PostgreSQL?
To install a PostgreSQL Extension, follow these steps:
1. Before installing the extension, first ensure that the
postgresql-contrib package is installed.
[root@dbdocs pgsql]# rpm -q postgresql-contrib
package postgresql-contrib is not installed
If the package is not installed, then install it first. To install the
postgresql-contrib package, the Linux repository must be configured.
If you have not set up a Linux repository yet, please refer to the article
How to configure a local Yum repository in RHEL Linux for guidance.
[root@dbdocs pgsql]# yum install postgresql-contrib
2. Connect to your PostgreSQL database using the superuser account
[postgres@dbdocs ~]$ psql -h 127.0.0.1 -U postgres -d dbdocs
Password for user postgres:
psql (15.2)
Type "help" for help.
dbdocs=#
3. Create the 'pg_trgm' extension or any other extension you wish to create.
dbdocs=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
dbdocs=# CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
dbdocs=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-------------------------------------------------------------------
pg_trgm | 1.6 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
dbdocs=#
The
pg_trgm extension has been successfully installed.
When creating an extension, there are additional options that you can include.
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema_name ]
[ VERSION version ]
[ CASCADE ]
If you are attempting to install
non-trusted modules, you must be a superuser to install them. Otherwise, you only need to have
CREATE privilege on the database where you intend to use the module.
When you use the
CREATE EXTENSION command, you are essentially loading a new extension into your current database.
Loading an extension involves running a script file associated with the extension. This script file typically creates new SQL objects like functions, data types, operators, and index support methods. Additionally, when you use
CREATE EXTENSION, the system keeps track of all the created objects' identities. This allows for easy removal of the extension later using the
DROP EXTENSION command.
The user who executes the
CREATE EXTENSION command becomes the owner of the extension. This ownership is important for future privilege checks. Usually, the user also becomes the owner of any objects created by the extension's script.
Loading an extension typically requires the same privileges that would be needed to create the individual objects within it. For most extensions, this means that superuser privileges are required. However, if the extension is marked as "trusted" in its control file, any user with the
CREATE privilege on the current database can install it. In this case, the extension object itself will be owned by the user who installed it, but the objects created within the extension will be owned by the bootstrap superuser (unless the extension's script explicitly assigns ownership to the user who installed it). This configuration allows the user who installed the extension to have the right to drop it but not modify individual objects within it.
Related content