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.

PostgreSQL 9 comprende il supporto nativo per la replica e una dozzina (e oltre) di nuove funzionalità principali in grado di attirare chiunque, dagli sviluppatori web agli hacker più accaniti.

Un numero così elevato di nuove funzionalità major non si era mai verificato in un singolo rilascio di PostgreSQL. Fra le principali novità, sono degne di citazione:

• Hot standby
• Streaming replication
• In-place upgrade
• Supporto a 64-bit per Windows
• Gestione di massa per i privilegi
• Blocchi anonimi e parametri nominali nella chiamata a stored procedure
• Nuove funzioni finestra e aggregati ordinati

… e molte altre ancora.

Per un elenco più dettagliato delle oltre 200 aggiunte e migliorie di questa versione, portata avanti da oltre un centinaio di sviluppatori, si prega di far riferimento alle note di rilascio.

"Questo genere di funzionalità aggiuntive continuano spiegano il perché attività tecnologiche di tipo mission critical possano contare sulla potenza, flessibilità e robustezza di PostgreSQL" Ram Mohan, CTO di Afilias.

Ulteriori informazioni su PostgreSQL 9.0:

• Note di rilascio (in inglese)
• Presskit
• Guida alla 9.0 (in inglese)

Scarica PostgreSQL 9.0 adesso:

• Pagina principale per il download
• Sorgenti
• Pacchetti binari
• One-Click Installer, comprendente binari per Windows

È da notare che, in seguito a una modifica nel catalogo di sistema, è necessario effettuare di nuovo initdb e ricaricare i database in caso di aggiornamento dalle versioni Beta precedenti. Cogliamo l’occasione per incoraggiare gli utenti a sfruttare questa opportunità per testare il funzionamento di pg_upgrade per l’aggiornamento da una versione 9.0 beta o da una versione precedente alla 9.0. Vi preghiamo di riportare i risultati ottenuti.

Se sei in grado di aiutare nella fase di beta testing, sei pregato di visitare la pagina con le istruzioni sul Beta testing di PostreSQL.

Nessun cambiamento nei comandi, nelle interfacce e nelle API sono attesi fra questa release e la versione finale. Le applicazioni che verranno messe in produzione con la versione 9.0 di PostgreSQL possono (e dovrebbero) essere provate con la versione 9.0rc1. Il rilascio di ulteriori versioni release candidate prima della versione finale dipenderà dal feedback degli utenti e dai bug che saranno segnalati dagli utilizzatori.

Il codice sorgente, insieme a installer binari per diverse piattaforme, sono a disposizione dal sito di PostgreSQL:

• Sorgente
• One-Click Installer, comprendente binari Win64
• Binari per altre piattaforme
• Release Note
• Partecipare alla fase di test

Streaming replication (che potrebbe essere tradotta in replica a flusso continuo) è un’altra funzionalità, complementare a Hot Standby, che permette a PostgreSQL di fare un grande balzo in avanti.

Nonostante infatti ci siano già soluzioni di terze parti per la replica di database PostgreSQL in grado di soddisfare esigenze specifiche, la nuova release porterà una versione semplice, solida e soprattutto integrata per la replica master-slave che sarà molto probabilmente usata di default nelle installazioni in alta disponibilità (high availability, con l’accoppiata Hot Standby e Streaming replication).

L’obiettivo di streaming replication è quello di migliorare il meccanismo di archiviazione, rendendolo il più continuo possibile (senza dover attendere la spedizione dei file di log). I server in standby possono adesso connettersi al server primario (master) in modo da farsi spedire le informazioni necessarie dal Write Ahead Log (WAL).

Ciò avviene sulla base non di file interi (WAL segments, tipici della replica asincrona basata su log shipping), ma in termini di singoli record per il WAL (che possono essere considerati dei veri e propri "frammenti" di questi file).

Streaming Replication è un meccanismo asincrono e il server in standby non è garantito essere sempre in pari e aggiornato con il master. A differenza di altri metodi di replica però, il ritardo è molto leggero e può anche essere piccolo come una singola transazione (anche se ciò dipende dalla velocità della rete, dall’attività del database e dai settaggi di hot standby). Inoltre, il carico sul server master è minimo, garantendo pertanto il supporto di decine di server slave.

I database primario e standby sono identici per quanto riguarda i dati memorizzati a basso livello (a dire il vero sono quasi identici, ma non vi preoccupate se i file di dati non hanno le stesse checksum).

Per abilitare Streaming Replication, wal_level dovrebbe essere impostato a 'archive' o 'hot standby' in modo da attivare l’archiviazione continua.

Sul primario, nel file postgresql.conf:

max_wal_senders = 5 # Numero massimo di processi 'wal_senders'
# responsabili della gestione di una connessione
# con un server standby
wal_keep_segments = 10 # Numero di segmenti (file) WAL che il master
# deve tenere per emergenza all'interno di pg_xlog
# nel caso in cui il server in streaming replication
# rimanga indietro e necessiti di recuperare il file WAL
# per scopi di replica
# wal_level
wal_level=hot_standby # attiva anche hot standby e le connessioni in lettura
#wal_level=archive # abilita invece sola replica, senza hot standby

Sul/sui server in standby, all’interno del file recovery.conf:

standby_mode = 'on'
# Stringa di connessione per raggiungere il database primario
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'

Sul/sui server in standby, all’interno del file postgresql.conf:

# wal_level (stesso valore impostato sul master, in caso di failover)
wal_level=hot_standby # attiva anche hot standby e le connessioni in lettura
#wal_level=archive # abilita invece sola replica, senza hot standby
hot_standby=on/off # attivare hot standby

All’interno del file pg_hba.conf, deve esserci una entry per l’abilitazione delle connessioni di replica. Il database fasullo è replication e l’utente disegnato dovrebbe essere superuser. Fate attenzione a non allargare l’accesso a questo utente in quanto molte informazioni privilegiate e protette possono essere estratte dai record WAL.

Sul PostgreSQL primario, nel file pg_hba.conf, inserire:

host replication foo 192.168.1.100/32 md5

Allo stesso modo di Hot Standby anche questa funzionalità è ricca e complessa. E’ consigliato leggere la documentazione di PostgreSQL. Inoltre, è ottima prassi mettere in pratica procedure di test per il failover e lo switchover prima di andare in produzione.

Una cosa da tenere presente è che si può utilizzare congiuntamente Hot Standby e Streaming Replication. Ciò significa che è possibile ottenere una quasi immediata replica sui nodi standby ed eseguire query su di essi (come query di reportistica). Non è da escludere infine un utilizzo combinato delle soluzioni: un server in replica può essere soltanto Hot Standby (con log shipping) e un altro con solo Streaming Replication (senza accettare query in sola lettura).

Nota: Ricordo che questo speciale sulla versione 9 è fortemente ispirato dal wiki di PostgreSQL e in molti casi ne rappresenta una fedele traduzione in lingua italiana.

Alcuni cambiamenti significativi sono stati introdotti nella versione Beta2, che richiederà ad alcune applicazioni di eseguire di nuovo il test per la 9.0. Questi includono:

• Correzioni per questioni di sicurezza, come nell’annuncio del 17 maggio scorso
• Modifiche alla sintassi per parametri nominali
• aggiunta di pg_upgrade ai moduli addizionali (contrib)
• Modifiche a parametri e opzioni nel file recovery.conf
• diverse correzioni in seguito a segnalazioni riportati con la versione Beta1

E’ da notare che, in seguito a una modifica nel catalogo di sistema, è necessario effettuare di nuovo initdb e ricaricare i database in caso di aggiornamento dalla versione Beta1. Cogliamo l’occasione per incoraggiare gli utenti a sfruttare questa opportunità per testare il funzionamento di pg_upgrade per l’aggiornamento dalla Beta1 o da una versione precedente alla 9.0. Vi preghiamo di riportare i risultati ottenuti.

Se sei in grado di aiutare nella fase di beta testing, sei pregato di visitare la pagina con le istruzioni sul Beta testing di PostreSQL.

Le versioni beta non sono stabili per natura e non dovrebbero mai essere utilizzate in ambienti di produzione; sono esclusivamente ad uso di test. Altre versioni beta oppure una versione di tipo "release candidate" saranno disponibili fra alcune settimane.

Il codice sorgente, insieme a installer binari per diverse piattaforme, sono a disposizione dal sito di PostgreSQL:

• Sorgente
• One-Click Installer, comprendente binari Win64
• Binari per altre piattaforme
• Release Note
• Partecipare alla fase di test