🦑 📯 🚴🏻 PgGraph - utilitaire d'archivage et de recherche de dépendances de table dans PostgreSQL 😡 👩🏾‍🤝‍👨🏻 😎

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

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

archive_table - archivage récursif / suppression de lignes avec des clés primaires spécifiées
get_table_references - trouve les dépendances pour une table (affichera les tables référencées et référencées par celle spécifiée)
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 au sein de l'équipe CRM des gestionnaires de prêts hypothécaires chez 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, lorsque les arbres étaient grands, Domclick était une startup, et au lieu d'un microservice sur un 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 hautement connectées et énormes 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 grandes tables et d'un tas de connexions 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 tous les jours les anciens enregistrements des tables les plus volumineuses et chargées vers les tables d'archivage (par exemple, à partir de taskc task_archive).

Cette tâche est compliquée par le grand nombre de relations entre les tables: il ne suffit pas de transférer des lignes de taskà task_archive, avant cela, vous devez faire la même chose de manière récursive avec toutes les tasktables de référence .

Je vais démontrer avec un exemplebase de données de démonstration de postgrespro.ru :

Disons que nous devons supprimer des enregistrements d'une table Flights. Postgres ne nous permettra pas de faire cela comme ça: nous devons d'abord supprimer les enregistrements de toutes les tables de référence, et de manière récursive vers des tables auxquelles personne ne fait référence.

Dans notre exemple, il se Flightsréfère à Ticket_flights, et à lui - Boarding_passes.

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

Obtenez les valeurs des clés primaires (PK) des lignes dans Ticket_flights, qui font référence aux lignes supprimées dans Flights.
Nous obtenons le PK des lignes Boarding_passesauxquelles il est fait référence Ticket_flights.
Nous supprimons les lignes par PK de l'élément 2 du tableau Boarding_passes.
Nous supprimons les lignes par PK de l'article 1 dans Ticket_flights.
Nous supprimons des lignes de Flights.

Le résultat est 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 à partir de la ligne de commande ( pggraph …).
Utilisation en 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'

Courir 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 de position:

action- l'effet désiré: archive_table, get_table_referencesou get_rows_references.

Arguments nommés:

--config_path - chemin vers le fichier de configuration;
--table - la table avec laquelle vous souhaitez effectuer l'action;
--ids- liste des identifiants séparés par des virgules, par exemple 1,2,3(facultatif);
--log_path - chemin vers le dossier pour les journaux (paramètre facultatif, par défaut - dossier d'accueil);
--log_level - niveau de journalisation (paramètre optionnel, par défaut - INFO).

Exemples de commandes

Archiver une table

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 books vers books_archive ).

La suppression sans archivage est également prise en charge: pour ce faire, définissez le paramètre sur_archive = false dans config.ini ).

Les paramètres requis sont config_path, table et ids .

Après le démarrage, les enregistrements idsde la table tableet de toutes les tables qui y font référence seront supprimés de manière récursive .

$ 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

Recherche de dépendances pour une table spécifiée

Une fonction pour trouver les dépendances de la table spécifiée table. Les paramètres obligatoires sont config_pathet table.

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

in_refs- dictionnaire des tables référentes à celle donnée, où key est le nom de la table, valeur est la liste des objets Clé étrangère ( pk_main- clé primaire dans la table principale, pk_ref- clé primaire dans la table référente, fk_ref- nom de la colonne qui est la clé étrangère de la table source);
out_refs - le dictionnaire des tables auquel se réfère celui donné.

$ 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 liens vers des chaînes avec la clé primaire spécifiée

Une fonction pour rechercher des lignes dans d'autres tables qui font référence à idsdes lignes de table via une clé étrangère table. Les paramètres obligatoires sont config_path, tableet ids.

Après le lancement, un dictionnaire avec la structure suivante sera affiché à 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 s'exécuter dans la console, la bibliothèque peut être utilisée en code Python. Vous trouverez ci-dessous des exemples d'appels dans l'environnement interactif iPython.

Archiver une 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 - 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

Recherche de 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 liens vers 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 la licence MIT, ainsi que dans le référentiel PyPI .

Les commentaires, commits et suggestions sont les bienvenus.

Je vais essayer de répondre aux questions dans la mesure du possible ici et dans le référentiel.

PgGraph - utilitaire d'archivage et de recherche de dépendances de table dans PostgreSQL

Contexte

Comment utiliser

Installation et configuration

Courir depuis la console

Paramètres

Exemples de commandes

Archiver une table

Recherche de dépendances pour une table spécifiée

Recherche de liens vers des chaînes avec la clé primaire spécifiée

Utilisation dans le code

Archiver une table

Recherche de dépendances pour une table spécifiée

Recherche de liens vers des chaînes avec la clé primaire spécifiée

More articles: