Per lungo tempo una delle più note mancanze di PostgreSQL è stata la possibilità di parallelizzare le query. Con l’uscita della versione 9.6 non sarà più così. È stato infatti svolto un grande lavoro sul tema, per il quale il primo risultato è stato il commit 80558c1, in cui viene introdotta la parallelizzazione dei sequential scan in alcuni casi che vedremo nel corso di questo articolo.

Innanzitutto, una premessa: lo sviluppo di questa feature è stato continuo e alcuni parametri hanno cambiato nome nel susseguirsi di commit. L’articolo è stato scritto con un checkout al 17 giugno, e presenta alcune caratteristiche che saranno presenti solo dalla beta2 della 9.6.

Rispetto alla major 9.5 sono stati introdotti nuovi parametri all’interno della configurazione. Questi sono:

• max_parallel_workers_per_gather: il numero di worker che possono assistere un sequential scan su una tabella;
• min_parallel_relation_size: la dimensione minima che deve avere una relazione affinché il planner consideri l’uso di worker aggiuntivi;
• parallel_setup_cost: parametro del planner che valuta il costo di istanziare un worker;
• parallel_tuple_cost: parametro del planner che valuta il costo di trasferire una tupla da un worker a un altro;
• force_parallel_mode: parametro utile per i test, forza il parallelismo anche su query su cui il planner agirebbe in altri modi.

Vediamo come i worker aggiuntivi possono essere usati per velocizzare le nostre query. Creiamo una tabella di test con un campo INT e cento milioni di record:

postgres=# CREATE TABLE test (i int);
CREATE TABLE
postgres=# INSERT INTO test SELECT generate_series(1,100000000);
INSERT 0 100000000
postgres=# ANALYSE test;
ANALYZE

Di default PostgreSQL ha max_parallel_workers_per_gather impostato a 2, per cui verranno attivati due worker durante un sequential scan.

Un semplice sequential scan non presenta novità alcuna:

postgres=# EXPLAIN ANALYSE SELECT * FROM test;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1442478.32 rows=100000032 width=4) (actual time=0.081..21051.918 rows=100000000 loops=1)
 Planning time: 0.077 ms
 Execution time: 28055.993 ms
(3 rows)

È infatti richiesta la presenza di una clausola WHERE per la parallelizzazione:

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i=1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..964311.60 rows=1 width=4) (actual time=3.381..9799.942 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..963311.50 rows=0 width=4) (actual time=6525.595..9791.066 rows=0 loops=3)
         Filter: (i = 1)
         Rows Removed by Filter: 33333333
 Planning time: 0.130 ms
 Execution time: 9804.484 ms
(8 rows)

Possiamo tornare al comportamento precedente e osservarne le differenze impostando max_parallel_workers_per_gather a 0:

postgres=# SET max_parallel_workers_per_gather TO 0;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i=1;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1692478.40 rows=1 width=4) (actual time=0.123..25003.221 rows=1 loops=1)
   Filter: (i = 1)
   Rows Removed by Filter: 99999999
 Planning time: 0.105 ms
 Execution time: 25003.263 ms
(5 rows)

Un tempo 2.5 volte maggiore.

Non sempre il planner considera un sequential scan parallelo la migliore opzione. Se la query non è abbastanza selettiva e ci sono molte tuple da trasferire, è possibile che sia preferito un sequential scan "classico":

postgres=# SET max_parallel_workers_per_gather TO 2;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i<90000000;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1692478.40 rows=90116088 width=4) (actual time=0.073..31410.276 rows=89999999 loops=1)
   Filter: (i < 90000000)
   Rows Removed by Filter: 10000001
 Planning time: 0.133 ms
 Execution time: 37939.401 ms
(5 rows)

Infatti, se proviamo a forzare un sequential scan parallelo, otteniamo un risultato peggiore:

postgres=# SET parallel_tuple_cost TO 0;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i<90000000;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..964311.50 rows=90116088 width=4) (actual time=0.454..75546.078 rows=89999999 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..1338795.20 rows=37548370 width=4) (actual time=0.088..20294.670 rows=30000000 loops=3)
         Filter: (i < 90000000)
         Rows Removed by Filter: 3333334
 Planning time: 0.128 ms
 Execution time: 83423.577 ms
(8 rows)

Possiamo incrementare il numero di worker fino a raggiungere max_worker_processes (default: 8). Ripristiniamo il valore di parallel_tuple_cost vediamo quello che accade aumentando max_parallel_workers_per_gather a 8.

postgres=# SET parallel_tuple_cost TO DEFAULT ;
SET
postgres=# SET max_parallel_workers_per_gather TO 8;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i=1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..651811.50 rows=1 width=4) (actual time=3.684..8248.307 rows=1 loops=1)
   Workers Planned: 6
   Workers Launched: 6
   ->  Parallel Seq Scan on test  (cost=0.00..650811.40 rows=0 width=4) (actual time=7053.761..8231.174 rows=0 loops=7)
         Filter: (i = 1)
         Rows Removed by Filter: 14285714
 Planning time: 0.124 ms
 Execution time: 8250.461 ms
(8 rows)

Nonostante PostgreSQL potesse usare fino a 8 worker, ne ha instanziati solo 6. Questo perché Postgres ottimizza il numero di worker anche in base alle dimensioni della tabella e al parametro min_parallel_relation_size. Il numero dei worker messi a disposizione da postgres si basa su una successione geometrica di ragione 3 il cui primo termine è min_parallel_relation_size. Facciamo un esempio. Considerando gli 8MB del default del parametro:

Possiamo vedere che, essendo la nostra tabella 3458MB, 6 è il massimo numero di worker disponibili.

postgres=# \dt+ test
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 3458 MB |
(1 row)

Per concludere, una breve dimostrazione dei miglioramenti ottenuti da attraverso questa patch. Lanciando la nostra query abilitando un numero crescente di worker, otteniamo i seguenti risultati:

Possiamo vedere che i tempi migliorano notevolmente, fino ad arrivare ad un terzo del valore iniziale. È semplice da spiegare anche il fatto che non ci siano miglioramenti fra l’uso di tre e 6 worker: la macchina su cui è stato eseguito il test ha 4 cpu disponibili, per cui dopo 3 worker più il processo originale i risultati si stabilizzano.

Per concludere, con la 9.6 PostgreSQL ha posto le basi per la parallelizzazione delle query, di cui il sequential scan parallelo è solo il primo, ottimo, risultato. Vedremo infatti come sempre nella 9.6 siano state parallelizzate anche le aggregazioni, ma questo è materiale per un altro articolo che uscirà nelle prossime settimane.

Questi nuovi operatori potranno essere aggiunti alla clausola GROUP BY e permetteranno il raggiungimento di risultati aggregati in modo più veloce e con un codice più pulito di quanto fosse possibile fare in precedenza, utilizzando più query GROUP BY e UNION ALL.

