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.

_images/schemas.jpg

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

Let’s create a new schema and move a table into it. First, create a new schema in the database:

CREATE SCHEMA census;

Next, we will move the nyc_census_blocks table to the census schema:

ALTER TABLE nyc_census_blocks SET SCHEMA census;

If you’re using the psql command-line program, you’ll notice that nyc_census_blocks has disappeared from your table listing now! If you’re using PgAdmin, you might have to refresh your view to see the new schema and the table inside it.

You can access tables inside schemas in two ways:

  • by referencing them using schema.table notation

  • by adding the schema to your search_path

Explicit referencing is easy, but it gets tiring to type after a while:

SELECT * FROM census.nyc_census_blocks LIMIT 1;

Manipulating the search_path is a nice way to provide access to tables in multiple schemas without lots of extra typing.

You can set the search_path at run time using the SET command:

SET search_path = census, public;

This ensures that all references to relations and functions are searched in both the census and the public schemas. Remember that all the PostGIS functions and types are in public so we don’t want to drop that from the search path.

Setting the search path every time you connect can get tiring too, but fortunately it’s possible to permanently set the search path for a user:

ALTER USER postgres SET search_path = census, public;

Now the postgres user will always have the census schema in their search path.

37.3. Creating a User Schema

Users like to create tables, and PostGIS users do so particularly: analysis operations with SQL demand temporary tables for visualization or interim results, so spatial SQL tends to require that users have CREATE privileges more than ordinary database workloads.

By default, every role in Oracle is given a personal schema. This is a nice practice to use for PostgreSQL users too, and is easy to replicate using PostgreSQL roles, schemas, and search paths.

Create a new user with table creation privileges (see PostgreSQL Security for information about the postgis_writer role), then create a schema with that user as the authorization:

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

If you log in as that user, you’ll find the default search_path for PostgreSQL is actually this:

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:

  • The user exists, with the ability to create spatial tables.

  • The user’s named schema exists, and the user owns it.

  • The user’s search path has the user schema first, so new tables are automatically created there, and queries automatically search there first.

That’s all there is to it, the user’s default work area is now nicely separated from any tables in other schemas.