Capitolo 3. Amministrazione di PostGIS
Indietro     Avanti

Capitolo 3. Amministrazione di PostGIS

Indice

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.

Set work_mem, reload the configuration so new connections use the change, and display the current value. Use SHOW ALL to list all settings.

Code
ALTER SYSTEM SET work_mem = '256MB';
SELECT pg_reload_conf();
SHOW work_mem;

Oltre alle impostazioni di Postgres, PostGIS ha alcune impostazioni personalizzate che sono elencate in Sezione 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:

    Code
    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:

    Code
    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 Sezione 7.22, «Grand Unified Custom Variables (GUCs)».

Se vuoi abilitare il supporto per i raster offline:

Code
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

Code
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

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

Code
POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"
[Nota]

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

Se hai compilato ed installato il modulo extensions/postgis, puoi aggiungere il supporto spaziale in un database usando il meccanismo per le estensioni (EXTENSION)

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:

Code
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)

[Nota]

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:

Code
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

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.

Code
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.

Code
ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();

This second call is needed to rebundle the postgis_raster extension.

Code
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:

Code
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:

Output
No migration path defined for … to 3.7.0dev

È quindi necessario eseguire un backup del database, crearne uno nuovo come descritto in Sezione 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:

Output
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":

Code
ALTER EXTENSION postgis UPDATE TO "3.7.0devnext";
        ALTER EXTENSION postgis_topology UPDATE TO "3.7.0devnext";
[Nota]

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.
[Nota]

If you are upgrading PostGIS extension from a version prior to 3.0.0, you will have a new extension postgis_raster which you can safely drop, if you don't need raster support.
Code
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.

The dump/reload process is assisted by the postgis_restore script which takes care of skipping from the dump all definitions which belong to PostGIS (including old ones), allowing you to restore your schemas and data into a database with PostGIS installed without getting duplicate symbol errors or bringing forward deprecated objects.

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

La procedura è la seguente:

  1. 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.

    Code
    pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

  2. 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 Sezione 3.3.2, «Abilitare il database spazialmente senza usare l'ESTENSIONE (sconsigliato)» e Sezione 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.

  3. 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.

    Code
    postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt

Gli errori possono verificarsi nei seguenti casi:

  1. 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.

  2. 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:

    Code
    ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check check (srid 
> 0 AND srid < 999000 );
    Code
    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:

    Code
    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 :

    Code
    postgis_restore "/somepath/olddb.backup" > olddb.sql

    eseguire questo comando :

    Code
    grep -v IGNF olddb.sql > olddb-without-IGN.sql

    Create your newdb, activate the required PostGIS extensions, and insert the French IGN spatial reference definitions using this script. After these operations, import your data:

    Code
    psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt