In this example, I’m going to install the pgcrypto extension to be able to generate UUID data type.
Procedure
1. Access as Postgres superuser. By default, it is the postgres user.
sudo su - postgres
2. Access to the database using the super user (postgres).
psql -U postgres (11.2 (Ubuntu 11.2-1.pgdg18.04+1))Type "help" for help.
3. Lista all the databases to identify the database we want to install the extension, (Optional). user the command \l.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+-----------+----------+-------------+-------------+-----------------------
aurlendev | aurlendev | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dbaurlendev | aurlendev | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
4. The extensions need to be enabled in each database one by one. In this example, we’re going to enable pgcrypto in to the databse dbaurlendev. Connect to the database we want to enable the extension, using the command \c databasename.
\c dbaurlendev;
5. Execute the specific command to enable the extension. For pgcrypto, the command is CREATE EXTENSION IF NOT EXISTS “pgcrypto”;
CREATE EXTENSION IF NOT EXISTS "pgcrypto"; CREATE EXTENSION
6. Test the extension, using an application user. To generate UUID with pgcrypto the command is SELECT gen_random_uuid();
SELECT gen_random_uuid();
gen_random_uuid
--------------------------------------
0e2f81e0-d422-42e8-9cac-c34a5f1202bb