Instalar la última versión de Postgis 2.2 en Debian

En esta entrada les quiero enseñar lo que necesitan para trabajar bases de datos geométricas en software libre, instalando las siguientes herramientas postgresql 9.5, postgis 2.2 y PgAdmin3 en Debian, ademas vamos a importar nuestros archivos shape con la herramienta shp2pgsql-gui. Y al finalizar les enseñare a optimizar algunos parámetros del Postgresql para obtener un mejor rendimiento de nuestra base de datos.

Antes de empezar vamos a conocer cada una de estas herramientas y sus características.

PostgreSQL

postgresql-logo

PostgreSQL es un sistema de gestión de bases de datos objeto-relacional, distribuido bajo licencia BSD y con su código fuente disponible libremente. Es el sistema de gestión de bases de datos de código abierto más potente del mercado y en sus últimas versiones no tiene nada que envidiarle a otras bases de datos comerciales.

PostgreSQL utiliza un modelo cliente/servidor y usa multiprocesos en vez de multihilos para garantizar la estabilidad del sistema. Un fallo en uno de los procesos no afectará el resto y el sistema continuará funcionando.
A continuación tenéis un gráfico que ilustra de manera general los componentes más importantes en un sistema PostgreSQL.

pgclient_server_0

  • Aplicación cliente: Esta es la aplicación cliente que utiliza PostgreSQL como administrador de bases de datos. La conexión puede ocurrir via TCP/IP ó sockets locales.
  • Demonio postmaster: Este es el proceso principal de PostgreSQL. Es el encargado de escuchar por un puerto/socket por conexiones entrantes de clientes. También es el encargado de crear los procesos hijos que se encargaran de autentificar estas peticiones, gestionar las consultas y mandar los resultados a las aplicaciones clientes
  • Ficheros de configuración: Los 3 ficheros principales de configuración utilizados por PostgreSQL, postgresql.conf, pg_hba.conf y pg_ident.conf
  • Procesos hijos postgres: Procesos hijos que se encarga de autentificar a los clientes, de gestionar las consultas y mandar los resultados a las aplicaciones clientes
  • PostgreSQL share buffer cache: Memoria compartida usada por POstgreSQL para almacenar datos en caché.
  • Write-Ahead Log (WAL): Componente del sistema encargado de asegurar la integridad de los datos (recuperacion de tipo REDO)
  • Kernel disk buffer cache: Caché de disco del sistema operativo
  • Disco: Disco físico donde se almacenan los datos y toda la información necesaria para que PostgreSQL funcione

 

Postgis:

150px-Logo_square_postgis

PostGIS es una extensión que convierte el sistema de base de datos PostgreSQL en una base de datos espacial para su utilización en Sistema de Información Geográfica. La combinación de ambos es una solución perfecta para el almacenamiento, gestión y mantenimiento de datos espaciales. Se publica bajo la Licencia Pública General de GNU.

  • Es una alternativa real al software propietario superándole en estabilidad y rapidez.
  • Actualmente es la base de datos espacial de código abierto más ámpliamente utilizada. Muchas y muy variadas organizaciones de todo el mundo usan PostGIS, incluyendo agencias gubernamentales de riesgos adversos y organizaciones que almacenan terabytes de datos y sirven millones de peticiones web al día.
  • PostGIS es software libre, tiene licencia GNU General Public License (GPL).
  • Es compatible con los estándares de Open Geospatial Consortium (OGC), con el objetivo de facilitar el intercambio de información geográfica.
  • Soporta tipos de datos espaciales, índices espaciales y tiene cientos de funciones espaciales (+ 1000).
  • Permite importar y exportar datos fácilmente a través de  varias herramientas conversoras (shp2pgsql, pgsql2shp, ogr2ogr, dxf2postgis).

PgAdmin3

PgAdminLogo