Facciamo un esempio classico: supponiamo di voler analizzare le vendite di un negozio.

Identifichiamo quindi la vendita come il fatto da analizzare e per praticità d’esempio, consideriamo il prezzo come unica misura a cui siamo interessati. Le dimensioni della nostra analisi saranno il tempo, il luogo e il prodotto (lo script per la creazione delle tabelle e l’inserimento dei dati utilizzati in questi esempi possono essere scaricati da questo link www.dropbox.com/s/sk2n8uz3ddp01ss/create_tables_grouping_sets.sql?dl=0.

Vediamo adesso come utilizzare i nuovi operatori per condurre delle analisi sul db.

GROUPING SETS

Con GROUPING SETS è possibile poter definire insiemi differenti di campi su cui eseguire una GROUP BY e unirne insieme i risultati. Cerchiamo di comprendere con precisione il comportamento di questo operatore con un esempio.

Supponiamo di voler analizzare le vendite dei singoli negozi per anno, e di volere anche avere i dati annuali aggregati di tutti i negozi. Fino alla versione 9.4, avremo ottenuto questo risultato eseguendo UNION ALL:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY (l.nome_negozio, g.anno)
UNION ALL
SELECT NULL AS nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY anno
ORDER BY nome_negozio, anno;

Con la versione 9.5 possiamo utilizzare GROUPING SETS:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS ((l.nome_negozio, anno), anno)
ORDER BY l.nome_negozio, anno;

Come si può notare, è possibile specificare raggruppamenti su singoli campi o su insiemi di questi, purché ogni insieme sia delimitato da parentesi. L’output delle due query è identico:

 nome_negozio | anno | sum
--------------+------+-----
 Neg1         | 2014 |   7
 Neg1         | 2015 |  31
 Neg2         | 2014 |  10
 Neg2         | 2015 |  72
 Neg3         | 2014 |  20
 Neg3         | 2015 |  64
 Neg4         | 2014 |  32
 Neg4         | 2015 |  37
              | 2014 |  69
              | 2015 | 204

Tuttavia possiamo verificare come il piano della versione che usa GROUPING SETS sia nettamente migliore. Questo è il piano della versione con UNION ALL:

QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=359.27..362.45 rows=1270 width=68) (actual time=0.774..0.775 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  Append  (cost=133.95..293.80 rows=1270 width=68) (actual time=0.379..0.708 rows=10 loops=1)
             ->  HashAggregate  (cost=133.95..147.33 rows=1070 width=68) (actual time=0.379..0.403 rows=8 loops=1)
                   Group Key: l.nome_negozio, g.anno
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.128..0.253 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.073..0.150 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.016..0.033 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.026..0.026 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.012 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.023..0.023 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.008 rows=3 loops=1)
             ->  Subquery Scan on "*SELECT* 2"  (cost=131.28..135.78 rows=200 width=68) (actual time=0.297..0.303 rows=2 loops=1)
                   ->  HashAggregate  (cost=131.28..133.78 rows=200 width=36) (actual time=0.294..0.297 rows=2 loops=1)
                         Group Key: g_1.anno
                         ->  Hash Join  (cost=75.80..125.93 rows=1070 width=36) (actual time=0.097..0.215 rows=32 loops=1)
                               Hash Cond: (v_1.id_data = g_1.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=36) (actual time=0.050..0.120 rows=32 loops=1)
                                     Hash Cond: (v_1.id_luogo = l_1.id)
                                     ->  Seq Scan on vendite v_1  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.006..0.021 rows=32 loops=1)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=4) (actual time=0.017..0.017 rows=4 loops=1)
                                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                           ->  Seq Scan on luoghi l_1  (cost=0.00..16.30 rows=630 width=4) (actual time=0.004..0.008 rows=4 loops=1)
                               ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.015..0.015 rows=3 loops=1)
                                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                                     ->  Seq Scan on giorni g_1  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.003..0.006 rows=3 loops=1)
     Planning time: 1.433 ms
     Execution time: 1.330 ms

Mentre questo è il piano della versione con GROUPING SETS, più rapido e più gradevole alla vista:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=271.81..274.99 rows=1270 width=68) (actual time=0.475..0.476 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  GroupAggregate  (cost=179.76..206.34 rows=1270 width=68) (actual time=0.312..0.426 rows=10 loops=1)
             Group Key: g.anno, l.nome_negozio
             Group Key: g.anno
             ->  Sort  (cost=179.76..182.44 rows=1070 width=68) (actual time=0.287..0.299 rows=32 loops=1)
                   Sort Key: g.anno, l.nome_negozio
                   Sort Method: quicksort  Memory: 27kB
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.089..0.197 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.059..0.124 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.017..0.031 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.022..0.022 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.010 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.013..0.013 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.007 rows=3 loops=1)
     Planning time: 0.741 ms
     Execution time: 0.713 ms

Inoltre, potremmo voler raggruppate le nostre vendite per regione e categoria, con l’aggiunta di una riga che mostri il totale. In questo caso, utilizzeremo la seguente query:

SELECT l.regione, p.categoria, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
GROUP BY GROUPING SETS ((l.regione, p.categoria), ())
ORDER BY regione, categoria;

Ottenendo come risultato:

 regione | categoria | totale
---------+-----------+--------
 Toscana | cat1      |    104
 Toscana | cat2      |    100
 Umbria  | cat1      |     14
 Umbria  | cat2      |     55
         |           |    273

GROUPING SETS è l’operatore più generico dei tre, e permette di raggruppare dati in base a qualsiasi insieme di parametri definito dall’utente. CUBE e ROLLUP, come vedremo, sono solo comode scorciatoie per alcuni utilizzi molto comuni di GROUPING SETS.

CUBE

