Il existe différentes approches pour mettre à jour PostgreSQL, mais certaines entraînent des temps d'arrêt. Si vous souhaitez éviter les temps d'arrêt, utilisez la réplication pour la mise à niveau - logique ou physique (streaming), selon le scénario. Dans cet article, nous examinerons la différence entre la réplication logique et physique dans PostgreSQL. Ensuite, nous expliquerons en détail comment mettre à jour la version à l'aide de la réplication logique tout en évitant les temps d'arrêt des applications. Le prochain article traitera de la réplication physique.
Dans les articles précédents, nous avons déjà parlé des méthodes de mise à jour de PostgreSQL ( mise à niveau de PostgreSQL à l'aide de pg_dumpall et mise à niveau de PostgreSQL à l'aide de pg_dump / pg_restore ) dans le cadre de la série mise à niveau ou migration d'anciennes versions de PostgreSQL . Mais ces deux méthodes n'excluent pas les temps d'arrêt.
Types de réplication logique
Nous discutons ici de 2 types de réplication:
- Réplication entre PostgreSQL 10 et 11 à l'aide de la réplication logique intégrée.
- Réplication entre PostgreSQL 9.4 (ou antérieur à PG 11) et PostgreSQL 11 à l'aide de l'extension pglogical .
Pour minimiser les temps d'arrêt, vous pouvez mettre à niveau à l'aide de la réplication. Lorsque toutes les données pertinentes sont répliquées sur un autre dernier serveur PostgreSQL, vous transférez simplement l'application vers le nouveau serveur avec un temps d'arrêt minimal - bien que, bien sûr, tout dépend de la complexité de la pile d'applications.
La réplication logique dans PostgreSQL permet aux utilisateurs de répliquer de manière sélective des tables et d'ouvrir un serveur de sauvegarde pour les opérations d'écriture. La réplication physique dans PostgreSQL se fait en blocs. Dans ce cas, chaque base de données de l'assistant est répliquée sur le serveur de sauvegarde, inaccessible aux opérations d'écriture. Ensuite, nous appellerons le streaming de réplication physique.
Lorsque vous utilisez la réplication logique sur un serveur de secours, vous pouvez activer la réplication à partir de plusieurs maîtres. Ceci est utile dans les situations où vous devez répliquer des données de plusieurs bases de données PostgreSQL (OLTP) vers un seul serveur PostgreSQL pour les rapports et le stockage des données.
Le principal avantage de la réplication logique par rapport au streaming est qu'avec la réplication logique, vous pouvez répliquer les modifications de l'ancienne version de PostgreSQL vers la nouvelle. La réplication de flux ne fonctionne que lorsque le maître et le serveur de sauvegarde ont la même version principale. Idéalement, des versions supplémentaires devraient également correspondre.
Réplication entre PostgreSQL 10 et 11
À partir de PostgreSQL 10, la réplication logique est disponible par défaut. Par conséquent, vous pouvez facilement répliquer la base de données PostgreSQL 10 dans PostgreSQL 11. La réplication logique utilise le modèle de publication et d'abonnement. Le nœud qui soumet les modifications devient l'éditeur. Et le nœud abonné à ces modifications devient un abonné. Il peut y avoir plusieurs abonnements par publication.
Publication
Une publication est un tableau de modifications créé par un groupe de tables. Il s'agit d' un ensemble de modifications ou d' un ensemble de réplication . Les publications ne peuvent contenir que des tableaux, mais pas d'autres objets. DML dans ces tables peut être répliqué, mais DDL ne peut pas.
Dans la publication, vous pouvez choisir le type de DML à répliquer: INSÉRER, SUPPRIMER, METTRE À JOUR ou TOUT. Par défaut, ALL est sélectionné. La table doit avoir un identifiant de réplique afin de répliquer les opérations UPDATE et DELETE sur l'abonné. Les identificateurs de répliques vous aident à trouver les lignes mises à jour ou supprimées.
La clé primaire de la table est l'identifiant de réplique par défaut. Ou vous pouvez faire de l'identifiant un index unique avec des valeurs NOT NULL. Si vous n'avez pas de clé primaire ou d'index unique sans valeurs NULL, définissez replica_identity sur FULL. Dans ce cas, Postgres utilise la chaîne entière comme clé. Mais ce n'est pas très rationnel.
Si une table sans clé primaire et identificateur de réplique est ajoutée à la publication par défaut après une opération UPDATE ou DELETE, des erreurs peuvent se produire.
Abonnement
Un abonné peut s'abonner à une ou plusieurs publications. Avant d'ajouter un abonnement, assurez-vous que les tables répliquées sont créées sur le nœud d'abonné. Pour ce faire, videz uniquement les schémas de l'éditeur vers l'abonné.
Exemple de réplication logique
L'exemple suivant décrit la réplication logique uniquement entre les versions 10 et 11 de PostgreSQL.
Créez une publication sur le site de l'éditeur. Ajoutez tous ou seulement quelques tableaux à la publication.
-- For adding ALL Tables in Database CREATE PUBLICATION percpub FOR ALL TABLES; -- For adding Selected Tables in Database CREATE PUBLICATION percpub FOR TABLE scott.employee scott.departments;
Sur le site abonné, créez un abonnement à cette publication. Effectuez un vidage DDL des tables sur l'abonné avant de créer l'abonnement, comme mentionné ci-dessus.
$ pg_dump -h publisher_server_ip -p 5432 -d percona -Fc -s -U postgres | pg_restore -d percona -h subscriber_node_ip -p 5432 -U postgres CREATE SUBSCRIPTION percsub CONNECTION 'host=publisher_server_ip dbname=percona user=postgres password=secret port=5432' PUBLICATION percpub;
Cette commande copie également les données existantes dans les tables. Si vous souhaitez désactiver la copie des données existantes, utilisez la commande suivante et seules les modifications apportées à l'éditeur seront copiées.
CREATE SUBSCRIPTION percsub CONNECTION 'host=publisher_server_ip dbname=percona user=postgres password=oracle port=5432' PUBLICATION percpub WITH (copy_data = false);
Suivez la réplication à l'aide de la commande suivante dans le nœud de l'éditeur:
$ psql \x select * from pg_stat_replication;
Réplication entre PostgreSQL 9.4 et PostgreSQL 11
Que faire avec les versions antérieures à PostgreSQL 10? Pour les versions 9.4 à 11, il existe une extension spéciale - pglogical . En utilisant pglogical, vous pouvez répliquer PostgreSQL 9.4 vers PostgreSQL 11 de deux manières.
Voici des instructions générales pour configurer la réplication entre PG 9.4 et PG 11 à l'aide de l'extension pglogical .
Étape 1. Considérez pgserver_94 comme serveur source avec la base de données percona_94 sur PostgreSQL 9.4. Créez l'extension suivante.
code
[pgserver_94:] $psql -d percona_94 -c "CREATE EXTENSION pglogical_origin" CREATE EXTENSION [pgserver_94:] $psql -d percona_94 -c "CREATE EXTENSION pglogical" CREATE EXTENSION
Étape 2. Ajoutez maintenant tout ou partie des tables au schéma ou à plusieurs schémas pour la réplication. Dans l'exemple suivant, vous voyez une erreur car l'une des tables n'a pas de clé primaire.
[pgserver_94:] $psql -d percona_94 psql (9.4.21) Type "help" for help. percona_94=# SELECT pglogical.create_node(node_name := 'provider1',dsn := 'host=192.168.0.24 port=5432 dbname=percona_94'); create_node ------------- 2976894835 (1 row) percona_94=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']); ERROR: table pgbench_history cannot be added to replication set default DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs HINT: Add a PRIMARY KEY to the table percona_94=# ALTER TABLE pgbench_history ADD PRIMARY KEY (tid,aid,delta); ALTER TABLE percona_94=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']); replication_set_add_all_tables -------------------------------- t (1 row)
Étape 3. Sur le nœud d'abonné, c'est-à-dire dans la base de données PostgreSQL 11, exécutez les commandes suivantes.
[pgserver_11:] $psql -d percona_11 psql (11.2) Type "help" for help. percona_11=# SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host=127.0.0.1 port=5432 dbname=percona_11 password=secret'); create_node ------------- 330520249 (1 row) percona_11=# SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=192.168.0.24 port=5432 dbname=percona_94 password=secret'); create_subscription --------------------- 1763399739 (1 row)
Étape 4. Vérifiez ensuite l'état de la réplication en envoyant une demande à plusieurs tables, que pglogical met toujours à jour:
percona_11=# select * from pglogical.local_sync_status; sync_kind | sync_subid | sync_nspname | sync_relname | sync_status | sync_statuslsn -----------+------------+--------------+------------------+-------------+---------------- f | 1763399739 | public | pgbench_accounts | r | 0/2EB7D48 f | 1763399739 | public | pgbench_history | r | 0/2EB7D48 f | 1763399739 | public | pgbench_tellers | r | 0/2EB7D48 f | 1763399739 | public | pgbench_branches | r | 0/2EB7D48 d | 1763399739 | | | r | 0/0 (5 rows) percona_11=# select * from pglogical.subscription; sub_id | sub_name | sub_origin | sub_target | sub_origin_if | sub_target_if | sub_enabled | sub_slot_name | sub_rep lication_sets | sub_forward_origins | sub_apply_delay ------------+---------------+------------+------------+---------------+---------------+-------------+----------------------------------------+---------------- -----------------------+---------------------+----------------- 1763399739 | subscription1 | 2976894835 | 330520249 | 2402836775 | 2049915666 | t | pgl_percona_11_provider1_subscription1 | {default,defaul t_insert_only,ddl_sql} | {all} | 00:00:00 (1 row)
Sélection de la clé primaire
Dans la deuxième étape, vous avez vu comment toutes les tables du schéma public ont été ajoutées au jeu de réplication en créant une clé primaire pour une table qui n'en avait pas. J'ai peut-être choisi la mauvaise clé primaire pour cette table, mais c'est juste pour la démonstration. Lorsque vous choisissez une clé primaire, assurez-vous qu'elle est correcte. Il doit être unique et utiliser des colonnes qui ne contiennent pas de valeurs NULL. Si vous ne trouvez pas la clé primaire correcte, cela peut entraîner un temps d'arrêt de l'application. Voici un exemple d'erreur qui peut se produire:
[pgserver_94:] $pgbench -c 10 -T 300 -n percona_94 Client 7 aborted in state 12: ERROR: duplicate key value violates unique constraint "pgbench_history_pkey" DETAIL: Key (tid, aid, delta)=(7, 63268, 2491) already exists.
Voici comment utiliser pglogical pour créer une réplication entre l'ancienne et la nouvelle version de PostgreSQL. Après avoir configuré la réplication, basculez simplement les applications vers la dernière version afin que le temps d'arrêt soit minimal.