Laboratorio Di Basi Di Dati 03 Il D B M S Postgre S Q L

1

A.A. 2005/2006 Basi di Dati e Laboratorio 1

Corso di Basi di Dati e Laboratorio

PostgreSQLAlfio Ferrara - Stefano Montanelli


Introduzione

• PgSQL– http://www.postgresql.org– DBMS relazionale Open Source (licenza GPL)– Applicazione Client – Server– La distribuzione consiste in un server e una

applicazione client da riga di comando (pgsql)– Disponibili diversi script di amministrazione

(initdb, pgdump, …)


Supporto allo standard

• Postgres supporta ampiamente lo standard SQL99

• Complex queries, Foreign keys, Triggers, Views, Transactional integrity, Multiversion concurrencycontrol

• Estensioni• Data types• Functions• Operators• Aggregate functions• Index methods• Procedural languages

2


Avvio del servizio

• Il servizio è avviato dall’istruzione:– postmaster -D /path/cluster -i

• Il server può gestire più database e essere associato a un cluster specifico

Avvio del servizio

Directory di memorizzazione fisica

Accetta connessioni non locali


Inizializzare un cluster

• Creare una directory (/path/cluster) con su cui l’utente amministratore (postgres) abbia i permessi in lettura/scrittura– initdb -D /path/cluster

• Si possono creare utenti e database senza utilizzare il client psql– createuser test / dropuser test– createdb testdb / dropdb testdb


Stato iniziale

• Inizialmente sono presenti i database template0 e template1

• Essi sono utilizzati come template per la creazione di nuovi database

• È possibile creare un proprio templatecome base per la generazione di nuovi database

3


Uso del client psql

• psql template1[-h host -U utente]– Se omesso viene assunto l’utente corrente e se

omesso il db si assume che il db abbia lo stesso nome dell’utente

• I comandi sono distinti in due tipologie:– Comandi del client: preceduti da \– Comandi SQL

• Help in linea– \?[comando]: help sui comandi del client– \h[comando]: help su sql

• Altri comandi utili– \l lista dei db, \d descrittore, \dt elenco tabelle, etc.


SELECT

• Il comando SELECT oltre che per normali query è utilizzato anche per attivare le funzioni postgres

• Esempi:– SELECT current_date;– SELECT 2 + 4;


Creazione di utenti e db

CREATE USER name [ [ WITH ] option [ ... ] ]

SYSID uid| CREATEDB | NOCREATEDB| CREATEUSER | NOCREATEUSER| IN GROUP groupname [, ...]| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'| VALID UNTIL 'abstime'

CREATE DATABASE name[ [ WITH ] [ OWNER [=] dbowner ]

[ TEMPLATE [=] template ][ ENCODING [=] encoding ][ TABLESPACE [=] tablespace ] ]

4


Creazione di tabelle

• La creazione di tabelle è conforme allo standard, ad eccezione dell’uso di tipi di dato particolari o funzioni

• Un esempio di estensione dello standard ècostituito dalla capacità di tabelle PSQL di ereditare da altre tabelle


Esempio di inheritancePostgresql tutorial (www.postgresql.org)

CREATE TABLE cities (name text,population real,altitude int

);

CREATE TABLE capitals (state char(2)

) INHERITS (cities);


Array

• Postgres supporta anche un tipo di dato array

• Permette l’uso di attributi multivalore e pertanto è da evitare perché non produce schemi normalizzati

5


Funzioni e estensioni

• Postgres implementa un numero molto vasto di funzioni e tipi di dato complessi

• Ricordiamo:– Gestione della concorrenza e transazioni– Estensioni procedurali di SQL– Gestione del partizionamento


Esempio: gestione di sequenze

• La sequenza viene poi manipolata con– Nextval(sequenza)– Currval(sequenza)– Setval(sequenza,valore[,booleano*])– * Determina se il nuovo valore influenza

nextval

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ][ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ][ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]


Informazioni di catalogo e di sistema

• Il catalogo è strutturato secondo tre schemi:– Pg_catalog: database di sistema (utenti, etc.)– Information_schema: contiene i metadati– Public: schema di default dei nuovi db

• Esempi:– SELECT * from pg_catalog.pg_user;– SELECT * from information_schema.tables;

6


Controllo dell’accesso

• Il controllo dell’accesso si effettua per mezzo del file– pg_hba.conf

• Il file viene collocato nel cluster dallo script initdb sulla base del default

• Modificando il file è possibile definire diverse politiche di accesso con regole analoghe a quelle dei firewall


Sintassi

– Local: unix socket– Host: TCP/IP– SSL: uso di connessioni protette– Auth-method: trust | reject | md5 | …– Auth-option: opzioni dei diversi metodi

local database user auth-method [auth-option]host database user CIDR-address auth-method [auth-option]hostssl database user CIDR-address auth-method [auth-option]hostnossl database user CIDR-address auth-method [auth-option]host database user IP-address IP-mask auth-method [auth-option]hostssl database user IP-address IP-mask auth-method [auth-option]hostnossl database user IP-address IP-mask auth-method [auth-option]


Esempi#Tutti gli utenti su tutti i db

# TYPE DATABASE USER CIDR-ADDRESS METHODlocal all all trust

# Lo stesso per TCP/IP

# TYPE DATABASE USER CIDR-ADDRESS METHODhost all all 127.0.0.1/32 trust

#Lo stesso ma specificando gli indirizzi via IP-MASK

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHODhost all all 127.0.0.1 255.255.255.255 trust

7


Esempi

# Consenti ad un utente da host 192.168.12.10 di connettersi al database # "postgres" se l’utente fornisce la password corretta

# TYPE DATABASE USER CIDR-ADDRESS METHODhost postgres all 192.168.12.10/32 md5


Dump e restore

• Copiare e salvare un database– pg_dump [opzioni] nomedb > file– pg_dumpall consente il dump delll’intero

cluster• Le opzioni dello script consentono di determinare

se effettuare il dump del solo schema o anche dei dati e di determinare l’utente

• Restore– Psql nomedb < file

• Occorre creare il database vuoto e eventuali utenti associati al db


Dump

• Il dump può essere diviso su più file– pg_dump dbname | split -b 1m - filename– cat filename* | psql dbname

• Oppure compresso– pg_dump dbname | gzip > filename.gz– cat filename.gz | gunzip | psql dbname

Laboratorio Di Basi Di Dati 03 Il D B M S Postgre S Q L

Technology

Transcript of Laboratorio Di Basi Di Dati 03 Il D B M S Postgre S Q L