How to Install extensions in PostgreSQL?
corpline

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.


Rate Your Experience

: 0 : 0

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Navigation Panel

All Rights Reserved @2023 | www.dbdocs.net | Privacy Policy | Disclaimer| Cookie Policy