🥇PgGraph - en Utility fir d'Archivéierung an d'Sich vun Tabellabhängegkeeten am PostgreSQL

Haut wëll ech Habr Lieser mat engem Utility geschriwwen am Python presentéieren fir mat Tabellabhängegkeeten an der PostgreSQL DBMS ze schaffen.

D'API vum Utility ass einfach a besteet aus dräi Methoden:

archive_table - rekursiv Archivéieren / Läschen Reihen mat spezifizéierte Primärschlësselen
get_table_references - Sich no Ofhängegkeete fir en Dësch (wäert Tabelle weisen, déi vum spezifizéierte referenzéierten an déi referenzéieren)
get_rows_references - Sich no Reihen an aneren Dëscher déi spezifizéiert Zeilen an der gewënschter Tabell referenzéieren

Virgeschicht

Mäin Numm ass Oleg Borzov, ech sinn en Entwéckler am CRM Team fir Hypothekarkredit Manager zu Domklik.

D'Haaptdatenbank vun eisem CRM System ass ee vun de gréissten a punkto Volumen an der Firma. Et ass och ee vun deenen eelsten: et erschéngt um ganz Start vum Projet, wéi d'Beem grouss waren, Domklik war e Startup, an amplaz vun engem Mikroservice op engem fashionable Python asynchrone Kader gouf et e grousse Monolith am PHP.

Den Iwwergank vu PHP op Python war ganz laang an erfuerdert gläichzäiteg Ënnerstëtzung vu béide Systemer, wat den Design vun der Datebank beaflosst.

Als Resultat hu mir eng Datebank mat enger grousser Zuel vun héich verbonnen a riesegen Dëscher mat enger Rëtsch Indexer fir verschidden Aarte vu Ufroen. All dëst beaflosst negativ d'Performance vun der Datebank: wéinst groussen Dëscher an enger Rëtsch Bezéiungen tëscht hinnen ass d'Komplexitéit vun den Ufroen konstant eropgaang, wat besonnesch kritesch ass fir déi meescht gelueden Dëscher.

Fir d'Laascht op d'Datebank ze reduzéieren, hu mir beschloss e Skript ze schreiwen, deen al records vun de meeschte voluminösen a geluedenen Dëscher op archivéiert (zum Beispill vun task в task_archive).

Dës Aufgab ass komplizéiert vun der grousser Zuel vu Relatiounen tëscht Dëscher: einfach Zeile réckelen aus task в task_archive ass net genuch, ier Dir musst dat selwecht rekursiv mat all deene Referenzen maachen task Dëscher.

Ech wäert mat engem Beispill demonstréieren Demo Datebank vum Site postgrespro.ru:

Loosst eis soen, mir mussen records aus engem Dësch läschen Flights. Postgres wäert eis net erlaben dat just esou ze maachen: Mir mussen éischt records aus all Referenz Dëscher läschen, a sou weider rekursiv erof op Dëscher déi net vu jidderengem referenzéiert sinn.

An eisem Beispill um Flights bezitt Ticket_flights, an op hir - Boarding_passes.

Dofir musst Dir et an dëser Reiefolleg läschen:

Mir kréien d'Primärschlësselen (PK) Wäerter vun de Reihen an Ticket_flights, déi op d'Reihen bezéien, déi geläscht ginn Flights.
Mir kréien PK Reien Boarding_passes, déi op Ticket_flights.
Mir läschen Reihen vum PK vum Schrëtt 2 an der Tabell Boarding_passes.
Läschen Linnen vun PK aus Schrëtt 1 an Ticket_flights.
Ewechzehuelen Linnen aus Flights.

D'Resultat war en Utility mam Numm PgGraph, dee mir decidéiert hunn Open Source ze maachen.

Wéi benotzen

D'Utility ënnerstëtzt zwee Benotzungsmodi:

Rufft vun der Kommandozeil (pggraph …).
Benotzung am Python Code (Klass PgGraphApi).

Installatioun a Konfiguratioun

Als éischt musst Dir den Utility vum Pypi Repository installéieren:

pip3 install pggraph

Erstellt dann eng config.ini Datei op der lokaler Maschinn mat der Konfiguratioun vun der Datebank an dem Archivskript:

