Emmagatzematge Joan Puigcerver Ibáñez j.puigcerveribanez@edu.gva.es Llicència: CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Emmagatzematge en PostgreSQL PostgreSQL guarda totes les dades dins del directori PGDATA. Tot i així, permet repartir els objectes en altres ubicacions del disc mitjançant tablespaces, per exemple, per aprofitar un disc més ràpid o amb més capacitat. En aquests apunts es veu, en primer lloc, com es gestionen els tablespaces i, després, com s'assignen a les bases de dades i als objectes que contenen. Tablespaces Un tablespace en PostgreSQL és simplement un directori del sistema de fitxers on es guarden els fitxers dels objectes que s'hi assignen. No cal definir fitxers de dades ni mides inicials o màximes: la mida creix segons l'espai disponible al sistema de fitxers. Documentació oficial: Tablespaces – PostgreSQL Creació d'un tablespace (CREATE TABLESPACE) El directori ha d'existir prèviament i pertànyer a l'usuari del sistema operatiu que executa PostgreSQL: mkdir -p /var/lib/postgresql/tablespaces/vendes chown postgres:postgres /var/lib/postgresql/tablespaces/vendes A continuació, es crea el tablespace amb CREATE TABLESPACE: CREATE TABLESPACE vendes LOCATION '/var/lib/postgresql/tablespaces/vendes'; El catàleg del sistema permet consultar els tablespaces existents i la seua ubicació: SELECT spcname, pg_tablespace_location(oid) AS location FROM pg_tablespace; spcname | location ------------+----------------------------------------- pg_default | pg_global | vendes | /var/lib/postgresql/tablespaces/vendes S'observa que els tablespaces pg_default i pg_global no mostren cap ubicació. Són els tablespaces predefinits, que es troben dins de PGDATA. Documentació oficial: CREATE TABLESPACE – PostgreSQL Eliminació d'un tablespace (DROP TABLESPACE) Un tablespace es pot eliminar amb DROP TABLESPACE, sempre que no continga cap objecte. Per tant, abans cal moure o eliminar els objectes que conté i canviar el tablespace per defecte de les bases de dades que l'utilitzen: DROP TABLESPACE vendes; Documentació oficial: DROP TABLESPACE – PostgreSQL Bases de dades Un servidor (clúster) PostgreSQL pot allotjar múltiples bases de dades independents, cadascuna amb el seu propi conjunt d'esquemes, taules i permisos. Cada base de dades és totalment independent: no es poden fer consultes entre bases de dades diferents del mateix clúster sense mecanismes addicionals (com dblink o foreign data wrappers). Cada base de dades té un tablespace per defecte, on es guarden els objectes que s'hi creen sense indicar cap tablespace. Si no s'indica res, és pg_default, és a dir, el directori PGDATA. Creació d'una base de dades (CREATE DATABASE) Una base de dades nova es crea a partir d'una plantilla. En l'exemple següent, es crea la base de dades vendes_db a partir de template0, amb la codificació UTF-8 i amb vendes com a tablespace per defecte: CREATE DATABASE vendes_db TEMPLATE template0 ENCODING 'UTF8' TABLESPACE vendes; Les bases de dades existents es poden consultar amb la metaordre \l de psql: \l O bé mitjançant el catàleg del sistema, que també permet vore el tablespace per defecte de cadascuna: SELECT datname, datdba::regrole AS owner, spcname FROM pg_database d JOIN pg_tablespace t ON d.dattablespace = t.oid; datname | owner | spcname -----------+----------+------------ postgres | postgres | pg_default template1 | postgres | pg_default template0 | postgres | pg_default vendes_db | postgres | vendes S'observa que vendes_db utilitza el tablespace vendes, mentre que la resta de bases de dades continuen en pg_default. Documentació oficial: CREATE DATABASE – PostgreSQL Canvi del tablespace per defecte (ALTER DATABASE) Per canviar el tablespace per defecte d'una base de dades existent, s'utilitza ALTER DATABASE ... SET TABLESPACE. Aquesta ordre mou físicament tots els objectes que estaven en l'antic tablespace per defecte al nou: ALTER DATABASE vendes_db SET TABLESPACE pg_default; Ningú pot estar connectat a la base de dades mentre se'n canvia el tablespace per defecte, ni tan sols la sessió que executa l'ordre. Per això, cal executar ALTER DATABASE connectat a una altra base de dades, com postgres. El paràmetre default_tablespace té prioritat sobre el tablespace per defecte de la base de dades. Aquest paràmetre es pot definir per sessió (SET default_tablespace = vendes;) o per base de dades (ALTER DATABASE vendes_db SET default_tablespace = vendes;). A diferència de ALTER DATABASE ... SET TABLESPACE, només afecta els objectes nous i no en mou cap d'existent. Documentació oficial: ALTER DATABASE – PostgreSQL Taules i índexs Encara que una base de dades tinga un tablespace per defecte, cada taula o índex es pot guardar en un tablespace diferent. Això permet, per exemple, posar els índexs en un disc més ràpid que les dades. Creació en un tablespace concret Per crear una taula o un índex en un tablespace concret, s'utilitza la clàusula TABLESPACE: CREATE TABLE productes ( id SERIAL PRIMARY KEY, nom VARCHAR(50), preu NUMERIC(10,2) ) TABLESPACE vendes; CREATE INDEX idx_productes_nom ON productes (nom) TABLESPACE vendes; INSERT INTO productes (nom, preu) VALUES ('Producte A', 10), ('Producte B', 20); El catàleg del sistema permet consultar a quin tablespace pertany cada objecte: SELECT relname, spcname FROM pg_class c LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid WHERE relname IN ('productes', 'idx_productes_nom'); relname | spcname -------------------+--------- productes | vendes idx_productes_nom | vendes Els objectes guardats en el tablespace per defecte de la base de dades tenen el valor reltablespace a 0. Documentació oficial de PostgreSQL CREATE TABLE CREATE INDEX Canvi de tablespace (ALTER TABLE i ALTER INDEX) Es pot canviar el tablespace d'una taula o d'un índex existent amb SET TABLESPACE, sense necessitat d'aturar la base de dades: ALTER TABLE productes SET TABLESPACE pg_default; ALTER INDEX idx_productes_nom SET TABLESPACE pg_default; Si cal moure totes les taules o tots els índexs d'un tablespace alhora, es pot utilitzar la forma ALL IN TABLESPACE: ALTER TABLE ALL IN TABLESPACE vendes SET TABLESPACE pg_default; ALTER INDEX ALL IN TABLESPACE vendes SET TABLESPACE pg_default; Mentre es mou, la taula queda bloquejada: PostgreSQL copia tots els seus fitxers al nou tablespace i cap altra sessió hi pot accedir fins que acaba. Documentació oficial de PostgreSQL ALTER TABLE ALTER INDEX 📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web. 🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez