How to manage extensions in PostgreSQL

Managing PostgreSQL extensions adds database features such as additional functions, data types, operators, and index support without pushing that logic into application code. Keeping extensions explicit and well-maintained improves portability between environments and reduces surprises during server upgrades or database restores.

An extension is a server-provided bundle of SQL objects (and sometimes native shared libraries) that is enabled per database. CREATE EXTENSION registers the extension in the current database, creates its objects into a schema, and records the installed version in system catalogs such as pg_extension, while pg_available_extensions shows what the server can provide.

Extension availability depends on files installed on the database server, not on the client. Creating or updating extensions may require elevated privileges, and some extensions additionally require changes to shared_preload_libraries plus a service restart before they can be used. Dropping an extension removes its objects and can break dependent tables, functions, and application queries.

1. Connect to the target PostgreSQL database using psql.

   $ psql -d appdb -c "SELECT current_database();"
    current_database 
   ------------------
    appdb
   (1 row)

2. List installed extensions in the current database.

   $ psql -d appdb -c "\dx"
                                               List of installed extensions
           Name        | Version |   Schema   |                              Description                               
   --------------------+---------+------------+------------------------------------------------------------------------
    pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
    plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
   (2 rows)

   \dx reports extensions enabled in the current database only.

3. List extensions available for installation on the server.

   $ psql -d appdb -c "SELECT name, default_version, installed_version, comment FROM pg_available_extensions WHERE name IN ('citext','pg_trgm','uuid-ossp') ORDER BY name;"
      name    | default_version | installed_version |                              comment                              
   -----------+-----------------+-------------------+-------------------------------------------------------------------
    citext    | 1.6             |                   | data type for case-insensitive character strings
    pg_trgm   | 1.6             |                   | text similarity measurement and index searching based on trigrams
    uuid-ossp | 1.1             |                   | generate universally unique identifiers (UUIDs)
   (3 rows)

   An extension missing from this list is not installed on the server filesystem.

4. Create the extension in the current database.

   $ psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"
   CREATE EXTENSION

   Creating many extensions requires a superuser role, and extensions are enabled per database rather than per server.

5. Verify the extension version and installed schema.

   $ psql -d appdb -c "\dx \"uuid-ossp\""
                             List of installed extensions
      Name    | Version | Schema |                   Description                   
   -----------+---------+--------+-------------------------------------------------
    uuid-ossp | 1.1     | public | generate universally unique identifiers (UUIDs)
   (1 row)

   Use the SCHEMA clause in CREATE EXTENSION to control where extension objects are installed.

6. Test an object provided by the extension.

   $ psql -d appdb -c "SELECT uuid_generate_v4();"
              uuid_generate_v4           
   --------------------------------------
    712d497a-6d88-4fd3-a12e-14a8560e985b
   (1 row)

7. Update an installed extension to the latest available version on the server.

   $ psql -d appdb -c "ALTER EXTENSION \"uuid-ossp\" UPDATE;"
   NOTICE:  version "1.1" of extension "uuid-ossp" is already installed
   ALTER EXTENSION

   Pin a specific version with ALTER EXTENSION … UPDATE TO '<version>' when a staged upgrade is required.

8. Remove an extension from the database when its objects are no longer needed.

   $ psql -d appdb -c "DROP EXTENSION IF EXISTS \"uuid-ossp\";"
   DROP EXTENSION

   DROP EXTENSION removes functions and types owned by the extension, and using CASCADE can drop dependent application objects.

9. Confirm the extension is no longer installed in the database.

   $ psql -d appdb -c "\dx"
                                               List of installed extensions
           Name        | Version |   Schema   |                              Description                               
   --------------------+---------+------------+------------------------------------------------------------------------
    pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
    plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
   (2 rows)