Replication PostgreSQL multi slaves

Introduction

Cette page traite de la mise en place d'une réplication PostgreSQL streaming asynchrone avec un master et deux slaves. Nos slaves seront configurés comme hot standby, c'est à dire qu'il sera possible d'exécuter des requêtes (en lecture uniquement) sur ceux-ci.

Contexte de cette mise en place :

Nous avons 3 serveurs Debian 8 (mis à jour) : Postgres01 (10.1.1.75) : sera notre Master Postgres02 (10.1.1.90) : sera un slave Postgres03 (10.1.1.199) : sera un second slave

Les commandes indiquées seront à effectuer en root quand elles sont précédées de "#" et en tant qu'utilisateur système postgres quand elles sont précédées de "$".

Installation de PostgreSQL et pré-configuration des trois serveurs

Toutes les commandes de cette partie sont à faire sur chacun des serveurs.

Dans un premier temps, il est recommandé d'avoir un fichier /etc/hosts peuplé avec les correspondances de chaque serveur, comme ceci :

# cat /etc/hosts
127.0.0.1       localhost

10.1.1.75       postgres01
10.1.1.90       postgres02
10.1.1.199      postgres03

On ajoute le dépôt APT PostgreSQL :

# echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" > /etc/apt/sources.list.d/postgres.list

On importe la clé public de ce dépôt :

# gpg --keyserver pgpkeys.mit.edu --recv-key 7FCC7D46ACCC4CF8 && gpg -a --export 7FCC7D46ACCC4CF8 | apt-key add -
gpg: directory `/root/.gnupg' created
gpg: new configuration file `/root/.gnupg/gpg.conf' created
gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
gpg: keyring `/root/.gnupg/secring.gpg' created
gpg: keyring `/root/.gnupg/pubring.gpg' created
gpg: requesting key ACCC4CF8 from hkp server pgpkeys.mit.edu
gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key ACCC4CF8: public key "PostgreSQL Debian Repository" imported
gpg: no ultimately trusted keys found
gpg: Total number processed: 1
gpg:               imported: 1  (RSA: 1)
OK

On met à jour le cache APT pour prendre en compte le dépôt :

# apt-get update

On installe le paquet postgresql (installera la dernière version stable soit la 9.5 au moment où sont écrites ces lignes) :

# apt-get install postgresql

On définit un mot de passe (de son choix mais sécurisé) et génère une paire de clé ssh (sans passphrase) pour l’utilisateur système postgres :

# passwd postgres

# su - postgres

$ ssh-keygen

On copie la clé ssh public de l’utilisateur postgres depuis et entre chaque serveur :

Depuis le premier serveur (postgres01 dans notre cas) vers le second (postgres02 pour nous) et troisisème (postgres03) :

root@postgres01:~# su - postgres

postgres@postgres01:~$ ssh-copy-id postgres02

postgres@postgres01:~$ ssh-copy-id postgres03

Depuis le second serveur vers le premier et troisième :

root@postgres02:~# su - postgres

postgres@postgres02:~$ ssh-copy-id postgres01

postgres@postgres02:~$ ssh-copy-id postgres03

Depuis le troisième serveur vers le premier et second :

root@postgres03:~# su - postgres

postgres@postgres03:~$ ssh-copy-id postgres01

postgres@postgres03:~$ ssh-copy-id postgres02

Configuration du Master

A faire uniquement sur le master (postgres01 dans notre cas) :

On crée le rôle/utilisateur de réplication :

# su - postgres

$ psql -c "CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'GmuY@8Smm%';"

Note : on définit une limite de connexion de 2 car nous avons 2 slaves

On édite le fichier de configuration principal de postgresql /etc/postgresql/9.5/main/postgresql.conf pour configurer les directives suivantes :

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -av %p postgres@10.1.1.90:/var/lib/postgresql/wal_archive/%f'
max_wal_senders = 2
wal_keep_segments = 256
hot_standby = on

Note: nous activons l’archivage vers notre second serveur (adaptez l’adresse ip par celle de votre serveur) pour encore plus de précautions. Nous définissons également les paramètres hot_standby, bien que ignorés sur un master, dans le cas où celui-ci devait être rétrogradé en slave dans l’avenir.

