Prima di cominciare a parlare dell'argomento, su questo link potete trovare tutte le differenze tra MySQL e PostegreSQL.

L'utente postgres (del database, non del sistema ospite) è l'amministratore del database stesso e per default ha tutti i privilegi. Alcune operazioni possono essere fatte con apposite utility da riga di comando, altre invece si devono fare dal monitor interattivo psql (applicazione client). In entrambi i casi il server PostgreSQL deve essere in funzione.

Per connettersi a un database, es. template1

Durante l'installazione di PostgreSQL viene creato il database template1, per connettersi al server è necessario collegarsi ad un database, se non se ne desidera uno specifico si usa appunto template1:

psql template1

utilizza UNIX-Socket, si presenta come l'utente Unix corrente senza password. Quindi se si vogliono tutti i privilegi lo si deve fare non da utente root ma da utente postgres (eseguire primasu - postgres).

psql -h localhost -U username -W template1

si connette al database template1 su localhost (127.0.0.1) tramite TCP/IP. Si presenta come username e chiede una password prima della connessione.

Il controllo di accesso (chi, da dove, come, con quale password, ecc.) viene stabilito dal file /etc/postgresql/pg_hba.conf.

Per uscira dall'interprete si usa \q. Dentro l'interprete si può chiedere aiuto sui comandi dell'interprete stesso (slash commands) con \? oppure si chiede aiuto sui comandi SQL con \h.

Riassunto comandi utili

Vedere i database esistenti:    \l
Connettersi ad un database:     \connect dbname
Vedere le tabelle di un db:     \dt
Vedere utenti presenti:         SELECT * FROM pg_user;
Crea utente con password:       CREATE USER "username" PASSWORD 'secret';
Assegnare un DB ad un utente:   UPDATE pg_database SET datdba=104 WHERE datname='dbname';
Creare un DB con tutto:         CREATE DATABASE dbname OWNER dbuser ENCODING 'UTF8';
Con PG 8.4 ci sono più scelte:  CREATE DATABASE dbname OWNER dbuser LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8'
                                TEMPLATE template0 ENCODING 'UTF8';
Assegna privilegi su tabella:   GRANT ALL ON tablename TO "username";
Cambiare password utente DB:    ALTER USER "username" PASSWORD 'secret';
Rimuovere utente:               DROP USER username;
Uscire dalla sessione:          \q

Creazione di un database e un utente, privilegi

Creazione di un database: i file vengono creati nella directory /home/postgresql/8.1/main/ in un formato comprensibile solo a PostgreSQL. I nomi dei database esistenti sono contenuti nella tabella di sistema pg_database. Per sapere quali database esistono dal prompt di psql si utilizza il comando breve \l oppure un classico SELECT * FROM pg_database;.

Da riga di comando:  $ createdb prova
Da interprete psql:  template1=# CREATE DATABASE prova;

Usare un database piuttosto che un altro:

Da riga di comando:  $ psql prova
Da interprete psql:  template1=# \connect prova

Eliminazione di un database (e quindi dei file relativi):

Da riga di comando:  $ destroydb prova
Da interprete psql:  template1=# DROP DATABASE prova;

Creazione di un utente database, normalmente l'UID in ambiente postgres si fa in modo che sia lo stesso di quello Unix. Gli utenti creati vengono memorizzati nelle tabelle di sistema pg_user e pg_shadow. In questo caso l'utility da riga di comando è migliore perché interattiva.

Da riga di comando:  $ createuser nome
Da interprete psql:  template1=# CREATE USER "nome";
                     template1=# CREATE USER "nome" PASSWORD 'pippo';
                     template1=# ALTER USER "rubrica" PASSWORD 'pluto';

Per vedere gli utenti esistenti si può usare l'istruzione SQL: SELECT * FROM pg_user;. Per eliminare un utente Postgres si usa:

Da interprete psql:  template1=# DROP USER "nome";

