37. Esquemas en Postgresql

Las bases de datos de producción inevitablemente tienen un gran número de tablas y vistas, y administrarlas puede volverse difícil. Afortunadamente, PostgreSQL incluye el concepto de «_Schema».

Los esquemas son como carpetas y pueden contener tablas, vistas, funciones, secuencias y otras relaciones. Toda base de datos comienza con un esquema, el esquema público.

Dentro de ese esquema, la instalación predeterminada de PostGIS crea las relaciones de metadatos geometry_columns, geography_columns y spatial_ref_sys, así como todos los tipos y funciones utilizados por PostGIS. Así que los usuarios de PostGIS siempre necesitan acceder al esquema público.

En el esquema público también puedes ver todas las tablas que hemos creado hasta ahora en el taller.

37.1. ¿Por qué usar esquemas?

Hay dos buenas razones para utilizar esquemas:

• A los datos que se gestionan en un esquema se puede aplicar acciones masivas con facilidad.

  • Es más fácil hacer copias de seguridad de datos que están en un esquema separado: así, los datos volátiles pueden tener un programa de copias de seguridad diferente del de los datos no volátiles.

  • Es más fácil restaurar datos que están en un esquema separado: así, los esquemas orientados a aplicaciones pueden restaurarse por separado y se puede hacer una copia de seguridad de ellos para recuperarlos.

  • Es más fácil gestionar los cambios en la aplicación cuando los datos de la aplicación están en un esquema: así, una nueva versión del software puede funcionar con una estructura de tablas en un nuevo esquema, y la transición implica un simple cambio en el nombre del esquema.

• El trabajo de los usuarios puede limitarse a esquemas únicos para aislar las tablas analíticas y de prueba de las tablas de producción.

Así pues, a efectos de producción, mantener los datos de la aplicación separados en esquemas mejora la gestión; y a efectos de usuario, mantener a los usuarios en esquemas separados evita que la colisión no deseada de actividades entre ellos.

37.2. Creando un Esquema de Datos

Vamos a crear un nuevo esquema y mover una tabla dentro de él. Primero, crea un nuevo esquema en la base de datos:

CREATE SCHEMA census;

A continuación, moveremos la tabla nyc_census_blocks al esquema census:

ALTER TABLE nyc_census_blocks SET SCHEMA census;

Si estás usando el programa de línea de comandos psql, notarás que nyc_census_blocks ha desaparecido de tu lista de tablas ahora! Si estás usando PgAdmin, puede que tengas que refrescar tu vista para ver el nuevo esquema y la tabla dentro de él.

Puedes acceder a las tablas dentro de los esquemas de dos maneras:

• haciendo referencia a ellas usando la notación schema.table

• añadiendo el esquema a tu search_path

La referencia explícita es fácil, pero con el tiempo se vuelve tediosa de escribir:

SELECT * FROM census.nyc_census_blocks LIMIT 1;

Manipular el search_path es una buena manera de proporcionar acceso a tablas en múltiples esquemas sin mucho tipeo adicional.

Puedes establecer el search_path en tiempo de ejecución usando el comando SET:

SET search_path = census, public;

Esto asegura que todas las referencias a relaciones y funciones sean buscadas tanto en los esquemas census como public. Recuerda que todas las funciones y tipos de PostGIS están en public así que no queremos quitarlo del search path.

Configurar el search path cada vez que te conectas también puede ser cansado, pero afortunadamente es posible establecerlo de forma permanente para un usuario:

ALTER USER postgres SET search_path = census, public;

Ahora el usuario postgres siempre tendrá el esquema census en su search path.

37.3. Creación de un esquema de usuario

A los usuarios les gusta crear tablas, y los usuarios de PostGIS en particular: las operaciones de análisis con SQL requieren tablas temporales para visualización o resultados intermedios, así que el SQL espacial tiende a requerir que los usuarios tengan privilegios de CREATE más que las cargas de trabajo ordinarias de bases de datos.

De manera predeterminada, cada rol en Oracle recibe un esquema personal. Esta es una buena práctica para usar también con los usuarios de PostgreSQL, y es fácil de replicar usando roles, esquemas y search paths de PostgreSQL.

Crea un nuevo usuario con privilegios de creación de tablas (ver Seguridad en PostgreSQL para información sobre el rol postgis_writer), luego crea un esquema con ese usuario como autorización:

CREATE USER myuser WITH ROLE postgis_writer;
CREATE SCHEMA myuser AUTHORIZATION myuser;

Si inicias sesión como ese usuario, encontrarás que el search_path predeterminado para PostgreSQL es en realidad este:

show search_path;

  search_path
----------------
 "$user",public

The first schema on the search path is the user’s named schema! So now the following conditions exist:

• El usuario existe, con la capacidad de crear tablas espaciales.

• El esquema con el nombre del usuario existe, y el usuario es su propietario.

• El search path del usuario tiene primero el esquema del usuario, por lo que las nuevas tablas se crean automáticamente allí, y las consultas buscan automáticamente allí primero.

Eso es todo, el área de trabajo predeterminada del usuario ahora está bien separada de cualquier tabla en otros esquemas.