Tra le novità introdotte da PostgreSQL 10 sicuramente una delle più importanti è l’implementazione della replica logica nel core di Postgres.

Molti di voi avranno già provato o almeno sentito parlare di pglogical, estensione di PostgreSQL che fornisce il supporto per la replica logica, che, grazie al logical decoding dei WAL (presente dalla 9.4), permette di replicare una selezione di tabelle da un database ad un altro.

In questo articolo vedremo come adesso questo è possibile con PostgreSQL 10 e quali sono le differenze con pglogical. Inoltre, viene assunto che il lettore abbia una preparazione di base sulle nozioni di amministrazione di PostgreSQL e di manipolazione dei file di configurazione e di sicurezza.

Cos’è?

La replica logica si basa sull’architettura Pubblicazione/Sottoscrizione, si differenziano quindi due ruoli: un publisher e un subscriber. A differenza di quella fisica nella quale vengono replicate tutte le modifiche a livello binario, la replica logica permette di filtrare i cambiamenti da replicare scegliendo le tabelle e una combinazione di operazioni su di esse tra UPDATE, INSERT e/o DELETE. Per esempio è possibile replicare solo le UPDATE di una singola tabella da un server ad un altro a scopo di analisi e/o di aggregazione dei dati.

Come funziona

Sul publisher viene definita una “pubblicazione”, ovvero una selezione delle tabelle (o tutte) di un database a cui vengono associate le operazioni da replicare ai subscriber. Grazie alla decodifica logica dei WAL le operazioni sono ricostruite traducendo il codice binario e vengono selezionate solo quelle definite nella “pubblicazione” per essere trasmesse ai subscriber.

Configurazione

L’installazione di default predispone già impostate alcune delle opzioni necessarie a configurare la replica logica.

Per quanto riguarda il publisher:

• max_replication_slots = 10
  Almeno uno per ogni subscriber più alcuni per l’inizializzazione delle tabelle.
• max_wal_senders = 10
  Almeno uno per ogni replication_slot più quelli necessari per le repliche fisiche (es.: Barman).

Per quanto riguarda il subscriber:

• max_logical_replication_workers = 4
  Uno per sottoscrizione, più alcuni da considerare per la sincronizzazione delle tabelle.
• max_worker_processes = 10
  Almeno uno per ogni replication workers più uno.

[NOTA] Queste però non bastano per attivare la replica logica. Quello che manca, infatti, sono le informazioni all’interno del codice dei WAL necessarie per ricostruire le operazioni da filtrare e inviare ai subscriber. Per ottenere queste informazioni è necessario impostare sul publisher il parametro wal_level a logical e avviare (o riavviare) il servizio.

L’unico cambiamento che dobbiamo apportare è il seguente parametro sul publisher pippo-pg10:

• wal_level = logical

I valori preimpostati sono più che sufficienti per questo test. Tuttavia, va tenuto conto che sono parametri che vengono valorizzati all’avvio del servizio e quindi ogni ulteriore cambiamento viene applicato solo dopo un nuovo riavvio. In questa ottica è consigliabile impostare valori adeguati al numero di pubblicazioni e sottoscrizioni previsti, prima di dover riavviare il servizio in produzione.

Preparazione

Consideriamo un caso reale di utilizzo della replica logica, prendendo di esempio un cluster con due server PostgreSQL 10:

• pippo-pg10, ip: 192.168.42.110, publisher
• paperino-pg10, ip: 192.168.42.210, subscriber

Sul server pippo-pg10 sono presenti le tabelle che devono essere replicate su un database del server paperino-pg10, precedentemente create con pgbench, un tool molto utile per misurare le prestazioni di PostgreSQL.

[root@pippo-pg10 ~]# sudo -iu postgres
-bash-4.2$ psql

psql (10beta4)
Type "help" for help.
postgres=# \c pgbench
You are now connected to database "pgbench" as user "postgres".
pgbench=# \d
               List of relations
 Schema |       Name       | Type  |   Owner
--------+------------------+-------+------------
 public | pgbench_accounts | table | user_bench
 public | pgbench_branches | table | user_bench
 public | pgbench_history  | table | user_bench
 public | pgbench_tellers  | table | user_bench
(4 rows)

Creazione Tabelle

A causa delle restrizioni sulla replicazione non è possibile replicare la definizione di una tabella tanto meno di un database. Per questo occorre replicare manualmente la definizione sul server di sottoscrizione, per esempio partendo da un dump dello schema del database pgbench dal publisher:

-bash-4.2$ pg_dump -s -f pgbench.sql

Quindi creare l’utente e il database di destinazione nell’istanza PostgreSQL di paperino-pg10 (operazioni assenti nello script appena estratto):

[root@paperino-pg10 ~]# sudo -iu postgres
-bash-4.2$ createuser user_bench
-bash-4.2$ createdb pgbench -O user_bench

[NOTA] Non è necessario che l’utente e il database abbiano lo stesso nome, ma in questo caso si utilizza lo stesso nome per semplicità.

Adesso è possibile applicare lo script SQL sul server paperino-pg10:

-bash-4.2$ psql -d pgbench  < pgbench.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

Creazione Ruolo Replicazione

Nell’istanza pippo-pg10 è necessario creare il ruolo dedicato alla replicazione e definire opportunamente la regola nel pg_hba.conf che permetta la connessione dal subscriber paperino-pg10.

pgbench=# create role replica superuser replication;
CREATE ROLE

Creazione Pubblicazione

Una pubblicazione è definita come un set di cambiamenti su una o più tabelle appartenenti ad un solo database. Si possono creare più pubblicazioni in un solo database scegliendo di replicare una tra le operazioni di INSERT, UPDATE e DELETE o una loro combinazione. Ogni tabella può appartenere a più pubblicazioni e una singola pubblicazione può avere più sottoscrizioni.

In questo esempio assumiamo di voler replicare tutte le operazioni di modifica su tutte le tabelle di un database. Per creare questo tipo di pubblicazione è necessario essere superutenti (maggiori dettagli):

pgbench=# CREATE PUBLICATION pgbench_alltables FOR ALL TABLES;
CREATE PUBLICATION

Creazione Sottoscrizione

Una sottoscrizione viene definita su un nodo PostgreSQL, chiamato subscriber, con una connection string verso il server publisher e una lista di una o più pubblicazioni alle quali sottoscriversi. Il nome della sottoscrizione viene trasmesso al publisher come riconoscimento della connessione se non è definito il campo application_name nella stringa di connessione.

Per creare una sottoscrizione è necessario essere superutenti (maggiori dettagli).

Su paperino-pg10:

pgbench=# CREATE SUBSCRIPTION pgbench_rep CONNECTION 'dbname=pgbench host=192.168.42.110 user=replica' PUBLICATION pgbench_alltables;
NOTICE:  created replication slot "pgbench_rep" on publisher
CREATE SUBSCRIPTION

[NOTA] Un replication slot viene creato per ogni sottoscrizione, più uno temporaneo per la sincronizzazione iniziale dei dati.

Adesso è possibile controllare che l’inizializzazione dei dati nel subscriber rispecchi la situazione nel publisher:

sul publisher pippo-pg10

postgres=# \c pgbench
You are now connected to database "pgbench" as user "postgres".
pgbench=# select * from pg_publication;
      pubname      | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-------------------+----------+--------------+-----------+-----------+-----------
 pgbench_alltables |       10 | t            | t         | t         | t
(1 row)

pgbench=# select * from pgbench_branches limit 5;
 bid | bbalance | filler
-----+----------+--------
   1 |        0 |
   2 |        0 |
   3 |        0 |
   4 |        0 |
   5 |        0 |
(5 rows)

pgbench=# select * from pgbench_tellers limit 5;
 tid | bid | tbalance | filler
