Administrar un SGBD (Cualquiera)#

Aunque cada SGBD tiene sus particularidades, la idea de esta práctica es tener una guía de referencia que se pueda aplicar a cualquier SGBD.

En cada una de las fases he puesto preguntas que deberías saber responder para el SGBD concreto con el que estés trabajando.

Puedes usar la base de datos que quieras (Bases de Datos de Ejemplo)

Instalación#

  1. ¿Cómo voy a instalar el software?

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

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

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

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

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

  7. ¿Qué usuarios pueden acceder a ella?

  8. ¿Me puedo conectar desde localhost?

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

  10. ¿Me puedo conectar desde cualquier equipo en Internet?

El resultado final de esta fase es tener instalado un SGBD y poder decirle «hola», o sea «select “Hola”;» (y funciona sin errores)

Creación de una BD#

  1. ¿En qué archivo tengo mi modelo de datos?

  2. ¿Cual sería el esquema?

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

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

  5. ¿Puedo realizar consultas SQL en el SGBD?

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

  7. ¿Cómo hago para exportar la BD?

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

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

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

Administración#

Gestión de la Actividad#

  1. ¿Donde registra la actividad la BD? -> Logs

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

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

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

  5. ¿Para qué sirve cada uno de ellos?

Gestión de Usuarios#

  1. ¿Quien ha accedido y cuando?

  2. ¿Quien es el administrador de la BD?

  3. ¿Quien puede acceder a la BD?

Gestión de Almacenamiento#

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

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

Soluciones#

Supongamos que tienes tu modelo de datos en modelo.sql.

En SQLite#

¿Cómo voy a instalar el software?

Descargándolo de la página principal (el mismo ejecutable)

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

SQLite no es un servicio, es un programa que se ejecuta. Lo gestionas como cualquier programa. Para salir el comando en consola es .quit

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

No tiene. Sí puedes configurar algunos aspectos a través de los «dot commands» pero no tiene archivos de configuración como tal.

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

Al lanzar el programa, te dice qué versión es. El comando .help es muy útil y autoexplicativo.

$> sqlite3
          SQLite version 3.37.0 2021-12-09 01:34:53
          Enter ".help" for usage hints.
          Connected to a transient in-memory database.
          Use ".open FILENAME" to reopen on a persistent
          database.

sqlite> .help
           .auth ON|OFF             Show authorizer callbacks
           .backup ?DB? FILE        Backup DB (default "main") to FILE
           .bail on|off             Stop after hitting an error.  Default OFF
           (...)
¿En qué dirección y puerto está escuchando?

En ninguno. Es una aplicación autocontenida, está en el mismo archivo. Si tienes acceso al archivo, puedes acceder a la BD.

Su uso en remoto dependería del sistema operativo. Es decir, si tienes un sistema de archivos remoto (NFS, Samba, sshfs, etc).

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

El software ofrece la interfaz CLI directamente (autocontenida). El interfaz gráfico simplemente accede al archivo.

¿Qué usuarios pueden acceder a ella?

El que tenga permisos de ejecución sobre el programa y de acceso al sistema de archivos.

¿Me puedo conectar desde localhost?

Sí, desde donde tenga acceso al programa y al archivo.

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

No, salvo el uso en remoto a través del sistema de archivos, ya comentado antes.

¿Me puedo conectar desde cualquier equipo en Internet?

No, salvo el uso en remoto a través del sistema de archivos, ya comentado antes.

¿En qué archivo tengo mi modelo de datos?

En SQL en el archivo de texto modelo.sql

¿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)

¿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?
.read modelo.sql
¿Puedo realizar consultas SQL en el SGBD?

Sí claro, vía cliente CLI o GUI. Por ejemplo:

sqlite> select 12 * 8;
96
El archivo modelo.sql, ¿serviría para cualquier SGDB?

Depende, hay que importarlo y ver qué tipo de errores o avisos puede dar.

¿Cómo hago para exportar la BD?
sqlite> .output modelo.sql
sqlite> .fullschema
sqlite> .quit

Y comprueba el contenido de modelo.sql. También sirve el
comando .schema
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:

  1. Crear las tablas

  2. Definir las relaciones

  3. 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 sóla tabla de un archivo csv.

sqlite> .import --csv /Users/cesareox/Desktop/Data.csv mi_tabla

sqlite> select * from mi_tabla;

Es muy importante comprobar porque la importación puede dar muchos errores (delimitador de campos, campos de encabezado, caracteres especiales, etc). Lo habitual es preparar el archivo csv antes de la importación.

  1. Debes crear una tabla «equivalente» a la que vas a importar (p.ej.)

    https://linuxhint.com/wp-content/uploads/2022/06/image5-34.png
  2. Y luego importas « .import archivo.csv TU_TABLA», es decir la llenas de datos:

    https://linuxhint.com/wp-content/uploads/2022/06/image7-27.png
¿Cómo exporto una tabla de mi BD a un archivo CSV?
sqlite> .mode csv
sqlite> .output ejemplo.csv
sqlite> select * from ejemplo1;
sqlite> .quit

Y comprueba el archivo csv.

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

No se puede (o no encontré cómo hacerlo).

¿Donde registra la actividad la BD? -> Logs

SQLite no guarda logs (salvo estadísticas del sistema).

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

No se tiene archivos de configuración. Sí se pueden usar comandos (dotcommands)

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

No tiene un schema específico de tablas del sistema. Se puede usar el comando .stats, por ejemplo.

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

Ninguno. Simplemente el ejecutable, todo se realiza desde la consola o a través de opciones del comando.

¿Para qué sirve cada uno de ellos?

No aplica, en el caso de SQLite

¿Quien ha accedido y cuando?

No hay control de usuarios.

¿Quien es el administrador de la BD?

El usuario (del SO) que ejecuta el comando sqlite3

¿Quien puede acceder a la BD?

Cualquiera que tenga permisos de ejecución del comando, y de lectura/escritura en la BD

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

Pues por ejemplo en ejemplo.db (o el nombre que le quieras poner a tu BD).

¿Cómo puedo hacer una copia de seguridad de la BD?
sqlite> .output copia.sql
sqlite> .dump
sqlite> .quit

En MariaDB#

¿Cómo voy a instalar el software?

Lo descargas y lo instalas en tu sistema (depende de la arquitectura y sistema operativo). Disponible descarga directa en Windows y Linux. En MAC OS X tienes que instalarlo vía homebrew o usar mysql usando el paquete completo (.dmg)

Por ejemplo, en Debian: apt install mariadb-server

¿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.

Por ejemplo, en Debian usando systemctl: systemctl status mariadb

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

Existe un archivo de configuración de referencia: mariadb.conf. Puede ser un solo archivo o estar dividido en varios (usando la directiva include).

La configuración consiste en una serie de parámetros y valores en una archivo de texto. También se puede configurar en la consola usando comandos e instruccines SQL. Y también a la hora de lanzar el programa principal.

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

La documentación oficial está en mariadb.org . La versión y conexión más habitual (desde el mismo usuario de instalación) sería:

usuario-admin@equipo:~# mariadb --version
       mariadb  Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnueabihf (armv8l) using  EditLine wrapper

