PR0102 — Pràctica: PostgreSQL amb Docker

Informació de la pràctica

Camp	Detall
Mòdul	M0377 — Administració de SGBD
Resultat d'aprenentatge	RA1 — Instal·lació i configuració d'un SGBD
Durada estimada	3 hores
Modalitat	Individual
Entrega	Document PDF amb captures de pantalla i respostes

---

Objectius

En finalitzar aquesta pràctica, l'alumne serà capaç de:

• Descarregar i executar la imatge oficial de PostgreSQL 17 amb Docker.
• Connectar-se al servidor amb psql des del contenidor i des del sistema amfitrió.
• Crear una base de dades i un esquema dedicats amb la configuració correcta.
• Modificar el fitxer pg_hba.conf per habilitar l'accés remot.
• Crear taules relacionades i carregar dades de prova.
• Realitzar una exportació amb pg_dump i restaurar-la amb pg_restore.
• Consultar i interpretar els logs del servidor PostgreSQL.

---

Requisits previs

• Docker Desktop (Windows/macOS) o Docker Engine (Linux) instal·lat i funcionant.
• Mínim 2 GB de RAM lliure per al contenidor.
• Connexió a Internet per descarregar la imatge (aprox. 400 MB).
• Client psql instal·lat al sistema amfitrió (opcional, però recomanat).

Substitució del nom d'alumne

Al llarg d'aquesta pràctica, substituïu [nom-alumne] pel vostre nom i cognoms en format nom_cognom (sense espais, sense accents, tot en minúscules). Per exemple: maria_garcia.

---

Pas 1 — Descarregar la imatge de PostgreSQL 17

# Descarregar la imatge oficial de PostgreSQL 17
docker pull postgres:17

# Verificar que la imatge s'ha descarregat
docker images | grep postgres

# Inspeccionar la imatge
docker inspect postgres:17 | grep -A5 '"Env"'

Anoteu la mida de la imatge i les variables d'entorn disponibles (POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB, PGDATA).

---

Pas 2 — Executar el contenidor PostgreSQL

# Crear i iniciar el contenidor amb el vostre nom d'alumne
docker run -d \
  --name postgres-[nom-alumne] \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=PgRootPass_2024! \
  -e POSTGRES_DB=sgbd_[nom_alumne] \
  -p 5432:5432 \
  -v pg_[nom_alumne]_data:/var/lib/postgresql/data \
  postgres:17

# Exemple real:
# docker run -d --name postgres-maria-garcia ...

Verificació:

# Comprovar que el contenidor és en marxa
docker ps

# Seguir els logs fins que el servidor estigui llest
docker logs -f postgres-[nom-alumne]

# El servidor està llest quan apareix:
# LOG:  database system is ready to accept connections
# Premeu ^C per sortir del mode follow

---

Pas 3 — Connectar amb psql

Des del contenidorDes del sistema amfitrió

# Connexió com a superusuari postgres
docker exec -it postgres-[nom-alumne] psql -U postgres

# Connexió directament a la base de dades de l'alumne
docker exec -it postgres-[nom-alumne] psql \
    -U postgres \
    -d sgbd_[nom_alumne]

# Requereix el client psql instal·lat
# Ubuntu: sudo apt install -y postgresql-client-16

psql -h localhost -p 5432 -U postgres -d sgbd_[nom_alumne]
# Introduïu la contrasenya: PgRootPass_2024!

Un cop connectat, verifiqueu la connexió:

-- Informació de la connexió
\conninfo

-- Versió del servidor
SELECT version();

-- Mostrar totes les bases de dades
\l

-- Mostrar l'hora del servidor
SELECT now();

-- Mostrar la base de dades actual
SELECT current_database(), current_user;

Captureu la sortida d'aquestes comandes.

---

Pas 4 — Crear la base de dades i l'esquema de l'alumne

Treballant com a superusuari postgres:

-- Verificar que la base de dades existeix (creada automàticament per Docker)
\l

-- Crear un rol dedicat per a l'alumne (si no existeix)
CREATE ROLE alumne_[nom_alumne] WITH
    LOGIN
    PASSWORD 'AlumnePass_2024!'
    NOSUPERUSER
    CREATEDB
    NOCREATEROLE
    CONNECTION LIMIT 10;

-- Connectar a la base de dades de l'alumne
\c sgbd_[nom_alumne]

-- Crear un esquema de treball personal
CREATE SCHEMA IF NOT EXISTS [nom_alumne]
    AUTHORIZATION alumne_[nom_alumne];

-- Configurar el search_path de la base de dades
ALTER DATABASE sgbd_[nom_alumne]
    SET search_path TO [nom_alumne], public;