L’operatore CUBE raggruppa sull’insieme di tutte le combinazioni possibili dei vari campi passati come parametro. Supponiamo, ad esempio, di voler vedere i totali delle vendite organizzati per categoria, provincia e mese/anno. Lanciando la query:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY CUBE((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

otterremo il seguente risultato:

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Firenze   |           |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Perugia   |           |   11 | 2014 |     32
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
 Prato     |           |   11 | 2014 |     17
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Firenze   |           |   10 | 2015 |     40
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Perugia   |           |   10 | 2015 |     15
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
 Prato     |           |   10 | 2015 |     47
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Firenze   |           |   11 | 2015 |     24
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Perugia   |           |   11 | 2015 |     22
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
 Prato     |           |   11 | 2015 |     56
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
 Firenze   | cat1      |      |      |     44
 Firenze   | cat2      |      |      |     40
 Firenze   |           |      |      |     84
 Perugia   | cat1      |      |      |     14
 Perugia   | cat2      |      |      |     55
 Perugia   |           |      |      |     69
 Prato     | cat1      |      |      |     60
 Prato     | cat2      |      |      |     60
 Prato     |           |      |      |    120
           | cat1      |      |      |    118
           | cat2      |      |      |    155
           |           |      |      |    273

Avremmo potuto scrivere la stessa query usando GROUPING SETS in questo modo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  ((g.mese, g.anno),l.provincia),
  (p.categoria, l.provincia),
  (g.mese, g.anno),
  p.categoria,
  l.provincia,
  ()
)
ORDER BY anno, mese, provincia, categoria;

Il piano di esecuzione è lo stesso per entrambe le versioni:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------
     Sort  (cost=856.15..868.35 rows=4881 width=104)
       Sort Key: g.anno, g.mese, l.provincia, p.categoria
       ->  GroupAggregate  (cost=223.60..557.12 rows=4881 width=104)
             Group Key: p.categoria, l.provincia, g.mese, g.anno
             Group Key: p.categoria, l.provincia
             Group Key: p.categoria
             Group Key: ()
             Sort Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno
             Sort Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia
             ->  Sort  (cost=223.60..226.28 rows=1070 width=104)
                   Sort Key: p.categoria, l.provincia, g.mese, g.anno
                   ->  Hash Join  (cost=104.92..169.76 rows=1070 width=104)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=53.30..103.43 rows=1070 width=100)
                               Hash Cond: (v.id_prodotto = p.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=72)
                                     Hash Cond: (v.id_luogo = l.id)
                                     ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=44)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=36)
                                           ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36)
                               ->  Hash  (cost=18.50..18.50 rows=850 width=36)
                                     ->  Seq Scan on prodotti p  (cost=0.00..18.50 rows=850 width=36)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=12)
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=12)

ROLLUP