On édite maintenant le fichier d’authentification de postgresql /etc/postgresql/9.5/main/pg_hba.conf pour ajouter l’autorisation de notre utilisateur de réplication à se connecter depuis nos slaves soit la ligne suivante à la fin du fichier :

hostssl replication     repuser 10.1.1.0/24     md5

Veuillez adapter cette ligne en fonction du nom de votre utilisateur de réplication et du réseau (ou adresses ip respectives) de vos slaves

On redémarre le service postgresql pour prendre en compte notre configuration (en root) :

# systemctl restart postgresql

On s’assure que le service a démarré correctement :

root@postgres01:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Thu 2016-06-02 12:06:28 CEST; 22s ago
  Process: 77056 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 77056 (code=exited, status=0/SUCCESS)

root@postgres01:~# ps -u postgres u
USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  77038  0.0  4.4 227300 21836 ?        S    12:06   0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.
postgres  77040  0.0  0.8 227300  4160 ?        Ss   12:06   0:00 postgres: checkpointer process
postgres  77041  0.0  1.0 227300  4968 ?        Ss   12:06   0:00 postgres: writer process
postgres  77042  0.0  1.7 227300  8776 ?        Ss   12:06   0:00 postgres: wal writer process
postgres  77043  0.0  1.3 227728  6708 ?        Ss   12:06   0:00 postgres: autovacuum launcher process
postgres  77044  0.0  0.6  82244  3392 ?        Ss   12:06   0:00 postgres: archiver process
postgres  77045  0.0  0.8  82244  4244 ?        Ss   12:06   0:00 postgres: stats collector process

Configuration des slaves

Sur le premier slave

On commence par stopper le service postgresql :

# systemctl stop postgresql

On édite le fichier de configuration principal de postgresql /etc/postgresql/9.5/main/postgresql.conf pour configurer les même directives que sur le master, en adaptant juste l’ip du premier slave par celle du second slave dans la commande rsync d’archivage soit :

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -av %p postgres@10.1.1.199:/var/lib/postgresql/wal_archive/%f'
max_wal_senders = 2
wal_keep_segments = 256
hot_standby = on

On édite maintenant le fichier d’authentification de postgresql /etc/postgresql/9.5/main/pg_hba.conf pour ajouter l’autorisation de notre utilisateur de réplication à se connecter depuis nos autres serveurs cela ne servira qu’au cas où ce slave serait promu master :

hostssl replication     repuser 10.1.1.0/24     md5

Avec cette configuration similaire à celle de notre master cela permettra de promouvoir facilement ce slave en tant que master en cas de besoin.

Pour les opérations ci-dessous, connectez-vous en tant qu’utilisateur système postgres :

# su – postgres

On crée le répertoire de destination des archives wal du master :

$ mkdir /var/lib/postgresql/wal_archive

On supprime le répertoire de données postgresql :

$ rm -rf /var/lib/postgresql/9.5/main

On fait le base backup (adaptez l’ip par celle de votre master et le nom de votre utilisateur de réplication), le mot de passe de votre utilisateur de réplication vous sera demandé :

$ pg_basebackup -h <ip de votre master> -D /var/lib/postgresql/9.5/main -U repuser -v -P --xlog

On configure la réplication en créant le fichier /var/lib/postgresql/9.5/main/recovery.conf avec les paramètres suivants :

standby_mode = on
primary_conninfo = 'host=<ip de votre master> port=5432 user=repuser password=<mot de passe de repuser> sslmode=require application_name=<hostname de votre slave>'
trigger_file = '/var/lib/postgresql/9.5/postgres.trigger'

Le fichier trigger est le fichier qu’on ne crée que lorsque l’on veut que son slave arrête la réplication et commence à accepter les écritures c’est-à-dire quand on veut le promouvoir comme master. Avant de promouvoir un slave en cas de crash de son master il faut s’assurer que son master ne remontera pas.

On repasse root et on démarre le service :

# systemctl start postgresql

