ProHoster > Blog > Administration > PgGraph — un outil pour l'archivage et la recherche de dĂ©pendances de tables dans PostgreSQL

PgGraph — un outil pour l'archivage et la recherche de dĂ©pendances de tables dans PostgreSQL

PgGraph — un outil pour l'archivage et la recherche de dĂ©pendances de tables dans PostgreSQL
Aujourd'hui, je souhaite présenter aux lecteurs de Habr un utilitaire écrit en Python pour travailler avec les dépendances des tables dans la base de données PostgreSQL.

L'API de l'utilitaire est simple et se compose de trois méthodes :

  • archive_table — archivage/rĂ©cursif de la suppression des lignes avec les Primary Keys spĂ©cifiĂ©es
  • get_table_references — recherche des dĂ©pendances d'une table (affiche les tables auxquelles la table spĂ©cifiĂ©e fait rĂ©fĂ©rence et celles qui y font rĂ©fĂ©rence)
  • get_rows_references — recherche des lignes dans d'autres tables qui font rĂ©fĂ©rence aux lignes spĂ©cifiĂ©es dans la table souhaitĂ©e

Contexte

Je m'appelle Oleg Borzov, je suis dĂ©veloppeur dans l'Ă©quipe CRM pour les gestionnaires de prĂȘts hypothĂ©caires chez Domklik.

La base de données principale de notre systÚme CRM est l'une des plus volumineuses de l'entreprise. C'est aussi l'une des plus anciennes : elle est apparue dÚs le lancement du projet, lorsque les arbres étaient grands, que Domklik était une startup, et qu'au lieu d'un microservice sur un cadre Python asynchrone tendance, il y avait un énorme monolithe en PHP.

Le passage de PHP à Python a été trÚs long et a nécessité le soutien simultané des deux systÚmes, ce qui a eu un impact sur la conception de la base de données.

En consĂ©quence, nous avons une base de donnĂ©es avec un grand nombre de tables fortement liĂ©es et de taille Ă©norme, avec beaucoup d'index pour diffĂ©rents types de requĂȘtes. Tout cela affecte nĂ©gativement les performances de la base de donnĂ©es : en raison des grandes tables et des nombreuses relations entre elles, la complexitĂ© des requĂȘtes augmente constamment, ce qui est particuliĂšrement critique pour les tables les plus sollicitĂ©es.

Pour réduire la charge sur la base de données, nous avons décidé d'écrire un script qui déplacerait quotidiennement les anciennes entrées des tables les plus volumineuses et les plus sollicitées dans les tables d'archives (par exemple, depuis task dans task_archive).

Cette tĂąche est compliquĂ©e par le grand nombre de relations entre les tables : il ne suffit pas de dĂ©placer les lignes depuis task dans task_archive il faut au prĂ©alable faire la mĂȘme chose rĂ©cursivement avec toutes les tables qui y font rĂ©fĂ©rence. task Je vais dĂ©montrer avec l'exemple de

la base de données de démonstration du site postgrespro.ru Supposons que nous devons supprimer des enregistrements de la table:

PgGraph — un outil pour l'archivage et la recherche de dĂ©pendances de tables dans PostgreSQL
Flights . De maniÚre simple, Postgres ne nous permettra pas de le faire : nous devons d'abord supprimer les enregistrements de toutes les tables qui y font référence, et cela de maniÚre récursive jusqu'aux tables auxquelles personne ne fait référence.Dans notre exemple, sur

Ticket_flights . De maniĂšre simple, Postgres ne nous permettra pas de le faire : nous devons d'abord supprimer les enregistrements de toutes les tables qui y font rĂ©fĂ©rence, et cela de maniĂšre rĂ©cursive jusqu'aux tables auxquelles personne ne fait rĂ©fĂ©rence. fait rĂ©fĂ©rence Ă  , et celle-ci —Boarding_passes Donc, il faut supprimer dans cet ordre :.

