ProHoster > Blog > administration > PgGraph est un utilitaire pour archiver et rechercher les dépendances de tables dans PostgreSQL

PgGraph est un utilitaire pour archiver et rechercher les dépendances de tables dans PostgreSQL

PgGraph est un utilitaire pour archiver et rechercher les dépendances de tables dans PostgreSQL
Aujourd'hui, je souhaite présenter aux lecteurs Habr un utilitaire écrit en Python pour travailler avec les dépendances de tables dans le SGBD PostgreSQL.

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

  • archive_table - archivage/suppression récursif de lignes avec les clés primaires spécifiées
  • get_table_references — recherche de dépendances pour une table (affichera les tables référencées par celle spécifiée et celles qui y font référence)
  • get_rows_references - rechercher des lignes dans d'autres tables faisant référence aux lignes spécifiées dans la table souhaitée

Préhistoire

Je m'appelle Oleg Borzov, je suis développeur dans l'équipe CRM pour les gestionnaires de prêts hypothécaires à Domklik.

La base de données principale de notre système CRM est l'une des plus importantes en termes de volume de l'entreprise. C'est aussi l'un des plus anciens : il est apparu dès le lancement du projet, quand les arbres étaient grands, Domklik était une startup, et au lieu d'un microservice sur le framework asynchrone Python à la mode, il y avait un énorme monolithe en PHP.

La transition de PHP vers Python a été très longue et a nécessité le support simultané des deux systèmes, ce qui a affecté la conception de la base de données.

En conséquence, nous avons une base de données avec un grand nombre de tables énormes et hautement connectées avec un tas 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 tables volumineuses et de la multitude de relations entre elles, la complexité des requêtes augmente constamment, ce qui est particulièrement critique pour les tables les plus chargées.

Pour réduire la charge sur la base de données, nous avons décidé d'écrire un script qui transférerait les anciens enregistrements des tables les plus volumineuses et chargées vers les tables archivées (par exemple, de task в task_archive).

Cette tâche est compliquée par le grand nombre de relations entre les tables : il suffit de déplacer les lignes de task в task_archive ne suffit pas, avant cela il faut faire la même chose de manière récursive avec tous ceux qui font référence task les tables.

Je vais démontrer avec un exemple base de données de démonstration du site postgrespro.ru:

PgGraph est un utilitaire pour archiver et rechercher les dépendances de tables dans PostgreSQL
Disons que nous devons supprimer des enregistrements d'une table Flights. Postgres ne nous permet pas de faire cela comme ça : nous devons d'abord supprimer les enregistrements de toutes les tables de référence, et ainsi de suite de manière récursive jusqu'aux tables qui ne sont référencées par personne.

Dans notre exemple à Flights se réfère Ticket_flights, et sur elle - Boarding_passes.

Par conséquent, vous devez le supprimer dans cet ordre :

  1. Nous obtenons les valeurs des clés primaires (PK) des lignes dans Ticket_flights, qui font référence aux lignes à supprimer dans Flights.
  2. Nous obtenons des lignes PK Boarding_passes, qui font référence à Ticket_flights.
  3. Nous supprimons les lignes par PK de l'étape 2 du tableau Boarding_passes.
  4. Supprimez les lignes par PK de l'étape 1 dans Ticket_flights.
  5. Supprimer des lignes de Flights.

Le résultat a été un utilitaire appelé 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

Vous devez d'abord installer l'utilitaire à partir du référentiel Pypi :

pip3 install pggraph

Créez ensuite un fichier config.ini sur la machine locale avec la configuration de la base de données et le script d'archivage :

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Необязательный параметр, указано значение по умолчанию

[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчанию
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Exécuter depuis la console

Paramètres

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

optional arguments:
  -h, --help                    show this help message and exit
  --table TABLE                 table name
  --ids IDS                     primary key ids, separated by comma, e.g. 1,2,3
  --config_path CONFIG_PATH     path to config.ini
  --log_path LOG_PATH           path to log dir
  --log_level LOG_LEVEL         log level (debug, info, error)

Arguments positionnels :

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

Arguments nommés :

  • --config_path — chemin d'accès au fichier de configuration ;
  • --table — une table avec laquelle vous devez effectuer une action ;
  • --ids — liste d'identifiants séparés par des virgules, par exemple, 1,2,3 (paramètre facultatif);
  • --log_path — chemin d'accès au dossier des journaux (paramètre facultatif, par défaut — dossier personnel) ;
  • --log_level — niveau de journalisation (paramètre facultatif, la valeur par défaut est INFO).

Exemples de commandes

Archivage d'un tableau

La fonction principale de l'utilitaire est l'archivage des données, c'est-à-dire transférer des lignes de la table principale vers la table d'archive (par exemple, de la table livres в livres_archive).

La suppression sans archivage est également prise en charge : pour cela, vous devez définir le paramètre dans config.ini to_archive = faux).

Paramètres requis - config_path, table et identifiants.

Après le lancement, les enregistrements seront supprimés de manière récursive ids dans la table table et dans tous les tableaux 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 - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END

Rechercher des dépendances pour une table spécifiée

Fonction pour trouver les dépendances d'une table spécifiée table. Paramètres requis - config_path и table.

Après le lancement, un dictionnaire s'affichera à l'écran, où :

  • in_refs — un dictionnaire de tables référençant une table donnée, 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 de référencement, fk_ref — le nom de la colonne qui est la clé étrangère de la table source) ;
  • out_refs — un dictionnaire de tableaux auquel celui-ci fait référence.

$ 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 de références à des chaînes avec la clé primaire spécifiée

Fonction pour rechercher des lignes dans d'autres tables faisant référence à des lignes via une clé étrangère ids des tables table. Paramètres requis - config_path, table и ids.

Après le lancement, un dictionnaire avec la structure suivante s'affichera à l'écran :

{
	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

En plus de l'exécuter dans la console, la bibliothèque peut être utilisée en code Python. Des exemples d'appels dans l'environnement interactif iPython sont présentés ci-dessous.

Archivage d'un tableau

>>> 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 - START
2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVE REFERRING TABLES
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 - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rows by 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 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: 	END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by 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 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END

Rechercher des dépendances pour une 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 de références à des chaînes avec la clé primaire spécifiée

>>> 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 engagements et des suggestions.

J'essaierai de répondre aux questions au mieux de mes capacités ici et dans le référentiel.

Source: habr.com

Ajouter un commentaire