Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
# Utilizzo di estensioni e wrapper di dati esterni
Per estendere la funzionalità al cluster database Edizione compatibile con Aurora PostgreSQL, è possibile installare e utilizzare diverse *estensioni* PostgreSQL. Ad esempio, se il caso d'uso richiede l'immissione intensiva di dati su tabelle molto grandi, è possibile installare l'estensione `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` per partizionare i dati e quindi distribuire il carico di lavoro.
**Nota**
La funzionalità Trusted Language Extensions per PostgreSQL è supportata da Aurora PostgreSQL a partire dalla versione 14.5. Viene implementata come estensione `pg_tle` che puoi aggiungere ad Aurora PostgreSQL e che può essere utilizzata dagli sviluppatori per creare le proprie estensioni di PostgreSQL in un ambiente sicuro che riduce i requisiti di impostazione e configurazione, nonché gran parte dei test preliminari delle nuove estensioni. Per ulteriori informazioni, consulta [Utilizzo di Trusted Language Extensions per PostgreSQL](PostgreSQL_trusted_language_extension.md).
In alcuni casi, anziché installare un'estensione, è possibile aggiungere un *modulo* specifico all'elenco di `shared_preload_libraries` nel gruppo di parametri cluster database personalizzato del cluster database Aurora PostgreSQL. In genere, il gruppo di parametri cluster di database predefinito carica solo `pg_stat_statements`, ma sono disponibili diversi altri moduli da aggiungere all'elenco. Ad esempio, è possibile aggiungere funzionalità di pianificazione aggiungendo il modulo `pg_cron`, come descritto in [Pianificazione della manutenzione con l'estensione PostgreSQL pg\$1cron](PostgreSQL_pg_cron.md). Come altro esempio, è possibile registrare i piani di esecuzione delle query caricando il modulo `auto_explain`. Per ulteriori informazioni, consulta [Registrazione dei piani di esecuzione delle query](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#) nel AWS Knowledge Center.
Un'estensione che fornisce accesso a dati esterni è più specificatamente conosciuta come un *wrapper di dati esterno* (FDW). Ad esempio, l'estensione `oracle_fdw` consente al cluster database Aurora PostgreSQL di utilizzare i database Oracle.
È inoltre possibile specificare con precisione le estensioni che possono essere installate sull'istanza database Aurora PostgreSQL, elencandole nel parametro `rds.allowed_extensions`. Per ulteriori informazioni, consulta[Limitazione dell'installazione delle estensioni PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction.html).
Di seguito, puoi trovare informazioni sulla configurazione e l'utilizzo di alcune estensioni e moduli FDWs disponibili per Aurora PostgreSQL. Per semplicità, queste sono tutte denominate "estensioni". Per gli elenchi delle estensioni che è possibile utilizzare con le versioni di Aurora PostgreSQL attualmente disponibili, consultare la pagina relativa alle [versioni delle estensioni per Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) nelle *Note di rilascio di Aurora PostgreSQL*.
+ [Gestione di oggetti di grandi dimensioni con il modulo lo](PostgreSQL_large_objects_lo_extension.md)
+ [Gestione dei dati spaziali con estensione PostGIS](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [Gestione delle partizioni PostgreSQL con l'estensione pg\$1partman](PostgreSQL_Partitions.md)
+ [Pianificazione della manutenzione con l'estensione PostgreSQL pg\$1cron](PostgreSQL_pg_cron.md)
+ [Utilizzo di pgAudit per registrare l'attività del database](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [Utilizzo di pglogical per sincronizzare i dati tra le istanze](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [Interazione con un database Oracle utilizzando l'estensione oracle\$1fdw](postgresql-oracle-fdw.md)
+ [Interazione con i database MySQL utilizzando l'estensione mysql\$1fdw](postgresql-tds-fdw.md)
# Utilizzo del supporto delle estensioni delegate di Amazon Aurora per PostgreSQL
Utilizzando il supporto delle estensioni delegate di Amazon Aurora per PostgreSQL, è possibile delegare la gestione delle estensioni a un utente che non deve essere un `rds_superuser`. Con questo supporto delle estensioni delegate, viene creato un nuovo ruolo denominato `rds_extension` che è necessario assegnare a un utente per gestire altre estensioni. Questo ruolo può creare, aggiornare ed eliminare estensioni.
È possibile specificare le estensioni che possono essere installate sull’istanza database Aurora PostgreSQL, elencandole nel parametro `rds.allowed_extensions`. Per ulteriori informazioni, consulta [Utilizzo delle estensioni PostgreSQL con Amazon RDS per PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html).
È possibile limitare l’elenco delle estensioni disponibili che possono essere gestite dall’utente con il ruolo `rds_extension` utilizzando il parametro `rds.allowed_delegated_extensions`.
Il supporto delle estensioni delegate è disponibile nelle seguenti versioni:
+ Tutte le versioni successive
+ 15.5 e versioni successive alla 15
+ 14.10 e versioni successive alla 14
+ 13.13 e versioni successive alla 13
+ 12.17 e versioni successive 12
**Topics**
+ [Attivazione del supporto delle estensioni delegate per un utente](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [Configurazione utilizzata nel supporto delle estensioni delegate Aurora per PostgreSQL](#AuroraPostgreSQL.delegated_ext_config)
+ [Disattivazione del supporto per l’estensione delegata](#AuroraPostgreSQL.delegated_ext_disable)
+ [Vantaggi dell’utilizzo del supporto delle estensioni delegate di Amazon Aurora](#AuroraPostgreSQL.delegated_ext_benefits)
+ [Limitazione del supporto delle estensioni delegate di Aurora per PostgreSQL](#AuroraPostgreSQL.delegated_ext_limit)
+ [Autorizzazioni necessarie per determinate estensioni](#AuroraPostgreSQL.delegated_ext_perm)
+ [Considerazioni sulla sicurezza](#AuroraPostgreSQL.delegated_ext_sec)
+ [Disabilitazione dell’eliminazione delle estensioni con l’opzione cascade](#AuroraPostgreSQL.delegated_ext_drop)
+ [Estensioni di esempio che possono essere aggiunte utilizzando il supporto delle estensioni delegate](#AuroraPostgreSQL.delegated_ext_support)
## Attivazione del supporto delle estensioni delegate per un utente
È necessario eseguire le seguenti operazioni per abilitare il supporto delle estensioni delegate per un utente:
1. **Concedere il ruolo `rds_extension` a un utente**: connettersi al database come `rds_superuser` ed esegui il seguente comando:
```
Postgres => grant rds_extension to user_name;
```
1. **Impostare l’elenco delle estensioni disponibili per la gestione degli utenti delegati**: `rds.allowed_delegated_extensions` consente di specificare un sottoinsieme delle estensioni disponibili utilizzando `rds.allowed_extensions` nel parametro del cluster di database. È possibile eseguire questa operazione a uno dei seguenti livelli:
+ Nel cluster o nel gruppo di parametri dell'istanza, tramite l'API Console di gestione AWS o. Per ulteriori informazioni, consulta [Gruppi di parametri per Amazon Aurora](USER_WorkingWithParamGroups.md).
+ Utilizzare il seguente comando a livello di database:
```
alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
+ Utilizzare il seguente comando a livello di utente:
```
alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
**Nota**
Non è necessario riavviare il database dopo aver modificato il parametro dinamico `rds.allowed_delegated_extensions`.
1. **Consentire l’accesso all’utente delegato agli oggetti creati durante il processo di creazione dell’estensione**: alcune estensioni creano oggetti che richiedono la concessione di autorizzazioni aggiuntive prima che l’utente con il ruolo `rds_extension` possa accedervi. `rds_superuser` deve concedere all’utente delegato l’accesso a tali oggetti. Una delle opzioni consiste nell’utilizzare un trigger di evento per concedere automaticamente l’autorizzazione all’utente delegato.
**Esempio di attivazione di un evento**
Se si desidera consentire a un utente delegato con `rds_extension` di utilizzare estensioni che richiedono l’impostazione delle autorizzazioni sugli oggetti creati durante la creazione dell’estensione, è possibile personalizzare l’esempio seguente di trigger di evento e aggiungere solo le estensioni per le quali si desidera che gli utenti delegati abbiano accesso alla funzionalità completa. Questo trigger di evento può essere creato su template1 (il modello predefinito), pertanto tutti i database creati da template1 avranno tale trigger di evento. Quando un utente delegato installa l’estensione, questo trigger garantirà automaticamente la proprietà degli oggetti creati dall’estensione.
```
CREATE OR REPLACE FUNCTION create_ext()
RETURNS event_trigger AS $$
DECLARE
schemaname TEXT;
databaseowner TEXT;
r RECORD;
BEGIN
IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN
RAISE NOTICE 'SECURITY INVOKER';
RAISE NOTICE 'user: %', current_user;
FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands()
LOOP
CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension';
schemaname = (
SELECT n.nspname
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_namespace AS n
ON e.extnamespace = n.oid
WHERE e.oid = r.objid
);
databaseowner = (
SELECT pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d
WHERE d.datname = current_database()
);
RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner;
IF r.object_identity = 'address_standardizer_data_us' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'dict_int' THEN
EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner);
ELSIF r.object_identity = 'pg_partman' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'postgis_topology' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext();
```
## Configurazione utilizzata nel supporto delle estensioni delegate Aurora per PostgreSQL
| Nome configurazione | Description | Valore predefinito | Note | Chi può modificare o concedere l’autorizzazione |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | Questo parametro limita le estensioni che un ruolo rds\$1extension può gestire in un database. Deve essere un sottoinsieme di rds.allowed\$1extensions. | Stringa vuota | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/Aurora_delegated_ext.html) Per ulteriori informazioni sulla configurazione di questo parametro, consulta [Attivazione del supporto delle estensioni delegate per un utente](#AuroraPostgreSQL.delegated_ext_mgmt). | rds\$1superuser |
| `rds.allowed_extensions` | Questo parametro consente all’utente di limitare le estensioni che possono essere installate nell’istanza database Aurora PostgreSQL. Per ulteriori informazioni, consulta [Limitazione dell’installazione delle estensioni PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction). | "\$1" | Per impostazione predefinita, questo parametro è impostato su “\$1”, il che significa che tutte le estensioni supportate su RDS per PostgreSQL e Aurora PostgreSQL possono essere create da utenti con i privilegi necessari. Vuoto significa che non è possibile installare estensioni nell’istanza database Aurora PostgreSQL. | administrator |
| `rds-delegated_extension_allow_drop_cascade` | Questo parametro controlla la possibilità per l’utente con `rds_extension` di eliminare l’estensione utilizzando un’opzione cascade. | off | Per impostazione predefinita, `rds-delegated_extension_allow_drop_cascade` è impostato su `off`. Ciò significa che gli utenti con `rds_extension` non sono autorizzati a eliminare un’estensione utilizzando l’opzione cascade. Per consentire tale funzionalità, il parametro `rds.delegated_extension_allow_drop_cascade` deve essere impostato su `on`. | rds\$1superuser |
## Disattivazione del supporto per l’estensione delegata
**Disattivazione parziale**
Gli utenti delegati non possono creare nuove estensioni ma possono comunque aggiornare le estensioni esistenti.
+ Ripristinare `rds.allowed_delegated_extensions` sul valore predefinito nel gruppo di parametri del cluster di database.
+ Utilizzare il seguente comando a livello di database:
```
alter database database_name reset rds.allowed_delegated_extensions;
```
+ Utilizzare il seguente comando a livello di utente:
```
alter user user_name reset rds.allowed_delegated_extensions;
```
**Disattivazione completa**
La revoca del ruolo `rds_extension` a un utente ripristinerà le autorizzazioni standard per l’utente. L’utente non può più creare, aggiornare o eliminare le estensioni.
```
postgres => revoke rds_extension from user_name;
```
## Vantaggi dell’utilizzo del supporto delle estensioni delegate di Amazon Aurora
Utilizzando il supporto delle estensioni delegate di Amazon Aurora per PostgreSQL, è possibile delegare in modo sicuro la gestione delle estensioni agli utenti che non dispongono del ruolo `rds_superuser`. Questa funzionalità fornisce i seguenti vantaggi:
+ È possibile delegare facilmente la gestione delle estensioni agli utenti desiderati.
+ Non è richiesto il ruolo `rds_superuser`.
+ Offre la possibilità di supportare diversi set di estensioni per diversi database nello stesso cluster di database.
## Limitazione del supporto delle estensioni delegate di Aurora per PostgreSQL
+ Gli oggetti creati durante il processo di creazione dell’estensione possono richiedere privilegi aggiuntivi per il corretto funzionamento dell’estensione.
## Autorizzazioni necessarie per determinate estensioni
Per creare, utilizzare o aggiornare le seguenti estensioni, l’utente delegato deve disporre dei privilegi necessari sulle seguenti funzioni, tabelle e schemi.
| Estensioni che richiedono proprietà o autorizzazioni | Funzione | Tabelle | Schema | Dizionario di ricerca testuale | Comment |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | | us\$1gaz, us\$1lex, us\$1lex, I.us\$1rules | | | |
| amcheck | bt\$1index\$1check, bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions, part\$1config, part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | topology, layer | topology | | L’utente delegato deve essere il proprietario del database |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default, geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## Considerazioni sulla sicurezza
Tenere presente che un utente con il ruolo `rds_extension` sarà in grado di gestire le estensioni su tutti i database su cui ha il privilegio di connessione. Se l’intenzione è fare in modo che un utente delegato gestisca l’estensione su un singolo database, è buona norma revocare tutti i privilegi al pubblico su ciascun database, quindi concedere esplicitamente il privilegio di connessione per tale database specifico all’utente delegato.
Esistono diverse estensioni che possono consentire a un utente di accedere alle informazioni da più database. Prima di aggiungere queste estensioni a `rds.allowed_delegated_extensions`, assicurarsi che gli utenti a cui si concede `rds_extension` dispongano di funzionalità su più database. Ad esempio, `postgres_fdw` e `dblink` forniscono funzionalità per eseguire query su più database sulla stessa istanza o su istanze remote. `log_fdw` legge i file di log del motore postgres, che riguardano tutti i database dell’istanza, potenzialmente contenenti query lente o messaggi di errore provenienti da più database. `pg_cron` consente l’esecuzione di processi pianificati in background sull’istanza database e può configurare i processi per l’esecuzione in un database diverso.
## Disabilitazione dell’eliminazione delle estensioni con l’opzione cascade
La possibilità di eliminare l’estensione con l’opzione cascade da parte di un utente con il ruolo `rds_extension` è controllata dal parametro `rds.delegated_extension_allow_drop_cascade`. Per impostazione predefinita, `rds-delegated_extension_allow_drop_cascade` è impostato su `off`. Ciò significa che agli utenti con il ruolo `rds_extension` non è consentito eliminare un’estensione utilizzando l’opzione cascade, come mostrato nella query seguente.
```
DROP EXTENSION CASCADE;
```
In questo modo verranno eliminati automaticamente gli oggetti che dipendono dall’estensione e, di conseguenza, tutti gli oggetti che dipendono da tali oggetti. Il tentativo di utilizzare l’opzione cascade genererà un errore.
Per consentire tale funzionalità, il parametro `rds.delegated_extension_allow_drop_cascade` deve essere impostato su `on`.
La modifica del parametro dinamico `rds.delegated_extension_allow_drop_cascade` non richiede il riavvio del database. È possibile eseguire questa operazione a uno dei seguenti livelli:
+ Nel cluster o nel gruppo di parametri dell'istanza, tramite l'API Console di gestione AWS o.
+ Utilizzando il seguente comando a livello di database:
```
alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on';
```
+ Utilizzando il seguente comando a livello di utente:
```
alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on';
```
## Estensioni di esempio che possono essere aggiunte utilizzando il supporto delle estensioni delegate
+ `rds_tools`
```
extension_test_db=> create extension rds_tools;
CREATE EXTENSION
extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files';
ERROR: permission denied for function role_password_encryption_type
```
+ `amcheck`
```
extension_test_db=> CREATE TABLE amcheck_test (id int);
CREATE TABLE
extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000));
INSERT 0 100000
extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id);
CREATE INDEX
extension_test_db=> create extension amcheck;
CREATE EXTENSION
extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_check
extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_parent_check
```
+ `pg_freespacemap`
```
extension_test_db=> create extension pg_freespacemap;
CREATE EXTENSION
extension_test_db=> SELECT * FROM pg_freespace('pg_authid');
ERROR: permission denied for function pg_freespace
extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0);
ERROR: permission denied for function pg_freespace
```
+ `pg_visibility`
```
extension_test_db=> create extension pg_visibility;
CREATE EXTENSION
extension_test_db=> select * from pg_visibility('pg_database'::regclass);
ERROR: permission denied for function pg_visibility
```
+ `postgres_fdw`
```
extension_test_db=> create extension postgres_fdw;
CREATE EXTENSION
extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432');
ERROR: permission denied for foreign-data wrapper postgres_fdw
```
# Gestione di oggetti di grandi dimensioni con il modulo lo
Il modulo lo (estensione) è per utenti di database e sviluppatori che utilizzano database PostgreSQL tramite driver JDBC o ODBC. JDBC e ODBC presumono entrambi che il database gestisca l'eliminazione di oggetti di grandi dimensioni quando i riferimenti ad essi cambiano. Tuttavia, PostgreSQL non funziona in questo modo. PostgreSQL non ipotizza che un oggetto venga eliminato quando il suo riferimento cambia. Il risultato è che gli oggetti rimangono su disco, senza riferimenti. L'estensione lo include una funzione utilizzata per attivare le modifiche dei riferimenti per eliminare gli oggetti se necessario.
**Suggerimento**
Per determinare se il database può sfruttare l’estensione lo, utilizzare l’utilità `vacuumlo` per verificare la presenza di oggetti orfani di grandi dimensioni. Per ottenere il conteggio di oggetti orfani di grandi dimensioni senza eseguire alcuna azione, esegui l'utilità con l'opzione `-n`(no-op). Per scoprire come, consulta [vacuumlo utility](#vacuumlo-utility) seguente.
Il modulo lo è disponibile per Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 e versioni secondarie successive.
Per installare il modulo (estensione), sono necessari privilegi `rds_superuser`. L'installazione dell'estensione lo aggiunge quanto segue al database:
+ `lo`— Si tratta di un tipo di dati large object (lo) che è possibile utilizzare per oggetti binari di grandi dimensioni (BLOBs) e altri oggetti di grandi dimensioni. Il tipo di dati `lo` è un dominio del tipo di dati `oid`. In altre parole, è un identificatore di oggetti con vincoli opzionali. Per ulteriori informazioni, consulta [Identificatori di oggetti](https://www.postgresql.org/docs/14/datatype-oid.html) nella documentazione di PostgreSQL. In parole povere, è possibile utilizzare il tipo di `lo` dati per distinguere le colonne del database che contengono riferimenti a oggetti di grandi dimensioni da altri identificatori di oggetti (OIDs).
+ `lo_manage` – Questa è una funzione che puoi utilizzare nei trigger sulle colonne di tabella contenenti riferimenti di oggetti di grandi dimensioni. Ogni volta che elimini o modifichi un valore che fa riferimento a un oggetto di grandi dimensioni, il trigger scollega l'oggetto (`lo_unlink`) dal suo riferimento. Utilizza il trigger su una colonna solo se la colonna è l'unico riferimento del database all'oggetto di grandi dimensioni.
Per ulteriori informazioni sul modulo di oggetti di grandi dimensioni, consulta [lo](https://www.postgresql.org/docs/current/lo.html) nella documentazione di PostgreSQL.
## Installazione dell'estensione lo
Prima di installare l'estensione lo, assicurati di disporre dei privilegi `rds_superuser`.
**Installare l'estensione**
1. Utilizza `psql` per connetterti all'istanza database principale del cluster database Aurora PostgreSQL.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Specifica la password, quando richiesto. Il client `psql` si connette e visualizza il database di connessione amministrativa predefinito `postgres=>` come prompt.
1. Installa l'estensione come segue:
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
Ora puoi utilizzare il tipo di dati `lo` per definire le colonne nelle tabelle. Ad esempio, puoi creare una tabella (`images`) contenente dati immagine raster. Puoi utilizzare il tipo di dati `lo` per una colonna `raster`, come mostrato nell'esempio seguente, che crea una tabella.
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## Utilizzo della funzione di trigger lo\$1manage per eliminare gli oggetti
Puoi utilizzare la funzione `lo_manage` in un trigger su un `lo` o altre colonne di oggetti di grandi dimensioni per rimuovere (e prevenire oggetti orfani) quando `lo` viene aggiornato o eliminato.
**Impostare trigger su colonne che fanno riferimento a oggetti di grandi dimensioni**
+ Esegui una delle seguenti operazioni:
+ Crea un trigger BEFORE UPDATE OR DELETE su ogni colonna per contenere riferimenti univoci a oggetti di grandi dimensioni, utilizzando il nome della colonna come argomento.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ Applica un trigger solo quando la colonna viene aggiornata.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
La funzione di trigger `lo_manage` funziona solo nel contesto dell'inserimento o dell'eliminazione dei dati di colonna, a seconda di come definisci il trigger. Non ha alcun effetto quando esegui un'operazione `DROP` o `TRUNCATE` su un database. Ciò significa che devi eliminare le colonne di oggetti da qualsiasi tabella prima del rilascio, per evitare la creazione di oggetti orfani.
Ad esempio, supponi di voler rilasciare il database contenente la tabella `images`. Per eliminare la colonna procedi come segue.
```
postgres=> DELETE FROM images COLUMN raster
```
Ipotizzando che la funzione `lo_manage` sia definita su tale colonna per gestire le eliminazioni, ora puoi rilasciare la tabella in modo sicuro.
## Rimozione di oggetti orfani di grandi dimensioni orfani con `vacuumlo`
L’utilità `vacuumlo` identifica e rimuove oggetti orfani di grandi dimensioni dai database. Questa utilità è disponibile a partire da PostgreSQL 9.1.24. Se gli utenti del database utilizzano regolarmente oggetti di grandi dimensioni, si consiglia di eseguire `vacuumlo` occasionalmente per rimuovere gli oggetti orfani di grandi dimensioni.
Prima di installare l’estensione lo, è possibile utilizzare `vacuumlo` per valutare se il cluster di database Aurora PostgreSQL può trarne vantaggio. A questo scopo, esegui `vacuumlo` con l'opzione `-n` (no-op) per mostrare cosa viene rimosso, come illustrato di seguito:
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
Come mostra l'output, gli oggetti orfani di grandi dimensioni non sono un problema per questo particolare database.
Per ulteriori informazioni su questa utilità, consulta [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html) nella documentazione di PostgreSQL.
## Introduzione al funzionamento di `vacuumlo`
Il `vacuumlo` comando rimuove gli oggetti di grandi dimensioni orfani (LOs) dal database PostgreSQL senza influire o entrare in conflitto con le tabelle utente.
Il comando funziona come segue:
1. `vacuumlo`inizia creando una tabella temporanea contenente tutti gli Object IDs (OIDs) degli oggetti di grandi dimensioni presenti nel database.
1. `vacuumlo` analizza quindi ogni colonna del database che utilizza i tipi di dati `oid` o `lo`. Se `vacuumlo` trova un OID corrispondente in queste colonne, rimuove l’OID dalla tabella temporanea. `vacuumlo` controlla solo le colonne denominate specificatamente `oid` o `lo`, non i domini basati su questi tipi.
1. Le voci rimanenti nella tabella temporanea sono orfane LOs, che vengono `vacuumlo` quindi rimosse in modo sicuro.
## Miglioramento delle prestazioni di `vacuumlo`
È possibile migliorare le prestazioni di `vacuumlo` aumentando la dimensione del batch con l’opzione `-l`. Ciò consente `vacuumlo` di elaborarne più LOs contemporaneamente.
Se il sistema dispone di memoria sufficiente ed è possibile inserire completamente la tabella temporanea in memoria, l’aumento dell’impostazione `temp_buffers` a livello di database può migliorare le prestazioni. Ciò consente alla tabella di risiedere interamente in memoria, il che può migliorare le prestazioni complessive.
La seguente query stima la dimensione della tabella temporanea:
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## Considerazioni per oggetti di grandi dimensioni
Di seguito sono riportate alcune considerazioni importanti da tenere presenti quando si utilizza oggetti di grandi dimensioni:
+ `Vacuumlo`è l'unica soluzione in quanto attualmente non esiste un altro metodo per rimuovere gli orfani LOs.
+ Strumenti come la replica logica nativa e biologica e AWS DMS che utilizzano tecnologie di replica non supportano la replica di oggetti di grandi dimensioni.
+ Durante la progettazione dello schema del database, evitare di utilizzare oggetti di grandi dimensioni quando possibile e prendere in considerazione l’utilizzo di tipi di dati alternativi come `bytea`.
+ Esegui `vacuumlo` regolarmente, almeno una volta alla settimana, per evitare problemi con gli orfani. LOs
+ Utilizza un trigger con la `lo_manage` funzione sulle tabelle che memorizzano oggetti di grandi dimensioni per evitare che vengano creati oggetti orfani LOs .
# Gestione dei dati spaziali con estensione PostGIS
PostGIS è un'estensione di PostgreSQL per l'archiviazione e la gestione delle informazioni spaziali. Per ulteriori informazioni su PostGIS, consulta [PostGIS.net](https://postgis.net/).
A partire dalla versione 10.5, PostgreSQL supporta la libreria libprotobuf 1.3.0 utilizzata da PostGIS per lavorare con i dati delle tile vettoriali delle mappe.
La configurazione dell'estensione PostGIS richiede i privilegi `rds_superuser`. Ti consigliamo di creare un utente (ruolo) per gestire l'estensione PostGIS e i dati spaziali. L'estensione PostGIS e i relativi componenti aggiungono migliaia di funzioni a PostgreSQL. Considera la possibilità di creare l'estensione PostGIS nel proprio schema se ciò ha senso per il tuo caso d'uso. Nell'esempio seguente viene illustrato come installare l'estensione nel proprio database, ma questa operazione non è necessaria.
**Topics**
+ [Passaggio 1: Creazione di un utente (ruolo) per gestire l'estensione PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [Passaggio 2: Caricamento delle estensioni di PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [Fase 3: trasferimento della proprietà degli schemi di estensione](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [Fase 4: Trasferimento della proprietà delle tabelle PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [Passaggio 5: Verificare le estensioni](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [Passaggio 6: Aggiornamento dell'estensione PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [Versioni dell’estensione PostGIS](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [Aggiornamento di PostGIS 2 a PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)
## Passaggio 1: Creazione di un utente (ruolo) per gestire l'estensione PostGIS
Per prima cosa, esegui la connessione a un'istanza database RDS per PostgreSQL come utente con i privilegi `rds_superuser`. Se hai mantenuto il nome di default durante la configurazione dell'istanza, esegui la connessione come `postgres`.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Crea un ruolo separato (utente) per amministrare l'estensione PostGIS.
```
postgres=> CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE
```
Concedi a questo ruolo i privilegi `rds_superuser` per consentire l'installazione dell'estensione.
```
postgres=> GRANT rds_superuser TO gis_admin;
GRANT
```
Crea un database da utilizzare per gli artefatti PostGIS. Questa fase è facoltativa. In alternativa, puoi creare uno schema nel database utente per le estensioni PostGIS, ma anche questa operazione non è necessaria.
```
postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE
```
Concedi a `gis_admin` tutti i privilegi per il database `lab_gis`.
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
```
Esci dalla sessione ed esegui nuovamente la connessione all'istanza database RDS per PostgreSQL come `gis_admin`.
```
postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
```
Continua a configurare l'estensione come descritto nei passaggi successivi.
## Passaggio 2: Caricamento delle estensioni di PostGIS
L'estensione PostGIS include diverse estensioni correlate che interagiscono per fornire funzionalità geospaziali. A seconda del caso d'uso, è possibile che le estensioni create in questo passaggio non siano tutte necessarie.
Utilizzare `CREATE EXTENSION` le istruzioni per caricare le estensioni PostGIS.
```
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
```
È possibile verificare i risultati eseguendo la query SQL mostrata nel seguente esempio, che elenca le estensioni e i relativi proprietari.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+-----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
(4 rows)
```
## Fase 3: trasferimento della proprietà degli schemi di estensione
Usare le istruzioni ALTER SCHEMA per trasferire la proprietà degli schemi al ruolo `gis_admin`.
```
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
```
È possibile confermare la modifica della proprietà eseguendo la seguente query SQL. Oppure è possibile utilizzare il meta-comando `\dn` dalla riga di comando psql.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+---------------
public | postgres
tiger | gis_admin
tiger_data | gis_admin
topology | gis_admin
(4 rows)
```
## Fase 4: Trasferimento della proprietà delle tabelle PostGIS
**Nota**
Non modificare la proprietà delle funzioni PostGIS. Il corretto funzionamento e i futuri aggiornamenti di PostGIS richiedono che queste funzioni mantengano la proprietà originale. Per ulteriori informazioni sulle autorizzazioni PostGIS, consulta [PostgreSQL Security](https://postgis.net/workshops/postgis-intro/security.html).
Utilizza la funzione seguente per trasferire la proprietà delle tabelle PostGIS al ruolo `gis_admin`. Eseguire la seguente istruzione dal prompt di psql per creare la funzione.
```
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
```
Successivamente, eseguire la seguente query per eseguire la funzione `exec` che a sua volta esegue le istruzioni e altera le autorizzazioni.
```
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```
## Passaggio 5: Verificare le estensioni
Per evitare di dover specificare il nome dello schema, aggiungi lo schema `tiger` al percorso di ricerca usando il seguente comando.
```
SET search_path=public,tiger;
SET
```
Verifica lo schema `tiger` usando la seguente istruzione SELECT.
```
SELECT address, streetname, streettypeabbrev, zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
```
Per ulteriori informazioni su questa estensione, consulta [Tiger Geocoder](https://postgis.net/docs/Extras.html#Tiger_Geocoder) nella documentazione di PostGIS.
Verifica lo schema `topology` usando la seguente istruzione `SELECT`. Questa richiama la funzione `createtopology` per registrare un nuovo oggetto topologia (my\$1new\$1topo) con l'identificatore di riferimento spaziale specificato (26986) e la tolleranza predefinita (0,5). Per saperne di più, consulta la [CreateTopology](https://postgis.net/docs/CreateTopology.html)documentazione di PostGIS.
```
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
```
## Passaggio 6: Aggiornamento dell'estensione PostGIS
Ogni nuova versione di PostgreSQL supporta una o più versioni dell'estensione PostGIS compatibili con tale versione. L'aggiornamento del motore PostgreSQL a una nuova versione non aggiorna automaticamente l'estensione PostGIS. Prima di aggiornare il motore PostgreSQL, in genere si aggiorna PostGIS alla versione più recente disponibile per la versione di PostgreSQL corrente. Per informazioni dettagliate, vedi [Versioni dell’estensione PostGIS](#CHAP_PostgreSQL.Extensions.PostGIS).
Dopo l'aggiornamento del motore PostgreSQL, si aggiorna nuovamente l'estensione PostGIS alla versione supportata per la versione del motore PostgreSQL aggiornata. Per ulteriori informazioni sull'aggiornamento del motore PostgreSQL, consulta [Test di un aggiornamento del cluster database di produzione a una nuova versione principale](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
Puoi verificare la disponibilità di aggiornamenti della versione dell'estensione PostGIS sul cluster database Aurora PostgreSQL in qualsiasi momento. Per farlo, esegui il comando seguente. Questa funzione è disponibile con PostGIS 2.5.0 e versioni successive.
```
SELECT postGIS_extensions_upgrade();
```
Se l'applicazione non supporta la versione più recente di PostGIS, puoi installare una versione precedente di PostGIS disponibile nella versione principale, come indicato di seguito.
```
CREATE EXTENSION postgis VERSION "2.5.5";
```
Se desideri eseguire l'aggiornamento a una versione PostGIS specifica da una versione precedente, puoi anche utilizzare il seguente comando.
```
ALTER EXTENSION postgis UPDATE TO "2.5.5";
```
A seconda della versione da cui si esegue l'aggiornamento, potrebbe essere necessario utilizzare nuovamente questa funzione. Il risultato della prima esecuzione della funzione determina se è necessaria una funzione di aggiornamento aggiuntiva. Ad esempio, questo si verifica per l'aggiornamento da PostGIS 2 a PostGIS 3. Per ulteriori informazioni, consulta [Aggiornamento di PostGIS 2 a PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3).
Se l'estensione è stata aggiornata in preparazione a un aggiornamento della versione principale del motore PostgreSQL, puoi continuare con altre attività preliminari. Per ulteriori informazioni, consulta [Test di un aggiornamento del cluster database di produzione a una nuova versione principale](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
## Versioni dell’estensione PostGIS
Ti consigliamo di installare le versioni di tutte le estensioni, ad esempio PostGIS, come elencato in [Versioni delle estensioni per Aurora edizione compatibile con PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) nelle *Note di rilascio di Aurora PostgreSQL*. Per ottenere un elenco delle versioni disponibili nella versione, utilizza il comando seguente.
```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```
Puoi trovare le informazioni sulle versioni nelle sezioni seguenti delle *Note di rilascio di Aurora PostgreSQL*:
+ [Versioni delle estensioni per Aurora PostgreSQL 14](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Versioni delle estensioni per Aurora edizione compatibile con PostgreSQL 13](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Versioni delle estensioni per Aurora edizione compatibile con PostgreSQL 12](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Versioni delle estensioni per Aurora edizione compatibile con PostgreSQL 11](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Versioni delle estensioni per Aurora edizione compatibile con PostgreSQL 10](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Versioni delle estensioni per Aurora edizione compatibile con PostgreSQL 9.6](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## Aggiornamento di PostGIS 2 a PostGIS 3
A partire dalla versione 3.0, la funzionalità raster di PostGIS è ora un'estensione separata, `postgis_raster`. Questa estensione dispone di un proprio percorso di installazione e aggiornamento. Ciò rimuove dall'estensione `postgis` core dozzine di funzioni, tipi di dati e altri artefatti necessari per l'elaborazione di immagini raster. Ciò significa che se il caso d'uso non richiede l'elaborazione raster, non è necessario installare l'estensione `postgis_raster`.
Nel seguente esempio di aggiornamento, il primo comando di aggiornamento estrae la funzionalità raster nell'estensione `postgis_raster`. È quindi necessario un secondo comando di aggiornamento per eseguire l'aggiornamento di `postgis_raster` alla nuova versione.
**Per eseguire l'aggiornamento da PostGIS 2 a PostGIS 3**
1. Identifica la versione predefinita di PostGIS disponibile per la versione PostgreSQL sul cluster database Aurora PostgreSQL. A questo scopo, esegui la query seguente.
```
SELECT * FROM pg_available_extensions
WHERE default_version > installed_version;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.4 | 2.3.7 | PostGIS geometry and geography spatial types and functions
(1 row)
```
1. Identifica le versioni di PostGIS installate in ogni database sull'istanza di scrittura del cluster database Aurora PostgreSQL. In altre parole, esegui la query su ogni database utente come riportato di seguito.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------------------------------------
postgis | 2.3.7 | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)
```
Questa discrepanza tra la versione predefinita (PostGIS 3.1.4) e la versione installata (PostGIS 2.3.7) indica che è necessario aggiornare l'estensione PostGIS.
```
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
```
1. Esegui la seguente query per verificare che la funzionalità raster sia ora contenuta nel proprio pacchetto.
```
SELECT
probin,
count(*)
FROM
pg_proc
WHERE
probin LIKE '%postgis%'
GROUP BY
probin;
probin | count
--------------------------+-------
$libdir/rtpostgis-2.3 | 107
$libdir/postgis-3 | 487
(2 rows)
```
L'output mostra che c'è ancora una differenza tra le versioni. Le funzioni PostGIS sono versione 3 (postgis-3), mentre le funzioni raster (rtpostgis) sono versione 2 (rtpostgis-2.3). Per completare l'aggiornamento, esegui nuovamente il comando di aggiornamento, come riportato di seguito.
```
postgres=> SELECT postgis_extensions_upgrade();
```
Puoi ignorare i messaggi di avviso in sicurezza. Esegui nuovamente la seguente query per verificare che l'aggiornamento sia stato completato. L'aggiornamento è completato quando PostGIS e tutte le estensioni correlate non sono contrassegnate come necessarie di aggiornamento.
```
SELECT postgis_full_version();
```
1. Utilizza la seguente query per visualizzare il processo di aggiornamento completato e le estensioni impacchettate separatamente e verifica che le relative versioni corrispondano.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
----------------+---------+--------+---------------------------------------------------------------------
postgis | 3.1.5 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_raster | 3.1.5 | public | PostGIS raster types and functions
(2 rows)
```
L'output mostra che l'estensione PostGIS 2 è stata aggiornata a PostGIS 3 e che `postgis` e l'estensione `postgis_raster` ora separata sono entrambe versione 3.1.5.
Al termine dell'aggiornamento, se non prevedi di utilizzare la funzionalità raster, puoi rimuovere l'estensione come segue.
```
DROP EXTENSION postgis_raster;
```
# Gestione delle partizioni PostgreSQL con l'estensione pg\$1partman
Il partizionamento delle tabelle PostgreSQL fornisce un framework per la gestione ad alte prestazioni di input e reporting dei dati. Utilizzare il partizionamento per database che richiedono un input molto veloce di grandi quantità di dati. Il partizionamento fornisce anche query di tabelle di grandi dimensioni più veloci. Il partizionamento consente di conservare i dati senza influire sull'istanza del database perché richiede meno risorse I/O.
Utilizzando il partizionamento, è possibile suddividere i dati in blocchi di dimensioni personalizzate per l'elaborazione. Ad esempio, è possibile partizionare i dati delle serie temporali per intervalli quali orario, giornaliero, settimanale, mensile, trimestrale, annuale, personalizzato o qualsiasi combinazione di questi. Per un esempio di dati di serie temporali, se la tabella è stata partizionata per ora, ogni partizione conterrà un'ora di dati. Se si partiziona la tabella delle serie temporali per giorno, le partizioni conterranno i dati di un giorno e così via. La chiave di partizione controlla le dimensioni di una partizione.
Quando si utilizza un comando SQL `INSERT` o `UPDATE` in una tabella partizionata, il motore database indirizza i dati alla partizione appropriata. Le partizioni di tabella PostgreSQL che memorizzano i dati sono tabelle figlio della tabella principale.
Durante le letture delle query di database, l'ottimizzatore PostgreSQL esamina la clausola `WHERE` della query e, se possibile, indirizza la scansione del database solo alle partizioni pertinenti.
A partire dalla versione 10, PostgreSQL utilizza il partizionamento dichiarativo per implementare il partizionamento delle tabelle. Questo è noto anche come partizionamento PostgreSQL nativo. Prima di PostgreSQL versione 10, per implementare le partizioni venivano utilizzati i trigger.
Il partizionamento delle tabelle PostgreSQL fornisce le seguenti funzionalità:
+ Creazione di nuove partizioni in qualsiasi momento.
+ Intervalli di partizione variabili.
+ Partizioni scollegabili e ricollegabili utilizzando istruzioni DDL (Data Definition Language).
Ad esempio, le partizioni scollegabili sono utili per rimuovere i dati storici dalla partizione principale, conservando i dati storici per l'analisi.
+ Le nuove partizioni ereditano le proprietà della tabella di database padre, tra cui:
+ Indici
+ Chiavi primarie, che devono includere la colonna delle chiavi di partizione
+ Chiavi esterne
+ Vincoli check
+ Riferimenti
+ Creazione di indici per la tabella completa o per ogni partizione specifica.
Non è possibile modificare lo schema per una singola partizione. Tuttavia, è possibile modificare la tabella padre (ad esempio, aggiungendo una nuova colonna), che si propaga alle partizioni.
**Topics**
+ [Panoramica dell'estensione PostgreSQL pg\$1partman](#PostgreSQL_Partitions.pg_partman)
+ [Abilitazione dell'estensione pg\$1partman](#PostgreSQL_Partitions.enable)
+ [Configurazione delle partizioni utilizzando la funzione create\$1parent](#PostgreSQL_Partitions.create_parent)
+ [Configurazione della manutenzione delle partizioni utilizzando la funzione run\$1maintenance ance\$1proc](#PostgreSQL_Partitions.run_maintenance_proc)
## Panoramica dell'estensione PostgreSQL pg\$1partman
È possibile utilizzare l'estensione `pg_partman` PostgreSQL per automatizzare la creazione e la manutenzione delle partizioni di tabella. Per informazioni più generali, consulta [PG Partition Manager](https://github.com/pgpartman/pg_partman) nella documentazione di `pg_partman`.
**Nota**
L'estensione `pg_partman` è supportata su Aurora PostgreSQL versioni 12.6 e successive.
Invece di dover creare manualmente ogni partizione, è possibile configurare `pg_partman` con le seguenti impostazioni:
+ Tabella da partizionare
+ Tipo di partizione
+ Chiave di partizione
+ Granularità delle partizioni
+ Opzioni di pre-creazione e gestione delle partizioni
Dopo aver creato una tabella con partizioni PostgreSQL, la si registra con `pg_partman` chiamando la funzione `create_parent`. In questo modo vengono create le partizioni necessarie in base ai parametri passati alla funzione.
L'estensione `pg_partman` fornisce anche la funzione `run_maintenance_proc`, che è possibile chiamare su base pianificata per gestire automaticamente le partizioni. Per pianificare la creazione delle partizioni appropriate in base alle esigenze, puoi pianificare questa funzione in modo che venga eseguita periodicamente (ad esempio, ogni ora). È inoltre possibile assicurarsi che le partizioni vengano eliminate automaticamente.
## Abilitazione dell'estensione pg\$1partman
Se disponi di più database all'interno della stessa istanza database per cui desideri gestire le partizioni, è necessario abilitare l'estensione `pg_partman` separatamente per ogni database. Per abilitare l'estensione `pg_partman` per un database specifico, crea lo schema di manutenzione delle partizioni, quindi crea l'estensione `pg_partman` nel modo seguente:
```
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
```
**Nota**
Per creare l'estensione `pg_partman`, assicurati di disporre dei privilegi `rds_superuser`.
Se viene restituito un errore come il seguente, concedi i privilegi `rds_superuser` all'account o utilizza l'account utente avanzato.
```
ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.
```
Per concedere i privilegi `rds_superuser`, collegati con l'account utente avanzato ed emetti il seguente comando:
```
GRANT rds_superuser TO user-or-role;
```
Per gli esempi che mostrano l’uso dell’estensione pg\$1partman, si utilizza la tabella di database e la partizione di esempio seguenti. Questo database utilizza una tabella partizionata basata su un timestamp. Uno schema `data_mart` contiene una tabella denominata `events` con una colonna denominata `created_at`. Nella tabella `events` sono incluse le seguenti impostazioni:
+ Chiavi primarie `event_id` e `created_at`, che devono avere la colonna utilizzata per guidare la partizione.
+ Un vincolo di controllo `ck_valid_operation` per applicare i valori per una colonna della tabella `operation` .
+ Due chiavi esterne, dove una (`fk_orga_membership)`) punta alla tabella esterna `organization` e l'altra (`fk_parent_event_id`) è una chiave esterna autoreferenziata.
+ Due indici, dove uno (`idx_org_id`) è per la chiave esterna e l'altro (`idx_event_type`) è per il tipo di evento.
Le seguenti istruzioni DDL creano questi oggetti, che verranno inclusi automaticamente in ogni partizione.
```
CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
org_name TEXT,
CONSTRAINT pk_organization PRIMARY KEY (org_id)
);
CREATE TABLE data_mart.events(
event_id BIGSERIAL,
operation CHAR(1),
value FLOAT(24),
parent_event_id BIGINT,
event_type VARCHAR(25),
org_id BIGSERIAL,
created_at timestamp,
CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at),
CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'),
CONSTRAINT fk_orga_membership
FOREIGN KEY(org_id)
REFERENCES data_mart.organization (org_id),
CONSTRAINT fk_parent_event_id
FOREIGN KEY(parent_event_id, created_at)
REFERENCES data_mart.events (event_id,created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX idx_org_id ON data_mart.events(org_id);
CREATE INDEX idx_event_type ON data_mart.events(event_type);
```
## Configurazione delle partizioni utilizzando la funzione create\$1parent
Dopo aver abilitato l'estensione `pg_partman`, utilizza la funzione `create_parent` per configurare le partizioni all'interno dello schema di manutenzione delle partizioni. In questo esempio viene utilizzato l’esempio della tabella `events` creato in [Abilitazione dell'estensione pg\$1partmanConfigurazione della manutenzione delle partizioni utilizzando la funzione run\$1maintenance ance\$1proc](#PostgreSQL_Partitions.enable). Richiama la funzione `create_parent` come segue:
```
SELECT partman.create_parent(
p_parent_table => 'data_mart.events',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 day',
p_premake => 30);
```
I parametri sono i seguenti:
+ `p_parent_table` – La tabella partizionata padre. Questa tabella deve già esistere ed essere completa, deve ovvero includere lo schema.
+ `p_control` – Colonna su cui basare il partizionamento. Il tipo di dati deve essere intero o basato sul tempo.
+ `p_type`: il tipo è `'range'` o `'list'`.
+ `p_interval` – Intervallo di tempo o intervallo intero per ogni partizione. I valori di esempio includono `1 day`, `1 hour` e così via.
+ `p_premake` – Il numero di partizioni da creare in anticipo per supportare nuovi inserimenti.
Per una descrizione completa della funzione `create_parent`, consulta [Funzioni di creazione](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions) nella documentazione `pg_partman`.
## Configurazione della manutenzione delle partizioni utilizzando la funzione run\$1maintenance ance\$1proc
È possibile eseguire operazioni di manutenzione delle partizioni per creare automaticamente nuove partizioni, scollegare partizioni o rimuovere partizioni obsolete. La manutenzione delle partizioni si basa sulla funzione `run_maintenance_proc` dell'estensione `pg_partman` e dell'estensione `pg_cron`, che avvia un pianificatore interno. Lo scheduler `pg_cron` esegue automaticamente le istruzioni SQL, le funzioni e le procedure definite nei database.
Nell'esempio seguente viene utilizzato l'esempio della tabella `events` creata in [Abilitazione dell'estensione pg\$1partmanConfigurazione della manutenzione delle partizioni utilizzando la funzione run\$1maintenance ance\$1proc](#PostgreSQL_Partitions.enable) per impostare l'esecuzione automatica delle operazioni di manutenzione delle partizioni. Come prerequisito, aggiungere `pg_cron` al parametro `shared_preload_libraries` nel gruppo di parametri dell'istanza database.
```
CREATE EXTENSION pg_cron;
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
```
Di seguito, puoi trovare una step-by-step spiegazione dell'esempio precedente:
1. Modifica il gruppo di parametri associato all'istanza database e aggiungi `pg_cron` al valore del parametro `shared_preload_libraries`. Perché questa modifica abbia effetto, è necessario riavviare l'istanza database. Per ulteriori informazioni, consulta [Modifica dei parametri in un gruppo di parametri database in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
1. Emettere il comando `CREATE EXTENSION pg_cron;` utilizzando un account con le autorizzazioni `rds_superuser`. In questo modo, viene abilitata l’estensione `pg_cron`. Per ulteriori informazioni, consulta [Pianificazione della manutenzione con l'estensione PostgreSQL pg\$1cron](PostgreSQL_pg_cron.md).
1. Emettere il comando `UPDATE partman.part_config` per regolare le impostazioni `pg_partman` per la tabella `data_mart.events`.
1. Eseguire il comando `SET` . . . per configurare la tabella `data_mart.events`, con le seguenti clausole:
1. `infinite_time_partitions = true,` – Configura la tabella in modo da poter creare automaticamente nuove partizioni senza limiti.
1. `retention = '3 months',` – Configura la tabella in modo che venga conservata per un massimo di tre mesi.
1. `retention_keep_table=true `– Configura la tabella in modo che quando il periodo di conservazione è scaduto, la tabella non venga eliminata automaticamente. Le partizioni precedenti al periodo di conservazione vengono invece scollegate dalla tabella padre.
1. Eseguire il comando `SELECT cron.schedule` . . . per creare una chiamata di funzione `pg_cron`. Questa chiamata definisce la frequenza con cui lo scheduler esegue la procedura di manutenzione `pg_partman`, `partman.run_maintenance_proc`. Per questo esempio, la procedura viene eseguita ogni ora.
Per una descrizione completa della funzione `run_maintenance_proc`, consulta [Funzioni di manutenzione](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions) nella documentazione di `pg_partman`.
# Pianificazione della manutenzione con l'estensione PostgreSQL pg\$1cron
Puoi utilizzare l'estensione PostgreSQL `pg_cron` per pianificare i comandi di manutenzione all'interno di un database PostgreSQL. Per ulteriori informazioni sull'estensione, consulta [Che cos'è pg\$1cron?](https://github.com/citusdata/pg_cron) nella documentazione di pg\$1cron.
L'estensione `pg_cron` è supportata sul motore Aurora PostgreSQL versioni 12.6 e successive
Per ulteriori informazioni sull'uso di `pg_cron`, consulta [Schedule jobs with pg\$1cron on your RDS for PostgreSQL or your Aurora PostgreSQL-Compatible Edition databases](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/) (Pianificazione dei processi con pg\$1cron sui database RDS per PostgreSQL o Aurora edizione compatibile con PostgreSQL).
**Nota**
La versione dell’estensione `pg_cron` viene visualizzata come versione a due cifre, ad esempio 1.6, nella vista pg\$1available\$1extensions. Sebbene in alcuni contesti possano essere elencate versioni a tre cifre, ad esempio 1.6.4 o 1.6.5, è necessario specificare la versione a due cifre quando si esegue un aggiornamento dell’estensione.
**Topics**
+ [Configurazione dell'estensione pg\$1cron](#PostgreSQL_pg_cron.enable)
+ [Concessione delle autorizzazioni per utilizzare pg\$1cron agli utenti del database](#PostgreSQL_pg_cron.permissions)
+ [Programmazione di processi pg\$1cron](#PostgreSQL_pg_cron.examples)
+ [Riferimento per l'estensione pg\$1cron](#PostgreSQL_pg_cron.reference)
## Configurazione dell'estensione pg\$1cron
Configura l'estensione `pg_cron` come riportato di seguito:
1. Modifica il gruppo di parametri personalizzati associato all'istanza database PostgreSQL aggiungendo `pg_cron` al valore del parametro `shared_preload_libraries`.
Per rendere effettive le modifiche apportate al gruppo di parametri, riavvia l'istanza database PostgreSQL. Per ulteriori informazioni sull'utilizzo dei gruppi di parametri, consulta [Amazon Aurora PostgreSQL parametri](AuroraPostgreSQL.Reference.ParameterGroups.md).
1. Dopo il riavvio dell'istanza database PostgreSQL, esegui il comando riportato di seguito utilizzando un account che dispone delle autorizzazioni `rds_superuser`. Ad esempio, se hai utilizzato le impostazioni di default quando hai creato il cluster di database Aurora PostgreSQL, connettiti come utente `postgres` e crea l'estensione.
```
CREATE EXTENSION pg_cron;
```
Lo scheduler `pg_cron` è impostato nel database PostgreSQL predefinito denominato `postgres`. Gli oggetti `pg_cron` vengono creati in questo database `postgres` e tutte le azioni di pianificazione vengono eseguite in questo database.
1. Puoi usare le impostazioni predefinite oppure puoi pianificare i processi da eseguire in altri database all'interno dell'istanza database di PostgreSQL. Per pianificare i processi per altri database all'interno dell'istanza database di PostgreSQL, consulta l'esempio in [Pianificazione di un processo cron per un database diverso da quello predefinito](#PostgreSQL_pg_cron.otherDB).
## Concessione delle autorizzazioni per utilizzare pg\$1cron agli utenti del database
L'installazione dell'estensione `pg_cron` richiede privilegi `rds_superuser`. Tuttavia, le autorizzazioni per utilizzare `pg_cron` possono essere concesse (da un membro del gruppo/ruolo `rds_superuser`) ad altri utenti del database, in modo che possano pianificare i propri lavori. Ti consigliamo di concedere le autorizzazioni allo schema `cron` solo se in base alle esigenze se migliora le operazioni nell'ambiente di produzione.
Per concedere a un database l'autorizzazione utente nello schema `cron`, esegui il seguente comando:
```
postgres=> GRANT USAGE ON SCHEMA cron TO db-user;
```
Questo dà *db-user* il permesso di accedere allo `cron` schema per pianificare i cron job per gli oggetti a cui hanno i permessi di accesso. Se l'utente del database non dispone di autorizzazioni, il processo non va a buon fine dopo aver pubblicato il messaggio di errore nel file `postgresql.log`, come mostrato di seguito:
```
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
```
In altre parole, assicurarsi che gli utenti del database a cui sono concesse autorizzazioni per lo schema `cron`, dispongano anche di autorizzazioni sugli oggetti (tabelle, schemi e così via) che intendono pianificare.
I dettagli del processo cron e l’esito positivo o negativo sono anche acquisiti nella tabella `cron.job_run_details`. Per ulteriori informazioni, consulta [Tabelle per la pianificazione dei processi e l'acquisizione dello stato](#PostgreSQL_pg_cron.tables).
## Programmazione di processi pg\$1cron
Nelle sezioni seguenti viene illustrato come è possibile pianificare varie attività di gestione utilizzando le attività `pg_cron`.
**Nota**
Quando crei processi `pg_cron`, controlla che l'impostazione `max_worker_processes` sia maggiore del numero di `cron.max_running_jobs`. Un processo `pg_cron` non riesce se i processi worker in background vengono esauriti. Il numero predefinito di processi `pg_cron` è `5`. Per ulteriori informazioni, consulta [Parametri per la gestione dell'estensione pg\$1cron](#PostgreSQL_pg_cron.parameters).
**Topics**
+ [Vacuum di una tabella](#PostgreSQL_pg_cron.vacuum)
+ [Eliminazione della tabella della cronologia di pg\$1cron](#PostgreSQL_pg_cron.job_run_details)
+ [Registrazione degli errori solo nel file postgresql.log](#PostgreSQL_pg_cron.log_run)
+ [Pianificazione di un processo cron per un database diverso da quello predefinito](#PostgreSQL_pg_cron.otherDB)
### Vacuum di una tabella
Autovacuum gestisce la manutenzione del vacuum per la maggior parte dei casi. Tuttavia, potresti voler programmare un vacuum di una tabella specifica in un momento di tua scelta.
Di seguito è riportato un esempio di utilizzo della funzione `cron.schedule` per impostare un processo in modo da utilizzare `VACUUM FREEZE` su una tabella specifica ogni giorno alle 22:00 (GMT).
```
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
```
Dopo l'esecuzione dell'esempio precedente, è possibile controllare la cronologia nella tabella `cron.job_run_details` come riportato di seguito.
```
postgres=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)
```
Di seguito è riportata una query della tabella `cron.job_run_details` per visualizzare i processi non riusciti.
```
postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)
```
Per ulteriori informazioni, consulta [Tabelle per la pianificazione dei processi e l'acquisizione dello stato](#PostgreSQL_pg_cron.tables).
### Eliminazione della tabella della cronologia di pg\$1cron
La tabella `cron.job_run_details` contiene una cronologia di processi cron che può crescere a dismisura nel tempo. Si consiglia di pianificare un processo che elimini questa tabella. Ad esempio, conservare una settimana di voci può essere sufficiente per la risoluzione dei problemi.
Nell'esempio seguente viene utilizzata la funzione [cron.schedule](#PostgreSQL_pg_cron.schedule) per pianificare un processo che viene eseguito ogni giorno a mezzanotte per rimuovere la tabella `cron.job_run_details`. Il processo conserva solo gli ultimi sette giorni. Utilizza l'account `rds_superuser` per pianificare il processo come riportato di seguito.
```
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
```
Per ulteriori informazioni, consulta [Tabelle per la pianificazione dei processi e l'acquisizione dello stato](#PostgreSQL_pg_cron.tables).
### Registrazione degli errori solo nel file postgresql.log
Per disattivare la scrittura nella tabella `cron.job_run_details`, modifica il gruppo di parametri associato all'istanza database PostgreSQL e disattiva il parametro `cron.log_run`. L'estensione `pg_cron` non scrive più nella tabella e acquisisce gli errori solo nel file `postgresql.log`. Per ulteriori informazioni, consulta [Modifica dei parametri in un gruppo di parametri database in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
Utilizza il seguente comando per controllare il valore del parametro `cron.log_run`.
```
postgres=> SHOW cron.log_run;
```
Per ulteriori informazioni, consulta [Parametri per la gestione dell'estensione pg\$1cron](#PostgreSQL_pg_cron.parameters).
### Pianificazione di un processo cron per un database diverso da quello predefinito
I metadati per `pg_cron` sono tutti contenuti nel database predefinito PostgreSQL denominato `postgres`. Poiché i worker in background vengono utilizzati per l'esecuzione dei processi cron di manutenzione, puoi pianificare un processo in uno qualsiasi dei database all'interno dell'istanza database PostgreSQL.
**Nota**
Solo gli utenti con il ruolo `rds_superuser` o i privilegi `rds_superuser` possono elencare tutti i processi cron nel database. Gli altri utenti possono visualizzare solo i propri processi nella tabella `cron.job`.
1. Nel database cron, pianifica il processo come si farebbe normalmente utilizzando [cron.schedule](#PostgreSQL_pg_cron.schedule).
```
postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
```
1. Con il ruolo `rds_superuser`, aggiorna la colonna del database per il processo appena creato in modo che venga eseguito in un altro database all'interno dell'istanza database PostgreSQL.
```
postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
```
1. Verifica eseguendo una query sulla tabella `cron.job`.
```
postgres=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)
```
**Nota**
In alcune situazioni, è possibile aggiungere un processo cron che si intende eseguire in un database diverso. In questi casi, il processo potrebbe essere eseguito nel database predefinito (`postgres`) prima di aggiornare la colonna del database corretta. Se il nome utente dispone delle autorizzazioni, il processo viene eseguito correttamente nel database predefinito.
## Riferimento per l'estensione pg\$1cron
È possibile utilizzare i seguenti parametri, funzioni e tabelle con l'estensione `pg_cron`. Per ulteriori informazioni, consultare [Che cos'è pg\$1cron?](https://github.com/citusdata/pg_cron) nella documentazione di pg\$1cron.
**Topics**
+ [Parametri per la gestione dell'estensione pg\$1cron](#PostgreSQL_pg_cron.parameters)
+ [Riferimento alla funzione: cron.schedule](#PostgreSQL_pg_cron.schedule)
+ [Riferimento alla funzione: cron.unschedule](#PostgreSQL_pg_cron.unschedule)
+ [Tabelle per la pianificazione dei processi e l'acquisizione dello stato](#PostgreSQL_pg_cron.tables)
### Parametri per la gestione dell'estensione pg\$1cron
Di seguito è riportato l'elenco dei parametri che consentono di controllare il comportamento dell'estensione `pg_cron`.
| Parametro | Description |
| --- | --- |
| cron.database\$1name | Il database in cui vengono conservati i metadati `pg_cron`. |
| cron.host | Il nome host per connettersi a PostgressQL. Non è possibile modificare questo valore. |
| cron.log\$1run | Registra tutti i processi eseguiti nella tabella `job_run_details`. I valori sono `on` o `off`. Per ulteriori informazioni, consulta [Tabelle per la pianificazione dei processi e l'acquisizione dello stato](#PostgreSQL_pg_cron.tables). |
| cron.log\$1statement | Registra tutte le istruzioni cron prima di eseguirle. I valori sono `on` o `off`. |
| cron.max\$1running\$1jobs | Numero massimo di processi che possono essere eseguiti contemporaneamente. |
| cron.use\$1background\$1workers | Utilizza i worker in background anziché le sessioni client. Non è possibile modificare questo valore. |
Puoi utilizzare il comando SQL seguente per visualizzare questi parametri e i relativi valori.
```
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
```
### Riferimento alla funzione: cron.schedule
Questa funzione pianifica un processo cron. Il processo viene inizialmente pianificato nel database `postgres` predefinito. La funzione restituisce un valore `bigint` che rappresenta l'identificativo del processo. Per pianificare l'esecuzione di processi in altri database all'interno dell'istanza database di PostgreSQL, consulta l'esempio in [Pianificazione di un processo cron per un database diverso da quello predefinito](#PostgreSQL_pg_cron.otherDB).
La funzione ha due diverse sintassi.
**Sintassi**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**Parametri**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**Esempi**
```
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)
postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
```
### Riferimento alla funzione: cron.unschedule
Questa funzione elimina un processo cron. Puoi specificare `job_name` o `job_id`. Una policy assicura che l'utente sia il proprietario e possa rimuovere la pianificazione per il processo. La funzione restituisce un valore Booleano che indica la riuscita o l'errore.
La funzione ha la seguente sintassi.
**Sintassi**
```
cron.unschedule (job_id);
cron.unschedule (job_name);
```
**Parametri**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**Esempi**
```
postgres=> SELECT cron.unschedule(108);
unschedule
------------
t
(1 row)
postgres=> SELECT cron.unschedule('test');
unschedule
------------
t
(1 row)
```
### Tabelle per la pianificazione dei processi e l'acquisizione dello stato
Le seguenti tabelle vengono create e utilizzate per pianificare i processi cron e registrare il modo in cui i processi vengono completati.
| Tabella | Descrizione |
| --- | --- |
| cron.job | Contiene i metadati relativi a ciascun processo pianificato. La maggior parte delle interazioni con questa tabella dovrebbe essere eseguita tramite le funzioni `cron.schedule` e `cron.unschedule`. Si sconsiglia di concedere privilegi di aggiornamento/inserimento direttamente a questa tabella. In questo modo, si consente all'utente di aggiornare la colonna `username` da eseguire come `rds-superuser`. |
| cron.job\$1run\$1details | Contiene informazioni cronologiche sulle esecuzioni precedenti dei processi pianificati. Ciò è utile per analizzare lo stato, i messaggi restituiti e l'ora di inizio e di fine dall'esecuzione del processo. Per evitare che questa tabella cresca in maniera indefinita, è necessario eliminarla su base regolare. Per un esempio, consulta [Eliminazione della tabella della cronologia di pg\$1cron](#PostgreSQL_pg_cron.job_run_details). |
# Utilizzo di pgAudit per registrare l'attività del database
Gli istituti finanziari, gli enti governativi e molti settori devono conservare i *log di audit* per soddisfare i requisiti normativi. L'utilizzo dell'estensione PostgreSQL Audit (pgAudit) con il cluster database Aurora PostgreSQL consente di acquisire i record dettagliati richiesti in genere dai revisori o di soddisfare requisiti normativi. Ad esempio, è possibile impostare l'estensione pgAudit per tenere traccia delle modifiche apportate a database e tabelle specifici, per registrare l'utente che ha apportato la modifica e molti altri dettagli.
L'estensione pgAudit si basa sulla funzionalità dell'infrastruttura di registrazione PostgreSQL nativa estendendo i messaggi di registro con maggiori dettagli. In altre parole, l'approccio utilizzato per visualizzare il registro di audit è lo stesso utilizzato per visualizzare i messaggi di registro. Per ulteriori informazioni sulla registrazione PostgreSQL, consulta [](USER_LogAccess.Concepts.PostgreSQL.md).
L'estensione pgAudit consente di oscurare i dati sensibili, come le password in chiaro, dai registri. Se il cluster database Aurora PostgreSQL è configurato per registrare le istruzioni DML (Data Manipulation Language) come descritto in [](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md), il problema delle password in chiaro può essere evitato utilizzando l'estensione PostgreSQL Audit.
È possibile configurare l'audit sulle istanze database con un elevato grado di specificità. Puoi eseguire l'audit di tutti i database e di tutti gli utenti. In alternativa, è possibile scegliere di eseguire l'audit solo di determinati database, utenti e altri oggetti. È inoltre possibile escludere esplicitamente determinati utenti e database dall'audit. Per ulteriori informazioni, consulta [Esclusione di utenti o database dalla registrazione di audit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md).
Data la quantità di dettagli che è possibile acquisire, ti consigliamo di monitorare il consumo di archiviazione se utilizzi pgAudit.
L'estensione pgAudit è supportata su tutte le versioni di Aurora PostgreSQL disponibili. Per un elenco delle versioni di pgAudit supportate dalla versione di Aurora PostgreSQL, consulta [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) (Versioni delle estensioni per Amazon Aurora PostgreSQL) in *Release Notes for Aurora PostgreSQL* (Note di rilascio di Aurora PostgreSQL).
**Topics**
+ [Configurazione dell'estensione pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)
+ [Audit di oggetti di database](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md)
+ [Esclusione di utenti o database dalla registrazione di audit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)
+ [Riferimento per l'estensione pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md)
# Configurazione dell'estensione pgAudit
Per configurare l'estensione pgAudit sul cluster database Aurora PostgreSQL, aggiungi innanzitutto pgAudit alle librerie condivise nel gruppo di parametri cluster database personalizzato per il cluster database Aurora PostgreSQL. Per informazioni sulla creazione di un gruppo di parametri del·cluster database, consulta [Gruppi di parametri per Amazon Aurora](USER_WorkingWithParamGroups.md). Quindi, installa l'estensione pgAudit. Infine, specifica i database e gli oggetti di cui eseguire l'audit. Le procedure in questa sezione mostrano come fare. Puoi utilizzare la Console di gestione AWS o l'AWS CLI.
Per eseguire tutte queste attività, sono richieste autorizzazioni come il ruolo `rds_superuser`.
Le fasi seguenti si basano sull'ipotesi che il cluster database Aurora PostgreSQL sia associato a un gruppo di parametri cluster di database personalizzato.
## Console
**Per impostare l'estensione pgAudit**
1. Accedi alla Console di gestione AWS e apri la console Amazon RDS all'indirizzo [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Nel riquadro di navigazione, scegli l'istanza di scrittura del cluster database Aurora PostgreSQL .
1. Apri la scheda **Configurazione** per l'istanza di scrittura del cluster database Aurora PostgreSQL. Tra i dettagli dell'istanza, individua il collegamento **Parameter group** (Gruppo di parametri).
1. Scegli il collegamento per aprire i parametri personalizzati associati al cluster database Aurora PostgreSQL.
1. Nel campo di ricerca **Parametri**, digita `shared_pre` per trovare il parametro `shared_preload_libraries`.
1. Scegli **Edit parameters** (Modifica parametri) per accedere ai valori delle proprietà.
1. Aggiungi `pgaudit` all'elenco nel campo **Values** (Valori). Utilizza una virgola per separare gli elementi nell'elenco di valori.
![\[Immagine del parametro shared_preload_libaries con pgAudit aggiunto.\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Riavvia l'istanza di scrittura del cluster database Aurora PostgreSQL in modo che la modifica al parametro `shared_preload_libraries` diventi effettiva.
1. Quando l'istanza è disponibile, verifica che pgAudit sia stato inizializzato. Utilizza `psql` per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL, quindi esegui il comando seguente.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. Con pgAudit inizializzato, puoi ora creare l'estensione. L'estensione deve essere creata dopo aver inizializzato la libreria perché l'estensione `pgaudit` installa i trigger evento per l'audit delle istruzioni DDL (Data Definition Language).
```
CREATE EXTENSION pgaudit;
```
1. Chiudi la sessione `psql`.
```
labdb=> \q
```
1. Accedi alla Console di gestione AWS e apri la console Amazon RDS all'indirizzo [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Trova il parametro `pgaudit.log` nell'elenco e impostalo sul valore appropriato per il caso d'uso. Ad esempio, se si imposta il parametro `pgaudit.log` su `write` come mostrato nell'immagine seguente, gli inserimenti, gli aggiornamenti, le eliminazioni e alcuni altri tipi di modifiche vengono acquisiti nel registro.
![\[Immagine del parametro pgaudit.log con l'impostazione.\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/images/rpg_set_pgaudit-log-level.png)
Puoi anche scegliere uno dei seguenti valori per il parametro `pgaudit.log`.
+ none: valore predefinito. Non viene registrata alcuna modifica al database.
+ all: registra tutto (read, write, function, role, ddl, misc).
+ ddl: registra tutte le istruzioni DDL (Data Definition Language) non incluse nella classe `ROLE`.
+ function: registra le chiamate di funzione e blocchi `DO`.
+ misc: registra vari comandi come `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` e `SET`.
+ read: registra `SELECT` e `COPY` quando l'origine è una relazione (ad esempio una tabella) o una query.
+ role: registra le istruzioni correlate a ruoli e privilegi, ad esempio `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` e `DROP ROLE`.
+ write: registra `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` e `COPY` quando la destinazione è una relazione (tabella).
1. Scegli **Save changes** (Salva modifiche).
1. Apri la console di Amazon RDS all'indirizzo [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Nell’elenco Database, scegli l’istanza di scrittura del cluster di database Aurora PostgreSQL.
## AWS CLI
**Per configurare pgAudit**
Per configurare pgAudit utilizzando AWS CLI, chiama l'operazione [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) per modificare i parametri del registro di audit nel gruppo di parametri personalizzati, come illustrato nella procedura seguente.
1. Utilizza il seguente comando AWS CLI per aggiungere `pgaudit` al parametro `shared_preload_libraries`.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Utilizza il comando AWS CLI seguente per riavviare l'istanza di scrittura del cluster database Aurora PostgreSQL in modo che la libreria pgaudit venga inizializzata.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Quando l'istanza è disponibile, verifica che `pgaudit` sia stato inizializzato. Utilizza `psql` per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL, quindi esegui il comando seguente.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
Con pgAudit inizializzato, puoi ora creare l'estensione.
```
CREATE EXTENSION pgaudit;
```
1. Chiudi la sessione `psql` in modo da poter utilizzare AWS CLI.
```
labdb=> \q
```
1. Utilizza il comando AWS CLI seguente per specificare le classi di istruzioni che desideri registrare mediante la registrazione di audit della sessione. L'esempio imposta il parametro `pgaudit.log` su `write`, che acquisisce inserimenti, aggiornamenti ed eliminazioni nel registro.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
--region aws-region
```
Puoi anche scegliere uno dei seguenti valori per il parametro `pgaudit.log`.
+ none: valore predefinito. Non viene registrata alcuna modifica al database.
+ all: registra tutto (read, write, function, role, ddl, misc).
+ ddl: registra tutte le istruzioni DDL (Data Definition Language) non incluse nella classe `ROLE`.
+ function: registra le chiamate di funzione e blocchi `DO`.
+ misc: registra vari comandi come `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` e `SET`.
+ read: registra `SELECT` e `COPY` quando l'origine è una relazione (ad esempio una tabella) o una query.
+ role: registra le istruzioni correlate a ruoli e privilegi, ad esempio `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` e `DROP ROLE`.
+ write: registra `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` e `COPY` quando la destinazione è una relazione (tabella).
Riavvia l'istanza di scrittura del cluster database Aurora PostgreSQL utilizzando il comando AWS CLI seguente.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Audit di oggetti di database
Con pgAudit impostata sul cluster database Aurora PostgreSQL e configurata per i requisiti, informazioni più dettagliate vengono acquisite nel registro PostgreSQL. Ad esempio, sebbene la configurazione della registrazione PostgreSQL predefinita consenta di identificare la data e l'ora della modifica apportata a una tabella di database, con l'estensione pgAudit la voce di registro può includere lo schema, l'utente che ha apportato la modifica e altri dettagli a seconda della configurazione dei parametri dell'estensione. È possibile configurare l'audit per tenere traccia delle modifiche nei modi seguenti.
+ Per ogni sessione, per utente. Per il livello di sessione, è possibile acquisire il testo del comando completo.
+ Per ogni oggetto, per utente e per database.
La funzionalità di audit degli oggetti viene attivata quando il ruolo `rds_pgaudit` viene creato nel sistema e quindi aggiunto al parametro `pgaudit.role` nel gruppo di parametri personalizzati. Per impostazione predefinita, l'impostazione del parametro `pgaudit.role` viene annullata e l'unico valore consentito è `rds_pgaudit`. Nelle fasi seguenti si presume che `pgaudit` sia stato inizializzato e che l'estensione `pgaudit` sia stata creata seguendo la procedura in [Configurazione dell'estensione pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md).
![\[Immagine del file di registro di PostgreSQL dopo la configurazione di pgAudit.\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/images/pgaudit-log-example.png)
Come mostrato in questo esempio, la riga "LOG: AUDIT: SESSION" fornisce informazioni sulla tabella e il relativo schema, insieme ad altri dettagli.
**Per configurare l'audit degli oggetti**
1. Utilizza `psql` per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL.
```
psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
```
1. Crea un ruolo del database denominato `rds_pgaudit` utilizzando il comando seguente.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=>
```
1. Chiudi la sessione `psql`.
```
labdb=> \q
```
Nei passaggi successivi, utilizza AWS CLI per modificare i parametri del registro di audit nel gruppo di parametri personalizzati.
1. Utilizza il seguente comando AWS CLI per impostare il parametro `pgaudit.role` su `rds_pgaudit`. Per impostazione predefinita, questo parametro è vuoto e `rds_pgaudit` è l'unico valore consentito.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Utilizza il seguente comando AWS CLI per riavviare l'istanza di scrittura del cluster database Aurora PostgreSQL in modo da rendere effettive le modifiche apportate ai parametri.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Esegui il comando seguente per verificare che `pgaudit.role` sia impostato su `rds_pgaudit`.
```
SHOW pgaudit.role;
pgaudit.role
------------------
rds_pgaudit
```
Per testare la registrazione di pgAudit, è possibile eseguire diversi comandi di esempio da controllare. Ad esempio, si potrebbero eseguire i comandi seguenti.
```
CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id
----
(0 rows)
```
I registri del database devono contenere una voce simile alla seguente:
```
...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...
```
Per informazioni sulla visualizzazione dei registri, consulta [Monitoraggio dei file di log di Amazon Aurora](USER_LogAccess.md).
Per ulteriori informazioni sull'estensione pgAudit, consulta [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md) su GitHub.
# Esclusione di utenti o database dalla registrazione di audit
Come illustrato in [](USER_LogAccess.Concepts.PostgreSQL.md), i registri di PostgreSQL consumano spazio di archiviazione. L'uso dell'estensione pgAudit consente di aumentare il volume di dati raccolti nei registri in misura diversa, a seconda delle modifiche che vengono monitorate. Potrebbe non essere necessario eseguire l'audit di ogni utente o database nel cluster database Aurora PostgreSQL.
Per ridurre al minimo l'impatto sull'archiviazione ed evitare di acquisire inutilmente i record di audit, è possibile escludere utenti e database dall'audit. È anche possibile modificare la registrazione all'interno di una determinata sessione. Negli esempi seguenti viene mostrato come fare.
**Nota**
Le impostazioni dei parametri a livello di sessione hanno la precedenza sulle impostazioni nel gruppo di parametri cluster database personalizzato per l'istanza di scrittura del cluster database Aurora PostgreSQL. Per evitare che gli utenti del database ignorino le impostazioni di configurazione della registrazione di audit, accertati di modificare le loro autorizzazioni.
Supponi che il cluster database Aurora PostgreSQL sia configurato per eseguire l'audit dello stesso livello di attività per tutti gli utenti e i database. Decidi quindi di non voler eseguire l'audit dell'utente `myuser`. Puoi disattivare l'audit per `myuser` con il comando SQL seguente.
```
ALTER USER myuser SET pgaudit.log TO 'NONE';
```
Quindi, puoi utilizzare la seguente query per controllare la colonna `user_specific_settings` per `pgaudit.log` per verificare che sia impostata su `NONE`.
```
SELECT
usename AS user_name,
useconfig AS user_specific_settings
FROM
pg_user
WHERE
usename = 'myuser';
```
Viene visualizzato l'output riportato di seguito.
```
user_name | user_specific_settings
-----------+------------------------
myuser | {pgaudit.log=NONE}
(1 row)
```
Puoi disattivare la registrazione per un determinato utente durante la sessione con il database con il seguente comando.
```
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';
```
Utilizza la seguente query per controllare la colonna delle impostazioni per pgaudit.log per una combinazione specifica di utente e database.
```
SELECT
usename AS "user_name",
datname AS "database_name",
pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
usename = 'myuser'
AND datname = 'mydatabase'
ORDER BY
1,
2;
```
L'output visualizzato è simile al seguente.
```
user_name | database_name | settings
-----------+---------------+------------------
myuser | mydatabase | pgaudit.log=none
(1 row)
```
Dopo aver disattivato l'audit per `myuser`, decidi di non voler tenere traccia delle modifiche apportate a `mydatabase`. Puoi disattivare l'audit per il database specifico utilizzando il comando seguente.
```
ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';
```
Quindi, utilizza la seguente query per controllare la colonna database\$1specific\$1settings per verificare che pgaudit.log sia impostato su NONE.
```
SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';
```
Viene visualizzato l'output riportato di seguito.
```
database_name | database_specific_settings
---------------+----------------------------
mydatabase | {pgaudit.log=NONE}
(1 row)
```
Per ripristinare le impostazioni predefinite di myuser, utilizza il comando seguente:
```
ALTER USER myuser RESET pgaudit.log;
```
Per ripristinare i valori predefiniti delle impostazioni di un database, utilizza il comando seguente.
```
ALTER DATABASE mydatabase RESET pgaudit.log;
```
Per ripristinare l'impostazione predefinita di utente e database, utilizza il comando seguente.
```
ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;
```
Puoi anche acquisire eventi specifici nel registro impostando `pgaudit.log` su uno degli altri valori consentiti per il parametro `pgaudit.log`. Per ulteriori informazioni, consulta [Elenco delle impostazioni consentite per il parametro `pgaudit.log`](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings).
```
ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'
```
# Riferimento per l'estensione pgAudit
Puoi specificare il livello di dettaglio desiderato per il registro di audit modificando uno o più dei parametri elencati in questa sezione.
## Controllo del comportamento di pgAudit
Puoi controllare la registrazione di audit modificando uno o più dei parametri elencati nella tabella seguente.
| Parametro | Description |
| --- | --- |
| `pgaudit.log` | Specifica quali classi di istruzioni verranno registrate per registrazione di audit della sessione. I valori consentiti includono ddl, function, misc, read, role, write, none, all. Per ulteriori informazioni, consulta [Elenco delle impostazioni consentite per il parametro `pgaudit.log`](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings). |
| `pgaudit.log_catalog` | Quando è attivato (impostato su 1), aggiunge istruzioni all'audit trail se tutte le relazioni in un'istruzione si trovano in pg\$1catalog. |
| `pgaudit.log_level` | Specifica il livello di registro che verrà utilizzato per le voci di registro. Valori consentiti: debug5, debug4, debug3, debug2, debug1, info, notice, warning, log |
| `pgaudit.log_parameter` | Quando è attivato (impostato su 1), i parametri passati con l'istruzione vengono acquisiti nel registro di audit. |
| `pgaudit.log_relation` | Quando è attivato (impostato su 1), il registro di audit della sessione crea una voce di registro separata per ogni relazione (TABLE, VIEW e così via) a cui si fa riferimento in un'istruzione SELECT o DML. |
| `pgaudit.log_statement_once` | Specificate se la registrazione includerà il testo e i parametri dell'istruzione con la prima voce di registro per una statement/substatement combinazione o con ogni voce. |
| `pgaudit.role` | Specifica il ruolo principale da utilizzare per la registrazione di verifica degli oggetti. L'unica voce consentita è `rds_pgaudit`. |
## Elenco delle impostazioni consentite per il parametro `pgaudit.log`
| Valore | Description |
| --- | --- |
| nessuno | Questa è l’impostazione predefinita. Non viene registrata alcuna modifica al database. |
| tutto | Registra tutto (read, write, function, role, ddl, misc). |
| ddl | Registra tutte le istruzioni DDL (Data Definition Language) non incluse nella classe `ROLE`. |
| funzione | Registra le chiamate di funzione e i blocchi `DO`. |
| misc | Registra vari comandi, ad esempio `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` e `SET`. |
| read | Registra `SELECT` e `COPY` quando l'origine è una relazione (ad esempio una tabella) o una query. |
| role | Registra le istruzioni relative a ruoli e privilegi, ad esempio `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` e `DROP ROLE`. |
| write | Registra `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` e `COPY` quando la destinazione è una relazione (tabella). |
Per registrare più tipi di eventi con controllo di sessioni, utilizzare un elenco separato da virgole. Per registrare tutti i tipi di eventi, impostare `pgaudit.log` su `ALL`. Riavviare l'istanza database per applicare le modifiche.
Con il controllo dell'oggetto, è possibile perfezionare la registrazione di controllo per lavorare con relazioni specifiche. Ad esempio, è possibile richiedere la registrazione di audit per le operazioni `READ` su una o più tabelle.
# Utilizzo di pglogical per sincronizzare i dati tra le istanze
Tutte le versioni di Aurora PostgreSQL attualmente disponibili supportano l'estensione `pglogical`, che precede la funzionalità di replica logica funzionalmente simile introdotta nella versione 10 di PostgreSQL. Per ulteriori informazioni, consulta [Panoramica della replica logica di PostgreSQL con Aurora](AuroraPostgreSQL.Replication.Logical.md).
L'estensione `pglogical` supporta la replica logica tra due o più cluster database Aurora PostgreSQL. Supporta anche la replica tra diverse versioni di PostgreSQL e tra database in esecuzione in istanze database RDS per PostgreSQL e cluster database Aurora PostgreSQL. L'estensione `pglogical` utilizza un modello publish-subscribe per replicare le modifiche apportate alle tabelle e ad altri oggetti, come le sequenze, da un publisher in un subscriber. Si basa su uno slot di replica per garantire la sincronizzazione delle modifiche da un nodo publisher a un nodo subscriber, definiti come indicato di seguito.
+ Il *nodo publisher* è il cluster database Aurora PostgreSQL che costituisce l'origine dei dati da replicare in altri nodi. Il nodo publisher definisce le tabelle da replicare in un set di pubblicazione.
+ Il *nodo subscriber* è il cluster database Aurora PostgreSQL che riceve gli aggiornamenti WAL dal publisher. Il subscriber crea una sottoscrizione per connettersi al publisher e ottenere i dati WAL decodificati e contemporaneamente nel nodo publisher viene creato lo slot di replica.
Di seguito sono riportati gli argomenti sull'impostazione dell'estensione `pglogical`.
**Topics**
+ [Requisiti e limitazioni dell'estensione pglogical](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [Impostazione dell'estensione pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [Impostazione della replica logica per il cluster database Aurora PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [Riconnessione della replica logica dopo un aggiornamento principale](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [Gestione degli slot di replica logica per Aurora PostgreSQL RDS per](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [Riferimento sui parametri dell'estensione pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## Requisiti e limitazioni dell'estensione pglogical
Tutte le versioni attualmente disponibili di Aurora PostgreSQL supportano l'estensione `pglogical`.
Sia il nodo publisher che il nodo subscriber devono essere impostati per la replica logica.
Le tabelle da replicare da un nodo publisher a un nodo subscriber devono avere gli stessi nomi e lo stesso schema. Inoltre devono contenere le stesse colonne e le colonne devono utilizzare gli stessi tipi di dati. Le tabelle del publisher e del subscriber devono avere le stesse chiavi primarie. Si consiglia di utilizzare solo la CHIAVE PRIMARIA come vincolo univoco.
Le tabelle del nodo subscriber possono avere vincoli più permissivi rispetto ai vincoli CHECK e NOT NULL delle tabelle del nodo publisher.
L'estensione `pglogical` fornisce funzionalità, come la replica bidirezionale, che non sono supportate dalla funzionalità di replica logica integrata in PostgreSQL 10 e versioni successive. Per ulteriori informazioni, consulta [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/) (Replica bidirezionale di PostgreSQL utilizzando pglogical).
# Impostazione dell'estensione pglogical
Per impostare l'estensione `pglogical` per il cluster database Aurora PostgreSQL, aggiungi `pglogical` alle librerie condivise nel gruppo di parametri del·cluster database personalizzato per il cluster database Aurora PostgreSQL. È inoltre necessario impostare il valore del parametro `rds.logical_replication` su `1` per attivare la decodifica logica. Infine, crei l'estensione nel database. È possibile utilizzare Console di gestione AWS o AWS CLI per queste attività.
Per eseguire queste attività sono richieste le autorizzazioni del ruolo `rds_superuser`.
Le fasi seguenti si basano sull'ipotesi che il cluster database Aurora PostgreSQL sia associato a un gruppo di parametri cluster di database personalizzato. Per informazioni sulla creazione di un gruppo di parametri del·cluster database, consulta [Gruppi di parametri per Amazon Aurora](USER_WorkingWithParamGroups.md).
## Console
**Per impostare l'estensione pglogical**
1. Accedi a Console di gestione AWS e apri la console Amazon RDS all'indirizzo [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Nel riquadro di navigazione, scegli l'istanza di scrittura del cluster database Aurora PostgreSQL .
1. Apri la scheda **Configurazione** per l'istanza di scrittura del cluster database Aurora PostgreSQL. Tra i dettagli dell'istanza, individua il collegamento **Parameter group** (Gruppo di parametri).
1. Scegli il collegamento per aprire i parametri personalizzati associati al cluster database Aurora PostgreSQL.
1. Nel campo di ricerca **Parametri**, digita `shared_pre` per trovare il parametro `shared_preload_libraries`.
1. Scegli **Edit parameters** (Modifica parametri) per accedere ai valori delle proprietà.
1. Aggiungi `pglogical` all’elenco nel campo **Values** (Valori). Utilizza una virgola per separare gli elementi nell’elenco di valori.
![\[Immagine del parametro shared_preload_libraries con l'estensione pglogical aggiunta.\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. Individua il parametro `rds.logical_replication` e impostalo su `1` per attivare la replica logica.
1. Riavvia l'istanza di scrittura del cluster database Aurora PostgreSQL per rendere effettive le modifiche.
1. Quando l'istanza è disponibile, puoi utilizzare `psql` (o pgAdmin) per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Per verificare che l'estensione pglogical sia inizializzata, esegui il seguente comando.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pglogical
(1 row)
```
1. Verifica l'impostazione che abilita la decodifica logica, come indicato di seguito.
```
SHOW wal_level;
wal_level
-----------
logical
(1 row)
```
1. Crea l'estensione, come indicato di seguito.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Scegli **Save changes** (Salva modifiche).
1. Aprire la console Amazon RDS all'indirizzo [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Scegli l'istanza di scrittura del cluster database Aurora PostgreSQL dall'elenco di database per selezionarla, quindi scegli **Reboot** (Riavvia) dal menu Actions (Operazioni).
## AWS CLI
**Per impostare l'estensione pglogical**
Per configurare pglogical utilizzando AWS CLI, richiama l'[modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html)operazione per modificare determinati parametri nel gruppo di parametri personalizzato, come illustrato nella procedura seguente.
1. Utilizzate il AWS CLI comando seguente per aggiungere `pglogical` al parametro. `shared_preload_libraries`
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Usa il AWS CLI comando seguente `rds.logical_replication` `1` per impostare l'attivazione della funzionalità di decodifica logica per l'istanza writer del cluster Aurora PostgreSQL DB.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Usa il AWS CLI comando seguente per riavviare l'istanza writer dell'istanza DB RDS del cluster Aurora PostgreSQL DB per PostgreSQL .
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Quando l'istanza è disponibile, utilizza `psql` per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Crea l'estensione, come indicato di seguito.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Riavviare l'istanza writer dell'istanza database Aurora PostgreSQL DB RDS per PostgreSQL AWS CLI
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Impostazione della replica logica per il cluster database Aurora PostgreSQL
La seguente procedura mostra come avviare la replica logica tra due cluster database Aurora PostgreSQL. I passaggi presuppongono che sia l'origine (publisher) che la destinazione (subscriber) abbiano l'estensione `pglogical` impostata come descritto dettagliatamente in [Impostazione dell'estensione pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
**Nota**
`node_name` di un nodo subscriber non può iniziare con `rds`.
**Per creare il nodo publisher e definire le tabelle da replicare**
Questi passaggi presuppongono che il cluster database Aurora PostgreSQL abbia un'istanza di scrittura con un database contenente una o più tabelle che desideri replicare in un altro nodo. È necessario ricreare la struttura delle tabelle dal publisher nel subscriber, quindi prima, se occorre, recupera la struttura delle tabelle. Puoi farlo utilizzando il metacomando `psql` `\d tablename` e quindi creando la stessa tabella nell'istanza subscriber. Nella procedura seguente viene illustrato come creare una tabella di esempio nel publisher (origine) a scopo dimostrativo.
1. Utilizza `psql` per connetterti all'istanza che include la tabella da usare come origine per i subscriber.
```
psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
Se non hai una tabella esistente da replicare, puoi creare una tabella di esempio come indicato di seguito.
1. Crea una tabella di esempio utilizzando la seguente istruzione SQL.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Popola la tabella con i dati generati utilizzando la seguente istruzione SQL.
```
INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
INSERT 0 5000
```
1. Verifica che i dati siano presenti nella tabella utilizzando la seguente istruzione SQL.
```
SELECT count(*) FROM docs_lab_table;
```
1. Identifica il cluster database Aurora PostgreSQL come nodo publisher, come indicato di seguito.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_provider',
dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
create_node
-------------
3410995529
(1 row)
```
1. Aggiungi la tabella da replicare al set di replica predefinito. Per ulteriori informazioni sui set di replica, consulta [Replication sets](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets) (Set di replica) nella documentazione di pglogical.
```
SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
replication_set_add_table
---------------------------
t
(1 row)
```
L'impostazione del nodo publisher è completata. Ora puoi impostare il nodo subscriber per ricevere gli aggiornamenti dal publisher.
**Per impostare il nodo subscriber e creare una sottoscrizione per ricevere gli aggiornamenti**
Questi passaggi presuppongono che sia stata eseguita l'impostazione del cluster database Aurora PostgreSQL con l'estensione `pglogical`. Per ulteriori informazioni, consulta [Impostazione dell'estensione pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
1. Utilizza `psql` per connetterti all'istanza per cui vuoi ricevere gli aggiornamenti dal publisher.
```
psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Nel cluster database Aurora PostgreSQL del subscriber crea la stessa tabella presente nel publisher. In questo esempio, la tabella è `docs_lab_table`. È possibile creare la tabella come indicato di seguito.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Verifica che questa tabella sia vuota.
```
SELECT count(*) FROM docs_lab_table;
count
-------
0
(1 row)
```
1. Identifica il cluster database Aurora PostgreSQL come nodo subscriber, come indicato di seguito.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_target',
dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
create_node
-------------
2182738256
(1 row)
```
1. Crea la sottoscrizione.
```
SELECT pglogical.create_subscription(
subscription_name := 'docs_lab_subscription',
provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
replication_sets := ARRAY['default'],
synchronize_data := true,
forward_origins := '{}' );
create_subscription
---------------------
1038357190
(1 row)
```
Una volta completato questo passaggio, i dati della tabella del publisher vengono creati nella tabella del subscriber. È possibile verificare questa operazione utilizzando la seguente query SQL.
```
SELECT count(*) FROM docs_lab_table;
count
-------
5000
(1 row)
```
Da questo momento in poi, le modifiche apportate alla tabella del publisher vengono replicate nella tabella del subscriber.
# Riconnessione della replica logica dopo un aggiornamento principale
Prima di poter eseguire un aggiornamento della versione principale di un cluster database Aurora PostgreSQL impostata come nodo publisher per la replica logica, è necessario rimuovere tutti gli slot di replica, anche quelli non attivi. Si consiglia di deviare temporaneamente le transazioni del database dal nodo publisher, rimuovere gli slot di replica, aggiornare il cluster database Aurora PostgreSQL e quindi riconnettere e riavviare la replica.
Gli slot di replica sono ospitati solo nel nodo publisher. Il nodo subscriber Aurora PostgreSQL in uno scenario di replica logica non ha slot da rimuovere. Il processo di aggiornamento alla versione principale di Aurora PostgreSQL supporta l'aggiornamento del subscriber a una nuova versione principale di PostgreSQL indipendente dal nodo publisher. Tuttavia, il processo di aggiornamento interrompe il processo di replica e interferisce con la sincronizzazione dei dati WAL tra il nodo publisher e il nodo subscriber. È necessario riconnettere la replica logica tra publisher e subscriber dopo aver aggiornato il publisher, il subscriber o entrambi. Nella procedura seguente viene illustrato come determinare se la replica è stata interrotta e come risolvere il problema.
## Determinazione della replica logica interrotta
È possibile determinare che il processo di replica è stato interrotto eseguendo una query sul nodo publisher o sul nodo subscriber, come indicato di seguito.
**Per controllare il nodo publisher**
+ Utilizza `psql` per connetterti al nodo publisher e quindi esegui la query sulla funzione `pg_replication_slots`. Osserva il valore nella colonna attiva. Normalmente, la query restituisce `t` (true) per indicare che la replica è attiva. Se restituisce `f` (false), indica che la replica nel subscriber è stata interrotta.
```
SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f
(1 row)
```
**Per controllare il nodo subscriber**
Nel nodo subscriber è possibile verificare lo stato della replica in tre modi diversi.
+ Esamina i log di PostgreSQL sul nodo subscriber per trovare i messaggi di errore. Il log identifica gli errori nei messaggi che includono il codice di uscita 1, come illustrato di seguito.
```
2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
```
+ Esegui la query sulla funzione `pg_replication_origin`. Connettiti al database sul nodo subscriber utilizzando `psql` ed esegui la query sulla funzione `pg_replication_origin`, come indicato di seguito.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+--------
(0 rows)
```
Se il set di risultati è vuoto, la replica è stata interrotta. Normalmente, viene restituito l'output riportato di seguito.
```
roident | roname
---------+----------------------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
+ Esegui la query sulla funzione `pglogical.show_subscription_status` come illustrato nell'esempio seguente.
```
SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
---====----------------+--------+-------------------------------------
docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
Questo output mostra che la replica è stata interrotta. Lo stato è `down` e normalmente l'output mostra lo stato `replicating`.
Se il processo di replica logica è stato interrotto, è possibile riconnettere la replica seguendo questi passaggi.
**Per riconnettere la replica logica tra i nodi publisher e subscriber**
Per riconnettere la replica, devi prima disconnettere il subscriber dal nodo publisher e quindi riconnettere la sottoscrizione, come descritto in questi passaggi.
1. Connettiti al nodo subscriber utilizzando `psql`, come indicato di seguito.
```
psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Disattiva la sottoscrizione utilizzando la funzione `pglogical.alter_subscription_disable`.
```
SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
1. Ottieni l'identificatore del nodo publisher eseguendo la query su `pg_replication_origin`, come indicato di seguito.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+-------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
1. Utilizza la risposta restituita dal passaggio precedente con il comando `pg_replication_origin_create` per assegnare l'identificatore che può essere usato dalla sottoscrizione una volta riconnessa.
```
SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
pg_replication_origin_create
------------------------------
1
(1 row)
```
1. Attiva la sottoscrizione specificando il nome con lo stato `true`, come illustrato nell'esempio seguente.
```
SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
alter_subscription_enable
---------------------------
t
(1 row)
```
Controlla lo stato del nodo. Lo stato deve essere `replicating` come mostrato nell'esempio.
```
SELECT subscription_name,status,slot_name
FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
-------------------------------+-------------+-------------------------------------
docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)
```
Verifica lo stato dello slot di replica del subscriber sul nodo publisher. La colonna `active` dello slot deve restituire `t` (true) per indicare che la replica è stata riconnessa.
```
SELECT slot_name,plugin,slot_type,active
FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t
(1 row)
```
# Gestione degli slot di replica logica per Aurora PostgreSQL RDS per
Prima di poter eseguire un aggiornamento alla versione principale su un'istanza di scrittura del cluster database Aurora PostgreSQL che funge da nodo publisher in uno scenario di replica logica, è necessario rimuovere gli slot di replica nell'istanza. Il processo di verifica preliminare dell'aggiornamento alla versione principale avvisa che l'aggiornamento non può procedere fino a quando gli slot non vengono rimossi.
Per identificare gli slot di replica creati utilizzando l'estensione `pglogical`, accedi a ciascun database e recupera il nome dei nodi. Quando esegui la query sul nodo subscriber, nell'output viene restituito sia il nodo publisher che il nodo subscriber, come mostrato nell'esempio seguente.
```
SELECT * FROM pglogical.node;
node_id | node_name
------------+-------------------
2182738256 | docs_lab_target
3410995529 | docs_lab_provider
(2 rows)
```
Puoi ottenere i dettagli sulla sottoscrizione con la seguente query.
```
SELECT sub_name,sub_slot_name,sub_target
FROM pglogical.subscription;
sub_name | sub_slot_name | sub_target
----------+--------------------------------+------------
docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)
```
A questo punto puoi rimuovere la sottoscrizione, come indicato di seguito.
```
SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
drop_subscription
-------------------
1
(1 row)
```
Dopo aver rimosso la sottoscrizione, puoi eliminare il nodo.
```
SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
drop_node
-----------
t
(1 row)
```
Puoi verificare che il nodo sia stato eliminato, come indicato di seguito.
```
SELECT * FROM pglogical.node;
node_id | node_name
---------+-----------
(0 rows)
```
# Riferimento sui parametri dell'estensione pglogical
Nella tabella sono illustrati i parametri associati all'estensione `pglogical`. Parametri come `pglogical.conflict_log_level` e `pglogical.conflict_resolution` vengono utilizzati per gestire i conflitti di aggiornamento. I conflitti possono emergere quando vengono apportate modifiche localmente alle stesse tabelle che hanno una sottoscrizione con il publisher. I conflitti possono verificarsi anche in altri scenari, ad esempio la replica bidirezionale o quando più subscriber eseguono la replica dallo stesso publisher. Per ulteriori informazioni, consulta [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/) (Replica bidirezionale di PostgreSQL utilizzando pglogical).
| Parametro | Description |
| --- | --- |
| pglogical.batch\$1inserts | Esegue inserimenti batch, se possibile. Non impostato per impostazione predefinita. Imposta "1" per attivarlo, "0" per disattivarlo. |
| pglogical.conflict\$1log\$1level | Imposta il livello di log da utilizzare per la registrazione dei conflitti risolti. I valori di stringa supportati sono debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic. |
| pglogical.conflict\$1resolution | Imposta il metodo da utilizzare per risolvere i conflitti quando sono risolvibili. I valori di stringa supportati sono error, apply\$1remote, keep\$1local, last\$1update\$1wins, first\$1update\$1wins. |
| pglogical.extra\$1connection\$1options | Specifica le opzioni di connessione da aggiungere a tutte le connessioni dei nodi peer. |
| pglogical.synchronous\$1commit | Valore di commit sincrono specifico pglogical |
| pglogical.use\$1spi | Utilizza la SPI (Server Programming Interface) invece dell'API di basso livello per applicare le modifiche. Imposta "1" per attivarlo, "0" per disattivarlo. Per ulteriori informazioni sulla SPI, consulta [Server Programming Interface](https://www.postgresql.org/docs/current/spi.html) nella documentazione PostgreSQL. |
# Utilizzo dei wrapper di dati esterni supportati per Amazon Aurora PostgreSQL
Un wrapper di dati esterni (FDW) è uno specifico tipo di estensione che consente l'accesso a dati esterni. Ad esempio, l'estensione `oracle_fdw` consente all'istanza database Aurora PostgreSQL di interagire con i database Oracle.
Di seguito sono disponibili le informazioni sui diversi wrapper di dati esterni di PostgreSQL supportati.
**Topics**
+ [Utilizzo dell'estensione log\$1fdw per accedere al registro di database utilizzando SQL](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [Utilizzo dell'estensione postgres\$1fdw per accedere a dati esterni](postgresql-commondbatasks-fdw.md)
+ [Interazione con i database MySQL utilizzando l'estensione mysql\$1fdw](postgresql-mysql-fdw.md)
+ [Interazione con un database Oracle utilizzando l'estensione oracle\$1fdw](postgresql-oracle-fdw.md)
+ [Interazione con i database MySQL utilizzando l'estensione mysql\$1fdw](postgresql-tds-fdw.md)
# Utilizzo dell'estensione log\$1fdw per accedere al registro di database utilizzando SQL
Il cluster di database Aurora PostegreSQL supporta l'estensione `log_fdw` che consente di accedere al log del motore del database utilizzando un'interfaccia SQL. L'estensione `log_fdw` offre due nuove funzioni che semplificano la creazione di tabelle esterne per i registri di database:
+ `list_postgres_log_files` – Elenca i file nella directory dei registri di database e le dimensioni dei file in byte.
+ `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – Crea una tabella esterna per il file specificato nel database corrente.
Tutte le funzioni create da `log_fdw` sono di proprietà di `rds_superuser`. I membri del ruolo `rds_superuser` possono concedere l'accesso a queste funzioni ad altri utenti del database.
Per impostazione predefinita, i file di log vengono generati da Amazon Aurora nel formato `stderr` (errore standard), come specificato nel parametro `log_destination`. Esistono solo due opzioni per questo parametro, `stderr` e `csvlog` (valori separati da virgola, CSV). Se aggiungi l'opzione `csvlog` al parametro, Amazon Aurora genera entrambi i log `stderr` e `csvlog`. Ciò può influire sulla capacità di archiviazione del cluster di database, quindi è necessario tenere conto degli altri parametri che influiscono sulla gestione dei log. Per ulteriori informazioni, consulta [Impostazione della destinazione del registro (`stderr`, `csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format).
Un vantaggio della generazione dei registri `csvlog` è che l'estensione `log_fdw` consente di costruire le tabelle esterne con i dati suddivisi in diverse colonne. Per eseguire questa operazione, l'istanza deve essere associata a un gruppo parametri del database personalizzato in modo da poter modificare l'impostazione per `log_destination`. Per ulteriori informazioni su come fare, consulta [Gruppi di parametri per Amazon Aurora](USER_WorkingWithParamGroups.md).
L'esempio seguente presuppone che il parametro `log_destination` includa `cvslog`.
**Per utilizzare l'estensione log\$1fdw**
1. Installa l’estensione `log_fdw`.
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. Creare un server log come wrapper di dati esterno.
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. Selezionare tutti gli elementi da un elenco di file di registro.
```
postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;
```
Di seguito è riportata una risposta di esempio.
```
file_name | file_size_bytes
------------------------------+-----------------
postgresql.log.2023-08-09-22.csv | 1111
postgresql.log.2023-08-09-23.csv | 1172
postgresql.log.2023-08-10-00.csv | 1744
postgresql.log.2023-08-10-01.csv | 1102
(4 rows)
```
1. Creare una tabella con una singola colonna "log\$1entry" per i file selezionato.
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
La risposta non fornisce dettagli diversi da quello che la tabella ora esiste.
```
-----------------------------------
(1 row)
```
1. Selezionare un campione del file di registro. Il seguente codice recupera l'ora del log e la descrizione del messaggio di errore.
```
postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
```
Di seguito è riportata una risposta di esempio.
```
log_time | message
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)
```
# Utilizzo dell'estensione postgres\$1fdw per accedere a dati esterni
È possibile accedere ai dati in una tabella su un server di database remoto con l'estensione [postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html). Se si imposta una connessione remota dall'istanza database di PostgreSQL, l'accesso è disponibile anche alla replica di lettura.
**Usare postgres\$1fdw per accedere al server remoto del database**
1. Installare l'estensione postgres\$1fdw.
```
CREATE EXTENSION postgres_fdw;
```
1. Creare un server di dati esterni utilizzando CREATE SERVER.
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. Creare una mappatura dell'utente per identificare il ruolo da utilizzare sul server remoto.
**Importante**
Per oscurare la password in modo che non compaia nei log, impostare `log_statement=none` a livello di sessione. L’impostazione a livello di parametro non comporta l’oscuramento della password.
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. Creare una tabella che esegua la mappatura della tabella sul server remoto.
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# Interazione con i database MySQL utilizzando l'estensione mysql\$1fdw
Per accedere a un database compatibile con MySQL dal cluster di database Aurora PostgreSQL è possibile installare e utilizzare l'estensione `mysql_fdw`. Questo wrapper di dati esterni consente di interagire con RDS per MySQL, Aurora MySQL, MariaDB e altri database compatibili con MySQL. La connessione dal cluster di database Aurora PostgreSQL al database MySQL è crittografata in base al miglior tentativo a seconda delle configurazioni di client e server. Tuttavia, se lo si desidera, è possibile imporre l'utilizzo della crittografia. Per ulteriori informazioni, consulta [Utilizzo della crittografia in transito con l'estensione](#postgresql-mysql-fdw.encryption-in-transit).
L'estensione `mysql_fdw` è supportata su Amazon Aurora PostgreSQL 15.4, 14.9, 13.12, 12.16, e versioni successive. Supporta le operazioni di select, insert, update e delete da un database RDS for PostgreSQL su tabelle contenuto in un'istanza database compatibile con MySQL.
**Topics**
+ [Configurazione del database Aurora PostgreSQL per l'utilizzo dell'estensione mysql\$1fdw](#postgresql-mysql-fdw.setting-up)
+ [Esempio: utilizzo di un database Aurora MySQL da Aurora PostgreSQL](#postgresql-mysql-fdw.using-mysql_fdw)
+ [Utilizzo della crittografia in transito con l'estensione](#postgresql-mysql-fdw.encryption-in-transit)
## Configurazione del database Aurora PostgreSQL per l'utilizzo dell'estensione mysql\$1fdw
La configurazione dell'estensione `mysql_fdw` sul cluster di database Aurora PostgreSQL comporta il caricamento dell'estensione nel cluster di database e quindi la creazione del punto di connessione all'istanza database MySQL. Per tale attività, è necessario disporre delle seguenti informazioni sull'istanza database MySQL:
+ Nome host o endpoint. Per trovare l'endpoint di un cluster di database Aurora MySQL è possibile utilizzare la console. Scegliere la scheda Connectivity & security (Connettività e sicurezza) e cercare nella sezione Endpoint and port (Endpoint e porta).
+ Numero della porta. La porta di default per MySQL è 3306.
+ Nome del database. L'identificatore del database.
È inoltre necessario fornire l'accesso al gruppo di sicurezza o alla lista di controllo degli accessi (ACL) per la porta MySQL 3306. Il cluster di database Aurora PostegreSQL e il cluster di database Aurora MySQL necessitano dell'accesso alla porta 3306. Se l'accesso non è configurato correttamente, quando si cerca di connettersi alla tabella compatibile con MySQL comparirà un messaggio di errore simile al seguente:
```
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)
```
Nella seguente procedura, l'utente (utilizzando l'account `rds_superuser`) crea il server esterno. Quindi concede l'accesso al server esterno a specifici utenti. Questi utenti creano quindi i propri mapping agli account utente MySQL appropriati per interagire con l'istanza database MySQL.
**Per utilizzare mysql\$1fdw per accedere al server database MySQL**
1. Effettuare la connessione all'istanza database PostgreSQL utilizzando un account che dispone del ruolo `rds_superuser`. Se al momento della creazione del cluster di database Aurora PostgreSQL sono stati accettati i valori predefiniti, il nome utente è `postgres` e lo strumento a riga di comando `psql` può essere usato per collegarsi come segue:
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. Installare l'estensione `mysql_fdw` come segue:
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
Dopo aver installato l'estensione sul cluster di database Aurora PostgreSQL imposta il server esterno che fornisce la connessione a un database MySQL.
**Per creare il server esterno**
Esegui queste attività sul cluster di database Aurora PostgreSQL. La procedura presuppone che l'utente sia connesso come utente con i privilegi di `rds_superuser`, come `postgres`.
1. Creazione di un server esterno nel cluster di database Aurora PostgreSQL:
```
postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Concedere agli utenti appropriati l'accesso al server esterno. Questi dovrebbero essere utenti non amministratori, cioè utenti senza il ruolo `rds_superuser`.
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
Gli utenti PostgreSQL creano e gestiscono le proprie connessioni al database MySQL tramite il server esterno.
## Esempio: utilizzo di un database Aurora MySQL da Aurora PostgreSQL
Supponi di disporre di una semplice tabella su un'istanza database Aurora PostgreSQL. Gli utenti di Aurora PostgreSQL desiderano eseguire query sugli elementi (`SELECT`), `INSERT`, `UPDATE` e `DELETE` contenute in tale tabella. Supponiamo che l'estensione `mysql_fdw` sia stata creata nell'istanza database RDS for PostgreSQL, come descritto nella procedura precedente. Dopo aver effettuato la connessione all'istanza database RDS for PostgreSQL come utente con i privilegi `rds_superuser`, è possibile procedere con i seguenti passaggi.
1. Nel cluster di database Aurora PostgreSQL crea un server esterno:
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Concedere l'utilizzo a un utente che non dispone delle autorizzazioni `rds_superuser`, ad esempio `user1`:
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. Connect as*user1*, quindi crea una mappatura per l'utente MySQL:
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. Creare di una tabella esterna collegata a una tabella MySQL:
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. Eseguire una semplice query sulla tabella esterna:
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. È possibile aggiungere, modificare e rimuovere i dati dalla tabella MySQL. Ad esempio:
```
test=> INSERT INTO mytab values (2, 'mango');
INSERT 0 1
```
Eseguire nuovamente la query `SELECT` per visualizzare i risultati:
```
test=> SELECT * FROM mytab ORDER BY 1;
a | b
---+-------
1 | apple
2 | mango
(2 rows)
```
## Utilizzo della crittografia in transito con l'estensione
La connessione a MySQL da Aurora PostgreSQL utilizza la crittografia in transito (TLS/SSL) per impostazione predefinita. Tuttavia, la connessione torna a essere non crittografata quando la configurazione di client e server differiscono. È possibile applicare la crittografia a tutte le connessioni in uscita specificando l'opzione `REQUIRE SSL` sugli account utente RDS for MySQL. Lo stesso approccio funziona anche per gli account utente MariaDB e Aurora MySQL.
Per gli account utente MySQL configurati su `REQUIRE SSL`, il tentativo di connessione non riesce se non è possibile stabilire una connessione sicura.
Per applicare la crittografia agli account utente esistenti del database MySQL è possibile utilizzare il comando `ALTER USER`. La sintassi varia a seconda della versione MySQL, come mostrato nella tabella seguente. Per ulteriori informazioni, consultare la voce [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html) nel *Manuale di riferimento di MySQL*.
| MySQL 5.7, MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
Per ulteriori informazioni sull'estensione `mysql_fdw`, consultare la documentazione di [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw).
# Interazione con un database Oracle utilizzando l'estensione oracle\$1fdw
Per accedere a un database Oracle dal cluster di database Aurora PostgreSQL è possibile installare e utilizzare l'estensione `oracle_fdw`. Questa estensione è un wrapper di dati esterni per database Oracle. Per ulteriori informazioni sull'estensione, consultare la documentazione di [oracle\$1fdw](https://github.com/laurenz/oracle_fdw).
L'estensione `oracle_fdw` è supportata su Aurora PostgreSQL 12.7 (Amazon Aurora versione 4.2) e versioni successive.
**Topics**
+ [Attivazione dell'estensione oracle\$1fdw](#postgresql-oracle-fdw.enabling)
+ [Esempio: utilizzo di un server esterno collegato a un database Amazon RDS for Oracle](#postgresql-oracle-fdw.example)
+ [Utilizzo della crittografia in transito](#postgresql-oracle-fdw.encryption)
+ [Informazioni sulla visualizzazione pg\$1user\$1mappings e sulle autorizzazioni](#postgresql-oracle-fdw.permissions)
## Attivazione dell'estensione oracle\$1fdw
Per utilizzare l'estensione oracle\$1fdw, eseguire la procedura riportata di seguito.
**Come attivare l'estensione oracle\$1fdw**
+ Eseguire il seguente comando utilizzando un account con le autorizzazioni `rds_superuser`.
```
CREATE EXTENSION oracle_fdw;
```
## Esempio: utilizzo di un server esterno collegato a un database Amazon RDS for Oracle
L'esempio seguente mostra l'utilizzo di un server esterno collegato a un database Amazon RDS for Oracle.
**Come creare un server esterno collegato a un database RDS for Oracle**
1. Annotare le seguenti informazioni sull'istanza database RDS for Oracle:
+ Endpoint
+ Porta
+ Nome del database
1. Creare un server esterno.
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. Concedere l'utilizzo a un utente che non dispone dei privilegi `rds_superuser`, ad esempio`user1`.
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. Connettersi come `user1` e creare una mappatura a un utente Oracle.
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. Creare una tabella esterna collegata a una tabella Oracle.
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Eseguire una query sulla tabella esterna.
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
Se la query segnala il seguente errore, controllare il gruppo di sicurezza e la lista di controllo degli accessi (ACL) per assicurarsi che entrambe le istanze possano comunicare.
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## Utilizzo della crittografia in transito
La crittografia da PostgreSQL a Oracle in transito si basa su una combinazione di parametri di configurazione client e server. Per un esempio di utilizzo di Oracle 21c, consultare [Informazioni sui valori per la negoziazione di crittografia e integrità](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5) nella documentazione Oracle. Il client utilizzato per oracle\$1fdw su Amazon RDS è configurato con `ACCEPTED`, il che significa che la crittografia dipende dalla configurazione del server di database Oracle e utilizza Oracle Security Library (libnnz) per la crittografia.
Se il database si trova su RDS for Oracle, consultare [Crittografia di rete nativa Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html) per configurare la crittografia.
## Informazioni sulla visualizzazione pg\$1user\$1mappings e sulle autorizzazioni
Il catalogo PostgreSQL `pg_user_mapping` archivia la mappatura da un utente Aurora PostgreSQL all'utente in un server remoto di dati esterni. L'accesso al catalogo è limitato, ma puoi usare la visualizzazione `pg_user_mappings` per vedere le mappature. Di seguito è possibile trovare un esempio che mostra come si applicano le autorizzazioni con un database Oracle, sebbene le stesse informazioni si applichino più in generale a qualsiasi wrapper di dati esterno.
Nel seguente output sono presenti ruoli e autorizzazioni mappati su tre diversi utenti di esempio. Gli utenti `rdssu1` e `rdssu2` sono membri del ruolo `rds_superuser`, mentre `user1` non lo è. Nell'esempio viene utilizzato il metacomando `psql` `\du` per elencare i ruoli esistenti.
```
test=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
rdssu1 | | {rds_superuser}
rdssu2 | | {rds_superuser}
user1 | | {}
```
Tutti gli utenti, inclusi gli utenti che godono dei privilegi `rds_superuser`, sono autorizzati a visualizzare le proprie mappature utente (`umoptions`) nella tabella `pg_user_mappings`. Come mostrato nell'esempio seguente, quando `rdssu1` cerca di ottenere tutte le mappature utente, viene generato un errore anche se gode dei privilegi `rdssu1``rds_superuser`:
```
test=> SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
```
Di seguito vengono riportati alcuni esempi.
```
test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd}
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd}
(3 rows)
test=> SET SESSION AUTHORIZATION user1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+--------------------------------
16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd}
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
```
A causa delle differenze nell'implementazione di `information_schema._pg_user_mappings` e `pg_catalog.pg_user_mappings`, un `rds_superuser` creato manualmente richiede autorizzazioni aggiuntive per visualizzare le password in `pg_catalog.pg_user_mappings`.
Non sono necessarie autorizzazioni aggiuntive per un `rds_superuser` che desideri visualizzare le password in `information_schema._pg_user_mappings`.
Gli utenti che non dispongono del ruolo `rds_superuser` possono visualizzare le password in `pg_user_mappings` solo nelle seguenti condizioni:
+ L'utente corrente è l'utente mappato e possiede il server oppure detiene il privilegio `USAGE` su di esso.
+ L'utente corrente è il proprietario del server e la mappatura è per `PUBLIC`.
# Interazione con i database MySQL utilizzando l'estensione mysql\$1fdw
È possibile utilizzare l'estensione `tds_fdw` per PostgreSQL per accedere ai database che supportano il protocollo TDS (Tabular Data Stream), ad esempio i database Sybase e Microsoft SQL Server. Questo wrapper di dati esterni consente di connettersi dal proprio cluster di database Aurora PostgreSQL ai database che utilizzano il protocollo TDS, incluso Amazon RDS for Microsoft SQL Server. Per ulteriori informazioni, consultare la documentazione di [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) su GitHub.
L'estensione `tds_fdw` è supportata su Amazon Aurora PostgreSQL versioni 13.6 e successive.
## Configurazione del database Aurora PostgreSQL per l'utilizzo dell'estensione tds\$1fdw
Nelle procedure seguenti, è possibile trovare un esempio di configurazione e utilizzo di `tds_fdw` con un cluster di database Aurora PostgreSQL. Prima di potersi connettere a un database di SQL Server utilizzando `tds_fdw` è necessario disporre delle seguenti informazioni sull'istanza:
+ Nome host o endpoint. Per trovare l'endpoint di un'istanza database RDS for SQL Server è possibile utilizzare la console. Scegliere la scheda Connectivity & security (Connettività e sicurezza) e cercare nella sezione Endpoint and port (Endpoint e porta).
+ Numero della porta. Il numero di porta predefinito per Microsoft SQL Server è 1433.
+ Nome del database. L'identificatore del database.
È inoltre necessario fornire l'accesso al gruppo di sicurezza o alla lista di controllo degli accessi (ACL) per la porta SQL Server 1433. Sia il cluster di database Aurora PostegreSQL che l'istanza database RDS for MySQL Server necessitano dell'accesso alla porta 1433. Se l'accesso non è configurato correttamente, quando si tenta di eseguire una query su Microsoft SQL Server viene visualizzato il seguente messaggio di errore:
```
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
```
**Per utilizzare tds\$1fdw per connettersi a un database di SQL Server**
1. Collegarsi all'istanza principale del cluster di database Aurora PostgreSQL utilizzando un account che dispone del ruolo `rds_superuser`:
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. Installare l'estensione `tds_fdw`:
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
Dopo che l'estensione è stata installata sul cluster di database Aurora PostgreSQL , è necessario configurare il server esterno.
**Per creare il server esterno**
Eseguire queste attività sul cluster di database Aurora PostgreSQL utilizzando un account che dispone dei privilegi `rds_superuser`.
1. Creazione di un server esterno nel cluster di database Aurora PostgreSQL:
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing');
CREATE SERVER
```
Per accedere ai dati non ASCII sul lato SQLServer, crea un collegamento server con l'opzione character\$1set nel cluster database Aurora PostgreSQL:
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing', character_set 'UTF-8');
CREATE SERVER
```
1. Concedere le autorizzazioni a un utente che non dispone del ruolo `rds_superuser`, ad esempio `user1`:
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. Collegarsi come user1 e quindi creare una mappatura per l'utente SQL Server:
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. Creare una tabella esterna collegata a una tabella SQL Server:
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Eseguire una query sulla tabella esterna:
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
### Utilizzo della crittografia in transito per la connessione
La connessione da Aurora PostgreSQL verso SQL Server utilizza la crittografia in transito (TLS/SSL) in base alla configurazione del database SQL Server. Se SQL Server non è configurato per la crittografia, il client RDS per PostgreSQL che effettua la richiesta al database di SQL Server torna a comunicare in modalità non crittografata.
È possibile imporre l'utilizzo della crittografia per la connessione alle istanze database RDS for SQL Server impostando il parametro `rds.force_ssl`. Per scoprire come fare, consultare [Imposizione dell'utilizzo di SSL per le connessioni all'istanza database](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing). Per ulteriori informazioni sulla configurazione di SSL/TLS per RDS for SQL Server, consultare [Utilizzo di SSL con un'istanza database Microsoft SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html).