Administrar un SGBD (Cualquiera) - documentación de Apuntes de Informática (FP)

¿Cómo voy a instalar el software?

Descargas la versión para tu sistema operativo y lo instalas.

Por ejemplo, en Debian:

> apt install postgresql

¿Donde está el software? ¿Cómo gestiono (arrancar, parar, reiniciar, ver estado) el servicio?

Se instala como un servicio en el sistema. La gestión de los servicios depende del sistema. Es importante conocer la arquitectura básica del sistema (tutorial-arch)

Por ejemplo, en Debian usando systemctl:

postgres@tu-equipo:~$ systemctl status postgresql
 postgresql.service - PostgreSQL RDBMS
 Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
 Active: active (exited) since Wed 2023-02-01 13:26:52 AST; 5 days ago
 Process: 812 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 812 (code=exited, status=0/SUCCESS)
 CPU: 10ms

¿Cuales son los archivos de configuración? ¿Cómo los edito?

Los archivos principales de configuración son dos:

Configuración General: postgresql.conf
Autenticación (Host Based Authentication): pg_hba.conf

Hay también un tercero, menos usado (pg_ident.conf) para mapear usuarios postgres y usuarios del sistema.

Se modifican con un editor de texto.

¿Donde está la documentación oficial para consulta? ¿Qué versión estoy utilizando?

La documentación oficial está en posgresql.org . Para consultar la versión que estoy utilizando:

postgres@equipo:~$ psql
  psql (13.9 (Raspbian 13.9-0+deb11u1))
  Type "help" for help.

postgres=# select version();
  version
  --------------------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 13.9 (Raspbian 13.9-0+deb11u1) on       arm-unknown-linux-gnueabihf, compiled by gcc (Raspbian      10.2.1-6+rpi1) 10.2.1 20210110, 32-bit (1 row)

postgres=# help -- Acceso a ayuda interactiva de consola

¿En qué dirección y puerto está escuchando?

La instalación predeterminada es:

Equipo: en localhost (127.0.0.1)
Puerto (TCP): 5432 (o socket unix)

Se puede realizar una configuración más avanzada para personalizar la dirección, puerto y control de acceso. Siempre en un puerto no privilegiado (> 1024)

¿Cómo me voy a conectar? Vía CLI (consola) o GUI (gráfico)

Además del software servidor, se instala el software CLI predeterminado (app-psql). El servicio tiene que estar operativo, accesible desde el cliente y necesitas la cadena de conexión (host:port/user:passwd) para poder conectarte.

Un ejemplo podría ser:

> psql --host EQUIPO --port PUERTO --user=TU --password=TU_CLAVE TU_BASE_DE_DATOS

o también usando la cadena de conexión (conninfo)

> psql postgresql://TU:TU_CLAVE@EQUIPO:5432/TU_BASE_DE_DATOS

que también se puede usar utilizando pares clave=valor, por
ejemplo

> psql "host=localhost port=5432 dbname=TU_BASE_DE_DATOS
connect_timeout=10"

¿Qué usuarios pueden acceder a ella?

Si no configuras nuevos usuarios, el usuario administrador es postgres. No es root, es decir, se crea un usuario del sistema especial para la gestión del SGBD.

Además, necesitas conectarte desde el usuario postgres del sistema operativo. De forma predeterminada es así de restrictivo.

¿Me puedo conectar desde localhost?

Sí claro. En ese caso se usa el socket unix (un archivo) en vez de la conexión por puerto TCP.

¿Me puedo conectar desde un equipo de la red local?

Sí claro, usando un socket internet. Necesitas configurarlo explícitamente. Y recuerda que, además, la red tiene que estar correctamente configurada y el puerto accesible (sin firewall).

¿Me puedo conectar desde cualquier equipo en Internet?

Sí claro, usando socket internet y el servidor atendiendo en una dirección IP pública. Necesitas configurarlo explícitamente. Y recuerda que, además, la red tiene que estar correctamente configurada y el puerto accesible (sin firewall)

¿En qué archivo tengo mi modelo de datos?

En SQL en el archivo de texto. Aunque lo ideal es realizar una importación y exportación de la base de datos usando las utilidades propias del SGBD.

¿Cual sería el esquema?

Todas las instrucciones del lenguaje DDL (Data Definition Language), es decir todas las instrucciones SQL que definen objetos SQL (CREATE).

Además en postgres se pueden crear esquemas diferentes dentro de la base de datos. No confundas el «concepto genérico» de esquema (como estructura de una base de datos) con el «particular de Postgresql» (puedes agrupar los objetos de una BD en varios esquemas).

¿Cual sería el contenido (si lo tuviera)?

Todas las instrucciones del lenguaje DML (Data Manipulation Language), es decir todas las instrucciones SQL que consultan y modifican los objetos SQL (tipo SELECT, INSERT, UPDATE, DELETE).

