PostgreSQL¶

Introducción¶

El objetivo de este tutorial sobre PostgreSQL es que el usuario sea capaz de crear y eliminar bases de datos y acceder a ellas para la manipulación de los datos.

Por esto, los puntos siguientes están pensados para dar una introducción simple a PostgreSQL, a los conceptos básicos sobre bases de datos relacionales y al lenguaje SQL. No se requiere experiencia en sistemas UNIX ni en programación.

Tras el tutorial, es posible continuar el aprendizaje leyendo la documentación oficial del proyecto, en inglés, en la que se puede encontrar abundante información sobre el lenguaje SQL, el desarrollo de aplicaciones para PostgreSQL y la configuración y administración de servidores.

Arquitectura cliente/servidor¶

Al igual que el resto de componentes instalados, PostgreSQL utiliza un modelo cliente/servidor, ya explicado en la introducción.

Las aplicaciones cliente pueden ser de naturaleza muy diversa: una herramienta orientada a texto (psql), una aplicación gráfica (pgAdmin3), un servidor web que accede a la base de datos para mostrar las páginas web, o una herramienta de mantenimiento de bases de datos especializadas. Algunas aplicaciones de cliente se suministran con la distribución PostgreSQL mientras que otras son desarrolladas por los usuarios.

Creación de una base de datos¶

El primer paso para trabajar con PostgreSQL es crear una base de datos. Para ello es necesario ejecutar como usuario postgres el comando createdb:

$ sudo su postgres
$ createdb mibd

Si no se tiene acceso físico al servidor o se prefiere acceder de forma remota es necesario utilizar un cliente SSH. La siguiente instrución:

$ ssh geo@190.109.197.226

conecta al servidor 190.109.197.226 con el usuario geo.

Ejercicio: Conectar al sistema desde Windows y crear una base de datos.

Generalmente el mejor modo de mantener la información en la base de datos es utilizando un usuario distinto a postgres, que sólo debería usarse para tareas administrativas. Es posible incluso crear más de un usuario con diferentes derechos (SELECT, INSERT, UPDATE, DELETE) para tener un entorno más seguro. Sin embargo, esto queda fuera del ámbito de este tutorial y se conectará siempre con el usuario postgres.

Acceso a una base de datos¶

Una vez la base de datos ha sido creada es posible utilizar un cliente para conectar a ella. Existen varias maneras:

• psql: el programa de terminal interactivo de PostgreSQL que permite introducir de forma interactiva, editar y ejecutar comandos SQL. Veremos más adelante qué es SQL. Es el que utilizaremos.
• una herramienta existente con interfaz gráfica, como pgAdmin, que veremos brevemente.
• una aplicación personalizada desarrollada con algún lenguaje para el que haya un driver de acceso. Esta posibilidad no se trata en esta formación.

Para conectar con pgAdmin se deberá seleccionar el menu File > Add Server y registrar el nuevo servidor con su dirección IP y el puerto en el que está escuchando (5432 por defecto). También habrá que indicar el nombre de usuario con el que se desea hacer la conexión.

Una vez se tiene configurada una entrada para la base de datos en pgAdmin, es posible conectar a dicho servidor haciendo doble click en dicha entrada.

Una vez creada, es posible selecionar la nueva base de datos y mostrar el árbol de objetos que contiene. Se puede ver el esquema “public” que no contiene ningún elemento.

Para seguir interactuando con la base de datos abriremos una ventana SQL clicando sobre el siguiente icono:

Que abrirá una ventana que permite enviar comandos SQL al servidor de base de datos. Probemos con los siguientes comandos:

SELECT version ();
SELECT current_date;
SELECT 2 + 2;

psql¶

También podemos conectar a la base de datos con psql. Podemos conectar con psql desde cualquier máquina que tenga una versión de psql compatible con el servidor. El propio servidor tiene dicho programa instalado y es obviamente compatible por lo que la mejor opción es acceder al servidor:

$ ssh geo@190.109.197.226

Es posible especificar al comando psql la base de datos a la que se quiere acceder, el usuario con el que se quiere realizar el acceso y la instrucción que se quiere ejecutar en el sistema. Los valores concretos utilizados dependerán de la configuración concreta del servidor. En adelante usaremos el usuario de base de datos postgres y la base de datos geoserverdata.

La siguiente instrucción invoca la función version:

$ psql -U postgres -d test_database -c "SELECT version ()"
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)

Otros ejemplos:

$ psql -U postgres -d test_database -c "SELECT current_date"
    date
------------
 2012-09-11
(1 row)

$ psql -U postgres -d test_database -c "SELECT 2 + 2"
 ?column?
----------
        4
(1 row)

Todos estos comandos SQL pueden ser ejecutados usando otro parámetro del programa psql. La opción -f permite especificar un fichero que contiene instrucciones SQL. Así, por ejemplo sería posible crear un fichero en /tmp/mi_script.sql con el siguiente contenido:

