Calendario dell'Avvento ITPUG: 24 Dicembre

Non è molto originale, ma ad ogni modo Auguri a tutti!

% psql -c "select initcap( chr( 65 ) || chr( 117 ) || chr( 103 ) || chr( 117 ) || chr( 114 ) || chr( 105 ) || chr( 33 ) );"

ITPUG's Advent Calendar

This December, borrowing a concept commonly used in other communities, I decide to challenge the ITPUG members in writing an Advent Calendar.

The idea is quite simple: write a post (related to PostgreSQL of course) per day.

We started a little late, on December 3rd, and were able to push an article almost every day so far. Posts were pushed to the ITPUG members mailing list, generating several interesting discussions among the italian community.

It is possible to find the posts at our (italian-language) planet at www.planetpostgresql.it; please note that all articles on the planet are from my blog because I did the effort to "forward" them to the planet.

I hope this would be the first of several ITPUG Advent Calendars, and I hope other PostgreSQL Users' Groups will do the same in the future.

I'd like to thank all PostgreSQL developers, translators, testers for the great product they continue to deliver us.

And I wish a Merry Christmas to you all.

Calendario dell'Avvento ITPUG: 23 Dicembre

Un'utility interessante che mi sono sempre riproposto di sperimentare è pgCluu.

Pgcluu viene distribuito con due eseguibili: pgcluu e pgcluu_collectd. Il secondo è la parte che si preoccupa di collezionare le statistiche e di archiviarle su file system, in maniera simile a quanto fatto da altri tool di monitoring (ad esempio flow-tools). Il programma pgcluu è invece il responsabile di fare il reporting delle statistiche.

In default la collezione delle statistiche avviene su due fronti: il cluster e il sistema, mediante sar. E' comunque possibile disabilitare l'uso di sar, e in particolare lo switch -h (che svolge la stessa funzione dell'omonimo di psql) disabilita l'uso di sar.

Da notare che nonostante gli eseguibili siano due, la pagina di manuale è una sola ed è riferita a pgcluu(1).

% export STAT_DIR="/tmp/pgcluu_stats_collection";
% mkdir $STAT_DIR && pgcluu_collectd -d demodb -h localhost -M 200MB -D -i 10 -U luca --stat-type all $STAT_DIR
LOG: Detach from terminal with pid: 4442

Si è creata una directory dove verranno collezionate le statistiche e si è lanciato il programma di raccolta dati. Le opzioni usate fanno si che il processo vada in background (-D), si colleghi al database "demodb" (-d) con utente "luca" (-U) sulla macchina locale (-h). Per evitare l'esecuzione incontrollata si fissa una soglia di raccolta dati con tetto massimo di 200 MB (-M), si abilita la raccolta di tutte le statistiche (--stat-type) presenti in PostgreSQL (ossia si legge pg_stats_all) e si vuole che le statistiche siano collezionate ogni 10 secondi (-i).

Attenzione all'opzione -D che può risultare subdola: se il database richiede una password per l'utente il prompt per la password non viene richiesta e pgcluu_collectd termina subito. Occorre quindi utilizzar eil file ~/.pgpass per permettere la connessione automatica (come per psql - in effetti si usa psql per collezionare i dati).

Nella directory STAT_DIR si troverà un file testuale "sysinfo.txt" che contiene un dump di svariate informazioni di sistema (spazio dischi, dmesg, sysctl, ecc.). Altri file comprendono le statistiche di sistema in formato CSV:

Ora generando un po' di carico sul database sotto analisi:

demodb=# CREATE TABLE baz(value int);
demodb=# insert into baz select generate_series(1, 1000);
demodb=# insert into baz select generate_series(1, 1000000);
demodb=# insert into baz select generate_series(1, 1000000);


A questo punto si può generare il report:

% export STAT_OUTPUT_DIR=/tmp/pgcluu_stat_output
% mkdir $STAT_OUTPUT_DIR && pgcluu -o $STAT_OUTPUT_DIR -v -S $STAT_DIR
...
DEBUG: Inserting menu into all html files.

E il gioco è fatto: puntando il proprio browser alla directory di output si avrà accesso alle statistiche collezionate in un modo un po' piu' user-friendly.

Da notare che il manuale di pgcluu indica di terminare esplicitamente il demone di raccolta statistiche prima di generare il report, ma io ho provato a farlo anche senza fermarlo e tutto pare funzionare ugualmente. Unica pecca: non si possono generare i report sovrascrivendoli di volta in volta, perché pgcluu si rifiuta. Ne consegue che per eseguire il tutto in formato schedulato occorre prevedere lo svuotamento della directory di output o almeno un qualche modo di rotazione.

Ah, ultima cosa:

% file `which pgcluu`
/usr/local/bin/pgcluu: Perl script, UTF-8 Unicode text executable

Calendario dell'Avvento ITPUG: 22 Dicembre

Esiste un Foreign Data Wrapper di SQLite.

Io personalmente non lo uso per scopi specifici, quanto piu' per "collegare" un'istanza PostgreSQL a diversi database del mio desktop KDE. Molte applicazioni KDE infatti si appoggiano a SQLite, e a dire il vero anche altri strumenti come fossil (fossil-scm.org) lo fanno. Lo scopo pe rme è quello solo e puramente di studio dei meccanismi interni di aclune applicazioni, un giorno forse avrò anche bisogno di manipolare in diretta i dati. Ad ogni modo l'uso è abbastanza semplice, quasi banale per chi è abituato ai fdw.

Faccio un esempio specifico: digikam

== Passo 1: fare il dump dello schema del database ==

% sqlite3 -interactive digikam4.db
sqlite> .output digikam.sql
sqlite> .schema
sqlite> .q

== Passo 2: preparare uno script di migrazione ==

Mi faccio aiutare qui da Perl (lo script lo riporto in seguito):

% perl export.pl digikam.sql digikam_server /tmp/digikam4.db >
export_digikam.sql

così facendo creo un file export_digikam.sql che creerà il server "digikam_server" partendo dal file di dump "digikam.sql". Il nome del file "digikam.db" serve solo per collegare il data wrapper al database SQLite giusto.

== Passo 3: caricare le tabelle e il foreign data wrapper ==

Da un promp psql è abbstanza banale accedere via fdw:

image=# CREATE EXTENSION sqlite_fdw;
image=# \i ~/Pictures/digikam_export.sql

e tutto è fatto!
Ok, vediamo quanta "roba" c'è nel database:

image=# SELECT count(id) as Foto, sum( filesize ) / (1024 * 1024 *
1024) as Gigabytes FROM images;

foto  | gigabytes
------+-----------
39491 | 285




===== script perl di conversione ======
#! /usr/bin/perl

use v5.10;
my $IN;
my $server = $ARGV[ 1 ];
my $file = $ARGV[ 2 ];
open $IN, "<", $ARGV[ 0 ] || die "\nImpossibile leggere file dump\n$!"; 
say "CREATE SERVER $server FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database \'$file\');"; while (<$IN>) {

if ( /CREATE TABLE (\w+)/ ) {
  $table = $1;
  $sql = "CREATE FOREIGN TABLE $1 (\n";
}
elsif ( $table ){
  chomp;
  s/DATETIME/TIMESTAMP/;
  s/PRIMARY KEY/NOT NULL/;
  s/\(//;
  $sql .= $_ . "\n" unless /(UNIQUE | \)\; ) /xms;
  if ( /[\)\;]/ ){
    $sql =~ s/,$//;
    $sql .= ") \n\t SERVER $server \n\t OPTIONS (table \'$table\' );\n" ;
    say $sql;
    undef $table;
    undef $sql;
  }
 }

}

Calendario dell'Avvento ITPUG: 21 Dicembre

Questa mattina Denis batte tutti sul tempo e pubblica sulla mailing list un articolo veramente piacevole da leggere e ben dettagliato su come usare l'estensione pg_partman che consente di automatizzare molti dei compiti del partizionamento in PostgreSQL.

L'articolo di Denis fornisce un esempio pratico e illustra anche alcuni dei problemi che potrebbero insorgere nell'utilizzo del partizionamento.

Calendario dell'Avvento ITPUG: 20 Dicembre

Vorrei introdurre una utility con la quale sto spendendo un po' di tempo e che, anche se non direttamente collegata a PostgreSQL, può semplificare la gestione di un database: sqitch.
Sqitch funziona con molti database, fra i quali ovviamente PostgreSQL, producendo degli script sql che vengono usati per fare il deploy/aggiornamento/test di un database.
Sqitch concettualmente funziona un po' come un repository Git/hg:

• si installa uno schema "sqitch" nel database sotto controllo;
• si usa questo schema per memorizzare le modifiche effettuate e "firmate" con un hash crittografico

Il tutto è trasparente all'utente, che usa il solo comando "sqitch" un po' come si usa git (e molti comandi e il loro output sono simili, come ad esempio log, status, add).
Attenzione però che sqitch non sostituisce un sistema di controllo delle versioni, quindi è buona norma usare sqitch tenendo comunque gli script SQL sotto controllo delle versioni.

Ecco un rapidissimo esempio per la generazione di due tabelle in un database (demodb) esistente.

• crare un nuovo progetto e definire il database

% sqitch --engine pg init itpug
% sqitch target add itpug db:pg://luca:pwd@localhost/demodb
così facendo si crea un progetto "itpug" e si definisce che quando ci si riferisce al database "itpug" si punta in realtà a demodb.

• si aggiunge lo script per la prima tabella

% sqitch add authors -m "Tabella autori"                                                        
Created deploy/authors.sql
Created revert/authors.sql
Created verify/authors.sql
Added "authors" to sqitch.plan
% echo "CREATE TABLE authors( pk serial NOT NULL, name text NOT NULL, primary key(pk));" > deploy/authors.sql
% echo "DROP TABLE authors CASCADE;" > revert/authors.sql

così facendo sqitch crea tre script sql (vuoti) per il deploy, l'undeploy e la verifica (qui lasciato vuoto).

• si crea una tabella con dipendenza dalla precedente, così da informare sqitch in che ordine le modifiche al database vanno applicate

% sqitch add posts --requires authors -m "Tabella dei post"                                      
Created deploy/posts.sql
Created revert/posts.sql
Created verify/posts.sql
Added "posts [authors]" to sqitch.plan
% echo "CREATE TABLE posts( pk serial NOT NULL, content text , authors_pk integer, primary key(pk), foreign key(authors_pk) references authors(pk) );" > deploy/posts.sql
% echo "DROP TABLE posts;" > revert/posts.sql

• deploy del database, e si nota come sqitch si colleghi a demodb per creare le tabelle nel giusto ordine.

% sqitch deploy itpug                                                                            
Adding registry tables to itpug
Deploying changes to itpug
  + authors .. psql:deploy/authors.sql:1: NOTICE:  CREATE TABLE will create implicit sequence "authors_pk_seq" for serial column "authors.pk"
psql:deploy/authors.sql:1: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
ok
  + posts .... psql:deploy/posts.sql:9: NOTICE:  CREATE TABLE will create implicit sequence "posts_pk_seq" for serial column "posts.pk"
psql:deploy/posts.sql:9: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "posts_pkey" for table "posts"
ok

Da qui in avanti si può usare "log" o "status" per sapere in che stato ci si trova, se ci sono script non ancora inviati al server, ecc.
Occorre però specificare sempre a quale database si fa riferimento (che qui è stato denominato itpug per comodità). Se ad esempio eseguiamo status fra due deploy si avrebbe:

% sqitch status itpug                                                                            
# On database itpug
# Project:  itpug
# Change:   173fb5f9cf9f5048e5b2baf6e6699984b4fdb9fe
# Name:     authors
# By:       Luca Ferrari,,,
#
Undeployed change:
  * posts


notare come l'output sia simile a quello di un rcs distribuito, anche se qui git/hg/bzr non sono nemmeno stati toccati.


Ultimissima cosa: gli script crewti da sqitch non sono "vuoti" ma contengono  informazioni su come gestire le dipendenze. Di conseguenza, sovrascrivere tali script come ho fatto nel passo 3 è sbagliato. Meglio quindi editarli nel proprio editor preferito. Per meglio comprendere, consideriamo di aggiungere un'altra tabella con la dipendenza dalle altre due:

% sqitch add statistics --requires authors --requires posts                                      
Created deploy/statistics.sql
Created revert/statistics.sql
Created verify/statistics.sql
Added "statistics [authors posts]" to sqitch.plan

% cat deploy/statistics.sql                                                                      
-- Deploy statistics
-- requires: authors
-- requires: posts

BEGIN;

-- XXX Add DDLs here.

COMMIT;

Ecco quindi che quei commenti sql all'inizio del file indicano a sqitch come procedere.

Se si vuole esplorare come sqitch gestisce il database e la sua storia si può partire dalla tabella sqitch.changes.

Me @ Planet PostgreSQL

This is my first attempt to appear on Planet PostgreSQL.

I'm Luca, the current president of the Italian PostgreSQL Users' Group (ITPUG) and I'm a PostgreSQL addicted.

Unluckily I'm currently not using PostgreSQL in a day-by-day job, but I'm following the evolution of the project and using it wherever is possible.

I hope to able to contribute to the community somehow.

Calendario dell'Avvento ITPUG: 19 Dicembre

Per l'articolo di oggi mi faccio aiutare da un post passato in -advocacy per fare qualche considerazione.
PostgreSQL potrebbe essere il quarto piu' popolare database del pianeta. Dico potrebbe perché ovviamente non ha senso la misura fatta da dbengines.com. Almeno non per me.
Non si tratta infatti di una misura tecnica o tecnologica, quanto piu' sociale.
 

Ora leggendo la metodologia applicata si vede che la metrica tiene presente  quanto si parla nel web di un database. La differenza di punteggio fra PostgreSQL e gli altri tre prodotti che lo precedono è sconvolgente. Ma come si può notare i posti fuori dal podio hanno uno score non troppo dissimile e, a mio avviso, tutti una buona qualità tecnica.
Cosa significa questo in soldoni? Che chi usa PostgreSQL preferisce parlare di cose tecniche e non filosofeggiare. E ciò è bene fino a quando non ci si scontra con queste classifiche, che inevitabilmente potrebbero portare qualcuno a fare la scelta sbagliata su indici forse poco attendibili. 

Tutto questo per ricordare a noi tutti che quanto piu' materiale siamo in grado di produrre e diffondere su PostgreSQL, tanto piu' il database continuerà a guadagnare in popolarità, e tanto piu' sarà facile (per noi) avere a che fare con installazioni PostgreSQL.

Calendario dell'Avvento ITPUG: 18 Dicembre

Personalmente uso Emacs per quasi tutto, eccetto che per collegarmi ad

un database PostgreSQL.

Non che Emacs non possa farlo, ma semplicemente mi trovo meglio con psql per svariate ragioni, compreso il fatto che a volte devo accedere a macchine che non hanno Emacs installato (e non sono raggiungibili da macchine con Emacs installato). Ad ogni modo Emacs dispone di una serie di modi sql-* per svariati database, fra i quali ovviamente PostgreSQL.

Ora perché diavolo qualcuno dovrebbe collegarsi a un database tramite Emacs? Semplicemente perché inglobare il client dentro all'editor facilita lo scambio dati fra script, server, email, e qualunque altro buffer abbiate aperto (inclusi i commit per le versioni)!

Riporto una minimale configurazione per un ambiente con due database (ovviamente username e password sono inventati):

(add-hook 'sql-interactive-mode-hook
 (lambda ()
  (toggle-truncate-lines t)))

(setq sql-connection-alist
'((TEST (sql-product 'postgres)
    (sql-port 5432)
    (sql-server "localhost")
    (sql-user "demo")
    (sql-password "demo")
    (sql-database "demodb"))
 (PROD (sql-product 'postgres)
    (setq sql-port 5432)
    (sql-user "demo")
    (sql-password "demo")
    (sql-database "demodb") ) ) )


(defun pg-connect (which)).
  (interactive "SQuale connessione database?" )
    (setq sql-product 'postgres)
    (let ()
     (message "Connessione al database %s" which)
      (sql-connect which ) ) )

Anzitutto si abilita la modalità "linee lunghe", perché visualizzare l'output delle query con le andate a capo è abbastanza fastidioso. Poi si definiscono due database, nominati TEST e PROD, con i relativi parametri di connessione. Si possono definire quante connessioni si vuole.

Infine la funzione pg-connect chiede all'utente a quale database collegarsi: M-x pg-connect e poi si inserisce il nome del database (TEST o PROD).

Dopodiché emacs apre il prompt (psql) del database in un buffer separato.

Notare che questa configurazione funziona su Emacs 24.4.1 ma mi ha

dato qualche grattacapo su 24.3

Esce PostgreSQL 9.4

Sotto Natale il PostgreSQL Global Development Group ci regala la nuova versione stabile del fantastico ORDBMS: PostgreSQL 9.4.

A few ridicolous (to me) Perl requests

Usually I don't get personal on blogging and commenting: everyone is free to express herself on the net about pretty much any subject. And I'm not getting personal on this set of entries too, but I have to spend a few words on a Perl blogger that is requesting loudly for a set of shortcuts in the language that make no sense at all.

One is about the versioning of the Perl language: stating that Perl 6 is a different beast from Perl 5 is quite easy. But pretending to have different numbering of both versions is awkward. For external developers Perl 6 is the new version of the Perl language, and this is the thruth. The fact that Perl 6 has evolved as a more complex beast than a "simple language improvement" is another fact, but having to number them independently will cause a lot of confusion.

Another issue is about enabling use warnings; silently when specifying use 5.x;. While I understand that it would be much more easy to use a single line pragma, the author does not realize that there is a lot of legacy code that do not explicitly negates warnings since they were not enabled by default. And keeping backward compatibility is really important.

I don't want my programs to behave strangely just because a future version of Perl let me write simpler code: I just want to write simpler code in the future, without having to constantly refactor old code.

On the same mindset if the request to have package to return automatically a true value. Again, the point is valid and I believe in the future could be a reasonable way of programming, but it is not the right choice for legacy code. And moreover, it does not make sense at all when developers will use /package/ multiple times within the same file (because Perl is not Java, right?).

Calendario dell'Avvento ITPUG: 17 Dicembre