Per garantire o rimuovere tutti i privilegi su una tabella ad un particolare utente:

Da interprete psql:  template1=>GRANT ALL ON tabella TO "nome";
                     template1=>REVOKE ALL ON tabella FROM "nome";

Per dare i permessi su tutto un database ad un certo utente, si cambia il suo datdba nella tabella pg_database (vedi sopra). Per vedere i permessi attualmente impostati si usa \z.

I template1 e template0

Quando viene creato un database nuovo viene utilizzato automaticamente come modello il template1. Questo significa che ogni modifica effettuata al template (aggiunta di funzioni, linguaggi, ecc.) la troveremo anche nei nuovi database.

Esiste anche il template0, questo dovrebbe rimanere una copia vergine di un template vuoto per tutta la vita del database: non si dovrebbe apportare mai alcuna modifica a template0.

Backup e restore di un database

Tre esempi di salvataggio da riga di comando:

Salvare tutto un database: struttura dati e dati.
Salvare solo lo schema (struttura dati) del database.
Salvataggio quando non è richiesta autenticazione e si vuole il dump in formato custom (compresso) invece che plain-text SQL.

L'opzione -c comporta che nello script SQL generato saranno incluse le istruzione per cancellare gli oggetti database prima di crearli.

pg_dump -h [hostname] -U [username] -W -c [database] > database.dmp
pg_dump -h [hostname] -U [username] -W -c --schema-only [database] > database-schema.sql
pg_dump -Fc [database] > database.dmp