pgAdmin 3 es una herramienta de código abierto para la administración de bases de datos PostgreSQL y derivados (EnterpriseDB Postgres Plus Advanced Server y Greenplum Database). Incluye:

  • Interfaz administrativa gráfica
  • Herramienta de consulta SQL (con un EXPLAIN gráfico)
  • Editor de código procedural
  • Agente de planificación SQL/shell/batch
  • Administración de Slony-I

pgAdmin se diseña para responder a las necesidades de la mayoría de los usuarios, desde escribir simples consultas SQL hasta desarrollar bases de datos complejas.

La interfaz gráfica soporta todas las características de PostgreSQL y hace simple la administración. Está disponible en más de una docena de lenguajes y para varios sistemas operativos, incluyendo Microsoft Windows , Linux, FreeBSD, Mac OSX y Solaris.

Instalar los paquetes PostgreSQL 9.5, PostGIS 2.2 y pgAdmin3

Para hacer la instalación vamos a usar el repositorio apt de PostgreSQL para contar con las versiones más recientes de los paquetes necesarios para instalar PostGIS, además de PostgreSQL y PgAdmin3.

Antes de comenzar, debemos comprobar la versión del sistema operativo que estamos utilizando, para ello vamos a un terminal root y ejecutamos el siguiente comando:

Esta es la salida de mi sistema operativo

El “Codename” es el nombre de la distribución de linux donde estamos haciendo la instalación, en nuestro caso  es “wheezy” el cual vamos a usar en el siguiente comando

El comando anterior agrego una entrada en nuestro archivo “sources.list” puedes comprobar rápidamente con el comando cat

Necesitamos importa la clave del repositorio, lo vamos a hacer con el siguiente comando:

Actualizamos la lista de paquetes con el comando “update” de aplicación “apt”:

Luego de tener configurado el repositorio apt de postgresql podemos hacer la instalación de los paquetes necesarios via apt

Adicional mente podemos instalar la extensión pgRouting por si es necesaria más adelante en nuestro proyecto.

pgRouting es una extensión que añade enrutamiento y funcionalidad de análisis de redes a las bases de datos PostGIS/PostgreSQL. Es un proyecto de OSGeo Labs de la Fundación OSGeo y está incluido en OSGeo Live.

Configuración inicial de nuestro postgresql 9.5

Antes debemos saber cómo ubicar los archivos de configuración de nuestra instalación de  postgresql, lo vamos a hacer desde una consola root y con la ayuda del cliente psql.

Esta es la salida del SQL anterior

Ahora vamos a permitir el acceso remoto a nuestros usuarios para que puedan conectarse a la la base de datos utilizando el cliente pgAdmin3

Con el comando nano, en una consola root editamos el archivo pg_hba.conf

Agregamos la siguiente línea al final del archivo, esta nos va a permitir conectarnos desde cualquier ip usando contraseña bajo el protocolo SSL:

NOTA: En nano tenemos que ejecutar CTRL-O seguido de <ENTER> para guardar los cambios y luego CTRL-X para salir del archivo.

Luego vamos a editar el archivo  postgresql.conf

Buscamos la directiva “listen_addresses” y la descomentamos (Quitar en numeral #)  luego cambiamos ‘localhost’ por ‘*’ esto va habilitar las conexiones remotas al servidor.

Reiniciamos el servicio de postgres para que tome el cambio

NOTA: Se puede ejecutar actualizar los cambios en el archivo de configuración sin bajar el servicio con el siguiente comando:

Verificar que el servicio esté arriba y escuchando por el puerto 5432

Ejemplo de la salida del comando anterior:

Por seguridad la contraseña del usuario postgresql no viene asignada, podemos asignarle una clave compleja y luego crear un usuario de administración el cual vamos a utilizar siempre.

Crear un nuevo usuario para utilizarlo desde PgAdmin3

Vamos a crear el usuario para hacer las conexiones al servidor de base de datos, es muy recomendado no usar el usuario postgres para hacer el trabajo diario sobre la base de datos.

Crear la plantilla de base de datos “template_postgis”

La plantilla “template_postgis” va a contener todas las funcionalidades de postgis la cual usaremos para crear nuestras base de datos espaciales.

Para esto debemos entrar en una consola de root y ejecutar lo siguiente:

Configurar el PgAdmin3 para iniciar sesión en nuestra base de datos espacial

Vamos a iniciar nuestro pgAdmin3 para ello vamos al menú de Programación y seleccionamos pgAdmin III

PgAdmin3 Paso1

Una vez iniciado nos mostrará la pantalla de inicio de pgAdmin3 ahora vamos a crear una nueva conexión para poder tener acceso al servidor de base de datos instalado.

 

PgAdmin3 Paso3

Seleccionamos la opción “Add a connection to a server” y nos aparece la siguiente pantalla:

PgAdmin3 Paso4

 

Aquí configuramos nuestra conexión, en nuestro caso estoy usando localhost y el usuario geoserver creado anteriormente.

Ahora vamos a crear una base de datos espacial, hacemos clic derecho sobre Databases y seleccionamos New Database

PgAdmin3 Paso5

En la pestaña “Properties” colocamos el nombre de  nuestra base de datos en nuestro caso se debe llamar mexico_city y el dueño le asignamos geoserver.

PgAdmin3 Paso6

En la pestaña “Definition” colamos el encoding UTF-8 y el template creado previamente “template_postgis”

PgAdmin3 Paso7

Enhorabuena ya hemos creado nuestra primera base de datos espacial, para comprobar verificamos que se crearon las extensiones y los esquemas que se heredaron de nuestro “template_postgis”

PgAdmin3 Paso8

 

Importar los archivos shape utilizando la herramienta shp2pgsql-gui

En nuestra instalación de PostGis contiene algunos comando útiles para importar archivos, en nuestro caso vamos a usar la herramienta shp2pgsql-gui. Que nos permitirá conectarse rápidamente a su nueva base de datos PostGIS e importar un archivo shape.

En una terminal ejecutamos el siguiente comando::

Esta nos va a mostrar la pantalla de inicio de herramienta para importar y exportar archivos shape.

shp2pgsql-gui Paso1

Ahora vamos a crear una nueva conexión a la base de datos creada previamente “mexico_city” haciendo clic en el botón “View connection details…”

shp2pgsql-gui Paso2

Ahora vamos a agregar nuestros archivos shp, para ello hacemos clic en el botón Add File

shp2pgsql-gui Paso3

En este ejemplo voy a utilizar una cartografía base que tengo de la Ciudad de Mexico seleccionando los archivos mexico-city_mexico_osm_line.shp, mexico-city_mexico_osm_point.shp y mexico-city_mexico_osm_polygon.shp

shp2pgsql-gui Paso5

Luego de seleccionados los archivos procedemos a configurar los nombres de las tablas así como colocamos el SRID en 4326

shp2pgsql-gui Paso6

En el botón Options… indicamos que el encoding de nuestros DBF es LATIN1

shp2pgsql-gui Paso7

Ahora importamos los datos a nuestra base de datos haciendo clic en el botón Import…

shp2pgsql-gui Paso8

Podemos comprobar que el comando se ejecuto correctamente viendo la salida de Log Window.

shp2pgsql-gui Paso9

También podemos ver nuestras nuevas tablas en la base de datos mexico_city.

shp2pgsql-gui Paso10

 

Optimización del Servidor PostgreSQL para producción.

Vamos a modificar la configuración por defecto de nuestro servidor PostgreSQL, por defecto viene con una configuración diseñada para “trabajar en todas partes”.

Este pequeño tuning de postgreSQL nos va a servir para manejar los altos volúmenes de data que requiere un sistema de información geográfico.

Las modificaciones del tuning de PostgreSQL que realizaremos serán efectuadas en el archivo “postgresql.conf”

Para comenzar debemos hacer un respaldo de la configuración actual:

Editamos el archivo con el comando nano

Las características que vamos a modificar son las siguientes: shared_buffers, work_mem, effective_cache_size, maintenance_work_mem

shared_buffers

La edición de esta opción es la forma más sencilla de mejorar el rendimiento del servidor de base de datos, de hecho por defecto el valor es bastante bajo para un entorno de producción. Por lo que siguiendo las recomendaciones de la comunidad de los expertos, estableceremos en Mb el shared_buffer a un 25% de la memoria RAM disponible en el sistema.

NOTA: Podemos usar el comando “free” o “cat /proc/meminfo | grep MemTotal” para saber la memoria total del sistema operativo

shared_buffer = Memoria RAM Disponible * 0.25

NOTA: cambiar “Memoria RAM disponible * 0.25” por el resultado de dicha multiplicación.

NOTA 2: Hay parámetros del kernel que necesitan ser ajustados para permitir grandes valores de shared_buffers, ya que él mismo pone límites a la cantidad de memoria compartida se puede asignar (para evitar accidentes). Estos serían los cálculos que tendríamos que efectuar para la asignación en bytes de los parámetros a continuación:

kernel.shmmax = shared_buffer * 1024 * 1024

kernel.shmall = kernel.shmmax / 4096

Una vez realizados los cálculos procedemos a editar el archivo /etc/sysctl.conf y podemos aplicar los cambios ya sea reiniciando el servidor o mediante el siguiente comando:

sysctl -p

work_mem

Esta opción se utiliza para controlar la cantidad de memoria utilizada en operaciones de ordenación y tablas hash. Esto no es un parámetro de todo el sistema, se refiere a los recursos utilizados por una operación. Así que si una consulta compleja tiene varias operaciones de ordenación entonces esta utilizará múltiples unidades “work_mem”.

El valor a asignar al work_mem podremos calcularlo de la siguiente manera:

work_mem = Memoria RAM Disponible * 0.15

effective_cache_size

Este valor indica a PostgreSQL cuánta memoria tiene disponible para los datos de almacenamiento en caché y ayuda en determinar si es o no viable utilizar un índice. Entonces se debe establecer la cantidad de memoria asignada a “shared_buffers” más la cantidad de caché de sistema operativo disponible. A menudo esto es más que 50% de la memoria total del sistema.

Podremos calcular el valor mediante la siguiente fórmula:

effective_cache_size =  Memoria RAM Disponible * 0.75

maintenance_work_mem

Como su nombre lo indica, este es el equivalente de “work_mem”, pero para consultas de tipo “mantenimiento” (VACÍO, CREATE INDEX, entre otras).

Dado que estas consultas no se ejecutan con mucha frecuencia, se recomienda la ejecución de las mismas cuando el servidor presente una carga baja, ya que por lo general estas tareas de mantenimiento necesitan bastante tiempo para ejecutarse.

Asignar un buen valor a este parámetro te ayudará a garantizar que las consultas de mantenimiento terminen tan rápido como sea posible.

Calcular el valor del parámetro de la siguiente manera:

effective_cache_size =  Memoria RAM Disponible / 8

Se puede conseguir mas información de tuning a traves de la wiki oficial de postgresql

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server/es

 

3 thoughts on “Instalar la última versión de Postgis 2.2 en Debian

  1. Hola José Luis, te felicito por tu blog, posee mucha información detallada. Estoy iniciandome en el mundo de GIS. Mi deseo es tener un servidor de mapas para que los mismos estén disponibles para muchos usuarios desde la Web. Ahora mi pregunta es, para realizar y modificar mapas lo debo hacer desde el Qgis, por ejemplo?. Saludos

    • Hola Oswaldo, gracias por tu comentario si cuando se trata de modificar datos es mejor hacerlo con una herramienta de escritorio como Qgis.
      Saludos,

  2. HOla, mil gracias por tu post, está genial y ayuda a sobremanera, solo que en mi distro de debian no ejecuta el shp2pgsql-gui. he buscado por todos lados y no encuentro una solución. espero y me pudieras dar alguna idea de cómo resolver este proceso.