Vorrei spendere alcune parole su un progetto, apparentemente abbandonato, ma che potrebbe portare nuova linfa a PostgreSQL e il suo ecosistema: PostgreSQL Magazine.
Circa due anni fa venne lanciata l'idea di questa rivista online, disponibile all'indirizzo http://www.pgmag.org
Io ho seguito i lavori ma non ho partecipato attivamente perché in quel periodo ero alle prese con un'altra rivista, e comunque ho visto nascere il primo numero promozionale che se ben ricordo venne diffuso a PGCon 2012. Sull'onda del successo iniziale si giunse ad un primo numero ufficiale, e poi da lì al secondo che ahimé è rimasto work in progress.
Lungi da me affermare che il progetto è morto, ma sulla mailing list non si ha piu' attività.
Allora perché ne parlo? Perché forse qualcuno è interessato a contribuire all'iniziativa e ha le capacità/volontà/tempo/risorse per rimettere in carreggiata questo sforzo di diffondere ancora di piu' l'amato database.

Calendario dell'Avvento ITPUG: 15 Dicembre

Io sono sempre stato dell'opinione che i commenti siano anche piu' importanti del codice. Però si sa, i programmatori sono pigri, gli admin pure e i commenti spesso si perdono.
Però si può commentare anche un database, e questo potrebbe tornare molto utile. Non parlo dei commenti negli script SQL ( -- e /* */ ), ma del comando COMMENT di PostgreSQL  che consente di attaccare una stringa di testo ad un oggetto di database (tabella, colonna, constraint, funzione, ...).
 

 Per cosa si può usare questa funzionalità? Ad esempio per creare una sorta di versioning del poveraccio: se ogni commento include un numero di versione (generato manualmente o automaticamente) si può capire a che versione un database/tabella è, e quindi si potrebbe intervenire piu' rapidamente per scoprire eventuali anomalie. Ma vediamo COMMENT in pratica:

# COMMENT ON CONSTRAINT imu IS 'no comment!';

(visto il giorno fatidico!)
Piu' seriamente:

# COMMENT ON TABLE foo IS 'Tabella foo versione [1.1]';


Ed è tutto! Solo una stringa può essere agganciata ad un oggetto, e quindi ricommentare lo stesso oggetto equivale a sovrascrivere il commento precedente, mentre impostarlo a NULL equivale a rimuoverlo. I commenti vengono salvati nella tabella pg_description.
Da qui a vedere un dump completo di una tabella il passo è breve:

# WITH rel_comments AS (
SELECT c.relname  AS tabella, CASE WHEN d.objsubid = 0 THEN
'--tabella--' ELSE '(#' || d.objsubid || ') ' || (SELECT attname FROM
pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = d.objsubid )
END AS colonna, d.description AS commento
FROM pg_class c JOIN pg_description d ON d.objoid = c.oid )

SELECT * FROM rel_comments WHERE tabella = 'test';


 tabella |   colonna   |           commento
---------+-------------+------------------------------

  test    | --tabella-- | Tabella foo versione [1.1]
  test    | (#1) pk     | Prima versione della colonna

Un po' piu' complesso risulta invece aggiornare in append i commenti, a meno di non passare per gli oid. E' comunque possibile creare una funzione che accetti il nome della tabella e della colonna, estragga i dati necessari e faccia UPDATE di pg_description concatenando (o sostituendo) il commento.

Calendario dell'Avvento ITPUG: 14 Dicembre

Al primo PGDay.IT del 2007 partecipò anche Mr. Ishikawa, allora presidente del Japan PostgreSQL Users' Group (JPUG). Nel suo talk egli spiegò, fra le altre cose, che il simbolo del JPUG non prevedeva un elefante, come avviene per PostgreSQL e di conseguenza per molti progetti correlati, bensì una tartaruga. La ragione di questa scelta era etica e morale: in Giappone un gruppo appartanente ad un movimento religioso materializzò una serie di attacchi terroristici, forse il piu' famoso dei quali quello alla metropolitana di Tokyo. Durante questi attacchi i terroristi portavano delle maschere raffiguranti un elefante blu.
Ecco quindi che, mentalmente, per i giapponesi è un brutto segno avere un simbolo con elefante, specialmente se blu. 

Insomma, la stessa etica e rigore mentale che si trova anche nel nostro paese!

Ora però perché proprio la tartaruga? Spulciando in giro si può scoprire che POSTGRES, il fratello maggiore di PostgreSQL e figlio di Ingres, aveva come logo proprio una tartaruga. Ancora meglio: il motto del progetto sembrava essere "it's slow but it gets there". 

Indubbiamente si nota lo spirito scherzoso che ha sempre
contraddistinto i prodotti Berkely.

Calendario dell'Avvento ITPUG: 13 Dicembre

E' il turno di Matteo, vicepresidente di ITPUG, che sulla mailing list soci presenta i risultati di un esperimento di join fra datatype numerici di differnti formati e dimensioni.

Materiale molto completo, con anche un grafico che spiega chiaramente quali siano i datatype piu' rapidi per i join (gli int4).

Calendario dell'Avvento ITPUG: 12 Dicembre

Perché va bene avere la replica, HA e fratelli, ma un buon dump ha il
suo valore!
Penso che sia capitato piu' o meno a tutti di scrivere uno script shell per automatizzare il backup (dump) di uno o piu' database PostgreSQL. Personalmente io mi sono trovato nella situazione di scrivere (e riciclare) script di backup che creassero file il cui nome era parlante, includendo nome del database, data del backup, ora del backup, ecc. Il passo successivo è scrivere un ciclo che iteri su tutti i database nel cluster.
Una simile automazione si trova anche negli script che vengono installati automaticamente in una macchina FreeBSD (502.postgres); lo script fa esattamente quello che ho appena descritto, ovvero:

1) itera su tutti i database presenti nel cluster (andando a leggere il catalogo di sistema)
    SELECT datname FROM pg_database WHERE datname!='template0'

