Chapter 3. Amministrazione di PostGIS

Chapter 3. Amministrazione di PostGIS
Prev		Next

3.1. Messa a punto delle prestazioni

La messa a punto delle prestazioni di PostGIS è simile a quella di qualsiasi altro carico di lavoro di PostgreSQL. L'unica considerazione aggiuntiva è che le geometrie e i raster sono solitamente di grandi dimensioni, quindi le ottimizzazioni relative alla memoria hanno generalmente un impatto maggiore su PostGIS rispetto ad altri tipi di query PostgreSQL.

Per informazioni generali sull'ottimizzazione di PostgreSQL, consultare Tuning your PostgreSQL Server.

PostgreSQL configuration can be set at the server level without touching postgresql.conf or postgresql.auto.conf by using the ALTER SYSTEM command.

ALTER SYSTEM SET work_mem = '256MB';
-- this forces non-startup configs to take effect for new connections
SELECT pg_reload_conf();
-- show current setting value
-- use SHOW ALL to see all settings
SHOW work_mem;

Oltre alle impostazioni di Postgres, PostGIS ha alcune impostazioni personalizzate che sono elencate in Section 7.22, “Grand Unified Custom Variables (GUCs)”.

3.1.1. Avviamento

Queste impostazioni sono configurate in postgresql.conf:

constraint_exclusion

Predefinito: partition
This is generally used for table partitioning. The default of "partition" forces the planner to only analyze tables for constraint consideration if they are in an inherited hierarchy and avoids paying the planner penalty otherwise.

shared_buffers

Default: ~128MB
Impostate circa il 25%-40% della RAM disponibile. Su Windows potrebbe non essere possibile impostare un valore così alto.

max_worker_processes controls the maximum number of background processes available to the server and therefore also bounds how many workers can participate in parallel queries.

Predefinito: 8
Imposta il numero massimo di processi in background che il sistema può supportare. Questo parametro può essere impostato solo all'avvio del server.

3.1.2. Tempo di esecuzione

work_mem - imposta la dimensione della memoria utilizzata per le operazioni di ordinamento e le query complesse

Predefinito: 1-4MB
Aumentate per database di grandi dimensioni, query complesse, molta RAM
Riducete in caso di molti utenti contemporanei o di poca RAM.
Se avete molta RAM e pochi sviluppatori:
```
SET work_mem TO '256MB';
```

maintenance_work_mem - la dimensione della memoria utilizzata per VACUUM, CREATE INDEX, ecc.

Predefinito: 16-64MB
Generalmente troppo basso - blocca l'I/O, blocca gli oggetti durante lo swapping della memoria
Consigliamo da 32 MB a 1 GB sui server di produzione con molta RAM, ma dipende dal numero di utenti contemporanei. Se avete molta RAM e pochi sviluppatori:
```
SET maintenance_work_mem TO '1GB';
```

max_parallel_workers_per_gather

Parallel query support in modern PostgreSQL and PostGIS uses this setting to decide how many workers a plan may request. If set to higher than 0, then some queries such as those involving relation functions like ST_Intersects can use multiple processes and can run more than twice as fast when doing so. If you have a lot of processors to spare, you should change the value of this to as many processors as you have. Also make sure to bump up max_worker_processes to at least as high as this number.

Predefinito: 0
Imposta il numero massimo di worker che possono essere avviati da un singolo nodo Gather. I lavoratori paralleli vengono presi dal pool di processi stabilito da max_worker_processes. Si noti che il numero di lavoratori richiesto potrebbe non essere effettivamente disponibile al momento dell'esecuzione. In questo caso, il piano verrà eseguito con un numero di lavoratori inferiore a quello previsto, il che potrebbe essere inefficiente. Impostando questo valore a 0, che è quello predefinito, si disabilita l'esecuzione parallela delle query.

3.2. Configurare il supporto raster

Se hai abilitato il supporto per i raster vorrai leggere la prossima sezione per configurarlo correttamente.

Out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. You can also use the more cross-platform approach of setting the corresponding Section 7.22, “Grand Unified Custom Variables (GUCs)”.

