Table of Contents

PostgreSQL amministrazione

Vedere anche Utilizzo di PostgreSQL.

Il client psql

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?

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

Tablespace

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.

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

Tabelle di sistema (System Catalogs)

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.

pg_user
pg_shadow La password è un hash MD5.
pg_database
pg_tablespace
pg_language Linguaggi creati nel DB, simile a createlang --list.
pg_namespace Schemi.

Schemi (Namespaces)

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:

information_schema Contiene un'insieme di VIEW che descrivono gli oggetti definiti nel database stesso.
pg_catalog Contiene le tabelle di sistema.
pg_toast Contiene le tabelle TOAST, il metodo usato da PostgreSQL per gestire oggetti più grandi di 8 kB.
pg_toast_temp_1 Spazio di lavoro temporaneo per le tabelle TOAST.
public Schema predefinito, per gli oggetti creati dall'utente.

Autenticazione

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
Tipo di connessione
local Unix-domain socket.
host TCP/IP, sia SSL che non-SSL.
hostssl TCP/IP con SSL.
hostnossl TCP/IP senza SSL.

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

Metodo di autenticazione
trust Sempre accettato.
reject Sempre rifiutato.
md5 Password trasmessa come hash MD5, resistente allo sniff. Rimpiazza i metodi crypt e password.
ident Ottiene il nome utente fornito dal sistema operativo e applica la mappa di permessi indicata in pg_ident.conf.
L'uso più comune è tuttavia con la clausola sameuser.

Manutenzione

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

Installazione su CentOS CentOS release 5.2 (Final)

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.

Creare un template GIS

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';

Creare un database geografico dal template

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

Creare una tabella con colonna geometrica

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));

Abilitare PostGIS in un database esistente

# 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.

Restore di una tabella da utente normale

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ì.

Dump da riga di comando Unix

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

Restore selettivo

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 e troubleshooting

EXPLAIN

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 QueryExplain optionsVerbose (off)
  3. Scrivere la query
  4. Menu QueryExplain 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;

Logging

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

Debug attività

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;