SELECT version ();
SELECT current_date;
SELECT 2 + 2;

Y ejecutarlo con la instrucción:

$ psql -U geoserver -d geoserverdata -f /tmp/mi_script.sql

                                               version
------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.11 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 32-bit
(1 row)

    date
------------
 2014-02-11
(1 row)

 ?column?
----------
        4
(1 row)

Como se puede observar, se ejecutan todos los comandos del script sql uno detrás de otro.

Consola psql interactiva¶

También es posible, y conveniente para tareas de mayor complejidad, entrar al modo interactivo de psql. Para ello podemos omitir el parámetro -c:

$ psql -U postgres -d test_database

o conectar sin especificar la base de datos y usar el comando \c dentro de psql:

$ psql -U postgres
=# \c test_database
You are now connected to database "mibd" as user "postgres".

En el resto de la documentación se seguirán enviando comandos SQL desde la línea de comandos del sistema operativo ($) usando el parámetro -c o el parámetro -f, como especificado anteriormente. Sin embargo, se especifica a continuación una mínima referencia sobre los comandos que se pueden ejecutar en la línea de comandos de postgresql (=#)

Para obtener el listado de las bases de datos existentes en el sistema, usar el comando \l:

=# \l

Y para listar tablas del esquema por defecto de la base de datos actual (public):

=# \dt

Si queremos listar las tablas que hay en otro esquema es posible utilizar la siguiente sintaxis:

=# \dt gis.*

Por último, para obtener información sobre cualquier objeto de la base de datos es posible utilizar el comando \d:

=# \d gis.categorias

Se puede añadir un + para obtener información más detallada:

=# \d+ gis.categorias

=# \?

Cargando información desde shapefile: shp2pgsql¶

El parámetro -f es extremadamente útil cuando queremos usar PostgreSQL junto con su extensión espacial PostGIS para la carga de datos desde shapefile. Para ello contamos con shp2pgsql, que es capaz de generar un script SQL a partir de un shapefile que al ejecutar en PostgreSQL generará una tabla espacial con los mismos datos del shapefile.

La sintaxis básica es sencilla:

shp2pgsql <opciones> <nombre_shapefile_origen> <nombre_tabla_destino>

Las opciones más interesantes son:

• -I crea un índice espacial de la columna de geometrías. Esto impacta significativamente en el rendimiento posterior, y su uso es recomendado en todos los casos.
• -s <srid> indica el sistema de referencia de coordenadas en el que está expresada la capa. Suele corresponder con el código numérico EPSG. Consultar http://spatialreference.org/ para buscar los posibles códigos.
• -W <encoding> en ocasiones los atributos alfanuméricos con caracteres especiales (acentos) vienen codificados en LATIN1, y hay que indicarlo con éste parámetro.

Por ejemplo:

$ shp2pgsql -I -s 4326 provincias.shp provincias

El comando anterior realmente muestra por pantalla el script, lo cual no es muy útil y además tarda mucho tiempo (con Ctrl+C es posible cancelar la ejecución en la mayoría de los casos). Para que realmente sea útil tenemos que almacenar los datos en un fichero que luego podamos pasar a psql con el parámetro -f. Esto lo podemos hacer redireccionando la salida estándar a un fichero temporal:

$ shp2pgsql -I -s 4326 provincias.shp provincias > /tmp/provincias.sql

Es posible que durante este proceso obtengamos un error similar a éste:

Unable to convert data value to UTF-8 (iconv reports "Invalid or incomplete multibyte or wide character"). Current encoding is "UTF-8". Try "LATIN1" (Western European), or one of the values described at http://www.postgresql.org/docs/current/static/multibyte.html.

lo cual quiere decir que la codificación utilizada para almacenar los textos en el fichero .dbf no es UTF-8, que es la que espera el programa shp2pgsql por defecto. También nos sugiere que intentemos LATIN1. Para decirle al programa qué codificacion utilizamos, podemos especificar el parámetro -W:

$ shp2pgsql -I -s 4326 -W LATIN1 provincias.shp provincias > /tmp/provincias.sql

Y si nuestros datos están en LATIN1 se generará el script sin ningún problema.

A continuación no tenemos más que cargar el fichero recién generado con psql:

$ psql -U postgres -d geoserverdata -f /tmp/provincias.sql

Tras la ejecución podemos ver con cualquier sistema GIS que soporte conexiones PostGIS 2.0 (como QGis) que se ha creado una tabla en PostreSQL/PostGIS con los mismos datos que contenía el shapefile.

El siguiente aspecto que tenemos que tener en cuenta, es que el sistema de referencia de coordenadas (CRS) no está especificado. Por ejemplo, ejecutando esta instrucción:

$ psql -U postgres -d geoserverdata -c "select * from geometry_columns"

 f_table_catalog | f_table_schema |      f_table_name       | f_geometry_column | coord_dimension | srid |      type
-----------------+----------------+-------------------------+-------------------+-----------------+------+-----------------
 geoserverdata   | public         | provincias              | geom              |               2 | 4326 | MULTIPOLYGON

podemos observar que la tabla recién creada tiene un campo srid, que indica el código EPSG del sistema de coordenadas utilizado.

Por último, es recomendable crear nuestros datos en un esquema distinto de public para facilitar las copias de seguridad y las actualizaciones de PostGIS, por motivos que no se tratan en esta documentación:

$ psql -U postgres -d geoserverdata -c "create schema gis"
CREATE SCHEMA
$ shp2pgsql -I -s 4326 provincias.shp gis.provincias > /tmp/provincias.sql

Incluso es posible cargar en PostgreSQL el fichero resultante con una única línea, sólo enlazando la salida de shp2pgsql con la entrada de psql mediante una tubería de linux “|”:

$ shp2pgsql -I -s 4326 provincias.shp gis.provincias | psql -U postgres -d geoserverdata

Por ejemplo los siguientes comandos cargan una serie de datos en PostGIS, en la base de datos geoserver:

$ psql -U postgres -d geoserver -c "create schema gis"
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_adm0.shp gis.admin0 | psql -U postgres -d geoserverdata
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_adm1.shp gis.admin1 | psql -U postgres -d geoserverdata
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_adm2.shp gis.admin2 | psql -U postgres -d geoserverdata
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_rails.shp gis.ferrovia | psql -U postgres -d geoserverdata
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_roads.shp gis.vias | psql -U postgres -d geoserverdata
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_water_areas_dcw.shp gis.zonas_agua | psql -U postgres -d geoserverdata
$ shp2pgsql -I -s 4326 -W LATIN1 /tmp/datos/ARG_water_lines_dcw.shp gis.lineas_agua | psql -U postgres -d geoserverdata

Nótese que todos estos pasos se pueden simplificar en sólo dos, que cargarían todos los shapefiles de un directorio:

$ psql -U postgres -d geoserver -c "create schema gis"
$ for i in `ls /tmp/datos/*.shp`; do shp2pgsql -I -s 4326 $i gis.${i%.shp} | psql -U postgres -d geoserverdata; done

El siguiente ejemplo crea una base de datos llamada analisis y dentro de ella un esquema llamado gis. Luego se instala la extensión PostGIS y por último se cargan en la base de datos todos los shapefiles existentes en el directorio Escritorio/datos/analisis:

$ psql -U postgres -c "create database analisis"
$ psql -U postgres -d analisis -c "create schema gis"
$ psql -U postgres -d analisis -c "create extension postgis"
$ for i in `ls /tmp/datos/analisis/*.shp`; do shp2pgsql -I -s 25830 $i gis.${i%.shp} | psql -U postgres -d analisis; done

Creación de copias de seguridad¶

Un aspecto importante a la hora de administrar un servidor de base de datos es la creación de copias de seguridad.

Para hacer y restaurar la copia de seguridad se utilizan los comandos pg_dump y pg_restore en la línea de comandos del sistema operativo. El comando pg_dump tiene la siguiente sintaxis:

pg_dump <options> <database>

Entre las opciones más interesantes están:

• username: nombre del usuario con el que conectar a la base de datos para realizar la copia: –username=geo
• password: clave para conectar a la base de datos
• host: dirección del servidor de base de datos. Se puede omitir si es la máquina desde la cual se lanza el comando: –host=192.168.2.107
• schema: esquema que se quiere copiar. Si no se especifica se copiarán todos los esquemas.
• format: formato de la copia. Para obtener un formato compatible con pg_restore es necesario especificar “c”: –format=c
• file: fichero donde queremos guardar la copia de seguridad: –file=/tmp/db.backup

Así, si accedemos a la base de datos “geoserverdata” con el usuario “geoserver” y quisiéramos hacer una copia del esquema “gis” podríamos ejecutar la siguiente instrucción desde la línea de comandos del servidor de base de datos:

$ pg_dump --username=geoserver --format=c --schema=gis --file=/tmp/gis.backup geoserverdata

Dicho comando creará un fichero en /tmp/gis.backup con la copia de todos los datos que hay en el esquema “gis”.

Para recuperar la copia se puede utilizar el comando pg_restore:

$ pg_restore --username=geoserver --dbname=geoserverdata /tmp/gis.backup

Si el esquema existe, el comando pg_restore dará un error por lo que si queremos reemplazar los contenidos del esquema deberemos renombrar el esquema primero con la siguiente instrucción:

$ psql --username=geoserver --dbname=geoserverdata --command="alter schema gis rename to gis2"

Una vez la copia de seguridad ha sido recuperada de forma satisfactoria es posible eliminar el esquema renombrado:

$ psql --username=geoserver --dbname=geoserverdata --command="drop schema gis2 cascade"

Más información¶

La página web de PostgreSQL se puede consultar aquí [1]. En ella hay abundante información en inglés [2], así como listas de correo en español [3].

También se puede descargar un curso de PostGIS de bastante difusión [4].

Referencias¶