Come impostare la replica Master-Slave per PostgreSQL 9.6 su Ubuntu 16.04

PostgreSQL o Postgres è un sistema di gestione di database relazionali a oggetti open source (ORDBMS) con oltre 15 anni di sviluppo attivo. È un potente server di database e può gestire carichi di lavoro elevati. PostgreSQL può essere utilizzato su server Linux, Unix, BSD e Windows.

La replica del database master/slave è un processo di copia (sincronizzazione) dei dati da un database su un server (il master) a un database su un altro server (gli slave). Il principale vantaggio di questo processo è distribuire i database su più macchine, in modo che quando il server master ha un problema, ci sia una macchina di backup con gli stessi dati disponibili per gestire le richieste senza interruzioni.

PostgreSQL fornisce diversi modi per replicare un database. Può essere utilizzato per scopi di backup e per fornire un server di database ad alta disponibilità. In questo tutorial, ti mostreremo come installare e configurare la replica Master-Slave di PostgreSQL 9.6 su un server Ubuntu 16.04. Utilizzeremo la modalità Hot standby, ed è un ottimo punto di partenza per imparare PostgreSQL in profondità.

Cosa faremo:

1. Installare PostgreSQL 9.6.
2. Configurare il firewall UFW.
3. Configurare il server MASTER.
4. Configurare il server SLAVE.
5. Copiare i dati di PostgreSQL dal MASTER allo SLAVE.
6. Testare.

Prerequisiti:

• 1 server Ubuntu 16.04 - MASTER - Consente lettura e scrittura - IP: 10.0.15.10
• 1 server Ubuntu 16.04 - SLAVE - Solo lettura - IP: 10.0.15.11
• Privilegi di root

Passo 1 - Installare PostgreSQL 9.6

In questo tutorial, installeremo l’ultima versione di PostgreSQL 9.6. Nel repository ufficiale di Ubuntu, forniscono solo PostgreSQL 9.5, quindi dobbiamo installare l’ultima versione direttamente dal repository di PostgreSQL.

Aggiungi il repository postgreSQL 9.6 alla directory sources.list.d.

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list

E importa la chiave di firma di PostgreSQL nel sistema.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Ora aggiorna il repository di sistema con il comando apt.

apt-get update

Successivamente, installa il pacchetto PosgreSQL 9.6 con il comando apt qui sotto.

apt-get install -y postgresql-9.6 postgresql-contrib-9.6

Se l’installazione è stata completata, aggiungila per avviarsi automaticamente all’avvio.

systemctl enable postgresql

Per impostazione predefinita, PostgreSQL è in esecuzione sull’indirizzo IP localhost (127.0.0.1) con la porta 5432 su Ubuntu, controllalo con il comando netstat.

netstat -plntu

PostgreSQL 9.6 è in esecuzione sul sistema. Nel passaggio successivo, dobbiamo configurare una password per l’utente postgres.

Dall’account root, accedi all’utente postgres con il comando su, quindi accedi al terminale front-end di postgres psql.

su - postgres  
psql

Si prega di cambiare la password per l’utente postgres e controllare le informazioni di connessione con le query postgres qui sotto.

\password postgres  
\conninfo

PostgreSQL 9.6 è stato installato sul sistema, è in esecuzione senza errori e la password per l’utente postgres è stata aggiornata.

Passo 2 - Configurare il firewall UFW

UFW o Uncomplicated Firewall è un’applicazione per gestire il firewall basato su iptables su Ubuntu. UFW è lo strumento di configurazione del firewall predefinito per Ubuntu Linux e fornisce un modo user-friendly per configurare il firewall.

Possiamo installare ufw dal repository principale di Ubuntu con il comando apt.

apt-get install -y ufw

Aggiungi nuovi servizi al firewall UFW: aggiungi i servizi SSH e PostgreSQL con i comandi qui sotto.

ufw allow ssh  
ufw allow postgresql

Abilita il firewall UFW e controlla lo stato.

ufw enable  
ufw status

Il firewall UFW è stato installato e il servizio PostgreSQL è stato aggiunto.

NOTA:

Esegui il passo 1 e il passo 2 sul server MASTER e SLAVE.

Passo 3 - Configurare il server PostgreSQL MASTER

Il server master ha l’indirizzo IP 10.0.15.10, e il servizio postgres verrà eseguito sotto quell’IP con la porta predefinita. Il server master avrà il permesso per la LETTURA e la SCRITTURA sul database e eseguirà la replica streaming sul server slave.

Vai alla directory di configurazione di postgres ‘/etc/postgresql/9.6/main‘ e modifica il file postgresql.conf con vim.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Decommenta la riga ‘listen_addresses’ e cambia il valore all’indirizzo IP del server master ‘10.0.15.10‘.

listen_addresses = '10.0.15.10'

Decommenta la riga ‘wal_level’ e cambia il valore a ‘hot_standby‘.

wal_level = hot_standby

Per il livello di sincronizzazione, utilizzeremo la sincronizzazione locale. Decommenta e cambia il valore della riga come sotto.

synchronous_commit = local

Abilita la modalità di archiviazione e cambia l’opzione archive_command al comando ‘cp %p /var/lib/postgresql/9.6/main/archive/%f’.

archive_mode = on  
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'

Per le impostazioni di ‘Replica’, in questo tutorial utilizziamo solo 2 server, master e slave, decommenta la riga ‘wal_sender‘ e cambia il valore a 2, e per il valore ‘wal_keep_segments‘ è 10.

max_wal_senders = 2  
wal_keep_segments = 10

Per il nome dell’applicazione, decommenta la riga ‘synchronous_standby_names’ e cambia il valore al nome ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Salva il file ed esci dall’editor.

Nel file postgresql.conf, la modalità di archiviazione è abilitata, quindi dobbiamo creare una nuova directory per l’archivio. Crea una nuova directory di archivio, cambia i permessi e cambia il proprietario all’utente postgres.

mkdir -p /var/lib/postgresql/9.6/main/archive/  
chmod 700 /var/lib/postgresql/9.6/main/archive/  
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

Successivamente, modifica il file pg_hba.conf per la configurazione dell’autenticazione.

vim pg_hba.conf

Incolla la configurazione qui sotto alla fine della riga.

# Localhost  
host    replication     replica          127.0.0.1/32            md5  
   
# Indirizzo IP Master di PostgreSQL  
host    replication     replica          10.0.15.10/32            md5  
   
# Indirizzo IP Slave di PostgreSQL  
host    replication     replica          10.0.15.11/32            md5

Salva ed esci, quindi riavvia PostgreSQL.

systemctl restart postgresql

PostgreSQL è in esecuzione sotto l’indirizzo IP 10.0.15.10, controllalo con il comando netstat.

netstat -plntu

Successivamente, crea un nuovo utente per la replica. Creeremo un nuovo utente chiamato ‘replica‘ con la password ‘aqwe123@‘. Si prega di scegliere una password sicura qui per la propria configurazione! Accedi all’utente postgres e accedi al terminale front-end di postgres psql.

su - postgres  
psql

Crea un nuovo utente ‘replica‘ con la password ‘aqwe123@‘ con la query postgres qui sotto.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

Ora controlla il nuovo utente con la query ‘du‘ qui sotto, e vedrai l’utente replica con privilegi di replica.

\du

La configurazione del server MASTER è stata completata.

Passo 4 - Configurare il server SLAVE

Il server SLAVE ha l’indirizzo IP 10.0.15.11. E questo server avrà solo un permesso di LETTURA sul database. Il server di database Postgres verrà eseguito sotto l’indirizzo IP del server, non un IP localhost.

Ferma il servizio postgres sul server slave con il comando systemctl qui sotto.

systemctl stop postgresql

Vai alla directory di configurazione di Postgres ‘/etc/postgresql/9.6/main‘, quindi modifica il file di configurazione ‘postgresql.conf‘.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Decommenta la riga listen_addresses e sostituisci il valore con l’indirizzo IP del server slave 10.0.15.11.

listen_addresses = '10.0.15.11'

Decommenta la riga ‘wal_level’ e cambia il valore a ‘hot_standby‘.

wal_level = hot_standby