On s’assure que le service a correctement démarré :

root@postgres02:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Thu 2016-06-02 12:53:42 CEST; 1min 6s ago
  Process: 8894 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 8894 (code=exited, status=0/SUCCESS)

root@postgres02:~# ps -u postgres u
USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres   8878  0.0  4.4 227308 21892 ?        S    12:53   0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.
postgres   8879  0.0  1.1 227376  5700 ?        Ss   12:53   0:00 postgres: startup process   recovering 00000001000000000000000
postgres   8880  0.0  0.8 227308  4140 ?        Ss   12:53   0:00 postgres: checkpointer process
postgres   8881  0.0  1.0 227308  5236 ?        Ss   12:53   0:00 postgres: writer process
postgres   8882  0.0  0.6  82252  3316 ?        Ss   12:53   0:00 postgres: stats collector process
postgres   8883  0.0  1.7 238064  8520 ?        Ss   12:53   0:00 postgres: wal receiver process   streaming 0/30003E0

Si on se connecte à notre master, on peut déjà verifier que notre replication entre le master et le premier slave est fonctionnelle :

On se connecte sur notre master et on se loggue en utilisateur système postgres :

# su - postgres

Puis on se connecte à postgresql :

$ psql

On active l’affichage étendu et on vérifie notre réplication :

postgres=# \x
Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 78879
usesysid         | 16384
usename          | repuser
application_name | postgres02
client_addr      | 10.1.1.90
client_hostname  |
client_port      | 49009
backend_start    | 2016-06-02 12:53:36.641823+02
backend_xmin     |
state            | streaming
sent_CRElocation    | 0/30004C0
write_location   | 0/30004C0
flush_location   | 0/30004C0
replay_location  | 0/30004C0
sync_priority    | 0
sync_state       | async

Notre réplication streaming asynchrone avec notre premier slave s’est bien faite. A ce stade on a une réplication Master/slave classique (un seul slave en streaming asynchrone)

Sur le second slave

On refait à l’identique ce qui a été fait sur le premier slave en adaptant évidemment son hostname dans le fichier recovery.conf et l’adresse ip dans la commande d’archivage dans le fichier de configuration principal si vous souhaitez également activer l'archivage sur ce second slave.

Vérification et test de notre réplication master multi slaves

On se connecte sur le master et on se loggue en utilisateur postgres :

# su – postgres

Puis on se connecte à postgresql :

$ psql

On active l’affichage étendu et on vérifie notre réplication :

postgres=# \x
Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 78879
usesysid         | 16384
usename          | repuser
application_name | postgres02
client_addr      | 10.1.1.90
client_hostname  |
client_port      | 49009
backend_start    | 2016-06-02 12:53:36.641823+02
backend_xmin     |
state            | streaming
sent_location    | 0/5000140
write_location   | 0/5000140
flush_location   | 0/5000140
replay_location  | 0/5000140
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+------------------------------
pid              | 82725
usesysid         | 16384
usename          | repuser
application_name | postgres03
client_addr      | 10.1.1.199
client_hostname  |
client_port      | 51754
backend_start    | 2016-06-02 14:31:43.759683+02
backend_xmin     |
state            | streaming
sent_location    | 0/5000140
write_location   | 0/5000140
flush_location   | 0/5000140
replay_location  | 0/5000140
sync_priority    | 0
sync_state       | async

On a donc bien nos 2 réplications avec nos deux serveurs slaves (postgres02 et postgres03).

Toujours connecté à postgresql sur le master, on crée une base de donnée (ex : checkrep) :

postgres=# CREATE DATABASE checkrep;

On vérifie que cette base de données s’est bien répliqué sur nos 2 slaves :

root@postgres02:~# su - postgres

postgres@postgres02:~$ psql
psql (9.5.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 checkrep  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

root@postgres03:~# su - postgres
postgres@postgres03:~$ psql
psql (9.5.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 checkrep  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

La base de données que nous avons crée sur notre master s’est donc bien automatiquement répliquée sur nos deux slaves.

Pour la supprimer, depuis le master :

postgres=# DROP DATABASE checkrep;