Tutorial postgresql: gestione rapida

Una volta installato Postgresql (installazione dipendente dal sistema operativo e dal gestore dei pacchetti) il numero dei comandi che si possono impartire alla nostra base di dati è enorme.
Prima di tutto, se non è stato già fatto durante l’installazione, bisogna creare l’utente che accederà direttamente alla base di dati per evitare che lo faccia root, con ovvi rischi di sicurezza.

Le operazioni che seguono vengono solitamente effettuate durante l’installazione.
E’ bene fare un controllo prima di proseguire.

Creiamo l’utente con
$ adduser postgres

Creiamo la directory data, la directory dove Postgresql memorizzerà le tabelle

$ mkdir /usr/local/pgsql/data
Quindi assegniamo i permessi all’utente appena creato
$ chown postgres /usr/local/pgsql/data

Le operazioni che seguono invece sono tipicamente eseguite dopo l’installazione standard

Loggarsi come utente postgres, che in realtà è il root del database ed inizializziamo la directory “data” appena creata
$ su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

ATTENZIONE: se la fase di installazione ha già creato questa directory, il sistema ritorna un errore quando si cercherà di re-inizializzarla. Se si vogliono fare le cose in modo pulito dovremmo rimuovere il contenuto della /usr/local/pgsql/data e ripetere initdb come nell’ultimo comando.

Per avviare il database all’avvio del sistema consultare la documentazione del proprio sistema operativo o distribuzione GNU/Linux.

Database di test
Una volta installato è saggio creare un database di prova per controllare che tutto sia al posto giusto


$ su - postgres
$ /usr/local/pgsql/bin/createdb test
$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=#


Un modo per controllare la base di dati senza ricorrere alla shell interattiva o al php via web consiste nell’installare pgadmin, un magnifico gestore per postgresql.

Creare utenti
Per creare utenti per il DBMS è necessario lanciare un createuser da postgres in modo che il nuovo utenti potrà creare database ma non creare nuovi utenti.

postgres@localhost:~$ createuser nuovoutente
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
postgres@localhost:~$


Cambiare la password
Se ci colleghiamo al database come utente postgres, possiamo modificare la password di accesso dell’utente proprietario del database:

postgres@localhost:~$ psql nomedatabase
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.
…
nomedatabase=# alter user nomeutente password ‘nuovapassword’;
ALTER USER
nomedatabase=#


In questo modo nuovoutente può essere utilizzato da script accessibili da Internet. Per permettere connessioni TCP/IP bisogna modificare pg_hba.conf e riavviare il PostgreSQL server per attivare le modifiche:


#TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host web webuser 127.0.0.1 255.255.255.255 md5


Creare un db al volo
Per creare una base di dati da linea di comando

postgres@localhost:~$ createdb -h localhost -U utenteweb -W base-dati-web


Con l’opzione -W si forza la richiesta della password
con l’opzione -h si forza l’utilizzo di una connessione TCP/IP.

Connessione
Per connettersi alla base di dati appena creata:

utente@localhost:~$ psql nome-database nome-utente

Dump del database
pg_dump dbname > file.dmp

I dump creati con pg_dump sono consistenti.
Tutti gli aggiornamenti della base di dati durante l’esecuzione di pg_dump non saranno memorizzati nel file.dmp creato.
pg_dump non blocca le altre operazioni in esecuzione sul database, ad eccezione delle operazioni che devono operare in modalità esclusiva, come per esempio il VACUUM FULL per la manutenzione).

Restore del database
psql nomedatabase < infile

nomedatabase non viene creato. Bisogna crearlo prima di fare il restore (per esempio con createdb -T template0 nomedatabase)

Una volta eseguito il restore si consiglia di eseguire un ANALYZE su ogni database in modo che l’optimizer abbia statistiche valide.
Eseguire quindi vacuumdb -a -z to VACUUM ANALYZE all databases; oppure manualmente VACUUM ANALYZE.

E’ possibile anche fare il dump di un database remoto, da una macchina all’altra con
pg_dump -h host1 dbname | psql -h host2 dbname
(esegue il dump del dbname sull’host1 sull’host2).

Per fare il backup dell’intero cluster si usa il comando
pg_dumpall > cluster.dmp

e per il restore dell’intero cluster
psql -f cluster.dmp template1

Ovviamente bisogna essere il superuser del database dovendo fare il dump di tutti i db e di tutti gli utenti ad essi associati.

Usare dump compressi
Per comprimere il dump di un database molto grande
pg_dump dbname | gzip > data.dmp.gz

e il comando duale per ricaricare (dopo aver creato il database)
createdb dbname
gunzip -c data.dmp.gz | psql dbname oppure cat data.dmp.gz | gunzip | psql dbname