Nous récupérons les valeurs des clés primaires (Primary Keys, PK) des lignes dans

  1. , qui font rĂ©fĂ©rence aux lignes Ă  supprimer dans , et celle-ci —Nous obtenons les PK des lignes . De maniĂšre simple, Postgres ne nous permettra pas de le faire : nous devons d'abord supprimer les enregistrements de toutes les tables qui y font rĂ©fĂ©rence, et cela de maniĂšre rĂ©cursive jusqu'aux tables auxquelles personne ne fait rĂ©fĂ©rence..
  2. , qui font rĂ©fĂ©rence Ă  Donc, il faut supprimer dans cet ordre :, qui font rĂ©fĂ©rence Ă  , et celle-ci —.
  3. Nous supprimons les lignes par PK de la p.2 dans le tableau Donc, il faut supprimer dans cet ordre :.
  4. Nous supprimons les lignes par PK de la p.1 dans , et celle-ci —.
  5. Nous supprimons les lignes de . De maniÚre simple, Postgres ne nous permettra pas de le faire : nous devons d'abord supprimer les enregistrements de toutes les tables qui y font référence, et cela de maniÚre récursive jusqu'aux tables auxquelles personne ne fait référence..

Le résultat est un utilitaire nommé PgGraph, que nous avons décidé de rendre open source.

Comment utiliser

L'utilitaire prend en charge deux modes d'utilisation :

  • Appel depuis la ligne de commande (pggraph 
).
  • Utilisation dans le code Python (classe PgGraphApi).

Installation et configuration

Tout d'abord, il faut installer l'utilitaire depuis le dépÎt Pypi :

pip3 install pggraph

Ensuite, créez un fichier config.ini sur votre machine locale avec la configuration de la base de données et du script d'archivage :

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; ParamÚtre optionnel, valeur par défaut indiquée

[archive]  ; Cette section est facultative, valeurs par défaut indiquées ci-dessous
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Lancement depuis la console

ParamĂštres

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
arguments positionnels :
  action        action requise : archive_table, get_table_references, get_rows_references

arguments optionnels :
  -h, --help                    afficher ce message d'aide et quitter
  --table TABLE                 nom de la table
  --ids IDS                     ids de clés primaires, séparés par des virgules, par exemple 1,2,3
  --config_path CONFIG_PATH     chemin vers config.ini
  --log_path LOG_PATH           chemin vers le répertoire des logs
  --log_level LOG_LEVEL         niveau de log (debug, info, error)

Arguments positionnels :

  • action — action requise : archive_table, get_table_references ou get_rows_references.

Arguments nommés :

  • --config_path — chemin vers le fichier de configuration ;
  • --table — table sur laquelle effectuer l'action ;
  • --ids — liste d'id sĂ©parĂ©s par des virgules, par exemple, 1,2,3 (paramĂštre optionnel);
  • --log_path — chemin vers le dossier des logs (paramĂštre optionnel, par dĂ©faut — dossier personnel);
  • --log_level — niveau de journalisation (paramĂštre optionnel, par dĂ©faut — INFO).

Exemples de commandes

Archivage de la table

La fonction principale de l'utilitaire est l'archivage des données, c'est-à-dire le déplacement des lignes de la table principale vers la table d'archive (par exemple, de la table books dans books_archive).

La suppression sans archivage est également prise en charge : pour cela, il faut définir le paramÚtre dans config.ini to_archive = false).

Les paramùtres obligatoires sont — config_path, table et ids.

AprÚs le lancement, les enregistrements seront supprimés de maniÚre récursive ids dans la table table et dans toutes les tables qui y font référence.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - DÉBUT
2020-06-20 19:27:44 INFO: flights - début archive_recursive 3 lignes (profondeur=0)
2020-06-20 19:27:44 INFO:       DÉBUT ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:       ticket_flights - début archive_recursive 3 lignes (profondeur=1)
2020-06-20 19:27:44 INFO:               DÉBUT ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:               boarding_passes - début archive_recursive 3 lignes (profondeur=2)
2020-06-20 19:27:44 INFO:                       DÉBUT ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:                       FIN ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 lignes par ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - début archive_recursive 3 lignes (profondeur=2)
2020-06-20 19:27:44 INFO:                       DÉBUT ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:                       FIN ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 lignes par ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - début archive_recursive 3 lignes (profondeur=2)
2020-06-20 19:27:44 INFO:                       DÉBUT ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:                       FIN ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 lignes par ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - début archive_recursive 3 lignes (profondeur=2)
2020-06-20 19:27:44 INFO:                       DÉBUT ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:                       FIN ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 lignes par ticket_no, flight_id
2020-06-20 19:27:44 INFO:               FIN ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 lignes par ticket_no, flight_id
2020-06-20 19:27:44 INFO:       FIN ARCHIVE DES TABLES DE RÉFÉRENCE
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 lignes par id
2020-06-20 19:27:44 INFO: flights - FIN

