Chapter 3. Administración de PostGIS

Chapter 3. Administración de PostGIS
Prev		Next

3.1. Optimización del rendimiento

El ajuste del rendimiento de PostGIS es muy similar al ajuste de cualquier carga de trabajo de PostgreSQL. La única consideración adicional es que las geometrías y las ráster suelen ser grandes, por lo que las optimizaciones relacionadas con la memoria suelen tener un mayor impacto en PostGIS que en otros tipos de consultas de PostgreSQL.

Para obtener información general sobre cómo optimizar PostgreSQL, consulte Ajustar su servidor PostgreSQL.

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;

Además de la configuración de Postgres, PostGIS tiene algunas configuraciones personalizadas que se enumeran en Section 7.22, “Grand Unified Custom Variables (GUCs)”.

3.1.1. Inicio

Estos ajustes se configuran en postgresql.conf:

constraint_exclusion

Predeterminado: partición
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.

buffers_compartidos

Default: ~128MB
Configúre entre el 25 % y el 40 % de la RAM disponible. En Windows es posible que no pueda configurar un valor tan 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.

Predeterminado: 18
Establece el número máximo de procesos en segundo plano que el sistema puede admitir. Este parámetro solo se puede configurar al iniciar el servidor.

3.1.2. Runtime

work_mem - sets the size of memory used for sort operations and complex queries

Default: 1-4MB
Adjust up for large dbs, complex queries, lots of RAM
Adjust down for many concurrent users or low RAM.
If you have lots of RAM and few developers:
```
SET work_mem TO '256MB';
```

maintenance_work_mem - the memory size used for VACUUM, CREATE INDEX, etc.

Default: 16-64MB
Generally too low - ties up I/O, locks objects while swapping memory
Recommend 32MB to 1GB on production servers w/lots of RAM, but depends on the # of concurrent users. If you have lots of RAM and few developers:
```
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.

Default: 0
Sets the maximum number of workers that can be started by a single Gather node. Parallel workers are taken from the pool of processes established by max_worker_processes. Note that the requested number of workers may not actually be available at run time. If this occurs, the plan will run with fewer workers than expected, which may be inefficient. Setting this value to 0, which is the default, disables parallel query execution.

3.2. Configuración del soporte raster

Si ha activado el soporte raster, le recomendamos que lea a continuación cómo configurarlo correctamente.

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)”.

Si desea activar raster sin conexión:

POSTGIS_ENABLE_OUTDB_RASTERS=1

Cualquier otro ajuste, o ningún ajuste, desactivará los rasters fuera de la db.

Para habilitar todos los controladores de GDAL disponibles en su instalación de GDAL, configure esta variable de entorno del siguiente modo

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

Si sólo desea habilitar controladores específicos, configure su variable de entorno de la siguiente manera:

POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"


	Si está en windows, no cite la lista de controladores

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.

En Windows, si se ejecuta como un servicio, se puede configurar a través de las variables del sistema, a las que se puede acceder en Windows 7 haciendo clic con el botón derecho del ratón en Equipo-> Propiedades Configuración avanzada del sistema o en el explorador navegando hasta Panel de control\Todos los elementos del panel de control\Sistema. A continuación, haga clic en Configuración avanzada del sistema -> > Avanzadas -> > Variables de entorno y añada nuevas variables del sistema.

Después de configurar las variables de entorno, deberá reiniciar el servicio PostgreSQL para que los cambios surtan efecto.

3.3. Creación de bases de datos espaciales

3.3.1. Habilitar la base de datos espacial mediante EXTENSION

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

El núcleo de la extensión postgis incluye geometría, geografía, spatial_ref_sys y todas las funciones y comentarios. Raster y topología se empaquetan como una extensión separada.

Ejecute el siguiente fragmento de SQL en la base de datos que desee habilitar espacialmente:

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

3.3.2. Habilitar espacialmente la base de datos sin utilizar EXTENSION (desaconsejado)


	Por lo general, esto sólo es necesario si no puede o no quiere tener PostGIS instalado en el directorio de extensión de PostgreSQL (por ejemplo, durante las pruebas, el desarrollo o en un entorno restringido).

Para agregar objetos PostGIS y definiciones de funciones a su base de datos, cargue los distintos archivos sql ubicados en [prefix]/share/contrib, tal y como se especifica durante la fase de compilación.

Los objetos principales de PostGIS (tipos geométricos y geográficos, y sus funciones de soporte) se encuentran en el script postgis.sql. Los objetos ráster se encuentran en el script rtpostgis.sql. Los objetos topológicos se encuentran en el script topology.sql.

Para obtener un conjunto completo de identificadores de definición del sistema de coordenadas EPSG, también puede cargar el archivo de definiciones spatial_ref_sys.sql y rellenar la tabla spatial_ref_sys. Esto le permitirá realizar operaciones ST_Transform() en geometrías.

Si desea añadir comentarios a las funciones de PostGIS, los encontrará en el script postgis_comments.sql. Los comentarios se pueden ver simplemente escribiendo \dd [nombre_función] desde una ventana de terminal psql.

Ejecute los siguientes comandos Shell en su terminal:

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. Actualización de bases de datos espaciales

3.4.1. Actualización suave

Actualizar las bases de datos espaciales existentes puede resultar complicado, ya que requiere sustituir o introducir nuevas definiciones de objetos PostGIS.

Desafortunadamente, no todas las definiciones se pueden reemplazar fácilmente en una base de datos activa, por lo que a veces la mejor opción es un proceso de volcado/recarga.

PostGIS proporciona un procedimiento de ACTUALIZACIÓN SUAVE para versiones menores o correcciones de errores, y un procedimiento de ACTUALIZACIÓN COMPLETA para versiones principales.

Antes de intentar actualizar PostGIS, siempre es recomendable hacer una copia de seguridad de sus datos. Si utiliza el indicador -Fc con pg_dump, siempre podrá restaurar el volcado con una ACTUALIZACIÓN DURO.

3.4.1. Actualización suave

Si instaló su base de datos utilizando extensiones, también deberá actualizarla utilizando el modelo de extensiones. Si la instaló utilizando el antiguo método de script sql, le recomendamos que cambie su instalación a extensiones, ya que el método de script ya no es compatible.

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();

Si tiene varias versiones de PostGIS instaladas y no desea actualizar a la última, puede especificar explícitamente la versión de la siguiente manera:

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

Si recibe un aviso de error similar al siguiente:

No migration path defined for … to 3.7.0dev

A continuación, deberá realizar una copia de seguridad de su base de datos, crear una nueva tal y como se describe en Section 3.3.1, “Habilitar la base de datos espacial mediante EXTENSION” y, a continuación, restaurar su copia de seguridad sobre esta nueva base de datos.

Si recibe un mensaje de aviso como:

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

Entonces todo estará ya actualizado y podrá ignorarlo sin problema. UNLESS que esté intentando actualizar de una versión de desarrollo a la siguiente (que no tiene un nuevo número de versión); en ese caso, puede añadir «next» a la cadena de la versión, y la próxima vez tendrá que eliminar el sufijo «next» de nuevo:

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


	Si instaló PostGIS originalmente sin especificar una versión, a menudo puede omitir la reinstalación de la extensión postgis antes de restaurar, ya que la copia de seguridad solo tiene `CREATE EXTENSION postgis` y, por lo tanto, toma la última versión más reciente durante la restauración.

Si está actualizando la extensión PostGIS desde una versión anterior a la 3.0.0, tendrá una nueva extensión postgis_raster que puede eliminar de forma segura si no necesita compatibilidad con ráster. Puede eliminarla de la siguiente manera:

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. Actualización difícil

Por ACTUALIZACIÓN COMPLETA nos referimos al volcado/recarga completa de bases de datos habilitadas para PostGIS. Necesita una ACTUALIZACIÓN COMPLETA cuando cambia el almacenamiento interno de los objetos PostGIS o cuando no es posible realizar una ACTUALIZACIÓN PARCIAL. El apéndice Notas de la versión indica para cada versión si es necesario realizar un volcado/recarga (ACTUALIZACIÓN COMPLETA) para actualizar.

El proceso de volcado/recarga cuenta con la ayuda del script postgis_restore, que se encarga de omitir del volcado todas las definiciones que pertenecen a PostGIS (incluidas las antiguas), lo que le permite restaurar sus esquemas y datos en una base de datos con PostGIS instalado sin obtener errores de símbolos duplicados ni transferir objetos obsoletos.

Las instrucciones complementarias para usuarios de Windows están disponibles en Windows Hard upgrade.

El procedimiento es el siguiente:

Cree un volcado con «formato personalizado» de la base de datos que desea actualizar (llamémosla olddb) e incluya blobs binarios (-b) y salida detallada (-v). El usuario puede ser el propietario de la base de datos, no es necesario que sea la cuenta superusuario de Postgres.
```
pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
```
Realice una nueva instalación de PostGIS en una nueva base de datos; nos referiremos a esta base de datos como newdb. Consulte Section 3.3.2, “Habilitar espacialmente la base de datos sin utilizar EXTENSION (desaconsejado)” y Section 3.3.1, “Habilitar la base de datos espacial mediante EXTENSION” para obtener instrucciones sobre cómo hacerlo.

Las entradas spatial_ref_sys que se encuentren en su volcado se restaurarán, pero no sustituirán a las existentes en spatial_ref_sys. Esto es para garantizar que las correcciones del conjunto oficial se propaguen correctamente a las bases de datos restauradas. Si por cualquier motivo desea realmente sustituir las entradas estándar, simplemente no cargue el archivo spatial_ref_sys.sql al crear la nueva base de datos.

Si su base de datos es muy antigua o sabe que ha estado utilizando funciones obsoletas en sus vistas y funciones, es posible que tenga que cargar legacy.sql para que todas sus funciones y vistas, etc., vuelvan a funcionar correctamente. Haga esto solo si es realmente necesario. Si es posible, considere actualizar sus vistas y funciones antes de realizar el volcado. Las funciones obsoletas se pueden eliminar posteriormente cargando uninstall_legacy.sql.
Restaure su copia de seguridad en su nueva base de datos newdb utilizando postgis_restore. Si se produce algún error inesperado, psql lo imprimirá en el flujo de errores estándar. Mantenga un registro de dichos errores.
```
postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt
```

Pueden surgir errores en los siguientes casos:

Algunas de sus vistas o funciones utilizan objetos PostGIS obsoletos. Para solucionar esto, puede intentar cargar el script legacy.sql antes de restaurar o tendrá que restaurar a una versión de PostGIS que aún contenga esos objetos e intentar la migración de nuevo después de portar su código. Si la forma legacy.sql le funciona, no olvide corregir su código para dejar de utilizar funciones obsoletas y eliminarlas cargando uninstall_legacy.sql.
Algunos registros personalizados de spatial_ref_sys en el archivo de volcado tienen un valor SRID no válido. Los valores SRID válidos son mayores que 0 y menores que 999000. Los valores en el rango 999000.999999 están reservados para uso interno, mientras que los valores >999999 no se pueden utilizar en absoluto. Todos los registros personalizados con SRID no válidos se conservarán, y los valores > 999999 se moverán al rango reservado, pero la tabla spatial_ref_sys perdería una restricción de verificación que garantiza que se mantenga esa invariante y, posiblemente, también su clave principal (cuando varios SRID no válidos se convierten al mismo valor SRID reservado).

Para solucionar esto, debe copiar su SRS personalizado a un SRID con un valor válido (tal vez en el rango 910000..910999), convertir todas sus tablas al nuevo srid (consulte UpdateGeometrySRID), eliminar la entrada no válida de spatial_ref_sys y reconstruir las verificaciones 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));
    
```
Si está actualizando una base de datos antigua que contiene cartografía francesa IGN , es probable que los SRID estén fuera de rango y que, al importar la base de datos, aparezcan problemas como este:
```
WARNING: SRID 310642222 converted to 999175 (in reserved zone)
```
En este caso, puede intentar seguir los siguientes pasos: primero, elimine completamente el IGN del sql que resulta de postgis_restore. Por lo tanto, después de ejecutar:
```
postgis_restore "/somepath/olddb.backup" > olddb.sql
```
corre este comando:
```
grep -v IGNF olddb.sql > olddb-without-IGN.sql
```
Cree su nueva base de datos, active las extensiones Postgis necesarias e inserte correctamente el sistema francés IGN con: este script Después de estas operaciones, importe sus datos:
```
psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt
```