Se vuoi abilitare il supporto per i raster offline:

POSTGIS_ENABLE_OUTDB_RASTERS=1

Qualunque altro valore, o l'assenza di valore, disabilita il supporto per i raster offline (out-of-db).

Per abilitare tutti i driver GDAL disponibili nella tua installazione, valorizza la seguente variabile come segue

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

Se vuoi abilitare solo degli specifici driver, valorizza la variabile cosí:

POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"


	Su Windows, non usare le virgolette

Setting environment variables varies depending on OS. For PostgreSQL installed on Ubuntu or Debian via apt-postgresql, the preferred way is to edit /etc/postgresql/MAJOR/CLUSTER/environment where the placeholders refer to the PostgreSQL major version and cluster name.

Su Windows, se si esegue come servizio, è possibile impostare le variabili di sistema che, per Windows 7, si ottengono facendo clic con il pulsante destro del mouse su Computer->Proprietà Impostazioni di sistema avanzate o, in explorer, navigando su Pannello di controllo\Tutte le voci del pannello di controllo\Sistema. Quindi fare clic su Impostazioni di sistema avanzate ->Avanzate->Variabili d'ambiente e aggiungere nuove variabili di sistema.

Dopo aver settato le variabili ambientali, affinché le modifiche abbiano effetto dovrai far ripartire il servizio PostgreSQL.

3.3. Creazione di database spaziali

3.3.1. Abilitazione spaziale di database usando il metodo EXTENSION

If you have compiled and installed the extensions/postgis modules, you can turn a database into a spatial one using the EXTENSION mechanism.

L'estensione principale di postgis include geometria, geografia, spatial_ref_sys e tutte le funzioni e i commenti. Raster e topologia sono confezionati come estensione separata.

Lancia il seguente codice SQL nel database in cui vuoi abilitare il supporto spaziale:

CREATE EXTENSION IF NOT EXISTS plpgsql;
      CREATE EXTENSION postgis;
      CREATE EXTENSION postgis_raster; -- OPTIONAL
      CREATE EXTENSION postgis_topology; -- OPTIONAL

3.3.2. Abilitare il database spazialmente senza usare l'ESTENSIONE (sconsigliato)


	Questo è generalmente necessario solo se non si può o non si vuole installare PostGIS nella directory delle estensioni di PostgreSQL (ad esempio durante i test, lo sviluppo o in un ambiente limitato).

L'aggiunta degli oggetti e delle definizioni delle funzioni di PostGIS nel database avviene caricando i vari file sql che si trovano in [prefisso]/share/contrib come specificato durante la fase di compilazione.

Gli oggetti PostGIS di base (tipi di geometria e geografia e relative funzioni di supporto) si trovano nello script postgis.sql. Gli oggetti raster si trovano nello script rtpostgis.sql. Gli oggetti topologici si trovano nello script topology.sql.

Per un insieme completo di identificatori di definizione di sistemi di coordinate EPSG, è possibile anche caricare il file di definizioni spatial_ref_sys.sql e popolare la tabella spatial_ref_sys. In questo modo è possibile eseguire operazioni ST_Transform() sulle geometrie.

Se si desidera aggiungere commenti alle funzioni di PostGIS, è possibile trovarli nello script postgis_comments.sql. I commenti possono essere visualizzati semplicemente digitando \dd [nome_funzione] da una finestra di terminale psql.

Eseguire i seguenti comandi di Shell nel terminale:

DB=[yourdatabase]
    SCRIPTSDIR=`pg_config --sharedir`/contrib/postgis-3.6/

    # Core objects
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/spatial_ref_sys.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis_comments.sql # OPTIONAL

    # Raster support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/rtpostgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/raster_comments.sql # OPTIONAL

    # Topology support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/topology.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/topology_comments.sql # OPTIONAL

3.4. Aggiornamento dei database spaziali

3.4.1. Aggiornamento soft

L'aggiornamento dei database spaziali esistenti può essere complicato perché richiede la sostituzione o l'introduzione di nuove definizioni di oggetti PostGIS.

Purtroppo non tutte le definizioni possono essere facilmente sostituite in un database attivo, quindi a volte la cosa migliore è un processo di dump/reload.

PostGIS prevede una procedura di SOFT UPGRADE per i rilasci minori o di bugfix e una procedura di HARD UPGRADE per i rilasci maggiori.

Prima di tentare un aggiornamento di PostGIS, è sempre opportuno eseguire un backup dei dati. Se si usa il flag -Fc per pg_dump, si potrà sempre ripristinare il dump con un HARD UPGRADE.

3.4.1. Aggiornamento soft

Se il database è stato installato utilizzando le estensioni, è necessario effettuare anche l'aggiornamento nello stesso modo. Se l'installazione è stata eseguita con il vecchio metodo degli script sql, si consiglia di passare alle estensioni perché il metodo degli script non è più supportato.

3.4.1.1. Soft upgrade using extensions

If you originally installed PostGIS with extensions, then you need to upgrade using extensions as well. Doing a minor upgrade with extensions is fairly painless.

Use the PostGIS_Extensions_Upgrade function to upgrade to the latest version you have installed.

SELECT postgis_extensions_upgrade();

If you are upgrading a database that still runs PostGIS 2.5, issue an ALTER EXTENSION followed by postgis_extensions_upgrade() to make sure the legacy postgis_raster objects are folded back into the main extension.

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
-- This second call is needed to rebundle postgis_raster extension
SELECT postgis_extensions_upgrade();

Se sono installate più versioni di PostGIS e non si vuole aggiornare alla più recente, è possibile specificare esplicitamente la versione come segue:

ALTER EXTENSION postgis UPDATE TO "3.7.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0dev";

Se si ottiene un avviso di errore, qualcosa di simile a:

No migration path defined for … to 3.7.0dev

È quindi necessario eseguire un backup del database, crearne uno nuovo come descritto in Section 3.3.1, “Abilitazione spaziale di database usando il metodo EXTENSION” e quindi ripristinare il backup su questo nuovo database.

Se viene visualizzato un messaggio di avviso del tipo:

Version "3.7.0dev" of extension "postgis" is already installed

Allora tutto è già aggiornato e si può tranquillamente ignorare. A MENO CHE non si stia cercando di passare da una versione di sviluppo alla successiva (che non riceve un nuovo numero di versione); in questo caso si può aggiungere "next" alla stringa della versione e la prossima volta si dovrà eliminare il suffisso "next":

ALTER EXTENSION postgis UPDATE TO "3.7.0devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0devnext";


	Se PostGIS è stato installato originariamente senza una versione specificata, spesso è possibile saltare la reinstallazione dell'estensione postgis prima del ripristino, poiché il backup ha solo `CREATE EXTENSION postgis` e quindi preleva la versione più recente durante il ripristino.

Se si aggiorna l'estensione di PostGIS da una versione precedente alla 3.0.0, si avrà una nuova estensione postgis_raster che si può tranquillamente abbandonare, se non si ha bisogno del supporto raster. Si può abbandonare come segue:

DROP EXTENSION postgis_raster;

3.4.1.2. Soft upgrade without extensions

Extensions have been the supported installation method for many releases. If you still rely on an unpackaged installation, perform a HARD UPGRADE into a database created with extensions and migrate your data there.

3.4.1.3. Aggiornamento hard

Per HARD UPGRADE si intende il dump/reload completo dei database abilitati a PostGIS. È necessario un HARD UPGRADE quando la memoria interna degli oggetti PostGIS cambia o quando non è possibile effettuare un SOFT UPGRADE. L'appendice Release Notes riporta per ogni versione se è necessario un dump/reload (HARD UPGRADE) per l'aggiornamento.

Il processo di dump/reload è assistito dallo script postgis_restore che si occupa di saltare dal dump tutte le definizioni che appartengono a PostGIS (comprese quelle vecchie), consentendo di ripristinare gli schemi e i dati in un database con PostGIS installato senza incorrere in errori di simboli duplicati o di portare avanti oggetti deprecati.

Le istruzioni supplementari per gli utenti di Windows sono disponibili all'indirizzo Windows Hard upgrade.

La procedura è la seguente:

Creare un dump in formato personalizzato del database che si vuole aggiornare (chiamiamolo olddb), includendo l'output binario (-b) e verboso (-v). L'utente può essere il proprietario del database, non è necessario che sia il super account di postgres.
```
pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
```
Eseguire una nuova installazione di PostGIS in un nuovo database -- ci riferiremo a questo database come newdb. Per le istruzioni su come fare, fare riferimento a Section 3.3.2, “Abilitare il database spazialmente senza usare l'ESTENSIONE (sconsigliato)” e Section 3.3.1, “Abilitazione spaziale di database usando il metodo EXTENSION”.

Le voci di spatial_ref_sys trovate nel dump verranno ripristinate, ma non sovrascriveranno quelle esistenti in spatial_ref_sys. Questo per garantire che le correzioni del set ufficiale vengano propagate correttamente ai database ripristinati. Se per qualche motivo si desidera sovrascrivere le voci standard, è sufficiente non caricare il file spatial_ref_sys.sql durante la creazione del nuovo database.

Se il vostro database è molto vecchio o sapete di aver usato funzioni deprecate da tempo nelle vostre viste e funzioni, potreste aver bisogno di caricare legacy.sql affinché tutte le vostre funzioni, viste ecc. tornino correttamente. Fatelo solo se è veramente necessario. Se possibile, si consiglia di aggiornare le viste e le funzioni prima di eseguire il dump. Le funzioni deprecate possono essere rimosse in seguito caricando uninstall_legacy.sql.
Ripristinare il backup nel nuovo database newdb utilizzando postgis_restore. Eventuali errori imprevisti saranno stampati da psql nel flusso di errore standard. Conservare un registro di questi errori.
```
postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt
```

Gli errori possono verificarsi nei seguenti casi:

Alcune viste o funzioni fanno uso di oggetti PostGIS deprecati. Per risolvere questo problema si può provare a caricare lo script legacy.sql prima del ripristino, oppure si dovrà ripristinare una versione di PostGIS che contiene ancora questi oggetti e riprovare la migrazione dopo aver eseguito il porting del codice. Se il metodo legacy.sql funziona, non dimenticate di correggere il codice per smettere di usare le funzioni deprecate e di eliminarle caricando uninstall_legacy.sql.
Alcuni record personalizzati di spatial_ref_sys nel file di dump hanno un valore SRID non valido. I valori SRID validi sono maggiori di 0 e minori di 999000. I valori nell'intervallo 999000.999999 sono riservati per uso interno, mentre i valori > 999999 non possono essere utilizzati. Tutti i record personalizzati con SRID non validi verranno mantenuti, con quelli > 999999 spostati nell'intervallo riservato, ma la tabella spatial_ref_sys perderà un vincolo di controllo che garantisce il mantenimento dell'invariante e forse anche la sua chiave primaria (quando più SRID non validi vengono convertiti nello stesso valore SRID riservato).

Per risolvere il problema è necessario copiare l'SRS personalizzato in un SRID con un valore valido (forse nell'intervallo 910000...910999), convertire tutte le tabelle nel nuovo SRS (vedere UpdateGeometrySRID), eliminare la voce non valida da spatial_ref_sys e ricostruire i controlli con:
```
ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check check (srid 
> 0 AND srid < 999000 );
```
```
ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid));
    
```
Se state aggiornando un vecchio database contenente la cartografia francese IGN , probabilmente i SRID sono fuori range e vedrete, quando importate il database, problemi come questo:
```
WARNING: SRID 310642222 converted to 999175 (in reserved zone)
```
In questo caso, si può provare a procedere come segue: per prima cosa eliminare completamente l'IGN dallo sql risultante da postgis_restore. Quindi, dopo aver eseguito :
```
postgis_restore "/somepath/olddb.backup" > olddb.sql
```
eseguire questo comando :
```
grep -v IGNF olddb.sql > olddb-without-IGN.sql
```
Creare quindi il proprio newdb, attivare le estensioni Postgis richieste e inserire correttamente l'IGN del sistema francese con: questo script Dopo queste operazioni, importare i dati:
```
psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt
```