2) costruisce un file con il nome del database e la data attuale 

   now=`date "+%Y-%m-%dT%H:%M:%S"`
   ...
   file=${backupdir}/pgdump_${db}_${now}

3) effettua il backup effettivo del database

   daily_pgsql_pgdump_args="-U ${daily_pgsql_user} -bF c"
   pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}

Da notare l'uso del formato custom (opzione -F c) che consente il
restore parallelo qualora si voglia dare tutto in pasto a pg_restore.

Va anche detto che lo script in question effettua anche un dump usando
pg_dumpall (che poi viene compresso).

Lo script ha alcune spunti di riflessione interessanti, come ad esempio la protezione dei file di backup e la retention policy: tutti i file di backup giornalieri piu' vecchi di 7 (o della variabile ${daily_pgsql_savedays}) giorni vengono cancellati con l'uso di un find

find ... -a -mtime +${daily_pgsql_savedays} -delete


Insomma, questo script rappresenta un buon punto di partenza per la gestione schedulata dei backup, e ovviamente non si sostituisce ad altre tecniche e tecnologie piu' evolute. 

Sulla mailing list soci si è aperta una discussione con tanti spunti su come migliorare questo tipo di script, ad esempio aggiungendo logging via syslog, mount al volo dei file system di backup, controllo dello spazio disco, ecc.

Calendario dell'Avvento ITPUG: 10 Dicembre

Sul fantastico CPAN è possibile trovare una versione del famoso software di importazione di dati pgloader scritta (ovviamente) in Perl!

Ricordo che il software originale è stato scritto in Python.

Calendario dell'Avento ITPUG: 9 Dicembre

Oggi è il turno di Denis (seguito a ruota da Vincenzo), che sulla mailing list soci ci ricorda l'utilità della clausola WITH ORDINALITY: la possibilità di aggiungere una colonna con ordinamento (a dire il vero ordinalità) ad una query che abbia un funzione che ritorna un SET OF. 

In sostanza, grazie a questa possibilità si possono enumerare l'ordinamento dei risultati estratti dalla funzione e che compaiono nella clausola FROM della query.

Calendario dell'Avvento ITPUG: 8 Dicembre

Dave Page è, fra le altre cose, l'ideatore e lo sviluppatore principale di pgAdmin, il software desktop Open Source piu' utilizzato per l'amministrazione di un cluster PostgreSQL. Sul planet ufficiale è uscito un articolo di Dave che illustra la storia che ha portato pgAdmin fino alla terza versione (l'unica con la quale io abbia mai avuto a che fare).

Oltre ad essere un'interessante lettura per gli eventi storici che si sono susseguiti e che hanno portato Dave a diventare un hacker PostgreSQL, l'articolo in questione è molto importante perché svela che è in cantiere la prossima versione del software, denominata ovviamente pgAdmin 4. Questa versione sarà basata su Python e sarà web-based.

Già, pare che il team di pgAdmin non veda di buon occhio altri linguaggi, come Perl e Java, e questo mi fa pensare che non vedremo un merge delle forze con gli autori di un altro programma web based: PostgreSQL Studio.

Sono comunque convinto che il team di pgAdmin saprà dare alla community un ottimo prodotto, in piena coerenza con quanto fatto fino ad ora.

Calendario dell'Avvento ITPUG: 7 Dicembre

Il client psql permette di inviare l'output delle query su file, cosa abbastanza utile per generare al volo dei report csv, html, LaTeX, ecc. Ma oltre a questo psql include il flag -L (log appunto) che consente di replicare ogni output su un file di log.
In sostanza se si aggiunge il flag -L alla linea di comando normale di psql, specificando un file di log, si avrà che ogni query e il relativo output saranno mandati in copia anche su quel file, indipendentemente da dove l'utente abbia rediretto l'output.
Da notare che il log non rispetta i comandi di allineamento, quindi l'output nel log è sempre "allineato".
Inoltre il log riporta anche le query fatte, comprese le interrogazioni verso il catalogo di sistema (\d e similari), che a loro volta però vanno abilitate con -E.
Infine il log è (ovviamente) in append anche fra piu' sessioni, quindi il file rischia di crescere molto rapidamente.

Calendario dell'Avvento ITPUG: 6 Dicembre

Ecco una "piccola" query che dovrebbe dare uno sguardo piu' facilmente leggibile sui lock attualmente presenti nel sistema. Parto da un possibile output della query per meglio far capire:

-[ RECORD 1 ]---+-------------------------

--------------------------------------
pid             | 1492
locking_summary | RowExclusiveLock GRANTED on table public.listini
user_query      | insert into listini(prezzo) select generate_series(1,1000000);
by              | bsdmag started on 2014-12-05 13:15:02.759257+00 (running)

ovvero si riporta quale processo (pid) ha quali lock (locking_summary) per l'utente (by) che sta eseguendo la query (user_query) a partire dal tempo indicato (by). Altro non è che una reinterpretazione del join fra pg_locks e
pg_stat_activity.

Inserire la query in una vista, denominata verbose_locck, consente di eseguirla piu' facilmente; di seguito la definizione della query/vista:

CREATE VIEW verbose_locks
AS

WITH full_table_name AS
(
   SELECT 'table ' || nsp.nspname || '.' || clz.relname AS name, clz.oid
   FROM pg_class clz JOIN pg_namespace nsp ON clz.relnamespace = nsp.oid
   WHERE clz.relkind = 'r'
)

SELECT
l.pid   -- process identifier
-- locking information
, l.mode
  || CASE WHEN l.granted THEN ' GRANTED'
          ELSE ' WAITING'
     END
  || ' on ' ||
  CASE WHEN l.locktype = 'relation'
            THEN (SELECT name
                  FROM full_table_name
                  WHERE oid = l.relation )
       WHEN l.locktype = 'tuple'                THEN    ' row '
       WHEN l.locktype = 'transactionid'
            THEN        ' transaction ' || l.transactionid
       WHEN l.locktype = 'virtualxid'
            THEN    ' virtual transaction ' || l.virtualtransaction
       ELSE l.locktype
  END
  AS locking_summary

-- current query launched from the user
, a.current_query AS user_query
-- username and some other details about the query
, a.usename || ' started on ' || a.query_start || CASE WHEN a.waiting
THEN ' (waiting) '
                                                       ELSE ' (running) '
                                                  END
  AS by

FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid = a.procpid
WHERE l.pid <> pg_backend_pid() -- do not show myself

Calendario dell'Avvento ITPUG: 5 Dicembre

psql si presenta come una shell verso PostgreSQL. E come tutte le shell che si rispettino, anche psqlpermette all'utente di modificare il proprio prompt, adattandolo alle proprie esigenze.
L'analogia con le shell (csh, zsh, bash) è quanto mai obbligatoria, visto che i meccanismi sono i medesimi:

• definizione di una serie di variabili speciali che vengono espanse con dei valori prefissati (es. nome utente, nome del database, ecc.)
• interpolazione di alcune sequenze di escape (stile bash, ossia con le parentesi quadre!)
• definizione di una serie di variabili del programma psql che siusano per renderizzare il prompt

In psql il prompt viene renderizzato con due variabili:

• PROMPT1: il promp classico
• PROMPT2: il prompt delle righe di completamento comando (quando uno statement non viene completato su una sola riga)

Esiste anche un PROMPT3 che qui non mi interessa trattare.

Fatta questa premessa sul prompt di psql io sono solito modificare il comportamento standard per far assomigliare maggiormente il prompt di psql a quello delle mie shell utente. In particolare mi piace inserire l'informazione sull'utente correntemente connesso al database, il database (ovviamente), la macchina e la porta a cui sono collegato.
Questo consente rapidamente di capire a "chi" sono connesso quando tengo aperte piu' sessioni fra istanze differenti.

Il tutto si traduce come:

\set PROMPT1 '[%n@%/ %m:%>  %x %# '
 

 ossia
 

'nome_utente@database nome_host:porta_tcp #'

Abbastanza utile il marcatore %x che viene espanso in stringa vuota se non vi sono transazioni attive e in un carattere '*' se si è all'interno di un blocco di transazione.
Per il PROMPT2 (quello multiriga) solitamente opto per qualcosa di piu' corto: nome utente e database, giusto per essere sicuro di fare il COMMIT sul database giusto!

E per un po' di mal di testa, ecco una versione colorata da inserire nel proprio ~/.psqlrc:

\set PROMPT1 '%[\033[0;1;33m%][%[\033[0;1;

37m%]%n%[\033[0;1;31m%]@%[\033[0;1;37m%]%/%[\033[0;1;33m%]
%[\033[0;1;34m%]%m:%>% %[\
\033[0;1;33m%]%[\033[0;1;33m%]]%[\033[0;1;32m%] %x %# %[\033[0;1;37m%]'
\set PROMPT2 ' %n%[\033[0;1;31m%]@%[\033[0;1;37m%]%/%[\033[0;1;33m%]
%[\033[0;1;32m%] %x %# %[\033[0;1;37m%]'

Calendario dell'Avvento ITPUG: 4 Dicembre

Un altro giorno, un altro post a tema PostgreSQL.

Denis oggi ha postato un simpatico esempio sulla mailing list soci di ITPUG relativo al meteo. Già, perché grazie ai foreign data wrapper è possibile inglobare in un cluster dati veramente disparati, come quelli scaricati da un web service meteo (o da una connessione http).

L'esempio fa anche uso delle funzioni integrate di PostgreSQL per i tipi JSON.

Calendario dell'Avvento ITPUG: 3 Dicembre

psql è il mio client preferito per PostgreSQL, specialmente perché
sono un amante della command line.
Una funzione secondo me piuttosto utile di psql è il single-step mode:
se abilitato psql forza la richiesta di una conferma prima
dell'esecuzione di ogni istruzione SQL. Non è propriamente come
racchiudere il comando in una transazione, quanto piu' come eseguire
ogni istruzione in modo condizionale.
Ciò aiuta ad esempio l'esecuzione di script complessi per verificare
cosa si sta per eseguire.
Abilitare il single-step può essere fatto in due modi:
1) con il flag -s sulla linea di comando
2) con la variabile SINGLESTEP come segue
\set SINGLESTEP 'on'
(e analogamente 'off' per disabilitare la funzione)

La cosa utile del single-step è che procede alla sostituzione delle
variabili prima della richiesta della conferma. Supponiamo di avere uno script come il seguente:

CREATE TABLE foo(
       pk serial not null,
       value integer default 0,
       PRIMARY KEY(pk)
);

INSERT INTO foo(value) VALUES(1);
INSERT INTO foo(value) VALUES(2);
INSERT INTO foo(value) VALUES(3);
INSERT INTO foo(value) VALUES(:var1);

e di avviare psql, impostando il single-step e il valore della variabile var1:

# \set var1 '4'
# \set SINGLESTEP 'on'
# \i script.sql

durante l'esecuzione dello script si avrà che:

***(Single step mode: verify command)**********************

*********************
INSERT INTO foo(value) VALUES(4);
***(press return to proceed or enter x and return to cancel)********************

Quindi non solo viene richiesta conferma dell'esecuzione del comando,
ma si mostra anche il valore espanso della variabile.

Il calendario dell'Avvento ITPUG (?)

Seppur in ritardo oggi ho provato a lanciare la sfida del calendario dell'Avvento ITPUG. L'idea non è nuova, ci sono già alcune community che fanno il calendario dell'Avvento: si scrive qualcosa ogni giorno riguardo ad un progetto particolarmente interessante. Un esempio? Si veda il calendario di Catalyst.

Non so bene se la cosa riuscirà a prendere piede, ITPUG non è (ancora) pronta come infrastruttura (ad esempio il planet Italia non è denso di writers), ma penso che anche la mailing list soci possa andare bene come mezzo per la divulgazione di PostgreSQL. Perfino un social network è appropriato, l'importante è condividere link e contenuti.

Cosa scrivere? Beh, qualunque cosa sia correlata a PostgreSQL: tips and tricks, howtos, curiosità, notizie relative ad eventi, esempi di SQL virtuoso, ecc.

Quanto scrivere? Non c'è una lunghezza, da qualche riga a qualche pagina! L'importante è che l'argomento sia PostgreSQL-related.

Perché scrivere? Perché penso che sia importante condividere (anche delle banalità) su PostgreSQL, l'importante è abituarsi a condividere, e questo è un modo un po' forzato per scrivere e curiosare nei thread degli altri autori.

Quando scrivere? Ogni giorno!

Spero vivamente che la cosa abbia successo.

Ah, mi rendo anche disponibile a fare da forwarder per il planet Italia per chi non avesse un blog o non fosse ancora collegato a tale planet, ovviamente citando autore e lasciando inalterato il materiale fornito.