HowtoPostgreSQL/ReplicationLogique

Réplication Logique avec PostgreSQL
Troubleshooting
- ERREUR: could not map filenode “base/*/*” to relation OID

Réplication Logique avec PostgreSQL

https://www.postgresql.org/docs/11/logical-replication.html

Mise en place

Sur le serveur primaire et réplica, on s’assure d’avoir deux bases :

postgres $ createuser -p 5432 -P foo
Enter password for new role:

postgres $ createdb -p 5432 -O foo foo

Sur le serveur primaire, on modifie la directive wal_level :

wal_level = logical

Puis l’on crée un utilisateur replication et une publication liée à cette base :

postgres $ createuser -p 5432 -P --replication replication

postgres $ psql -p 5432 foo

foo=# GRANT SELECT on ALL TABLES IN SCHEMA public TO replication;
GRANT

foo=# CREATE PUBLICATION alltables FOR ALL TABLES;
CREATE PUBLICATION

Sur le primaire, on autorise l’utilisateur replication à se connecter dans pg_hba.conf :

host base replication 192.168.0.1/24 md5

Ensuite, il faut créer les tables de façon identique sur les bases, pour cela on dump le schema de la ou des bases concernée depuis le primaire vers le secondaire :

pg_dump -h IP_PRIMAIRE -p 5432 -s base1 | psql -h IP_SECONDAIRE -p 5432 base1

Sur le serveur réplica, on crée une subscription liée à cette base :

postgres $ psql -p 5432 foo

foo=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=192.0.2.1 port=5436 user=replication password=PASSWORD' PUBLICATION alltables;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION

Attention, contrairement à la réplication physique, rien n’empêche d’écrire sur le serveur réplica.

Enfin, l’insertion de données sur le serveur primaire, devrait provoquer le transfert de ces données sur le serveur réplica :

LOG:  logical replication table synchronization worker for subscription "mysub", table "t" has started

Si besoin, on peut « rafraichir » la subscription sur le serveur replica via la commande :

postgres $ psql -p 5432 foo

foo=# ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;

Pendant la copie des tables, on peux surveillé l’avancement de la copie avec la vue pg_subscription_rel la colonne srsubstate donne l’état d’avancement pour chaque table :

i = initialisation
d = copie des données en cours
s = synchronisé
r = prêt

Lorsque les tables sont toutes en status ‘r’ la copie des données des tables est terminées.

Administration

Ajout / modification d’une table

Si l’on veut ajouter ou modifier une table, il faut :

créer / modifier la table sur les 2 serveurs maître et réplica
relancer la commande suivante sur le serveur maître :

postgres $ psql -p 5432 foo

foo=# GRANT SELECT on ALL TABLES IN SCHEMA public TO replication;
GRANT

relancer la commande suivante sur le serveur réplica :

postgres $ psql -p 5432 foo

foo=# ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;

On peut ensuite insérer des données sur la nouvelle table qui sera répliquée.

A partir de PostgreSQL 15, si on veux modifié une VUE sur l’instance secondaire en réplication logique, il faut ajouter les droits CREATE à la base que l’on souhaite modifié sur le schéma public (si la base concerné est dans le schéma public, sinon il faut l’appliquer au schéma concerné) :

GRANT CREATE ON SCHEMA public TO foo;

Suspendre une réplication

Mettre en pause une réplication :

foo=# ALTER SUBSCRIPTION mysub DISABLE;

LOG:  le processus apply de réplication logique pour la souscription « mysub » s'arrêtera car la souscription a été désactivée

Reprendre une réplication :

foo=# ALTER SUBSCRIPTION mysub ENABLE;

Suppression

Pour supprimer une PUBLICATION sur le primaire :

DROP PUBLICATION alltables;

Pour supprimer une SUBSCRIPTION sur un secondaire

ALTER SUBSCRIPTION mysub DISABLE;
ALTER SUBSCRIPTION mysub SET (slot_name=NONE);
DROP SUBSCRIPTION mysub;

Puis on supprime le slot de réplication logique sur le primaire :

SELECT * FROM pg_drop_replication_slot('mysub');

Ajout d’un schema à une base / utilisateur répliqué

Si l’on ajoute un schéma à une base répliqué, pour que la syncho des données de ce shcéma se fasse, il faut que l’utilisateur SQL dédié à la réplication est le droit USAGE sur ce schéma :

foo=# GRANT USAGE ON SCHEMA bar TO replication;

Sinon la copié initiale des données de ce schéma n’est pas copié dans la réplication, on auras des erreurs de ce type dans les logs sur le master :

2020-05-30 00:06:26.163 CEST [40846] replication@foo ERROR: permission denied for schema bar
2020-05-30 00:06:26.163 CEST [40846] replication@foo STATEMENT: COPY bar."21GEHAVL" TO STDOUT

Sur le réplica on aura des erreurs de ce type :

020-05-30 15:55:57.594 CEST [986] LOG: le processus de synchronisation des tables en réplication logique pour la souscription « mysub_sql3 », table « 21NOHAII » a démarré
2020-05-30 15:55:57.601 CEST [985] ERREUR: n'a pas pu lancer la copie initiale du contenu de la table « bar.21GEHVRH » : ERROR: permission denied for schema bar

Monitoring

Sur le serveur secondaire, on peut surveiller le bon fonctionnement de la réplication ainsi, mais cela indique seulement l’état sur le secondaire, il ne surveille pas la réplication par rapport au primaire :

postgres=# select * from pg_subscription ;
 subdbid | subname | subowner | subenabled |                                subconninfo                                 | subslotname | subsynccommit | subpublications 
---------+---------+----------+------------+----------------------------------------------------------------------------+-------------+---------------+-----------------
   16385 | mysub   |       10 | t          | dbname=foo host=192.0.2.1 port=5432 user=replication password=PASSWORD     | mysub       | off           | {alltables}
(1 row)

postgres=# select * from pg_stat_subscription ;
 subid | subname |  pid  | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time        
-------+---------+-------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 16388 | mysub   | 12767 |       | 0/16A58D8    | 2019-09-17 21:09:16.512057+00 | 2019-09-17 21:09:16.512241+00 | 0/16A58D8      | 2019-09-17 21:09:16.512057+00

check_pgactivity

Pour surveiller la réplication logique depuis le primaire on utilise le check_pgactivity : https://github.com/OPMDG/check_pgactivity

En Debian 10, on installe le paquet check-pgactivity

On l’utilise avec le service replication_slots qui regarde le nombre de fichier WAL et le nombre de fichier dans pg_replslot pour chaque slot de réplication.

Si les fichiers WAL s’accumule ainsi que les fichiers dans pg_replslot/ c’est qu’il y a un problème de réplication, le check passe en critique est indique quel slot est impacté.

On positionne des valeurs de warning et critique comme ceci :

postgres@serv:~$ /usr/lib/nagios/plugins/check_pgactivity -s replication_slots -w 'wal=250,replslot=40' -c 'wal=350,replslot=80'

Pour que ce check fonctionne, il faut utiliser un utilisateur sans privilège particulier, mais avec le droit EXECUTE sur la fonction pg_ls_dir(text)

On peut créer l’utilisateur comme ceci :

postgres=# CREATE ROLE monitor WITH LOGIN PASSWORD 'foo';
postgres=# GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO monitor;

Utilisation de la réplication logique pour une upgrade de version de Postgresql

On va prendre par exemple le cas où l’on veut migrer un serveur primaire en Postgresql11 vers Postgresql13.

Il faut vérifier que sur le primaire toutes les tables d’une base de données ont une clé primaire, on peut exécuter cette requête qui va lister les tables qui n’ont pas de clé primaire :

SELECT tbl.table_schema, tbl.table_name FROM information_schema.tables tbl WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') AND NOT EXISTS ( SELECT 1 FROM information_schema.key_column_usage kcu WHERE kcu.table_name = tbl.table_name AND kcu.table_schema = tbl.table_schema );

On crée une nouvelle instance Postgresql en version 13, soit sur un serveur distant qui va être notre futur primaire, soit on crée une nouvelle instance en version 13 sur le primaire actuel, sur un port alternatif :

$ pg_createcluster 13 prim -p 5434

Si la nouvelle instance en PG13 est sur le même serveur on baisse les valeurs de shared_buffers, effective_cache_size et max_worker_processes pour ne pas saturer le serveur.

Dans l’idéal il ne faut pas qu’il ait de changement sur la structure pendant les opérations jusqu’à la bascule.
Faire un dump de la scructure (schema) et les rôles sur le primaire actuel et la réinjecter sur l’instance 5434 :

$ pg_dump -p 5432 --schema-only ma_base > ma_base.schema.sql
$ pg_dumpall -p 5432 -g > pg_global.sql
$ psql -p 5434 < pg_global.sql
$ psql -p 5434 ma_base < ma_base.schema.sql

Créer la subcription sur la nouvelle instance PG13 vers le primaire actuel :

ma_base=# CREATE SUBSCRIPTION s_upgrade CONNECTION 'dbname=ma_base host=IP_PRIMAIRE port=5432 user=repl password=mypass' PUBLICATION alltables;

Il faut faire une réplication logique pour chaque base (dump + injection schema + création de subscription) si l’instance Postgresql contient plusieurs base.
Suivre la copie des tables sur l’instance 5434 en surveillant la vue pg_subscription_rel

ma_base=# select * from pg_subscription_rel;

Lorsque les tables sont toutes en status ‘r’ c’est que la synchro de la réplication logique est terminé.
Par précaution, ont fait un dump de l’instance primaire ainsi que les fichiers de configuration, avant la bascule :

$ pg_dumpall | gzip -c > fichier_sauvegarde.sql.gz

Le jour ou l’on veut basculer sur l’instance PG13

Lorsque l’on voudra basculer sur l’instance PG13 faire ces étapes suivantes :

Bloquer les connexions avec pg_hba.conf sur le primaire actuel
Vérifié qu’il n’y ai plus de connexion depuis les serveurs web avec la vue pg_stat_activity, et kill des connections restante comme ceci :

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'ma_base' AND pid <> pg_backend_pid() AND backend_type = 'client_backend';

Recopie de l’avancement des sequences : Il faut recopier l’avancement des sequences faite depuis le primaire vers le futur sql11 comme ceci, par exemple :

$ psql -p 5432 -XAtqc "SELECT \$\$select setval('\$\$ || quote_ident(schemaname)||\$\$.\$\$|| quote_ident(sequencename) || \$\$', \$\$ || last_value || \$\$); \$\$ AS sql FROM pg_sequences" ma_base | psql -p 5434 ma_base

Il faut recopier l’avancement des séquences pour chaque base, si l’instance Postgresql contient plusieurs bases.

Arrêter l’instance primaire actuelle
Mettre à jours les paramètres shared_buffers et effective_cache_size etc… et faire écouter l’instance sur le port 5432 (si l’on a créé une instance sur le serveur primaire actuel)
Copié le pg_hba.conf sur la nouvelle instance.
Suppression de la souscription sur la nouvelle instance :

ma_base=# ALTER SUBSCRIPTION s_upgrade DISABLE;
ma_base=# ALTER SUBSCRIPTION s_upgrade SET (slot_name = NONE);
ma_base=# DROP SUBSCRIPTION s_upgrade;

Redémarrage de la nouvelle instance

# systemctl restart postgresql@13-prim.service

Troubleshooting

ERREUR: could not map filenode “base//” to relation OID

Si une erreur dans la réplication logique du type :

2022-08-29 10:12:57.380 CEST [12525] LOG:  le processus apply de réplication logique pour la souscription « mysub_sql10 » a démarré

2022-08-29 10:12:57.628 CEST [12525] ERREUR:  n'a pas pu recevoir des données du flux de WAL : ERREUR:  could not map filenode "base/16385/107076665" to relation OID

2022-08-29 10:12:57.632 CEST [27621] LOG:  processus en tâche de fond « logical replication worker » (PID 12525) a quitté avec le code de sortie 1

Il est possible que ce soit un conflit d’OID soit de relfilenode

Il faut regarder l’OID et le relfilenode qui est mentionner dans l’erreur sur le secondaire et sur le primaire.

On peux listé les OID, le nom de la table (relname) et le relfilenode avec la requête suivante sur la base où l’on a créer la SUBSCRIPTION :

bar2=# select oid,relname,relfilenode from pg_class;

Exemple de sortie :

bar2=# select oid,relname,relfilenode from pg_class;
   oid   |                             relname                             | relfilenode 
---------+-----------------------------------------------------------------+-------------
  101181 | primary_keys                                                    |           0
  101186 | tables                                                          |           0
  101851 | dblink_pkey_results                                             |           0
  101965 | tablefunc_crosstab_2                                            |           0
  101968 | tablefunc_crosstab_3                                            |           0
  101971 | tablefunc_crosstab_4                                            |           0
  101990 | pgstattuple_type                                                |           0
  101993 | statenbjourmax                                                  |           0
  101996 | statinfo                                                        |           0
  101999 | tokenout                                                        |           0
  102002 | tokentype                                                       |           0
  102837 | bbk_contents                                                    |      102837
  102814 | aerien_contrat_to_gesto_periode_id_seq                          |      102814
  102828 | aerien_contrat_to_gesto_tarif_id_seq                            |      102828
  102819 | pg_toast_102815                                                 |      102819
  102820 | pg_toast_102815_index                                           |      102820
  102821 | aerien_contrat_to_gesto_saison_id_seq                           |      102821
  102864 | yalago_produit_chambre                                          |     2976661
  102826 | pg_toast_102822                                                 |      102826
  102827 | pg_toast_102822_index                                           |      102827
  102815 | aerien_contrat_to_gesto_saison                                  |      102815
    2619 | pg_statistic                                                    |        2619
    1247 | pg_type                                                         |           0
  102835 | pg_toast_102829                                                 |      102835
  102836 | pg_toast_102829_index                                           |      102836
  102829 | bbk_api_id_mapping_produit                                      |      102829
  102841 | pg_toast_102837                                                 |      102841
  102842 | pg_toast_102837_index                                           |      102842
  102871 | all_exist_chambre                                               |           0
  102880 | pg_toast_102876                                                 |      102880
  114983 | zztemp_retro_except_alofvl1_pkey                                |     3031858
  111282 | zztemp_mise_a_jour_all_status_retro                             |     3031864
  111331 | zztemp_stock_except_gesto                                       |     3031867
  111335 | pg_toast_111331                                                 |     3031868
  102876 | bbk_reservation_hotel_externe                                   |      102876
  102881 | pg_toast_102876_index                                           |      102881

...

Dans le message d’erreur que l’on a précédemment, il nous retournes l’OID et le relfilenode suivant :

ERREUR:  could not map filenode "base/16385/107076665" to relation OID

Dans ce cas là, si on cherche le 107076665 dans la sortie de la requête select oid,relname,relfilenode from pg_class; on trouve le relfilenode de cette table, sur le serveur primaire :

bar2=# select oid,relname,relfilenode from pg_class where relfilenode = '107076665';
   oid   |                             relname                             | relfilenode 
---------+-----------------------------------------------------------------+-------------
   23135 | zztemp_stock_chambre                                            |   107076665

Une solution peut être de supprimer et recréer cette table sur le primaire, qui fait que ça réassigne un autre numéro de relfilenode a cette table, et ça peut débloquer la réplication sur le secondaire impacté.