ROLLUP, come CUBE, è una semplificazione di GROUPING SETS. In questo caso i raggruppamenti sono effettuati prima su tutti i campi su cui è stato dichiarato l’operatore, poi su tutti meno l’ultimo, su tutti meno gli ultimi due e così via. Ad esempio, usando ROLLUP al posto di CUBE nell’ultima query otterremo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY ROLLUP((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
           |           |      |      |    273

ovvero, l’equivalante di:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  (g.mese, g.anno),
  ()
)
ORDER BY anno, mese, provincia, categoria;

Conclusioni

Versione dopo versione PostgreSQL sta facendo passi da gigante in ambito OLAP e data warehouse – o, se preferite, BigData e Analytics. Oltre ai comandi mostrati in questo articolo, solo nella 9.5 ad esempio sono stati aggiunti gli indici BRIN, ereditarietà delle tabelle esterne e IMPORT FOREIGN SCHEMA, che permettono a PostgreSQL di gestire quantità ancora più grandi di dati e con più facilità.

Siamo sicuri che chi usa PostgreSQL per fare Business Intelligence apprezzerà notevolmente questa nuova major release.

L’intero codice usato in questo tutorial è presente su GitHub all’indirizzo https://github.com/2ndquadrant-it/vagrant-puppet-barman.

Configurare il Puppet Master: Vagrant

Innanzitutto, occorre modificare il Vagrantfile per avviare una terza macchina, chiamata puppet, che sarà il nostro Puppet Master. Questa macchina dovrà essere subito raggiungibile dagli agenti Puppet presenti su ognuna delle VM create, per cui, all’interno del primo script che lanciamo, configuriamo una entry per il suo indirizzo in /etc/hosts.

Sempre nello script inline dobbiamo abilitare l’agente Puppet. Questo è infatti disabilitato di default nelle distribuzioni derivate da Debian.

Infine, all’interno del Vagrantfile, andiamo ad effettuare una distinzione fra master e agenti. Il primo caricherà la propria configurazione direttamente da file, gli agenti lo faranno chiedendola al master e inviandogli i dati che devono scambiarsi con gli altri nodi. Per quest’ultima ragione un agente è impostato per girare anche sul master.

Il Vagrantfile prodotto è il seguente:

Vagrant.configure("2") do |config|
  {
    :puppet => {
      :ip      => '192.168.56.220',
      :box     => 'ubuntu/trusty64',
      :role    => 'master'
    },
    :pg => {
      :ip      => '192.168.56.221',
      :box     => 'ubuntu/trusty64',
      :role    => 'agent'
    },
    :backup => {
      :ip      => '192.168.56.222',
      :box     => 'ubuntu/trusty64',
      :role    => 'agent'
    }
  }.each do |name,cfg|
    config.vm.define name do |local|
      local.vm.box = cfg[:box]
      local.vm.hostname = name.to_s + '.local.lan'
      local.vm.network :private_network, ip: cfg[:ip]
      family = 'ubuntu'
      bootstrap_url = 'https://raw.github.com/hashicorp/puppet-bootstrap/master/' + family + '.sh'

      # Run puppet-bootstrap and enable the Puppet agent
      local.vm.provision :shell, :inline => <<-eos
        if [ ! -e /var/tmp/.bash.provision.done ]; then
          echo "192.168.56.220  puppet.local.lan        puppet puppetdb puppetdb.local.lan" >> /etc/hosts
          curl -L #{bootstrap_url} | bash
          puppet agent --enable
          touch /var/tmp/.bash.provision.done
        fi
      eos

      if cfg[:role] == 'master'
        # Puppet master needs RAM
        local.vm.provider "virtualbox" do |v|
          v.memory = 1024
        end

        # Provision the master with Puppet
        local.vm.provision :puppet do |puppet|
          puppet.manifests_path = "manifests"
          puppet.module_path = [".", "modules"]
          puppet.manifest_file = "site.pp"
          puppet.options = [
           '--verbose',
          ]
        end
      end

      # Puppet agents should be provisioned by the master
      local.vm.provision :puppet_server do |puppet|
        puppet.options = [
         '--verbose',
        ]
      end

    end
  end
end

Configurare il Puppet Master: Puppet

Una volta in possesso del Vagrantfile è il momento di andare a modificare il manifesto Puppet per attivare il master.
Prima di fare questo, però, è necessario installare altri due moduli: puppetlabs/puppetdb e stephenrjonson/puppet.

puppetlabs/puppetdb configura PuppetDB. PuppetDB utilizza un database PostgreSQL per raccogliere i fatti e le risorse esportate dai vari nodi dell’infrastruttura, in modo che questi possano scambiarsi informazioni e configurarsi uno in base agli altri.
stephenrjonson/puppet permette di configurare un server Puppet Master con Apache e Passenger, nonché di configurare gli agenti sui vari nodi della rete.

Modifichiamo quindi il Puppetfile in:

forge 'https://forgeapi.puppetlabs.com'
mod 'it2ndq/barman'
mod 'puppetlabs/postgresql'
mod 'puppetlabs/puppetdb'
mod 'stephenrjohnson/puppet'

Rispetto all’articolo precedente abbiamo rimosso la dipendenza dall’ultima versione su GitHub per il modulo it2ndq/barman, del quale è stata rilasciata la versione 1.0.0 (che utilizzeremo) negli ultimi giorni. Eseguiamo:

$ librarian-puppet install --verbose

A questo punto possiamo andare a modificare il manifesto site.pp, aggiungendo il nodo puppet e inserendo al suo interno il seguente frammento di codice per PuppetDB e il Puppet Master:

  # Setup PuppetDB
  class { 'puppetdb': }->
  # Setup Puppet Master, Apache and Passenger
  class { 'puppet::master':
    storeconfigs => true,
    autosign     => true,
    environments => 'directory',
  }->

Abbiamo così configurato il Puppet Master per accettare automaticamente connessioni da tutte le macchine (autosign) e distribuire i cataloghi, i fatti e le risorse esportate (storeconfig).
Infine, utilizziamo i directory environment di Puppet per distribuire il catalogo agli agenti. La directory standard per gli ambienti è /etc/puppet/environments e l’ambiente di default è production. Inseriamo quindi i nostri manifesti e moduli al suo interno. Poiché Vagrant già condivide la directory in cui si trova il Vagrantfile con le macchine create, possiamo fare un link simbolico a questa directory:

  # Have the manifest and the modules available for the master to distribute
  file {
    ['/etc/puppet/environments', '/etc/puppet/environments/production']:
      ensure => directory;
    '/etc/puppet/environments/production/modules':
      ensure => 'link',
      target => '/vagrant/modules';
    '/etc/puppet/environments/production/manifests':
      ensure => 'link',
      target => '/vagrant/manifests';
  }

A questo punto, su ogni nodo dell’infrastruttura, dovremo configurare l’agente. Esplicitiamo il Puppet Master, l’environment e il modo in cui deve essere eseguito. Eseguire l’agente via cron occupa meno risorse che farlo girare come demone:

  # Configure Puppet Agent
  class { 'puppet::agent':
    puppet_run_style => 'cron',
    puppet_server    => 'puppet.local.lan',
    environment      => 'production',
  }

Iniziamo adesso a condividere risorse fra i vari nodi. Poiché i nodi pg e backup avranno bisogno di comunicare fra loro via SSH, dovranno sapere qual è l’ip dell’altro server e averne la chiave all’interno di known_hosts. Provvediamo quindi ad esportare e raccogliere queste risorse su ogni nodo, ad esempio:

  @@host { 'backup_host':
    ensure       => 'present',
    name         => $::fqdn,
    host_aliases => $::hostname,
    ip           => '192.168.56.222',
  }

  @@sshkey { "${::hostname}_ecdsa":
    host_aliases => [ $::hostname, $::fqdn ],
    type         => 'ecdsa-sha2-nistp256',
    key          => $::sshecdsakey,
  }

  # Collect:
  Host <<| |>>
  Sshkey <<| |>>

barman::autoconfigure

Adesso, siamo liberi di andare a configurare il server PostgreSQL e il server Barman. Con la possibilità di usare l’autoconfigure, questa parte diventa molto più semplice rispetto all’articolo precedente.

Per il nodo backup basta infatti indicare di usare l’autoconfigure e quale ip deve esportare. La macchina Vagrant infatti possiede due indirizzi ip e noi dobbiamo forzare l’utilizzo di 192.168.56.222. Inoltre, andiamo a usare il pacchetto di PGDG, abilitando manage_package_repo:

  class { 'barman':
    autoconfigure       => true,
    exported_ipaddress  => '192.168.56.222/32',
    manage_package_repo => true,
  }

Sul nodo pg provvediamo a installare il server PostgreSQL e a dichiarare come -il server PostgreSQL- debba essere gestito da Barman, attraverso la classe barman::postgres. Questa classe esporta il cron per l’esecuzione del comando barman backup pg e la configurazione del server per Barman, che saranno importati dal server backup tramite autoconfigure:

  # Configure PostgreSQL
  class { 'postgresql::server':
    listen_addresses     => '*',
  }

  # Export the parameters required by Barman
  class { 'barman::postgres':
    retention_policy        => 'RECOVERY WINDOW OF 1 WEEK',
    minimum_redundancy      => 1,
    last_backup_maximum_age => '1 WEEK',
    reuse_backup            => 'link',
    backup_hour             => 1,
    backup_minute           => 0,
  }

Test

È possibile testare tutto quello che abbiamo visto finora clonando il progetto su GitHub ed eseguendo, all’interno della directory creata:

$ librarian-puppet install --verbose
$ vagrant up
$ vagrant provision
$ vagrant provision

Il sistema deve infatti effettuare tre provision (il primo è all’interno del primo vagrant up) prima che tutte le risorse esportate siano raccolte dai nodi. A questo punto possiamo entrare nella macchina backup e controllare che il backup possa essere effettuato:

$ vagrant ssh backup
root@backup:~# barman backup all
Starting backup for server pg in /var/lib/barman/pg/base/20150320T114208
Backup start at xlog location: 0/2000028 (000000010000000000000002, 00000028)
Copying files.
Copy done.
Backup size: 18.7 MiB. Actual size on disk: 18.7 MiB (-0.00% deduplication ratio).
Asking PostgreSQL server to finalize the backup.
Backup end at xlog location: 0/20000B8 (000000010000000000000002, 000000B8)
Backup completed
Processing xlog segments for pg
        Older than first backup. Trashing file 000000010000000000000001 from server pg
        000000010000000000000002
        000000010000000000000002.00000028.backup

Conclusioni

Anche se la configurazione iniziale di un Puppet Master può essere laboriosa, i vantaggi sono enormi. Non solo la configurazione di Barman è più semplice, ma qualunque altra aggiunta all’infrastruttura ne risulta estremamente semplificata. Ad esempio, aggiungere un server Icinga o Nagios diventa molto più semplice quando ogni singolo server può esportare i servizi che devono essere monitorati (check_postgres o barman check --nagios).

Inoltre, nell’esempio abbiamo utilizzato un solo server PostgreSQL e un solo server Barman, ma in caso di infrastrutture complesse con molti database server è possibile dichiarare più server Barman e utilizzare host_group per identificare i server Postgres dei quali i diversi server Barman devono fare il backup.

Concludo con un ringraziamento speciale a Alessandro Franceschi per l’idea iniziale di aggiungere un sistema di autoconfigurazione al modulo Barman.

Puppet: configurazione

Dopo aver definito le macchine come nell’articolo precedente, occorre definire i moduli Puppet necessari, che lasceremo gestire a librarian-puppet.

I moduli sono due:

1. puppetlabs/postgresql (https://github.com/puppetlabs/puppetlabs-postgresql/) per installare PostgreSQL sulla VM pg
2. it2ndq/barman (https://github.com/2ndquadrant-it/puppet-barman) per installare Barman su backup.

Per il primo useremo la versione presente su Puppet Forge, per il secondo effettueremo il clone da GitHub del progetto, per poterne sfruttare le modifiche più recenti.

Creiamo nella directory corrente un file chiamato Puppetfile con questo contenuto:

forge "https://forgeapi.puppetlabs.com"
mod "puppetlabs/postgresql"
mod "it2ndq/barman", :git => "git://github.com/2ndquadrant-it/puppet-barman.git"

Possiamo ora installare il modulo Puppet e le sue dipendenze eseguendo:

$ librarian-puppet install --verbose

Per quanto non indispensabile, il consiglio è quello di utilizzare sempre l’opzione --verbose ogni volta che si usa librarian-puppet.  Il comando è molto silenzioso ed è bene avere dettagli su quello che sta facendo, prima di scoprire (ad esempio) di avere buttato tempo prezioso aspettando che risolvesse senza successo un complicato conflitto di dipendenze.

Al completamento del comando, all’interno della directory corrente sarà presente la nuova directory modules che conterrà i moduli richiesti (barman, postgresql) e le loro dipendenze (apt, concat, stdlib). Inoltre, sarà stato creato il file Puppetfile.lock, che identifica dipendenze e versioni dei moduli installati, fissandole a protezione di aggiornamenti futuri. In questo modo, i successivi librarian-puppet install installeranno le stesse identiche versioni definite nel Puppetfile.lock, invece di possibili aggiornamenti.

A questo punto, aggiungiamo alla configurazione di Vagrant il fatto di utilizzare un manifesto Puppet per effettuare il provisioning dei server al termine dell’installazione di Puppet.
Modifichiamo quindi il Vagrantfile in questo modo:

Vagrant.configure("2") do |config|
  {
    :pg => {
      :ip      => '192.168.56.221',
      :box     => 'ubuntu/trusty64'
    },
    :backup => {
      :ip      => '192.168.56.222',
      :box     => 'ubuntu/trusty64'
    }
  }.each do |name,cfg|
    config.vm.define name do |local|
      local.vm.box = cfg[:box]
      local.vm.hostname = name.to_s + '.local.lan'
      local.vm.network :private_network, ip: cfg[:ip]
      family = 'ubuntu'
      bootstrap_url = 'https://raw.github.com/hashicorp/puppet-bootstrap/master/' + family + '.sh'

      # Run puppet-bootstrap only once
      local.vm.provision :shell, :inline => <<-eos
        if [ ! -e /tmp/.bash.provision.done ]; then
          curl -L #{bootstrap_url} | bash
          touch /tmp/.bash.provision.done
        fi
      eos

      # Provision with Puppet
      local.vm.provision :puppet do |puppet|
        puppet.manifests_path = "manifests"
        puppet.module_path = [".", "modules"]
        puppet.manifest_file = "site.pp"
        puppet.options = [
         '--verbose',
        ]
      end
    end
  end
end

Con le linee che abbiamo appena aggiunto, abbiamo dato a Vagrant l’istruzione di effettuare il provision delle macchine a partire dal file manifests/site.pp e che i moduli necessari al manifesto sono da cercare all’interno della directory modules. Il Vagrantfile è adesso nella sua forma definitiva.
Creiamo perciò la directory manifests all’interno della nostra directory di lavoro:

$ mkdir manifests

Andiamo quindi a scrivere in manifests una prima versione di site.pp, iniziando con una configurazione di default.

node backup {
  class { 'barman': 
    manage_package_repo => true,
  }
}
node pg {}

A questo punto, avviamo le due macchine virtuali. Se le avete distrutte al termine dell’articolo precedente, tutto quello che serve è eseguire:

$ vagrant up

Altrimenti, se le macchine sono ancora attive, occorreranno i comandi:

$ vagrant reload
$ vagrant provision

Possiamo osservare che sulla VM è stato creato un server Barman con una configurazione di base, senza alcun server configurato, connettendoci

$ vagrant ssh backup

e controllando il contenuto del file /etc/barman.conf:

# Main configuration file for Barman (Backup and Recovery Manager for PostgreSQL)
# Further information on the Barman project at www.pgbarman.org
# IMPORTANT: Please do not edit this file as it is managed by Puppet!
# Global options

[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
backup_options = exclusive_backup
minimum_redundancy = 0
retention_policy =
retention_policy_mode = auto
wal_retention_policy = main
configuration_files_directory = /etc/barman.conf.d

Procediamo adesso ad avviare un server PostgreSQL sulla macchina virtuale pg, modificando il site.pp. Dobbiamo tenere presente di quali siano i parametri richiesti da Barman al server, per cui dovremo impostare:

• wal_level almeno a livello archive
• archive_mode a on
• archive_command in modo che i WAL possano essere copiati su Barman
• una regola nel pg_hba.conf per consentire l’accesso al database dal server backup

Tutti questi parametri sono facilmente configurabili attraverso il modulo puppetlabs/postgresql.

Inoltre, aggiungiamo i parametri minimi di configurazione per il backup del server PostgreSQL pg sul nodo backup. Avremo bisogno di:

• una stringa di connessione PostgreSQL
• un file .pgpass per l’autenticazione
• un comando per connettersi via SSH
• effettuare lo scambio delle chiavi SSH.

it2ndq-barman genera automaticamente una coppia di chiavi pubblica/privata in ~barman/.ssh/. Scambiare in automatico le chiavi richiede la presenza di un puppet master per l’esportazione delle risorse. Configurare un puppet master è fuori dagli obiettivi di questo tutorial (ma sarà parte del prossimo, incentrato proprio sull’utilizzo di un puppet master e della classe barman::autoconfigure), quindi questo ultimo passo sarà eseguito manualmente.
Modifichiamo quindi il file site.pp come segue:

node backup {
  class { 'barman': 
    manage_package_repo => true,
  }
  barman::server {'test-server':
    conninfo     => 'user=postgres host=192.168.56.221',
    ssh_command  => 'ssh postgres@192.168.56.221',
  }
  file { '/var/lib/barman/.pgpass':
    ensure  => 'present',
    owner   => 'barman',
    group   => 'barman',
    mode    => 0600,
    content => '192.168.56.221:5432:*:postgres:insecure_password',
  }
}

node pg {
  class { 'postgresql::server':
    listen_addresses  => '*',
    postgres_password => 'insecure_password',
    pg_hba_conf_defaults => false,
  }
  postgresql::server::pg_hba_rule {'Local access':
    type        => 'local',
    database    => 'all',
    user        => 'all',
    auth_method => 'peer',
  }
  postgresql::server::pg_hba_rule {'Barman access':
    type        => 'host',
    database    => 'all',
    user        => 'postgres',
    address     => '192.168.56.222/32',
    auth_method => 'md5',
  }
  postgresql::server::config_entry {
    'wal_level'       : value => 'archive';
    'archive_mode'    : value => 'on';
    'archive_command' : value => 'rsync -a %p barman@192.168.56.222:/var/lib/barman/test-server/incoming/%f';
  }
  class { 'postgresql::server::contrib':
    package_ensure => 'present',
  }
}

Avendo modificato la configurazione delle macchine, è necessario rieseguire il provision:

$ vagrant provision

Quando le macchine sono su, possiamo procedere allo scambio di chiavi. Connettiamoci a pg:

$ vagrant ssh pg

e creiamo le chiavi per l’utente postgres, lasciando vuoto ogni campo durante l’esecuzione di ssh-keygen (ovvero, premendo sempre invio):

vagrant@pg:~$ sudo -iu postgres
postgres@pg:~$ ssh-keygen
postgres@pg:~$ cat .ssh/id_rsa.pub

L’ultimo comando emette una lunga stringa alfanumerica che deve essere inserita nel file ~barman/.ssh/authorized_keys su backup.

$ vagrant ssh backup
vagrant@backup:~$ sudo -iu barman
barman@backup:~$ echo "ssh-rsa ..." >> .ssh/authorized_keys

Allo stesso modo, copiamo la chiave dell’utente barman nel file authorized_keys dell’utente postgres su pg:

barman@backup:~$ cat .ssh/id_rsa.pub
ssh-rsa ...
barman@backup:~$ logout
vagrant@backup:~$ logout
$ vagrant ssh pg
vagrant@pg:~$ sudo -iu postgres
postgres@pg:~$ echo "ssh-rsa ..." >> .ssh/authorized_keys

A questo punto, facciamo una prima connessione in entrambe le direzioni fra i due server:

postgres@pg:$ ssh barman@192.168.56.222
barman@backup:$ ssh postgres@192.168.56.221

Eseguiamo un barman check per verificare il corretto funzionamento di Barman:

barman@backup:~$ barman check all
Server test-server:
        ssh: OK
        PostgreSQL: OK
        archive_mode: OK
        archive_command: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)

Ogni riga dovrebbe risultare OK. Adesso, per effettuare un backup, basterà lanciare:

barman@backup:$ barman backup test-server

Una configurazione realistica

La configurazione di Barman effettuata finora è molto semplice, ma è facilmente possibile aggiungere pochi parametri al site.pp per sfruttare al meglio tutte le feature di Barman, come le retention policy e il nuovo backup incrementale disponibile con Barman 1.4.0.

Concludiamo questo tutorial con un caso d’uso realistico, in cui i requisiti sono:

• un backup ogni notte alle 1:00;
• avere la possibilità  di effettuare una Point In Time Recovery in un qualunque istante di tempo nell’ultima settimana;
• avere sempre almeno un backup disponibile;
• segnalare un errore durante barman check nel caso il backup più recente sia più vecchio di una settimana;
• attivare il backup incrementale per risparmiare spazio su disco.

Usiamo quindi la risorsa file di Puppet per creare un .pgpass con i parametri di connessione al db e la risorsa cron per generare il job da ripetere ogni notte. Infine, modifichiamo il barman::server per aggiungere i parametri Barman necessari.
Il risultato finale è quello che segue:

node backup {
  class { 'barman':
    manage_package_repo => true,
  }
  barman::server {'test-server':
    conninfo                => 'user=postgres host=192.168.56.221',
    ssh_command             => 'ssh postgres@192.168.56.221',
    retention_policy        => 'RECOVERY WINDOW OF 1 WEEKS',
    minimum_redundancy      => 1,
    last_backup_maximum_age => '1 WEEK',
    reuse_backup            => 'link',
  }
  file { '/var/lib/barman/.pgpass':
    ensure  => 'present',
    owner   => 'barman',
    group   => 'barman',
    mode    => 0600,
    content => '192.168.56.221:5432:*:postgres:insecure_password',
  }
  cron { 'barman backup test-server':
    command => '/usr/bin/barman backup test-server',
    user    => 'barman',
    hour    => 1,
    minute  => 0,
  }
}

node pg {
  class { 'postgresql::server':
    listen_addresses  => '*',
    postgres_password => 'insecure_password',
    pg_hba_conf_defaults => false,
  }
  postgresql::server::pg_hba_rule {'Local access':
    type        => 'local',
    database    => 'all',
    user        => 'all',
    auth_method => 'peer',
  }
  postgresql::server::pg_hba_rule {'Barman access':
    type        => 'host',
    database    => 'all',
    user        => 'postgres',
    address     => '192.168.56.222/32',
    auth_method => 'md5',
  }
  postgresql::server::config_entry {
    'wal_level'       : value => 'archive';
    'archive_mode'    : value => 'on';
    'archive_command' : value => 'rsync -a %p barman@192.168.56.222:/var/lib/barman/test-server/incoming/%f';
  }
}

Conclusioni

Con 51 righe esatte di Puppet siamo riusciti a configurare una coppia di server PostgreSQL/Barman con delle impostazioni molto simili a quelle che potremmo volere in produzione. Abbiamo così combinato i vantaggi dell’avere un server Barman che gestisce i backup a quelli di avere un’infrastruttura gestita da Puppet, riutilizzabile e versionabile.

Nella prossima e ultima parte di questa serie di articoli vedremo come utilizzare un Puppet master per approfittare della possibilità di esportare risorse fra macchine diverse e così permettere alle due macchine di scambiarsi i parametri necessari al loro corretto funzionamento tramite la classe barman::autoconfigure.

Obiettivo di questo articolo, sviluppato in tre parti, sarà quello di automatizzare l’installazione di Barman e di configurarlo, sempre in modo automatico, per eseguire backup da un server PostgreSQL di test.
Questa prima parte è un aggiornamento di quanto scritto da Gabriele, ma con l’idea di creare due macchine virtuali invece di una sola.

it2ndq/barman è il modulo rilasciato da 2ndQuadrant Italia per gestire l’installazione di Barman tramite Puppet. Il modulo ha licenza GPLv3 ed è disponibile su GitHub all’indirizzo https://github.com/2ndquadrant-it/puppet-barman.
La procedura che segue è stata scritta avendo come base una Ubuntu 14.04 Trusty Tahr, ma può essere eseguita in modo simile su altre distribuzioni.

I requisiti

Per avviare il modulo per Barman su una macchina virtuale, abbiamo bisogno dei seguenti software:

• VirtualBox
• Vagrant
• Ruby >= 1.9
• Puppet
• librarian-puppet

Vagrant

Vagrant è un software per la gestione di macchine virtuali in grado di supportare numerosi software di virtualizzazione, con VirtualBox come default.

Installiamo quindi VirtualBox:

$ sudo apt-get install virtualbox virtualbox-dkms

L’ultima versione di Vagrant può essere scaricata dal sito e poi installata con il comando:

$ sudo dpkg -i /path/to/vagrant_1.7.2_x86_64.deb

Ruby

Per Ruby, il consiglio è di utilizzare rbenv, che permette di crearsi un ambiente di sviluppo Ruby in cui specificare la versione da usare.
Per installare rbenv il suggerimento è quello di usare rbenv-installer (https://github.com/fesplugas/rbenv-installer) e configurare Ruby per l’utente corrente, evitando così di sporcare l’ambiente Ruby di sistema.
Scarichiamo ed eseguiamo lo script:

$ curl https://raw.githubusercontent.com/fesplugas/rbenv-installer/master/bin/rbenv-installer | bash

Al termine, lo script chiederà di aggiungere le seguenti linee all’interno del file ~/.bash_profile:

export RBENV_ROOT="${HOME}/.rbenv"

if [ -d "${RBENV_ROOT}" ]; then
  export PATH="${RBENV_ROOT}/bin:${PATH}"
  eval "$(rbenv init -)"
fi

Ricarichiamo il ~/.bash_profile appena modificato:

$ exec bash -l

A questo punto installiamo una versione di Ruby e impostiamo l’utente per usare questa versione invece che quella di sistema:

$ rbenv install 2.1.5
$ rbenv global 2.1.5

Puppet

È necessario avere Puppet installato sulla macchina host. Installiamo quindi la gemma relativa:

$ gem install puppet

librarian-puppet

Infine, librarian-puppet è un tool per automatizzare la gestione dei moduli Puppet. Anche librarian-puppet è installabile come gemma:

$ gem install librarian-puppet

Vagrant: configurazione

Adesso che abbiamo le dipendenze a posto, possiamo iniziare a scrivere le configurazioni di Vagrant e Puppet per il nostro sistema di backup.
Iniziamo creando una directory in cui lavorare:

$ mkdir ~/vagrant_puppet_barman
$ cd ~/vagrant_puppet_barman

Vagrant ha bisogno di un file chiamato Vagrantfile dove leggere le configurazioni delle VM da avviare. Procediamo quindi con la scrittura del file.

Il seguente Vagrantfile avvia due VM Ubuntu Trusty, chiamate pg e backup, con indirizzi ip rispettivamente 192.168.56.221 e 192.168.56.222. Su entrambe le macchine verrà effettuato un provisioning attraverso uno script shell definito inline all’interno del file stesso. Tale script lancia puppet-bootstrap (https://github.com/hashicorp/puppet-bootstrap), uno script che automaticamente installa e configura Puppet su vari tipi di macchine. Siccome non è necessario che sia eseguito più di una volta, nello script inline è stato inserito un test per evitare ulteriori esecuzioni.

Vagrant.configure("2") do |config|
  {
    :pg => {
      :ip      => '192.168.56.221',
      :box     => 'ubuntu/trusty64'
    },
    :backup => {
      :ip      => '192.168.56.222',
      :box     => 'ubuntu/trusty64'
    }
  }.each do |name,cfg|
    config.vm.define name do |local|
      local.vm.box = cfg[:box]
      local.vm.hostname = name.to_s + '.local.lan'
      local.vm.network :private_network, ip: cfg[:ip]
      family = 'ubuntu'
      bootstrap_url = 'https://raw.github.com/hashicorp/puppet-bootstrap/master/' + family + '.sh'

      # Run puppet-bootstrap only once
      local.vm.provision :shell, :inline => <<-eos
        if [ ! -e /tmp/.bash.provision.done ]; then
          curl -L #{bootstrap_url} | bash
          touch /tmp/.bash.provision.done
        fi
      eos
    end
  end
end

Un test

Abbiamo definito due Ubuntu contenenti Puppet. Questo non è ancora il Vagrantfile definitivo, ma già permette di creare le due macchine. Se siete curiosi, è possibile controllare che le due macchine siano effettivamente create con il comando:

$ vagrant up

e successivamente connettersi usando i comandi:

$ vagrant ssh pg
$ vagrant ssh backup

Infine, le macchine possono essere distrutte con:

$ vagrant destroy -f

Prime conclusioni

In questa prima parte di tutorial abbiamo visto come configurare le dipendenze e siamo arrivati ad avere le due macchine virtuali su cui andare a installare, via Puppet, PostgreSQL e Barman.
La scrittura del manifesto Puppet per l’installazione vera e propria sarà argomento del prossimo articolo, in uscita la prossima settimana. A presto!

Nella puntata precedente, abbiamo introdotto la funzionalità di replica logica aggiunta a PostgreSQL 9.4. Continuiamo a nuotare nel mare di novità che la versione 9.4 porta nel campo Operation, con l’obiettivo di migliorare la gestione di database PostgreSQL per amministratori di sistema e DBA.

pg_prewarm

pg_prewarm è una nuova estensione per risolvere il problema dei rallentamenti post riavvio. In seguito a un riavvio di PostgreSQL, infatti, i buffer sono cancellati e Postgres non è più in grado di ritrovare subito in RAM i dati richiesti. Con pg_prewarm è possibile caricare in memoria una tabella importante immediatamente dopo il riavvio con un semplice

SELECT pg_prewarm('my_table');

In questo modo non sarà più necessario avere un database a regime per avere i dati in cache.

Gestione dei tablespace

Due piccole novità sono state introdotte per semplificare l’uso dei tablespace. La prima è l’introduzione del comando ALTER TABLESPACE … MOVE, che permette di spostare tabelle, indici e viste materializzate da un tablespace a un altro. La seconda è la sintassi CREATE TABLESPACE … WITH … options con cui diventa possibile impostare opzioni del tablespace direttamente in fase di creazione, risparmiando un secondo ALTER TABLESPACE. I due parametri possibili al momento sono seq_page_cost e random_page_cost, che possono essere utili al planner per comprendere quali dischi sono più veloci di altri.

CREATE TABLESPACE new_tblspc LOCATION 'my_dir' WITH random_page_cost = 1;
ALTER TABLESPACE old_tblspc MOVE TABLES TO new_tblspc;

Monitoraggio dell’archiviazione dei WAL

Nel corso del nostro lavoro su Barman in 2ndQuadrant, più volte ci siamo trovati a stimare la produzione di WAL di un server. Per questo, il nostro Gabriele Bartolini ha sviluppato questa patch. Adesso è possibile avere a disposizione una tabella di statistiche sull’attività dell’archiver che mostra il numero di WAL archiviati nel tempo (e, eventualmente, di archiviazioni fallite).

SELECT * FROM pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
 archived_count     | 4
 last_archived_wal  | 00000001000000000000000B
 last_archived_time | 2014-10-07 08:58:02.258657+00
 failed_count       | 0
 last_failed_wal    |
 last_failed_time   |
 stats_reset        | 2014-10-07 08:51:29.852523+00

Standby ritardati nel tempo

Se uno standby è uno strumento utile nel caso di crash del master, è totalmente inutile nel caso di un errore umano. Una “DROP TABLE” errata da parte dell’amministratore e diventa immediatamente necessaria una Point In Time Recovery. La possibilità di avere un server in replica che applichi i cambiamenti con un certo ritardo concede invece un intervallo di tempo per fermare la replica, evitando che l’errore si propaghi.

Per impostare ad esempio uno standby in ritardo di almeno un’ora, basta impostare nel recovery.conf:

min_recovery_apply_delay = 1h

Modifica della configurazione

Un altro nuovo comando introdotto nella 9.4 per rendere più comodo lavorare con PostgreSQL è ALTER SYSTEM. Adesso è diventato possibile cambiare il file postgresql.conf da dentro una connessione SQL. Con le sole eccezioni dei parametri impostabili in fase di compilazione e della PGDATA, gli altri possono essere modificati come dell’esempio successivo:

ALTER SYSTEM SET wal_level = hot_standby;

I nuovi valori saranno scritti nel file postgresql.auto.conf. Resta necessario eseguire un reload o un riavvio per i parametri che lo richiedono.

Performance dei WAL

Per quanto riguarda i WAL, non ci sono comandi nuovi, ma miglioramenti delle prestazioni. È stata ridotta la lock contention per le operazioni di insert nei WAL. Inoltre, è stata diminuita la dimensione dei WAL record generati dalle UPDATE. In questo modo sarà possibile effettuare più scritture impiegando minore I/O.

Conclusioni

Per noi amministratori di sistemi Linux e DBA, PostgreSQL 9.4 migliora ulteriormente la gestione ordinaria di database in continuità operativa. Inoltre, pone le basi per aprire nuovi orizzonti in ambito architetturale, fra cui la replica multi-master. Grazie alla replica logica, infatti, vedremo sicuramente gli strumenti di replica basati su trigger (come Londiste, Slony, Bucardo) aggiungere il supporto alla decodifica degli eventi direttamente da replication slot logici, rendendo molto più snella la gestione della replica. Se siete interessati, potete seguire il nostro blog con l’articolo di Giuseppe sulle novità per sviluppatori. A presto!

La versione 9.4 di PostgreSQL, in uscita fra pochi giorni, presenta numerose piccole novità per gli amministratori, in aggiunta all’introduzione del supporto alla replica logica, ovvero il primo passo verso una futura implementazione di replica multi-master in PostgreSQL. In questo articolo in due parti mostreremo le principali novità per gli amministratori, partendo proprio dalla replica logica, costituita da un insieme di nuove feature:

• Replication slot fisici
• WAL level “logical”
• Replication slot logici
• Decodifica logica
• Replica identity

Lo sviluppo di queste funzionalità è un frutto diretto del lavoro effettuato da 2ndQuadrant (e, in particolare, da Andres Freund) all’interno del progetto BDR (BiDirectional Replication), una soluzione open source di replica multi-master basata su PostgreSQL, il cui codice è progressivamente incluso nel core di Postgres con l’obiettivo di diventarne parte integrante nei prossimi anni.

Nel prossimo articolo, vedremo le altre novità dedicate agli amministratori.

Replication slot fisici

I replication slot fisici sono una struttura che mantiene la memoria dello stato di uno standby e dei WAL a questo necessari, anche quando lo standby è offline. In questo modo non è più indispensabile dover stimare wal_keep_segments o configurare il continuous archiving. Utili quindi per server in replica fisica, sono poi indispensabili alla replica logica, dove i server potrebbero avere contenuti differenti non replicati e non sarebbe così possibile ricostruire il server da zero.

Maggiori informazioni in un articolo di Craig Ringer sui replication slot di PostgreSQL 9.4.

Wal level “logical”

È stato introdotto il parametro wal_level = logical. Usando questa impostazione i WAL file avranno una dimensione leggermente maggiore di quanta ne abbiano adesso in hot_standby, ma conterranno le infomazioni necessarie al funzionamento della decodifica logica.

Replication slot logici

Una volta impostato wal_level = logical all’interno del postgresql.conf, sarà possibile iniziare a usare i replication slot logici. Simili come idea ai replication slot fisici, a differenza di questi operano su una singola base di dati, e inviano la sequenza di cambiamenti avvenuti su di essa.

Decodifica logica

La decodifica logica usa i replication slot e dei plugin di decodifica per inviare e rendere comprensibili a elementi esterni i cambiamenti avvenuti all’interno del db. Per la visualizzazione sono state sviluppate le funzioni pg_logical_slot_get_changes e pg_logical_slot_peek_changes, con la differenza che la prima consuma il cambiamento nella coda e la seconda lo legge soltanto. L’output della funzione dipende dal plugin usato per creare lo slot. Al momento ne sono stati sviluppati tre:

• test_decoding, il plugin di default;
• wal2json, che mostra i cambiamenti avvenuti in formato JSON;
• decoder_raw, che ricostruisce le query che hanno applicato la modifica.

Replica identity

REPLICA IDENTITY è un nuovo parametro a livello di tabella. Identifica quali informazioni devono essere scritte nei WAL per identificare le tuple modificate o rimosse se è impostato wal_level = logical. Esistono 4 valori:

• DEFAULT: scrive la precedente chiave primaria della tupla se è stata modificata
• USING INDEX idx: scrive le informazioni dell’indice usato, che deve essere UNIQUE, non parziale e NOT NULL.
• FULL: scrive tutte le colonne della vecchia tupla. Utile se manca una chiave primaria.
• NOTHING: non scrive informazioni sul vecchio record. È il default per le tabelle di sistema.

Utilizzando le funzioni e i plugin di decodifica, è possibile scrivere i propri consumer e rimuovere la dipendenza dei propri database da soluzioni di replica basate su trigger, ben più pesanti.

Il blog di Michael Paquier contiene un esempio di codice SQL che mostra l’uso della replica logica.

Conclusioni

Nella prossima parte, ci occuperemo delle altre novità principali nel campo Operation di PostgreSQL 9.4, fra cui pg_prewarm, tablespace, standby in ritardo, gestione dei WAL, ecc… Alla prossima puntata!