¿Cómo hago para crear la base de datos en mi SGBD? ¿Cómo importo ese archivo?

Antes de importar un modelo de datos, salvo que quiera hacerlo sobre esa base de datos en concreto, necesito crear primero una base de datos y conectarme a ella.

  postgres@equipo$ psql
    psql (13.9 (Raspbian 13.9-0+deb11u1))
    Type "help" for help.

  postgres=# create database ejemplo1;
          CREATE DATABASE

  postgres=# \c ejemplo1;
          You are now connected to database "ejemplo1" as user "postgres".

         ejemplo1=# \l
                              List of databases
                              Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
           -----------+----------+----------+-------------+-------------+-----------------------
           ejemplo1  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
           postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
           template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
           template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
           (4 rows)
  ejemplo1=# \d
             List of relations
             Schema |     Name      | Type  |  Owner
             --------+---------------+-------+----------
             public | primera_tabla | table | postgres

  ejemplo1=# \conninfo
           You are connected to database "ejemplo1" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

En este punto **tenemos Postgresql operativo** y **estamos conectados** a una bd (ejemplo1). Ya podemos importar nuestro modelo de datos (o cargar instrucciones SQL desde un archivo).

postgres@EQUIPO:~$ psql
  psql (13.9 (Raspbian 13.9-0+deb11u1))
  Digite «help» para obtener ayuda.

postgres=# \c ejemplo1;
  Ahora está conectado a la base de datos «ejemplo1» con el usuario «postgres».
ejemplo1=# \dt
  No se encontró ninguna relación.
ejemplo1=# \! ls
  ejemplo.sql
ejemplo1=# \include ejemplo.sql
  CREATE TABLE
ejemplo1=# \dt
   Listado de relaciones
   Esquema | Nombre  | Tipo  |  Dueño
   ---------+---------+-------+----------
   public  | ejemplo | tabla | postgres
   (1 fila)

Otra opción, quizá la más habitual, es utilizar el cliente psql directamente, redireccionando la entrada del comando. psql es muy potente y, sobre todo, permitiría usarlo en un programa externo (script).

postgres@tu_equipo:~$ psql ejemplo1 < ejemplo.sql

¿Puedo realizar consultas SQL en el SGBD?

Sí, desde cualquier equipo y usuario autorizado, ya sea vía cliente CLI o GUI. Por ejemplo:

ejemplo1=# select 10 * 4;
  ?column?
  ----------
  40
  (1 fila)

El archivo modelo.sql, ¿serviría para cualquier SGDB?

Depende, hay que importarlo y ver qué tipo de errores o avisos puede dar. Probablemente tengas que ajustar el código SQL para tu SGBD (salvo que sea un archivo SQL fruto de una exportación en ese SGBD).

Lo ideal es usar las utilidades de importación y exportación de esa BD en específico.

¿Cómo hago para exportar la BD?

Se usa el comando pg_dump (app-pgdump). Exportar e Importar en Posgres

~# pg_dump --host=localhost --username=postgres --dbname=ejemplo1 --file=backup.sql

El archivo (backup.sql) es un archivo SQL que te permite que lo puedas importar nuevamente al SGBD o, lo más habitual, usarlo en migraciones del sistema (o otra instancia o en actualizaciones).

Tengo una tabla en un archivo CSV, ¿cómo lo importo en mi BD? ¿Y si son varias?

La importación de varias tablas implica hacerlo a medida e implica un trabajo extra. Habría que:

Crear las tablas
Definir las relaciones
Importar una a una y teniendo cuidado de insertar los valores de forma adecuada

Es una importación no tan sencilla.

Lo más habitual es hacer la importación de una sola tabla de un archivo csv (Importar y exportar csv en postgresql ).

Descargas el archivo csv (o lo exportas desde una hoja de cálculo). Revisa el contenido. Por ejemplo:

postgres@tu_equipo> wget https://raw.githubusercontent.com/tugraz-isds/datasets/master/airports_airlines/Airlines.csv
postgres@tu_equipo> head -2 Airlines.csv
    Name, IATA, ICAO, Country, Active -> Te da una pista de los campos de la tabla
    Air Asia X,ZM,IDX,Indonesia,Y -> Te da una pista de los valore de la tabla, el caracter delimitador y el separador

Te conectas a la BD y creas una tabla que permita la importación

postgres@tu-equipo:~$ psql ejemplo1
ejemplo1=# create table aerolineas (Name varchar(128), IATA varchar(4), ICAO varchar(4), Country varchar(50), Active varchar(2));
    CREATE TABLE
ejemplo1=# \dt
    Listado de relaciones
    Esquema |   Nombre   | Tipo  |  Dueño
    ---------+------------+-------+----------
    public  | aerolineas | tabla | postgres
    public  | ejemplo    | tabla | postgres
    (2 filas)

Haces la importacion. Recuerda comprobar que no es tan fácil que salga todo bien (a la primera)

ejemplo1=# COPY aerolineas(Name, IATA, ICAO, Country, Active)
       FROM '/var/lib/postgresql/Airlines.csv'
       DELIMITER ','
       CSV HEADER;

COPY 511

Para intercambiar datos entre el sistema de archivos y Posgres se usa el comando COPY … FROM (sql-copy ). Permite realizar cierta personalización en el intercambio de datos de archivos a tablas.

Es muy importante comprobar la importación porque puede haber muchos errores (delimitador de campos, campos de encabezado, caracteres especiales, etc). Lo habitual es preparar el archivo csv antes de la importación y luego revisar la tabla por si tienes que depurar algunos valores.

¿Cómo exporto una tabla de mi BD a un archivo CSV?

Para intercambiar datos entre el sistema de archivos y Posgres, se usa el comando COPY … TO (sql-copy ). Permite realizar cierta personalización en el intercambio de datos de archivos a tablas.

ejemplo1=# copy aerolineas(name,icao) to '/var/lib/postgresql/aerolineas_exportado.csv'
                   with
                   (delimiter '|', quote '"',
                   FORCE_QUOTE (name,icao),
                   HEADER true,
                   FORMAT CSV);
   COPY 511
ejemplo1=# quit
postgres@tu-equipo:~$ head -3 aerolineas_exportado.csv
    name|icao
    "Air Asia X"|"IDX"
    "Alghanim"|"KYA"

En este caso personalicé el archivo CSV (tuve que forzar los caracteres delimitadores, sino simplemente usa los caracteres separadores).

¿Cómo personalizo el archivo CSV? (encabezado, separador y delimitador)

Descrito en el apartado anterior.

¿Donde registra la actividad la BD? -> Logs

PostgreSQL tiene un registro avanzado de control de avisos y errores (Error Reporting and Logging ), permite supervisar donde, cuando y qué de una forma muy detallada. Generalmente a través del archivo de configuración principal.

¿Qué niveles de supervisión se pueden configurar?

Por niveles de supervisión nos referimos a cual es el nivel de detalle que necesitamos supervisar. Generalmente es muy alto al principio (el máximo nivel de detalle) y vamos supervisando cada vez menos en producción.

Siempre será un equilibrio entre mucho detalle (y gasto computacional) y ninguno (y más rápido y sencillo).

Postgres tiene varios niveles de supervión (Message Severity )

¿Cuales son las tablas del sistema? ¿Cuántas BD está sirviendo?

Se usan comandos backslash (ves la ayuda con \?) . Ya visto en apartados anteriores. Por ejemplo:

\l muestra las bases de datos del sistema
\dt muestra las tablas de la BD a la que estás conectado
\d TABLA muestra la estructura de la tabla TABLA

¿Qué comandos específicos de administración tiene el SGBD?

Hay varios comandos (programas a nivel de sistema operativo) que funcionan como clientes de Postgres (PostgreSQL Client Applications ). Por ejemplo tres muy utilizados:

Creación de usuarios: app-createuser
Exportación de BD: app-pgdump
Consultar datos de configuración: app-pgconfig

¿Para qué sirve cada uno de ellos?

Son muchos. Mejor consultar la documentación oficial.

¿Quien ha accedido y cuando?

Depende mucho de las necesidades concretas pero una estrategia puede ser:

Activar el parámetro log_connections (boolean)
Consultar la tabla pg_stat_activity (u otras tablas del sistema)

¿Quien es el administrador de la BD?

Generalmente es el usuario postgres (postgres-user). Pero puede añadir y quitar usuarios con diferentes roles.

¿Quien puede acceder a la BD?

Depende de la configuración de usuarios y cómo se haga. La configuración puede ser muy detallada (user-manag)

¿En qué archivos están los objetos de la BD? ¿Cuántos objetos (y de qué tipo) hay?

Es un conjunto de archivos que se guardan en un directorio que depende de la instalación concreta. Típicamente $POSTGRES/data, donde $POSTGRES es el directorio de instalación. Pero puede variar de sistema en sistema. A este directorio se le suele llamar PGDATA y contiene diferentes archivos (storage-file-layout)

¿Cómo puedo hacer una copia de seguridad de la BD?

Usando la utilidad app-pgdump , por ejemplo:

>$ pg_dump ejemplo1 > copia.dump

Administrar un SGBD (Cualquiera)¶

Instalación¶

Creación de una BD¶

Administración¶

Gestión de la Actividad¶

Gestión de Usuarios¶

Gestión de Almacenamiento¶

Soluciones¶

En SQLite¶

En MariaDB¶

En PostgreSQL¶