====== PostgreSQL amministrazione ======

Vedere anche **[[..:sa:postgresql]]**.

===== Il client psql =====

Opzioni principali da riga di comando

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

A quale DB mi posso collegare?

  * postgres
  * template1
  * template0

Slash command fondamentali:

<code>
\?          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
</code>

===== Tablespace =====

**[[http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html|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).

<code sql>
CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
CREATE TABLE foo(i int) TABLESPACE space1;
SET default_tablespace = space1;
</code>

===== Tabelle di sistema (System Catalogs) =====

**[[http://www.postgresql.org/docs/8.3/static/catalogs.html|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) =====

**[[http://www.postgresql.org/docs/8.3/static/ddl-schemas.html|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.

<code sql>
\connect dbname
\dn
SHOW search_path;
SET search_path TO datirt10k,datirt2k,generici,public;
SHOW search_path;
</code>

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

<code sql>
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;
</code>

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

^ [[http://www.postgresql.org/docs/current/static/information-schema.html|information_schema]]  | Contiene un'insieme di VIEW che descrivono gli oggetti definiti nel database stesso.  |
^ [[http://www.postgresql.org/docs/current/static/catalogs.html|pg_catalog]]  | Contiene le tabelle di sistema.  |
^ [[http://www.postgresql.org/docs/current/static/storage-toast.html|pg_toast]]  | Contiene le tabelle TOAST, il metodo usato da PostgreSQL per gestire oggetti più grandi di 8 kB.  |
^ [[http://www.postgresql.org/docs/current/static/storage-toast.html|pg_toast_temp_1]]  | Spazio di lavoro temporaneo per le tabelle TOAST.  |
^ [[http://www.postgresql.org/docs/current/static/ddl-schemas.html|public]]  | Schema predefinito, per gli oggetti creati dall'utente.  |

===== Autenticazione =====

**[[http://www.postgresql.org/docs/8.3/static/client-authentication.html|Client Authentication]]**

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

<file>
# 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
</file>

^ Tipo di connessione  ^^
^ local      | [[wp>Unix_domain_socket|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 =====

**[[http://www.postgresql.org/docs/8.3/interactive/sql-vacuum.html|VACUUM]]**\\
**[[http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM|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.

<code sql>
VACUUM FULL VERBOSE ANALYZE;
</code>

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

<file>
autovacuum = on
autovacuum_naptime = 1min
track_counts = on
</file>


====== 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:

<code>
# 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';
</code>

===== Creare un database geografico dal template =====

L'operazione richiede i permessi di superutente Postgres:

<code sql>
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
</code>

===== Creare una tabella con colonna geometrica =====

<code sql>
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));
</code>

===== Abilitare PostGIS in un database esistente =====

<code>
# 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;
</code>

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

<code sql>
\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;
</code>

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

===== Restore di una tabella da utente normale =====

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

**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:

<code>
pg_dumpall
    --data-only
    --clean
    --inserts
    --globals-only
    --schema-only
</code>

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

<code>
pg_restore -U login -W -h host 
    --data-only
    -Fc
    filename.dump
</code>


===== 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:

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

====== Performance e troubleshooting ======

===== EXPLAIN =====

**[[http://www.postgresql.org/docs/current/interactive/performance-tips.html|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:

  - Costo stimato di preparazione (output della prima riga)
  - Costo stimato totale
  - Numero stimato di righe emesse
  - Lunghezza media stimata (in byte) delle righe emesse

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

Da **pgAdminIII**:
  - Finestra SQL
  - Menu //Query// => //Explain options// => //Verbose// (off)
  - Scrivere la query
  - Menu //Query// => //Explain// oppure F7


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

<code sql>
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);
</code>

Eseguire il comando

<code>
ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]
</code>

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.gz|comuni_italiani.sql}}**:

<code 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;
</code>

===== Logging =====

**[[http://www.postgresql.org/docs/current/static/runtime-config-logging.html|Error Reporting and Logging]]**

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

<file>
log_statement = 'all'  # none, ddl, mod, all
</file>

Altri parametri utili sono

<file>
client_min_messages = INFO
log_min_messages = DEBUG2
</file>

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:

<code>
/etc/init.d/postgresql-8.3 reload
</code>

===== Debug attività =====

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

<code sql>
SELECT datname, procpid, usename, current_query, waiting, query_start, backend_start
    FROM pg_stat_activity;
</code>