Gestione delle estensioni PostgreSQL

In PostgreSQL, le estensioni sono moduli che forniscono funzioni, operatori o tipi aggiuntivi. Molte estensioni sono disponibili in IBM Cloud® Databases for PostgreSQL. Per utilizzarli, imposta la password di amministratore per il tuo servizio, quindi utilizza psql per collegarti.

Elenco delle estensioni installate

Ottenere un elenco di tutte le estensioni installate su un database utilizzando il comando \dx.

Ad esempio, l'output per \dx quando viene eseguito sul database predefinito Databases for PostgreSQL mostra l'unica estensione installata.

ibmclouddb=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Installazione delle estensioni

Per installare un'estensione in un database utilizzare CREATE EXTENSION. Ad esempio, per installare pg_stat_statements sul database ibmclouddb, utilizzare il seguente comando:

ibmclouddb=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

Le estensioni vengono installate nello schema ibm_extension di sola lettura. Lo schema fa parte di search_path quindi gli oggetti di estensione non devono essere qualificati con uno schema. La modifica da schema public a schema ibm_extension è necessaria per proteggere la sicurezza e l'integrità dei tuoi dati.

Se si esegue il comando \dx dopo l'installazione di un'estensione, viene visualizzato nella tabella.

ibmclouddb=> \dx
                                             List of installed extensions
        Name        | Version |    Schema     |                              Description
--------------------+---------+---------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | ibm_extension | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

Le estensioni database in PostgreSQL sono gestite per database. Se si dispone di più database su cui è necessario installare un'estensione, eseguire il comando CREATE su ciascun database.

Aggiornamento delle estensioni

Per elencare le estensioni presenti nel database corrente per le quali è disponibile una versione più recente, eseguire la seguente query:

SELECT e.extname, e.extversion AS installed_version, a.default_version AS available_version
FROM pg_catalog.pg_extension AS e
JOIN pg_catalog.pg_available_extensions AS a ON a.name = e.extname
WHERE e.extversion <> a.default_version;

Per aggiornare un'estensione, eseguire il comando ALTER EXTENSION in ciascun database in cui è installata l'estensione:

ALTER EXTENSION <extension_name> UPDATE;

Le seguenti estensioni richiedono una procedura diversa:

  • Per aggiornare l'estensione " pg_repack ", eseguire il comando DROP EXTENSION pg_repack; e poi CREATE EXTENSION pg_repack; in ogni database che la utilizza.
  • PostgreSQL Anonymizer (anon) non supporta ALTER EXTENSION ... UPDATE. Per eseguire l'aggiornamento, consultare la sezione Aggiornamento di PostgreSQL Anonymizer.
  • L'aggiornamento dalla versione 3.x alla versione 4.0 comporta pgRouting la rimozione di diverse funzioni. Prima di effettuare l'aggiornamento, consultare pgRouting.

Note specifiche per l'estensione

pg_repack

  • La documentazione pg_repack

  • Quando si esegue il comando pg_repack, passare il flag -k per aggirare il controllo del superutente. Vedi il seguente esempio:

    pg_repack -k [OPTION]... [DBNAME]

  • Qualsiasi utente può eseguire pg_repack, ma il comando è in grado di ricompilare solo una tabella su cui dispone delle autorizzazioni.

  • pg_repack deve prendere un blocco esclusivo sugli oggetti che sta riorganizzando alla fine della riorganizzazione. Se non può ottenere questo blocco dopo un determinato periodo, annulla tutte le query in conflitto. Se non può farlo, la riorganizzazione ha esito negativo. Per impostazione predefinita, solo l'utente amministratore può annullare le query in conflitto. Per consentire agli altri utenti del database di annullare le query, assegna loro il ruolo “ pg_signal_backend ” tramite l’utente amministratore.

pgaudit

  • Il servizio carica la libreria pgaudit all'avvio. Non è possibile creare l'estensione " pgaudit ", pertanto le voci DDL nei registri di audit non includono il tipo e il nome dell'oggetto. Per abilitare i log di " pgaudit ", consultare Accessi con pgaudit.

pgvector

  • Per aggiungere l'estensione pgvector alla distribuzione, usare il comando create extension vector.
  • Importante: pgvector richiede PostgreSQL versione 15 o superiore.

Estensioni disponibili

Vedere il seguente elenco di tutte le estensioni disponibili. Per un elenco delle estensioni disponibili sulla distribuzione, utilizzare SELECT name FROM pg_available_extensions; in psql.

ibmclouddb=> SELECT name FROM pg_available_extensions order by 1;

             name
------------------------------
 address_standardizer
 address_standardizer_data_us
 amcheck
 autoinc
 bloom
 btree_gin
 btree_gist
 citext
 cube
 dblink
 dict_int
 dict_xsyn
 earthdistance
 file_fdw
 fuzzystrmatch
 hstore
 insert_username
 intagg
 intarray
 isn
 lo
 ltree
 moddatetime
 old_snapshot
 pageinspect
 pg_buffercache
 pg_freespacemap
 pg_prewarm
 pg_repack
 pg_stat_statements
 pg_surgery
 pg_trgm
 pg_visibility
 pg_walinspect
 pgaudit
 pgcrypto
 pgrouting
 pgrowlocks
 pgstattuple
 plpgsql
 postgis
 postgis_raster
 postgis_tiger_geocoder
 postgis_topology
 postgres_fdw
 refint
 seg
 sslinfo
 tablefunc
 tcn
 tsm_system_rows
 tsm_system_time
 unaccent
 uuid-ossp
 xml2
(55 rows)
ibmclouddb=> select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-2), 64-bit
(1 row)