-----+-----+----------+--------
   1 |   1 |        0 |
   2 |   1 |        0 |
   3 |   1 |        0 |
   4 |   1 |        0 |
   5 |   1 |        0 |
(5 rows)

e sul subscriber paperino-pg10

postgres=# \c pgbench
You are now connected to database "pgbench" as user "postgres".
pgbench=# select * from pg_subscription;
 subdbid |   subname   | subowner | subenabled |                   subconninfo                   | subslotname | subsynccommit |   subpublications
---------+-------------+----------+------------+-------------------------------------------------+-------------+---------------+---------------------
   16532 | pgbench_rep |       10 | t          | dbname=pgbench host=192.168.42.110 user=replica | pgbench_rep | off           | {pgbench_alltables}
(1 row)

pgbench=# select * from pgbench_branches limit 5;
 bid | bbalance | filler
-----+----------+--------
   1 |        0 |
   2 |        0 |
   3 |        0 |
   4 |        0 |
   5 |        0 |
(5 rows)

pgbench=# select * from pgbench_tellers limit 5;
 tid | bid | tbalance | filler
-----+-----+----------+--------
   1 |   1 |        0 |
   2 |   1 |        0 |
   3 |   1 |        0 |
   4 |   1 |        0 |
   5 |   1 |        0 |
(5 rows)

Le tabelle sul server paperino-pg10 sono state inizializzate!

Test Replica

Il test consiste nel verificare che le modifiche apportate alle tabelle sul server pippo-pg10 siano effettivamente replicate su paperino-pg10.

Su pippo-pg10:

pgbench=# BEGIN;
BEGIN
pgbench=# SELECT * FROM pgbench_accounts WHERE aid = 1;
 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |
(1 row)

pgbench=# UPDATE pgbench_accounts SET filler = 'Jonny was here' WHERE aid = 1;
UPDATE 1
pgbench=# select * from pgbench_accounts where aid = 1;
 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 | Jonny was here
(1 row)
pgbench=# COMMIT;
COMMIT
pgbench=#

Una volta eseguito il COMMIT nel WAL viene scritta l’operazione UPDATE appena eseguita e replicata al subscriber:

pgbench=# select * from pgbench_accounts where aid = 1;
 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 | Jonny was here
(1 row)

Attenzione! Le operazioni di UPDATE e DELETE sono replicate al subscriber solo sulle tabelle che possiedono una REPLICA IDENTITY, che di default è la chiave primaria. La tabella history di pgbench non sarebbe stata replicata in caso avessimo creato una pubblicazione limitata alle UPDATE e/o DELETE, perché non possiede una chiave primaria.

Considerazioni Conclusive

Replica Logica vs. Replica Fisica

La replica logica a differenza della replica fisica di un’istanza in hot-standby mode, non è da considerarsi un’architettura Master/Standby in quanto il subscriber è egli stesso un master, permettendo quindi le scritture su tutte le tabelle. Può essere comunque utilizzata in maniera simile usando utenti con permessi di sola lettura sul subscriber fino al prossimo switchover.

Per l’architettura multi-master, nella quale le modifiche vengono effettuate concorrentemente su più nodi, si rimanda alla lettura del sistema BDR.

Questa tecnologia permette di migrare da una major version di PostgreSQL ad un’altra, al contrario della replica fisica a causa dell’incompatibilità binaria tra differenti versioni.

Differenze con pglogical

L’implementazione nel core di PostgreSQL 10 della replica logica è un’eredità del progetto pglogical, che, essendo più maturo, possiede molte funzionalità in più. Tra le caratteristiche più importanti di pglogical vi è la possibilità di replicare le modifiche allo schema di un database e le DDL, quindi le definizioni delle tabelle. Inoltre è possibile configurare la risoluzione dei conflitti, mentre con PostgreSQL 10, in caso di conflitto la replica logica si interrompe ed è necessario l’intervento umano per la sua risoluzione. Un’altra funzionalità che rende pglogical molto potente è la possibilità di replicare specifiche righe e/o colonne di una tabella.