[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'

Vun der Konsole lafen

Parameteren

$ 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)

Positional Argumenter:

action - néideg Aktioun: archive_table, get_table_references oder get_rows_references.

Argumenter genannt:

--config_path - Wee fir d'Configuratiounsdatei;
--table - en Dësch mat deem Dir eng Aktioun maache musst;
--ids - Lëscht vun ID getrennt duerch Komma, zum Beispill, 1,2,3 (optional Parameter);
--log_path - Wee an den Dossier fir Logbicher (optional Parameter, par défaut - Heem Dossier);
--log_level - Loggingsniveau (optional Parameter, Standard ass INFO).

Kommando Beispiller

En Dësch archivéieren

D'Haaptfunktioun vum Utility ass d'Datenarchivéierung, d.h. Zeile vun der Haapttabell op d'Archivtabell iwwerdroen (zum Beispill aus der Tabell Bicher в books_archive).

Läschen ouni Archivéieren gëtt och ënnerstëtzt: dofir musst Dir de Parameter an config.ini setzen to_archive = falsch).

Néideg Parameteren - config_path, Dësch an ids.

Nom Start ginn records rekursiv geläscht ids an der Tabell table an all Dëscher, déi op et bezéien.

$ 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

Fannt Ofhängegkeete fir eng spezifizéiert Tabell

Funktioun fir Ofhängegkeete vun enger spezifizéierter Tabell ze fannen table. Néideg Parameteren - config_path и table.

Nom Start gëtt e Wierderbuch um Bildschierm ugewisen, wou:

in_refs - e Wierderbuch vun Dëscher, déi e bestëmmte referenzéieren, wou de Schlëssel den Numm vun der Tabell ass, de Wäert ass eng Lëscht vun auslännesche Schlësselobjekter (pk_main - primäre Schlëssel an der Haapttabell, pk_ref - primäre Schlëssel an der Referenztabell, fk_ref - den Numm vun der Kolonn déi den auslännesche Schlëssel fir d'Quelltabell ass);
out_refs - e Wierderbuch vun Dëscher op deen dee bezitt.

$ 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')]}}

Fannt Referenzen op Strings mat dem spezifizéierte Primärschlëssel

Funktioun fir no Reihen an aneren Dëscher ze sichen déi op Reihen iwwer Auslännesch Schlëssel bezéien ids Dëscher table. Néideg Parameteren - config_path, table и ids.

Nom Start gëtt e Wierderbuch mat der folgender Struktur um Bildschierm ugewisen:

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

Beispill Uruff:

$ 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'}]}}}

Benotzung am Code

Zousätzlech fir se an der Konsole ze lafen, kann d'Bibliothéik am Python Code benotzt ginn. Beispiller vun Uruff am iPython interaktiven Ëmfeld ginn hei ënnen gewisen.

En Dësch archivéieren

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

Fannt Ofhängegkeete fir eng spezifizéiert Tabell

>>> 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')]}}

Fannt Referenzen op Strings mat dem spezifizéierte Primärschlëssel

>>> 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'}]}}}

De Quellcode vun der Bibliothéik ass verfügbar op GitHub ënner MIT Lizenz, wéi och am Repository PyPI.

Ech wäert frou kommentéieren, engagéiert a Virschléi.

Ech probéieren d'Froen no der beschtméiglechst Fäegkeet hei an am Repository ze beäntweren.

Source: will.com

PgGraph ass en Utility fir d'Archivéieren an d'Tabellabhängegkeeten an PostgreSQL ze fannen

Virgeschicht

Wéi benotzen

Installatioun a Konfiguratioun

Vun der Konsole lafen

Parameteren

Kommando Beispiller

En Dësch archivéieren

Fannt Ofhängegkeete fir eng spezifizéiert Tabell

Fannt Referenzen op Strings mat dem spezifizéierte Primärschlëssel

Benotzung am Code

En Dësch archivéieren

Fannt Ofhängegkeete fir eng spezifizéiert Tabell

Fannt Referenzen op Strings mat dem spezifizéierte Primärschlëssel

Setzt e Commentaire annuléieren reply