usuario-admin@equipo:~# mariadb
       Welcome to the MariaDB monitor.  Commands end with ; or \g.
       Your MariaDB connection id is 33
       Server version: 10.5.15-MariaDB-0+deb11u1 Raspbian 11

       Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

       Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

       MariaDB [(none)]> \h

       General information about MariaDB can be found at    http://mariadb.org

       List of all client commands:
       Note that all text commands must be first on line and end with ';'
       ?         (\?) Synonym for `help'.
       clear     (\c) Clear the current input statement.
       connect   (\r) Reconnect to the server. Optional arguments are db and host.
       (...)
¿En qué dirección y puerto está escuchando?

La instalación predeterminada es en localhost (127.0.0.1) y en el puerto 3306 (TCP).

Pero se puede realizar una configuración más avanzada para personalizar la dirección, puerto y control de acceso.

¿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 (mariadb ). 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:

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

Y para conocer el estado del sistema, desde la consola puedes hacer:

> MariaDB [(none)]> status
--------------
mariadb  Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnueabihf (armv8l) using  EditLine wrapper

Connection id:            33
Current database:
Current user:             usuario@localhost
SSL:                      Not in use
Current pager:            stdout
Using outfile:            ''
Using delimiter:  ;
Server:                   MariaDB
Server version:           10.5.15-MariaDB-0+deb11u1 Raspbian 11
Protocol version: 10
Connection:               Localhost via UNIX socket
Server characterset:      utf8mb4
Db     characterset:      utf8mb4
Client characterset:      utf8
Conn.  characterset:      utf8
UNIX socket:              /run/mysqld/mysqld.sock
Uptime:                   21 min 39 sec
¿Qué usuarios pueden acceder a ella?

Si no configuras nuevos usuarios, el usuario administrador es el que realizó la instalación. Típicamente es root@localhost

¿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. Si usas 127.0.0.1 el sistema supone que quieres usar conexión TCP/IP (socket internet) en vez de socket unix.

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

Sí claro, usando 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. Supongamos modelo.sql.

Pero 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)

¿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?

Necesito crear primero una base de datos y conectarme a ella, antes de importar un modelo de datos (salvo que quiera hacerlo sobre esa base de datos en concreto).

MariaDB [(none)]> create database mi_bd;
       Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use mi_bd;
       Database changed
MariaDB [mi_bd]>

MariaDB [mi_bd]> show databases;
       +--------------------+
       | Database           |
       +--------------------+
       | information_schema |
       | mi_bd              |
       | mysql              |
       | performance_schema |
       +--------------------+
       4 rows in set (0.001 sec)

MariaDB [mi_bd]> show tables;
        Empty set (0.001 sec)

Y ahora ya podría importar el modelo de datos (se añadirá a lo que ya hay, salvo que incluyas alguna instrucción de borrado).

MariaDB [mi_bd]> show tables;
       Empty set (0.001 sec)

MariaDB [mi_bd]> system ls
       ejemplo.sql
MariaDB [mi_bd]> source ejemplo.sql
       Query OK (...)

MariaDB [mi_bd]> show tables;
       +-----------------+
       | Tables_in_mi_bd |
       +-----------------+
       | ejemplo1        |
       +-----------------+
       1 row in set (0.001 sec)
¿Puedo realizar consultas SQL en el SGBD?

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

MariaDB [mi_bd]> select 10 *4 ;
  +-------+
  | 10 *4 |
  +-------+
  |    40 |
  +-------+
    1 row in set (0.000 sec)
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 una utilidad (comando) específico msqldump (Exportar e Importar en MariaDB )

~# mysqldump -u tu_usuario -d la_bd > archivo.dump

La salida de ese comando es la que tienes que guardar en un archivo (archivo.dump). En este caso usas la redirección de la salida estándar (stdout) con el operador de redirección >

El archivo (archivo.dump) 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:

  1. Crear las tablas

  2. Definir las relaciones

  3. 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 sóla tabla de un archivo csv (Importar y exportar csv en mariadb ). Ojo que la tabla tiene que haber sido creada anteriormente (en este caso viajes)

LOAD  DATA LOCAL INFILE 'airtravel.csv'
       INTO TABLE viajes
       FIELDS
       TERMINATED BY ','
       ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       IGNORE 1 ROWS;

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 e ir probando.

Y revisa la tabla por si tienes que depurar algunos valores porque la importación no fue exactamente como pensabas.

¿Cómo exporto una tabla de mi BD a un archivo CSV?
SELECT mes, anho_1 from viajes
       INTO OUTFILE 'airtravel_nuevo.csv'
       CHARACTER SET 'utf8'
       FIELDS
       TERMINATED BY ';'
       ENCLOSED BY '"'
       LINES TERMINATED BY '\n';

El archivo airtravel_nuevo.csv se genera en un directorio predeterminado (por ejemplo /var/lib/msyql/mi_bd en este caso). Tienes que revisar en qué directorio lo guarda.

Tanto la importación como la exportación de archivos CSV se puede personalizar.

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

Descrito en el apartado anterior.

¿Donde registra la actividad la BD? -> Logs

Depende de cómo haya sido la instalación y el sistema operativo. Existen diferentes logs (MariaDB Server Monitoring Logs ) y se puede configurar de muchas formas (Maria DB Log Configuration )

Hay diferentes parámetros de configuración, por ejemplo log_output o general_log_file o sql_error_log_filename

¿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).

Se usa log_warnings con valores entre 0 y 11.

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

Se usa el comando SHOW especificando lo que ha de mostrar. En este caso concreto:

SHOW databases;
SHOW tables -- para la bd en la que estás conecatdo
¿Qué comandos específicos de administración tiene el SGBD?

Hay un montón de clientes y utilidades. El principal el cliente para conectarse (cliente) o las utilidades para importar (mariadb-import ) o exportar la base de datos (mariadb-dump )

¿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. En muchos casos es un requisito legal. MariaDB ha desarrollado un plugin de auditoría que permite el registro de la actividad de los usuarios.

¿Quien es el administrador de la BD?

Generalmente es el usuario que lo instala (root) pero se pueden configurar usuarios a medida.

¿Quien puede acceder a la BD?

Depende de la configuración de usuarios y cómo se haga. La configuración puede ser compleja (User Account Management )

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

Es un conjunto de archivos. Es más, depende mucho del sistema de almacenamiento que definas para cada base de datos.

Puede llegar a ser muy complejo (y completo).

En cualquier caso no puedes hacer una copia de seguridad usando el sistema de archivos (salvo en configuracione de mecanismos de almacenamiento muy específicos).

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

Usando la utilidad mariadb-dump , por ejemplo:

> mariadb-dump mi_bd > copia_seguridad.dump

En PostgreSQL#

¿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:

  1. Configuración General: postgresql.conf

  2. 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:

  1. Equipo: en localhost (127.0.0.1)

  2. 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:

  1. Crear las tablas

  2. Definir las relaciones

  3. 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 ).

  1. 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
    
  2. 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)
    
  3. 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:

  1. \l muestra las bases de datos del sistema

  2. \dt muestra las tablas de la BD a la que estás conectado

  3. \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:

  1. Creación de usuarios: app-createuser

  2. Exportación de BD: app-pgdump

  3. 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:

  1. Activar el parámetro log_connections (boolean)

  2. 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