Commenti

L’inizializzazione dei dati nelle tabelle del subscriber è una funzionalità molto utile, ma è possibile scegliere di non effettuarla durante la creazione della sottoscrizione.

Anche la replica logica può essere sottoposta alle stesse configurazioni di sincronizzazione delle transazioni tra standby e master, in modo da avere una perdita dati zero. Sono sufficienti gli stessi parametri della replica fisica per ottenere la sincronizzazione tra master e standby.

Nell’esempio di questo articolo, per attivare la replica logica sincrona è sufficiente configurare i seguenti parametri:

synchronous_commit = remote_apply
synchronous_standby_names = pgbench_rep

e riavviare PostgreSQL.

Il valore del parametro synchronous_standby_name corrisponde al nome della sottoscrizione. Maggiori dettagli sulla configurazione del parametro synchronous_standby_names.

Adesso mi rimane una domanda: qual è la differenza di velocità fra la replica fisica e quella logica? Potrebbe essere l’interessante argomento del mio prossimo articolo.

Il FOSS4G Nord America si terrà presso l’Hyatt Regency San Francisco Airport a Burlingame, California, dal 9 al 12 marzo 2015.

L’evento riunisce sviluppatori, utenti, decisori e osservatori da un ampio spettro di organizzazioni e campi di attività. Attraverso quattro giorni di workshop, presentazioni, discussioni, offre opportunità per la comunità FOSS4G per imparare, esplorare, condividere e collaborare sulle ultime idee e informazioni.

Martedì 10, alle 16.15, Gianni parlerà di decodifica logica, una importante caratteristica introdotta con la versione 9.4 di PostgreSQL.

Dopo una breve introduzione, Gianni si concentrerà sull’applicazione della decodifica logica per il controllo specifico di un database, confrontando questa soluzione con l’approccio tradizionale in termini di:

• semplicità
• prestazioni
• separazione tra dati in tempo reale e dati di audit

Per maggiori informazioni sull’evento: https://2015.foss4g-na.org

Nuove funzionalità aumenteranno flessibilità, scalabilità e prestazioni di PostgreSQL!

Flessibilità

Il nuovo tipo di dato JSONB di PostgreSQL 9.4, supporterà lookup veloci e query di ricerca semplici utilizzando gli indici GIN (Generalized Inverted Index). Gli utenti potranno estrarre e manipolare dati JSON con prestazioni che uguagliano e migliorano i database più comuni per la gestione di documenti.

Scalabilità

La decodifica logica (Logical Decoding) della versione 9.4, fornirà una nuova API per leggere, filtrare e manipolare il flusso di replica di PostgreSQL. Nuovi strumenti di replica, come la Replica Bi-Direzionale (BDR), e altri miglioramenti, come replication slot e standby in ritardo (time-delayed), semplificheranno la gestione e aumenteranno l’utilità dei server in replica.

Prestazioni

La versione 9.4 introduce miglioramenti che permetteranno agli utenti di sfruttare ancora di più i loro server PostgreSQL, fra cui:

• Indici GIN fino al 50% più piccoli e fino a 3 volte più veloci
• Viste Materializzate aggiornabili in modalità  concorrente per reportistica più veloce e più aggiornata
• Ricaricamento veloce della cache di un database PostgreSQL dopo un restart grazie a pg_prewarm
• Scrittura in parallelo più veloce sul log delle transazioni di PostgreSQL

Vuoi saperne di più?

Per ulteriori informazioni e spiegazioni sulle funzionalità aggiunte in PostgreSQL 9.4, si consiglia la consultazione del press kit ufficiale rilasciato dalla Comunità.

Segui inoltre la nostra serie di articoli in italiano su PostgreSQL 9.4, a partire dalle novità dedicate agli amministratori di sistema.

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!