Per recuperare il contenuto (il database deve essere gia' esistere):

psql -h [hostname] -U [username] -W [database] -e < database.dmp

NOTA: In caso di upgrade del database con cambio di major o minor version (le prime due parti del numero di versione) è necessario fare il dump e il successivo restore dei dati. Se invece cambia solo il numero di revision (il terzo numero), i dati rimangono compatibili e non è necessario fare dump/restore.

Dump degli utenti (ruoli)

Dump global objects that are common to all databases (pg_dump does not save these objects). This currently includes information about database users and groups, and access permissions that apply to databases as a whole:

pg_dumpall --clean --globals-only > globals.dump

This is an example of dump from an 8.4 cluster into a 9.1 cluster:

pg_dumpall --cluster 8.4/main --clean --globals-only > globals-8.4.dump
psql --cluster 9.1/main < globals-8.4.dump

Backup e restore selettivo

A volte non è desiderabile il restore completo di un database, ma solo di alcuni elementi. Ad esempio durante l'upgrade di un database PostGIS, le funzioni PostGIS e la tabellaspatial_ref_sys si preferisce averle dal nuovo template piuttosto che recuperarle dal vecchio dump. Come fare a preparare velocemente la lista delle cose da recuperare?

Anzitutto si effettua il dump nel formato custom archive di PostgreSQL (che tra l'altro è compresso):

pg_dump -Fc mydb > mydb.dump

Poi si genera la lista degli oggetti contenuti nel dump:

pg_restore -l mydb.dump > lista.txt

La lista ottenuta è facilmente filtrabile con grep o comunque la si può editare a mano. Alla fine si effettua il restore utilizzando la lista opportunamente scremata come elenco delle cose da recuperare:

pg_restore --clean -L lista.txt -h 127.0.0.1 -U username -W -d mydb mydb.dump

Il database mydb deve già esistere prima di lanciare il restore, gli oggetti restorati vengono eventualmente cancellari prima di essere ricreati (–clean).

Debug di PostgreSQL

Per vedere le query SQL eseguite sul server (ad esempio da programmi tipo MapServer e QGIS) si può attivare il debug in /etc/postgresql/8.3/main/postgresql.conf con queste opzioni:

log_statement = all
#log_min_messages = debug5
#client_min_messages = debug5

Altra cosa molto interessante è analizzare il query planner su come vengono eseguite le query, soprattutto per scoprire nested loop, ricerche sequenziali e altre cose che rallentano:

EXPLAIN ANALYZE SELECT
    toponimo,
    wpt
    FROM wpt_comuni_view
        WHERE (residenti > 20000);

Quando invece viene utilizzato un CURSOR per recuperare i record si dovrebbe poter fare un EXPLAIN ma senza ANALYZE:

BEGIN;
EXPLAIN DECLARE mycursor BINARY CURSOR FOR
    SELECT
        toponimo,
        wpt
        FROM wpt_comuni_view
            WHERE (
                wpt &&
                setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326)
            );
FETCH ALL IN mycursor;
END;

Modificare la data_directory

Il pacchetto PostgreSQL di Debian Etch usa dome data_directory la /var/lib/postgresql/8.1/main/. Pare che non ci siano controindicazioni a spostare tutto in /home/postgresql/creando un link simbolico:

/var/lib/postgresql -> /home/postgresql/

Attenzione a mantenere ownership e permission della /home/postgresql/. Non sembra necessario modificare il file di configurazione /etc/postgresql/8.1/main/postgresql.conf.

Lavorare con gli schemi

Come vedere gli schemi esistenti e come impostare una search_path tale che trovi le tabelle nei vari schemi.

\CONNECT dbname
\dn
SHOW search_path;
SET search_path TO datirt10k,datirt2k,generici,public;
SHOW search_path;

È possibile impostare una search_path predefinita per un utente:

ALTER USER test SET search_path TO "$user",public,foo;

L'impostazione dell'utente è visibile nella vista pg_user, nel campo useconfig.

Per dare i privilegi di lettura su uno schema e pieni privilegi su un altro:

GRANT USAGE ON SCHEMA datirt10k TO USER;
GRANT SELECT ON datirt10k.ac TO USER;
 
CREATE SCHEMA corso AUTHORIZATION USER;
GRANT ALL PRIVILEGES ON SCHEMA corso TO USER;

La parola chiave AUTHORIZATION fa sì che tutte le GRANT eseguite nello schema siano eseguite a nome dell'utente specificato.

Lavorare con le sequenze

Come impostare il prossimo valore di una sequenza (dopo questo comando nextval() ritornerà 1):

SELECT SETVAL('table_column_seq', 1, FALSE);

Anche questo comando imposta il prossimo valore usato da una sequenza:

ALTER SEQUENCE table_column_seq RESTART WITH 632;

Come impostare il campo di una tabella in modo che acquisisca il nextval di una sequenza:

ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT NEXTVAL('tablename_columnname_seq');

Per consultare il prossimo valore di una sequenza senza alterarlo:

SELECT * FROM tablename_columnname_seq;

Per convertire un campo INT in SERIAL, prima bisogna scoprire il valore massimo utilizzato nel campo, quindi si crea una sequenza che parte da quel valore + 1:

SELECT MAX(columnname) FROM tablename;
CREATE SEQUENCE tablename_columnname_seq START WITH <MAX+1> OWNED BY tablename.columnname;
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT NEXTVAL('tablename_columnname_seq');

Aggiunta di un linguaggio

Per poter creare funzioni in un database bisogna che sia disponibile un linguaggio. Ad esempio prima di creare le funzioni di PostGIS bisogna attivare il linguaggio plpgsql. Oltre al comando SQLCREATE LANGUAGE esiste anche l'utility da riga di comando createlang:

createlang plpgsql dbname

Per sapere quali linguaggi sono stati creati in un database:

createlang --list dbname

Elenco delle funzioni C

Con il comando \df non si vedono tutte le funzioni, ad esempio non si vedono quelle in C e quelle in plpgsql (verificare!). Questo un sistema per vederle:

SELECT proname 
    FROM pg_proc proc JOIN pg_language lang
        ON proc.prolang = lang.oid
    WHERE lang.lanname = 'plpgsql';

Cerca nel blog

Giacomo Calisse Web Site

Utilizzo di PostgreSQL