Vedere anche Utilizzo di PostgreSQL.

Opzioni principali da riga di comando

--username  (-U)
--password  (-W)
--host      (-h)
--dbname    (-d)
--port      (-p)
--file      (-f)
--command   (-c)
--output    (-o)

A quale DB mi posso collegare?

• postgres
• template1
• template0

Slash command fondamentali:

\?          Help
\l          Elenco database
\connect    Connessione ad un database
\d          Elenco delle (t)able, (v)iew, (s)equence, (i)ndex
\df         Elenco funzioni
\da         Elenco funzioni di aggregazione (molte righe, un risultato)
\db         Elenco dei tablespace
\dn         Elenco degli schemi (namespace)
\dp         Mostra i permessi sugli oggetti
\i <file>   Esegue il contenuto di un file SQL

Managing Tablespaces

È possibile definire delle directory nel filesystem dove gli oggetti del database sono fisicamente memorizzati. Una volta definito un tablespace è possibile farvi riferimento per nome quando si creano degli oggetti nel database.

• pg_default è il tablespace predefinito per template1 e template0 e quindi è il tablespace predefinito per tutti gli altri database creati.
• pg_global è il tablespace usato per gli shared system catalogs (tabelle di sistema condivise).

CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
CREATE TABLE foo(i INT) TABLESPACE space1;
SET default_tablespace = space1;

System Catalogs

Le tabelle di sistema sono il luogo dove un database relazionale memorizza i suoi metadati: informazioni riguardo le tabelle e le colonne, informazioni di amministrazione interna, ecc.

La maggior parte di queste tabelle sono copiate dal template al momento della creazione del database e sono quindi specifiche del DB. Alcune invece sono fisicamente condivise in tutto il cluster.

Schemas

Utenti e gruppi sono definiti a livello di cluster, non ci sono altre informazioni condivise tra database diversi. Questo significa che un client connesso ad un database può accedere solo ai dati di quel database. Se vuole accedere alle tabelle di un altro DB deve effettuare una nuova e diversa connessione.

Non possono esistere due tabelle con lo stesso nome nello stesso DB, a meno che non risiedano in namespace (schemi) differenti. Il concetto è simile a quello di una directory, salvo che gli schemi non pssono essere annidati.

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;

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

GRANT USAGE ON SCHEMA datirt10k TO student;
GRANT SELECT ON datirt10k.ac TO student;
 
CREATE SCHEMA toscana AUTHORIZATION student;
GRANT ALL PRIVILEGES ON SCHEMA toscana TO student;

Esistono alcuni schemi predefiniti (ereditati al momento della creazione dal template1), generalmente solo il public viene utilizzato dall'utente:

Client Authentication

Ecco un tipico esempio di file pg_hba.conf (Debian lo tiene in /etc/postgresql/8.3/main):

# PostgreSQL Client Authentication Configuration File
# ===================================================
# local      DATABASE  USER  METHOD  [OPTION]
# host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
# hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
# hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]

# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# Allow access to database "geodati" without password.
local   geodati     geodati_guest                     trust
host    geodati     geodati_guest 127.0.0.1/32        trust

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         192.168.2.0/24        md5

Ricordarsi di configurare il listen_addresses in postgresql.conf, altrimenti il bind avviene solo su 127.0.0.1.

VACUUM
The auto-vacuum daemon

L'operazione VACUUM recupera lo spazio occupato dai record inutili. Durante le normali operazioni di PostgreSQL i record che vengono cancellati o che diventano obsoleti a causa di una update non vengono fisicamente rimossi dalla tabella, rimangono presenti fino alla esecuzione di un VACUUM. È quindi necessario eseguire VACUUM periodicamente, specialmente su tabelle aggiornate di frequente.

VACUUM FULL VERBOSE ANALYZE;

Il full vacuum è in grado di recuperare più spazio, ma richiede più tempo e crea un lock esclusivo sulla tabella.

In generale conviene far eseguire il VACUUM automaticamente. A partire da PostgreSQL 8.1 esiste l'autovacuum daemon. Nella versione 8.3 la configurazione sta in postgresql.conf, fino alla versione 8.0 esisteva anche il file /etc/postgresql-common/autovacuum.conf, ora divenuto obsoleto. Ecco i parametri fondamentali (attenzione, nelle versioni precedenti i parametri sono diversi):

autovacuum = on
autovacuum_naptime = 1min
track_counts = on

Il pacchetto ufficiale fornito dalla distribuzione è postgresql-8.1.11-1.el5_1.1. Il pacchetto PostGIS viene installato dal repository http://apt.sw.be/redhat/el5/en/mirrors-rpmforge. Dipende dai pacchetti geos e proj, installati sempre da rpmforge.

Con l'installazione standard di CentOS e Debian per ottenere i privilegi di superutente Postgres è necessario essere utente root di Unix ed eseguire su - postgres (switch user postgres) prima di collegarsi al database:

# su - postgres
-bash-3.2$ psql
postgres=# CREATE DATABASE template_gis TEMPLATE template0;
postgres=# \q
-bash-3.2$ createlang plpgsql template_gis
-bash-3.2$ psql -f /usr/share/doc/postgis-1.3.2/lwpostgis.sql -d template_gis
-bash-3.2$ psql -f /usr/share/doc/postgis-1.3.2/spatial_ref_sys.sql -d template_gis
-bash-3.2$ psql
template_gis=# UPDATE pg_database SET datistemplate='t' WHERE datname = 'template_gis';

L'operazione richiede i permessi di superutente Postgres:

CREATE DATABASE prova_geom OWNER niccolo TEMPLATE template_gis;
\CONNECT prova_geom
GRANT ALL ON geometry_columns TO niccolo;
GRANT SELECT ON spatial_ref_sys TO niccolo;
\dp

CREATE TABLE coordinate (id  SERIAL);
SELECT AddGeometryColumn('coordinate','the_geom',4326,'POINT',2);
INSERT INTO coordinate (the_geom)
    VALUES (GeometryFromText('POINT(11.097 43.858)',4326));

# su - postgres
-bash-3.2$ createlang plpgsql niccolo
-bash-3.2$ psql -f /usr/share/doc/postgis-1.3.2/lwpostgis.sql -d niccolo
-bash-3.2$ psql -f /usr/share/doc/postgis-1.3.2/spatial_ref_sys.sql -d niccolo
-bash-3.2$ psql
postgres=# \connect niccolo
niccolo=# GRANT ALL ON geometry_columns TO niccolo;
niccolo=# GRANT SELECT ON spatial_ref_sys TO niccolo;

La stessa operazione fatta tutta da SQL (in genere l'aggiunta di un linguaggio in un database richiede i privilegi di superutente Postgres):

\CONNECT niccolo
CREATE LANGUAGE plpgsql;
\i /usr/share/doc/postgis-1.3.2/lwpostgis.sql
\i /usr/share/doc/postgis-1.3.2/spatial_ref_sys.sql
GRANT ALL ON geometry_columns TO niccolo;
GRANT SELECT ON spatial_ref_sys TO niccolo;

La stessa operazione può essere fatta da interfaccia grafica PgAdminIII o phpPgAdmin.

psql -U niccolo -W -h 127.0.0.1 -d niccolo
niccolo=> \i coord_loc_2001.dump

Nota: alcuni comandi contenuti nel dump falliscono (GRANT, set OWNER) perché richiedono privilegi di superutente, a volte va bene così.

pg_dumpall, pg_dump e pg_restore sono i comandi da utilizzare, ecco le opzioni fondamentali:

pg_dumpall
    --data-only
    --clean
    --inserts
    --globals-only
    --schema-only

pg_dump -U login -W -h host 
    --data-only
    --clean
    --inserts
    -Fc
    --no-owner
    --schema-only
    --no-privileges
    --table=table
    dbname

pg_restore -U login -W -h host 
    --data-only
    -Fc
    filename.dump

Si estrae dal dump (deve essere in fromato custom -Fc) la lista di tutti gli oggetti contenuti, quindi si edita la lista (manualmente o con tool tipo grep) e si esegue il pg_restore sulla lista ridotta:

pg_restore --list filename.dump > lista_oggetti_database
# Modificare la lista
pg_restore --use-list=lista_oggetti_database -d database filename.dump

Performance Tips

Con EXPLAIN l'istruzione non viene effettivamente eseguita, viene mostrato solo la previsione del planner. Con EXPLAIN ANALYZE invece l'istruzione viene eseguita e vengono mostrate sia le previsioni del planner che le performance reali.

L'EXPLAIN mostra:

1. Costo stimato di preparazione (output della prima riga)
2. Costo stimato totale
3. Numero stimato di righe emesse
4. Lunghezza media stimata (in byte) delle righe emesse

L'unità di costo è la “lettura di pagina da disco”.

Da pgAdminIII:

1. Finestra SQL
2. Menu Query ⇒ Explain options ⇒ Verbose (off)
3. Scrivere la query
4. Menu Query ⇒ Explain oppure F7

È possibile influenzare il planner scrivendo in modo diverso la query, ad esempio rendendo esplicito l'ordine del JOIN:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Eseguire il comando

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

quando si fanno sostanziali modifiche al contenuto di una tabella, anche se l'Auto-Vacuum Daemon dovrebbe provvedere a tenere aggiornate le statistiche (tabella pg_statistic).

Esempi da provare su questi dati comuni_italiani.sql:

EXPLAIN ANALYZE SELECT * FROM comuni_italiani WHERE toponimo = 'Firenze';
EXPLAIN ANALYZE SELECT * FROM regioni_italiane WHERE toponimo = 'Toscana';
 
EXPLAIN ANALYZE SELECT * FROM comuni_italiani ORDER BY toponimo;
EXPLAIN ANALYZE SELECT * FROM regioni_italiane ORDER BY toponimo;
 
CREATE INDEX comuni_italiani_toponimo_idx ON comuni_italiani (toponimo);
CREATE INDEX regioni_italiane_toponimo_idx ON regioni_italiane (toponimo);
 
SELECT
  c.toponimo AS comune,
  p.toponimo AS prov,
  r.toponimo AS regione
    FROM
      regioni_italiane r,
      province_italiane p,
      comuni_italiani c
    WHERE c.idprovincia = p.id_istat
    AND p.idregione = r.id_istat;

Error Reporting and Logging

Per loggare le query eseguite: nel file di configurazione /etc/postgresql/8.3/main/postgresql.conf aggiungere:

log_statement = 'all'  # none, ddl, mod, all

Altri parametri utili sono

client_min_messages = INFO
log_min_messages = DEBUG2

che determinano il livello di messaggio inviati al client oppure segnati nel file di log. Il livello va da DEBUG5 (massima quantità di dettaglio) a PANIC (solo errori fatali).

Dopo aver cambiato la configurazione è sempre necessario eseguire il reload di PostgreSQL:

/etc/init.d/postgresql-8.3 reload

Per capire in quale attività è impegnato Postgres è utile la seguente query:

SELECT datname, procpid, usename, current_query, waiting, query_start, backend_start
    FROM pg_stat_activity;