-- Donar privilegis sobre el nou esquema
GRANT ALL ON SCHEMA [nom_alumne] TO alumne_[nom_alumne];
GRANT USAGE ON SCHEMA public TO alumne_[nom_alumne];

-- Verificar la creació
\dn
\du alumne_[nom_alumne]

# Reconnecteu com a l'usuari alumne per verificar l'accés
docker exec -it postgres-[nom-alumne] psql \
    -U alumne_[nom_alumne] \
    -d sgbd_[nom_alumne]

-- Verificar el search_path actiu
SHOW search_path;
SELECT current_schema();

---

Pas 5 — Configurar pg_hba.conf per a accés remot

El fitxer pg_hba.conf controla quines connexions estan permeses. Per defecte, PostgreSQL a Docker ja permet connexions via TCP/IP des de totes les adreces usant contrasenya. En aquest pas aprendreu a modificar-lo explícitament.

# Trobar la ubicació del fitxer de configuració
docker exec postgres-[nom-alumne] psql -U postgres \
    -c "SHOW hba_file;"

# Copiar el fitxer al sistema amfitrió per editar-lo
docker cp postgres-[nom-alumne]:/var/lib/postgresql/data/pg_hba.conf \
    /tmp/pg_hba.conf

# Fer una còpia de seguretat
cp /tmp/pg_hba.conf /tmp/pg_hba.conf.original

Editeu /tmp/pg_hba.conf afegint les línies següents al final (per a accés remot des de la xarxa Docker i la xarxa local):

# Accés remot per a l'alumne des de la xarxa Docker (172.17.0.0/16)
host    sgbd_[nom_alumne]    alumne_[nom_alumne]    172.17.0.0/16    scram-sha-256

# Accés remot des de la xarxa local (adaptar a la vostra subxarxa)
host    sgbd_[nom_alumne]    alumne_[nom_alumne]    192.168.0.0/24   scram-sha-256

# Accés des del propi contenidor (loopback)
host    sgbd_[nom_alumne]    alumne_[nom_alumne]    127.0.0.1/32     scram-sha-256

# Copiar el fitxer modificat de tornada al contenidor
docker cp /tmp/pg_hba.conf \
    postgres-[nom-alumne]:/var/lib/postgresql/data/pg_hba.conf

# Recarregar la configuració sense reiniciar el servidor
docker exec postgres-[nom-alumne] psql -U postgres \
    -c "SELECT pg_reload_conf();"

# Verificar que la recàrrega ha anat bé (sense errors als logs)
docker logs postgres-[nom-alumne] --tail 20

# Comprovar la configuració activa
docker exec postgres-[nom-alumne] psql -U postgres \
    -c "SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules;"

Seguretat del pg_hba.conf

Mai poseu trust com a mètode per a connexions de xarxa. Sempre useu scram-sha-256 (PostgreSQL 10+) o com a mínim md5. El mètode trust permet connexions sense contrasenya a qualsevol host que coincideixi amb la regla.

---

Pas 6 — Crear taules i carregar dades

Connecteu-vos com a alumne_[nom_alumne] i creeu un esquema per a un sistema de gestió de cursos:

-- Connexió: docker exec -it postgres-[nom-alumne] psql -U alumne_[nom_alumne] -d sgbd_[nom_alumne]

-- Assegurar que estem a l'esquema de l'alumne
SET search_path TO [nom_alumne], public;

-- Tipus enumerats
CREATE TYPE estat_matricula AS ENUM ('pendent', 'activa', 'finalitzada', 'baixa');
CREATE TYPE nivell_curs AS ENUM ('bàsic', 'intermedi', 'avançat');

-- Taula de departaments
CREATE TABLE departaments (
    id          SERIAL PRIMARY KEY,
    codi        VARCHAR(10)  NOT NULL UNIQUE,
    nom         VARCHAR(100) NOT NULL,
    descripcio  TEXT,
    creat_a     TIMESTAMPTZ  DEFAULT now()
);

-- Taula de professors
CREATE TABLE professors (
    id              SERIAL PRIMARY KEY,
    nif             CHAR(9)      NOT NULL UNIQUE,
    nom             VARCHAR(80)  NOT NULL,
    cognoms         VARCHAR(150) NOT NULL,
    email           VARCHAR(200) NOT NULL UNIQUE,
    departament_id  INT          REFERENCES departaments(id) ON DELETE SET NULL,
    data_alta       DATE         DEFAULT CURRENT_DATE,
    actiu           BOOLEAN      DEFAULT TRUE
);

-- Índex per a cerques freqüents
CREATE INDEX idx_professors_departament ON professors(departament_id);
CREATE INDEX idx_professors_cognoms ON professors USING btree (cognoms);

-- Taula de cursos
CREATE TABLE cursos (
    id              SERIAL PRIMARY KEY,
    codi            VARCHAR(20)  NOT NULL UNIQUE,
    titol           VARCHAR(250) NOT NULL,
    descripcio      TEXT,
    nivell          nivell_curs  NOT NULL DEFAULT 'intermedi',
    hores           SMALLINT     NOT NULL CHECK (hores > 0 AND hores <= 2000),
    credits_ects    NUMERIC(4,1),
    professor_id    INT          REFERENCES professors(id) ON DELETE SET NULL,
    actiu           BOOLEAN      DEFAULT TRUE,
    creat_a         TIMESTAMPTZ  DEFAULT now(),
    actualitzat_a   TIMESTAMPTZ  DEFAULT now()
);

-- Taula d'alumnes
CREATE TABLE alumnes (
    id              SERIAL PRIMARY KEY,
    nif             CHAR(9)      NOT NULL UNIQUE,
    nom             VARCHAR(80)  NOT NULL,
    cognoms         VARCHAR(150) NOT NULL,
    email           VARCHAR(200) NOT NULL UNIQUE,
    data_naixement  DATE,
    poblacio        VARCHAR(100),
    telefon         VARCHAR(15),
    creat_a         TIMESTAMPTZ  DEFAULT now()
);

CREATE INDEX idx_alumnes_cognoms ON alumnes(cognoms);
CREATE INDEX idx_alumnes_email   ON alumnes(email);

-- Taula de matrícules (relació N:M entre alumnes i cursos)
CREATE TABLE matricules (
    id              SERIAL PRIMARY KEY,
    alumne_id       INT          NOT NULL REFERENCES alumnes(id)  ON DELETE CASCADE,
    curs_id         INT          NOT NULL REFERENCES cursos(id)   ON DELETE RESTRICT,
    estat           estat_matricula NOT NULL DEFAULT 'pendent',
    data_matricula  DATE         DEFAULT CURRENT_DATE,
    data_inici      DATE,
    data_fi_prevista DATE,
    nota_final      NUMERIC(4,2) CHECK (nota_final >= 0 AND nota_final <= 10),
    UNIQUE (alumne_id, curs_id)
);

CREATE INDEX idx_matricules_alumne ON matricules(alumne_id);
CREATE INDEX idx_matricules_curs   ON matricules(curs_id);
CREATE INDEX idx_matricules_estat  ON matricules(estat);

-- Comentaris descriptius als objectes
COMMENT ON TABLE departaments IS 'Departaments acadèmics del centre';
COMMENT ON TABLE cursos IS 'Oferta de cursos de formació professional';
COMMENT ON TABLE matricules IS 'Matrícules d alumnes als cursos';
COMMENT ON COLUMN matricules.nota_final IS 'Nota de 0 a 10, dos decimals';

Inseriu dades de prova:

-- Departaments
INSERT INTO departaments (codi, nom, descripcio) VALUES
    ('INF', 'Informàtica', 'Departament de sistemes informàtics i xarxes'),
    ('ADM', 'Administració', 'Departament de gestió empresarial'),
    ('SAN', 'Sanitat', 'Departament de cicles sanitaris'),
    ('COM', 'Comerç', 'Departament de màrqueting i comerç');

-- Professors
INSERT INTO professors (nif, nom, cognoms, email, departament_id) VALUES
    ('12345678A', 'Francesc', 'Barragán López', 'fbarragan@sapalomera.cat', 1),
    ('23456789B', 'Marta', 'Puig Rovira', 'mpuig@sapalomera.cat', 1),
    ('34567890C', 'Jordi', 'Mas Ferrer', 'jmas@sapalomera.cat', 2),
    ('45678901D', 'Anna', 'Vidal Serra', 'avidal@sapalomera.cat', 1);

-- Cursos
INSERT INTO cursos (codi, titol, nivell, hores, credits_ects, professor_id) VALUES
    ('M0377', 'Administració de SGBD', 'avançat', 165, 6.6, 1),
    ('M0375', 'Serveis de Xarxa', 'avançat', 132, 5.3, 2),
    ('M0373', 'Implantació de Sistemes Operatius', 'intermedi', 198, 7.9, 4),
    ('M0376', 'Administració de Sistemes Operatius', 'avançat', 132, 5.3, 2),
    ('M0487', 'Programació', 'intermedi', 198, 7.9, 3);

-- Alumnes (dades fictícies)
INSERT INTO alumnes (nif, nom, cognoms, email, data_naixement, poblacio) VALUES
    ('11111111A', 'Pau', 'Costa Pla', 'pau.costa@estudiant.cat', '2005-03-15', 'Blanes'),
    ('22222222B', 'Laia', 'Riu Bosch', 'laia.riu@estudiant.cat', '2004-11-22', 'Lloret de Mar'),
    ('33333333C', 'Marc', 'Font Nadal', 'marc.font@estudiant.cat', '2005-06-08', 'Tossa de Mar'),
    ('44444444D', 'Neus', 'Gómez Soto', 'neus.gomez@estudiant.cat', '2004-09-30', 'Blanes'),
    ('55555555E', 'Pol', 'Sala Mir', 'pol.sala@estudiant.cat', '2005-01-17', 'Malgrat de Mar'),
    ('66666666F', 'Júlia', 'Camps Tort', 'julia.camps@estudiant.cat', '2004-07-04', 'Calella');

-- Matrícules
INSERT INTO matricules (alumne_id, curs_id, estat, data_inici, data_fi_prevista, nota_final) VALUES
    (1, 1, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (1, 2, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (2, 1, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (2, 3, 'finalitzada','2024-09-16','2025-06-16', 7.50),
    (3, 1, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (3, 4, 'finalitzada','2024-09-16','2025-06-16', 8.25),
    (4, 1, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (4, 5, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (5, 2, 'pendent',   NULL, NULL, NULL),
    (6, 1, 'activa',    '2025-09-15', '2026-06-15', NULL),
    (6, 3, 'activa',    '2025-09-15', '2026-06-15', NULL);

-- Verificació de les dades
SELECT
    a.nom || ' ' || a.cognoms AS alumne,
    c.codi,
    c.titol,
    m.estat,
    m.nota_final
FROM matricules m
JOIN alumnes a ON m.alumne_id = a.id
JOIN cursos c  ON m.curs_id   = c.id
ORDER BY a.cognoms, c.codi;

-- Estadístiques
SELECT
    c.titol,
    COUNT(m.id) AS total_matricules,
    COUNT(CASE WHEN m.estat = 'activa' THEN 1 END) AS actives,
    ROUND(AVG(m.nota_final), 2) AS nota_mitja
FROM cursos c
LEFT JOIN matricules m ON c.id = m.curs_id
GROUP BY c.id, c.titol
ORDER BY total_matricules DESC;

---

Pas 7 — Exportació amb pg_dump i restauració amb pg_restore

Exportació en format custom (recomanat per a restauració)

# Format custom (binari, compressió integrada, restauració selectiva)
docker exec postgres-[nom-alumne] pg_dump \
    -U postgres \
    -d sgbd_[nom_alumne] \
    -n [nom_alumne] \
    --format=custom \
    --compress=9 \
    --verbose \
    -f /var/lib/postgresql/data/sgbd_[nom_alumne].dump

# Copiar el dump al sistema amfitrió
docker cp postgres-[nom-alumne]:/var/lib/postgresql/data/sgbd_[nom_alumne].dump \
    /tmp/sgbd_[nom_alumne].dump

# Verificar la mida
ls -lh /tmp/sgbd_[nom_alumne].dump

# Inspeccionar el contingut del dump
docker exec postgres-[nom-alumne] pg_restore \
    -l /var/lib/postgresql/data/sgbd_[nom_alumne].dump

Exportació en format SQL pla

# Format SQL pla (llegible per humans, portàtil)
docker exec postgres-[nom-alumne] pg_dump \
    -U postgres \
    -d sgbd_[nom_alumne] \
    -n [nom_alumne] \
    --format=plain \
    --no-owner \
    --no-acl \
    -f /var/lib/postgresql/data/sgbd_[nom_alumne].sql

# Verificar
docker exec postgres-[nom-alumne] wc -l \
    /var/lib/postgresql/data/sgbd_[nom_alumne].sql

Restauració en una nova base de dades

-- Crear una nova base de dades per a la restauració
-- docker exec -it postgres-[nom-alumne] psql -U postgres

CREATE DATABASE sgbd_[nom_alumne]_restore
    OWNER postgres
    ENCODING 'UTF8'
    TEMPLATE template0;

\q

# Restaurar des del format custom
docker exec postgres-[nom-alumne] pg_restore \
    -U postgres \
    -d sgbd_[nom_alumne]_restore \
    --verbose \
    --schema=[nom_alumne] \
    /var/lib/postgresql/data/sgbd_[nom_alumne].dump

# Verificar la restauració
docker exec postgres-[nom-alumne] psql \
    -U postgres \
    -d sgbd_[nom_alumne]_restore \
    -c "SET search_path TO [nom_alumne]; \dt"

docker exec postgres-[nom-alumne] psql \
    -U postgres \
    -d sgbd_[nom_alumne]_restore \
    -c "SET search_path TO [nom_alumne]; SELECT COUNT(*) FROM alumnes;"

Diferències entre formats de pg_dump

Format	Extensió	Compressió	Restauració selectiva	Ús recomanat
plain	.sql	No (o gzip extern)	No	Portabilitat, inspeccionar
custom	.dump	Sí (integrada)	Sí	Recomanat per a backups
directory	directori	Sí	Sí	Backups paral·lelitzats
tar	.tar	No	Sí	Compatibilitat

---

Pas 8 — Consultar i interpretar els logs

# Opció 1: Logs del contenidor Docker
docker logs postgres-[nom-alumne] --tail 100
docker logs postgres-[nom-alumne] 2>&1 | grep -i "error\|fatal\|warning"

# Opció 2: Logs dins del contenidor
docker exec postgres-[nom-alumne] ls -la /var/lib/postgresql/data/log/
docker exec postgres-[nom-alumne] tail -50 \
    /var/lib/postgresql/data/log/$(ls /var/lib/postgresql/data/log/ | tail -1)

-- Consultar logs des de psql (PostgreSQL 10+)
-- docker exec -it postgres-[nom-alumne] psql -U postgres

-- Veure l'arxiu de log actiu
SHOW log_directory;
SHOW log_filename;
SHOW logging_collector;

-- Monitoratge de connexions actives
SELECT pid, usename, application_name, client_addr,
       state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Activitat de la base de dades
SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit,
       ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'sgbd_[nom_alumne]';

-- Estadístiques de les taules
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- Generar intencionalment un error per veure'l als logs
-- (executeu des del psql i comproveu als logs del contenidor)
SELECT 1/0;  -- Genera ERROR: division by zero
SELECT * FROM taula_inexistent;  -- Genera ERROR: relation does not exist

Activació del log de consultes lentes

Per depurar problemes de rendiment, activeu el log de consultes lentes al postgresql.conf:

log_min_duration_statement = 500  # Registra consultes que triguen > 500ms
log_statement = 'ddl'             # Registra sempre DDL (CREATE, ALTER, DROP)

Recarregueu amb SELECT pg_reload_conf(); i repetiu algunes consultes per veure-les als logs.

---

Verificació final

-- Resum complet de l'esquema creat
-- docker exec -it postgres-[nom-alumne] psql -U postgres -d sgbd_[nom_alumne]

SET search_path TO [nom_alumne], public;

-- Taules i files
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS mida_total,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS mida_dades,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS mida_indexos
FROM pg_tables
WHERE schemaname = '[nom_alumne]'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Índexos creats
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE schemaname = '[nom_alumne]'
ORDER BY tablename, indexname;

-- Constraints (PK, FK, UNIQUE, CHECK)
SELECT conname, contype, conrelid::regclass AS taula
FROM pg_constraint
WHERE connamespace = '[nom_alumne]'::regnamespace
ORDER BY conrelid::regclass::text, contype;

---

Preguntes de reflexió

Responeu les preguntes següents al document d'entrega. Les respostes han de ser raonades (mínim 3-5 línies per pregunta).

1. Quina diferència hi ha entre el format custom i el format plain de pg_dump? En quines situacions usaríeu cadascun?
2. Per quina raó hem afegit entrades al pg_hba.conf per permetre l'accés des de la xarxa Docker (172.17.0.0/16)? Sense aquesta entrada, podria un altre contenidor de la mateixa xarxa Docker connectar-se al vostre PostgreSQL?
3. Quin és el propòsit del tipus de dades ENUM a PostgreSQL? Quins avantatges té respecte a usar una columna VARCHAR amb una restricció CHECK?
4. Observeu la consulta de cache_hit_ratio a pg_stat_database. Quina relació té amb el paràmetre shared_buffers? Quin valor de cache_hit_ratio es considera acceptable en producció?
5. Heu creat l'índex idx_professors_cognoms usant btree. PostgreSQL suporta altres tipus d'índex (hash, gist, gin, brin). Investigueu per a quin tipus de dades o consultes és més adequat l'índex GIN.
6. Quin efecte té la restricció ON DELETE CASCADE a la taula matricules? Proveu-la: elimineu un alumne i comproveu que les seves matrícules s'eliminen automàticament. Captureu la prova amb un SELECT abans i després de l'DELETE.