Per il livello di sincronizzazione, decommenta la riga synchronous_commit e cambia il valore come mostrato qui sotto.

synchronous_commit = local

Per l’impostazione di replica, decommenta la riga max_wal_senders e sostituisci il valore con ‘2‘ perché utilizziamo solo 2 server. E per wal_keep_segments, cambia il valore a ‘10‘.

max_wal_senders = 2  
wal_keep_segments = 10

Decommenta la riga ‘synchronous_standby_names’ per il nome dell’applicazione e cambia il valore al nome ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Abilita hot_standby per il server slave decommentando la seguente riga e cambiando il valore in ‘on‘.

hot_standby = on

Salva il file ed esci dall’editor.

Passo 5 - Copiare i dati di PostgreSQL dal MASTER allo SLAVE

Successivamente, vogliamo sostituire la directory principale di postgres sul server ‘SLAVE‘ con la directory principale dei dati dal server ‘MASTER‘.

Accedi al server SLAVE e accedi all’utente postgres.

su - postgres

Vai alla directory dei dati di postgres ‘main‘ e esegui il backup rinominando il nome della directory.

cd 9.6/  
mv main main-bekup

Crea una nuova directory ‘main‘ come utente ‘postgres‘ e assicurati di avere i permessi come la directory main-bekup.

mkdir main/  
chmod 700 main/

Successivamente, copia la directory principale dal server MASTER al server SLAVE con il comando pg_basebackup, utilizzeremo l’utente replica per eseguire questa copia di dati.

pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog  
Password:

Quando il trasferimento dei dati è completo, vai alla directory principale dei dati e crea un nuovo file recovery.conf.

cd /var/lib/postgresql/9.6/main/  
vim recovery.conf

Incolla la configurazione qui sotto:

standby_mode = 'on'  
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave001'  
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'  
trigger_file = '/tmp/postgresql.trigger.5432'

Salva ed esci, quindi cambia i permessi del file a 600 con chmod.

chmod 600 recovery.conf

Ora avvia PostgreSQL 9.6 sul server SLAVE e assicurati che il servizio postgres sia in esecuzione all’indirizzo IP 10.0.15.11 con netstat.

systemctl start postgresql  
netstat -plntu

Il trasferimento dei dati e la configurazione per il server SLAVE sono stati completati.

Passo 6 - Testare

Per il test, controlleremo lo stato della replica di PostgreSQL 9.6 e proveremo a creare una nuova tabella sul server MASTER, quindi controllare la replica controllando tutti i dati dal server SLAVE.

Accedi al server MASTER e accedi all’utente postgres.

su - postgres

Esegui i comandi psql qui sotto per vedere lo stato della replica.

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"  
psql -x -c "select * from pg_stat_replication;"

Otterrai i risultati come sotto.

Successivamente, prova a creare una nuova tabella dal server MASTER. Accedi all’utente postgres sul server master.

su - postgres  
psql

E crea una nuova tabella chiamata ‘replica_test‘ e inserisci dati nella tabella eseguendo le query postgres qui sotto.

CREATE TABLE replica_test (hakase varchar(100));  
INSERT INTO replica_test VALUES ('howtoforge.com');  
INSERT INTO replica_test VALUES ('Questo è dal Master');  
INSERT INTO replica_test VALUES ('replica pg di hakase-labs');

Successivamente, accedi all’utente postgres sul server SLAVE e accedi al terminale psql.

su - postgres  
psql

Controlla i dati nella tabella ‘replica_test‘ con la query postgres qui sotto.

select * from replica_test;

E otterrai gli stessi dati del MASTER, è stato replicato dal server MASTER al server SLAVE.

Test aggiuntivo:

Prova a scrivere sul server SLAVE con la query qui sotto.

INSERT INTO replica_test VALUES ('questo è SLAVE');

E riceverai un messaggio di errore riguardo alla query ‘Impossibile eseguire INSERT‘ sul server SLAVE.

L’installazione e la configurazione di PostgreSQL 9.6 con replica Master-Slave su Ubuntu 16.04 Xenial Xerus sono state completate con successo.

Riferimento

• https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/