Postgres: How to add an extension to a certain database

If you get errors like this:

CopyPG::InsufficientPrivilege: ERROR:  permission denied to create extension "btree_gist"
HINT:  Must be superuser to create this extension.

You need to install the extension as a user with sufficient rights.

1. Open the postgres console as a priviledged user:

   Copysudo -u postgres psql
   

2. Afterwards, switch to the databse which needs the extension to be installed:

   Copy\connect my_database
   

3. Then you can install the extension:

   CopyCREATE EXTENSION IF NOT EXISTS btree_gist;
   

4. You can verify the extension is installed with

   Copy\dx
   

   Sample output:

   Copy                           List of installed extensions
       Name    | Version |   Schema   |                  Description                  
   ------------+---------+------------+-----------------------------------------------
    btree_gist | 1.5     | public     | support for indexing common datatypes in GiST
    plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language