Recherche des dépendances pour la table spécifiée

Fonction de recherche des dĂ©pendances de la table spĂ©cifiĂ©e table. Les paramĂštres obligatoires sont — config_path et table.

AprĂšs le lancement, un dictionnaire sera affichĂ© Ă  l'Ă©cran oĂč :

  • in_refs — dictionnaire des tables renvoyant Ă  celle-ci, oĂč la clĂ© est le nom de la table, la valeur est une liste d'objets Foreign Key (pk_main — clĂ© primaire dans la table principale, pk_ref — clĂ© primaire dans la table rĂ©fĂ©rencĂ©e, fk_ref — nom de la colonne Ă©tant la clĂ© Ă©trangĂšre vers la table source) ;
  • out_refs — dictionnaire des tables vers lesquelles cette table renvoie.

$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Recherche des liens vers les lignes avec les Primary Key spécifiés

Fonction de recherche des lignes dans d'autres tables qui renvoient via Foreign Key à des lignes ids de la table table. Les paramùtres obligatoires sont — config_path, table et ids.

AprÚs le lancement, un dictionnaire sera affiché à l'écran avec la structure suivante :

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Exemple d'appel :

$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Utilisation dans le code

Outre l'exĂ©cution dans la console, la bibliothĂšque peut ĂȘtre utilisĂ©e dans du code Python. Voici des exemples d'appel dans un environnement interactif iPython.

Archivage de la table

>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flights - DÉBUT
2020-06-20 23:12:08 INFO: flights - commencer archive_recursive 2 lignes (profondeur=0)
2020-06-20 23:12:08 INFO: 	DÉBUT ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 DEBUG: 	ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: 	SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: 	ticket_flights - commencer archive_recursive 30 lignes (profondeur=1)
2020-06-20 23:12:08 INFO: 		DÉBUT ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 lignes par ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 lignes
2020-06-20 23:12:08 INFO: 		FIN ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 lignes par flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 lignes
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 lignes
2020-06-20 23:12:08 INFO: 	ticket_flights - commencer archive_recursive 30 lignes (profondeur=1)
2020-06-20 23:12:08 INFO: 		DÉBUT ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 lignes par ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 lignes
2020-06-20 23:12:08 INFO: 		FIN ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 lignes par flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 lignes
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 lignes
2020-06-20 23:12:08 INFO: 	ticket_flights - commencer archive_recursive 30 lignes (profondeur=1)
2020-06-20 23:12:08 INFO: 		DÉBUT ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 lignes par ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 lignes
2020-06-20 23:12:08 INFO: 		FIN ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 lignes par flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 lignes
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 lignes
2020-06-20 23:12:08 INFO: 	ticket_flights - commencer archive_recursive 3 lignes (profondeur=1)
2020-06-20 23:12:08 INFO: 		DÉBUT ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 3 lignes par ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 lignes
2020-06-20 23:12:08 INFO: 		FIN ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 lignes par flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 3 lignes
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 lignes
2020-06-20 23:12:08 INFO: 	FIN ARCHIVE DES TABLES RÉFÉRENCES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 lignes par flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: DELETE FROM flights by flight_id - 2 lignes
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 lignes
2020-06-20 23:12:09 INFO: flights - FIN

Recherche des dépendances pour la table spécifiée

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Recherche des liens vers les lignes avec les Primary Key spécifiés

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Le code source de la bibliothÚque est disponible sur GitHub sous licence MIT, ainsi que dans le dépÎt PyPI.

Je serai heureux de recevoir des commentaires, des commits et des suggestions.

Je tenterai de répondre aux questions dans la mesure du possible ici et dans le dépÎt.

Source : habr.com

Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS đŸ”„ Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS | ProHoster