🥇PgGraph – ein Dienstprogramm zum Archivieren und Durchsuchen von Tabellenabhängigkeiten in PostgreSQL

Heute möchte ich Habr-Lesern ein in Python geschriebenes Dienstprogramm für die Arbeit mit Tabellenabhängigkeiten im PostgreSQL-DBMS vorstellen.

Die API des Dienstprogramms ist einfach und besteht aus drei Methoden:

archive_table - Rekursives Archivieren/Löschen von Zeilen mit angegebenen Primärschlüsseln
get_table_references – Suche nach Abhängigkeiten für eine Tabelle (zeigt Tabellen an, auf die von der angegebenen Tabelle verwiesen wird, und diejenigen, die auf sie verweisen)
get_rows_references - Suchen Sie nach Zeilen in anderen Tabellen, die auf bestimmte Zeilen in der gewünschten Tabelle verweisen

Vorgeschichte

Mein Name ist Oleg Borzov, ich bin Entwickler im CRM-Team für Hypothekenkreditmanager in Domklik.

Die Hauptdatenbank unseres CRM-Systems ist volumenmäßig eine der größten im Unternehmen. Es ist auch eines der ältesten: Es erschien gleich zu Beginn des Projekts, als die Bäume groß waren, Domklik ein Startup war und anstelle eines Microservices auf einem modischen asynchronen Python-Framework ein riesiger Monolith in PHP existierte.

Der Übergang von PHP zu Python war sehr langwierig und erforderte die gleichzeitige Unterstützung beider Systeme, was sich auf das Design der Datenbank auswirkte.

Als Ergebnis verfügen wir über eine Datenbank mit einer großen Anzahl stark vernetzter und riesiger Tabellen mit einer Reihe von Indizes für verschiedene Arten von Abfragen. All dies wirkt sich negativ auf die Leistung der Datenbank aus: Aufgrund großer Tabellen und einer Vielzahl von Beziehungen zwischen ihnen nimmt die Komplexität der Abfragen ständig zu, was besonders für die am stärksten belasteten Tabellen von entscheidender Bedeutung ist.

Um die Belastung der Datenbank zu verringern, haben wir beschlossen, ein Skript zu schreiben, das alte Datensätze aus den umfangreichsten und am stärksten belasteten Tabellen in archivierte Tabellen (z. B. von) überträgt task в task_archive).

Diese Aufgabe wird durch die große Anzahl von Beziehungen zwischen Tabellen erschwert: Verschieben Sie einfach Zeilen aus task в task_archive reicht nicht aus, vorher müssen Sie das Gleiche rekursiv mit allen Verweisen tun task Tische.

Ich werde es anhand eines Beispiels demonstrieren Demodatenbank von der Website postgrespro.ru:

Nehmen wir an, wir müssen Datensätze aus einer Tabelle löschen Flights. Postgres erlaubt uns das nicht einfach so: Wir müssen zuerst Datensätze aus allen referenzierenden Tabellen löschen und so weiter rekursiv bis hin zu den Tabellen, auf die niemand verweist.

In unserem Beispiel bei Flights bezieht sich Ticket_flights, und auf ihr - Boarding_passes.

Daher müssen Sie es in dieser Reihenfolge löschen:

Wir erhalten die Primärschlüsselwerte (PK) der Zeilen in Ticket_flights, die sich auf die zu löschenden Zeilen beziehen Flights.
Wir erhalten PK-Reihen Boarding_passes, die sich beziehen auf Ticket_flights.
Wir löschen Zeilen nach PK aus Schritt 2 in der Tabelle Boarding_passes.
Löschen Sie Zeilen nach PK aus Schritt 1 in Ticket_flights.
Zeilen entfernen aus Flights.

Das Ergebnis war ein Dienstprogramm namens PgGraph, das wir als Open Source veröffentlichen wollten.

Wie zu verwenden

Das Dienstprogramm unterstützt zwei Nutzungsmodi:

Aufruf über die Kommandozeile (pggraph …).
Verwendung im Python-Code (Klasse PgGraphApi).

Installation und Konfiguration

Zuerst müssen Sie das Dienstprogramm aus dem Pypi-Repository installieren:

pip3 install pggraph

Anschließend erstellen Sie auf dem lokalen Rechner eine config.ini-Datei mit der Konfiguration der Datenbank und dem Archivierungsskript:

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

Von der Konsole ausführen

Parameter

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

Positionsargumente:

action - Benötigte Aktion: archive_table, get_table_references oder get_rows_references.

Benannte Argumente:

--config_path — Pfad zur Konfigurationsdatei;
--table — eine Tabelle, mit der Sie eine Aktion ausführen müssen;
--ids – Liste der durch Kommas getrennten IDs, zum Beispiel 1,2,3 (optionaler Parameter);
--log_path – Pfad zum Ordner für Protokolle (optionaler Parameter, standardmäßig – Home-Ordner);
--log_level — Protokollierungsstufe (optionaler Parameter, Standard ist INFO).

Befehlsbeispiele

Archivieren einer Tabelle

Die Hauptfunktion des Dienstprogramms ist die Datenarchivierung, d.h. Übertragen von Zeilen aus der Haupttabelle in die Archivtabelle (z. B. aus der Tabelle Bücher в Bücher_Archiv).

Auch das Löschen ohne Archivierung wird unterstützt: Hierzu müssen Sie den Parameter in der config.ini setzen to_archive = false).

Erforderliche Parameter - config_path, Tabelle und IDs.

Nach dem Start werden Datensätze rekursiv gelöscht ids in der Tabelle table und in allen Tabellen, die darauf verweisen.

$ 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

Suchen Sie nach Abhängigkeiten für eine bestimmte Tabelle

Funktion zum Finden von Abhängigkeiten einer angegebenen Tabelle table. Erforderliche Parameter - config_path и table.

Nach dem Start wird auf dem Bildschirm ein Wörterbuch angezeigt, in dem Folgendes steht:

in_refs – ein Wörterbuch von Tabellen, die auf eine bestimmte Tabelle verweisen, wobei der Schlüssel der Name der Tabelle und der Wert eine Liste von Fremdschlüsselobjekten ist (pk_main - Primärschlüssel in der Haupttabelle, pk_ref - Primärschlüssel in der referenzierenden Tabelle, fk_ref — der Name der Spalte, die der Fremdschlüssel für die Quelltabelle ist);
out_refs – ein Wörterbuch mit Tabellen, auf die sich dieses bezieht.

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

Suchen von Verweisen auf Zeichenfolgen mit dem angegebenen Primärschlüssel

Funktion zum Suchen nach Zeilen in anderen Tabellen, die über Fremdschlüssel auf Zeilen verweisen ids Tabellen table. Erforderliche Parameter - config_path, table и ids.

Nach dem Start wird auf dem Bildschirm ein Wörterbuch mit folgender Struktur angezeigt:

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

Beispielaufruf:

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

Verwendung im Code

Die Bibliothek kann nicht nur in der Konsole ausgeführt werden, sondern auch im Python-Code verwendet werden. Beispiele für Aufrufe in der interaktiven iPython-Umgebung sind unten aufgeführt.

Archivieren einer Tabelle

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

Suchen Sie nach Abhängigkeiten für eine bestimmte Tabelle

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

Suchen von Verweisen auf Zeichenfolgen mit dem angegebenen 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'}]}}}

Der Quellcode der Bibliothek ist verfügbar unter GitHub unter MIT-Lizenz, sowie im Repository PyPI.

Ich freue mich über Kommentare, Zusagen und Vorschläge.

Ich werde versuchen, die Fragen hier und im Repository so gut wie möglich zu beantworten.

Source: habr.com

PgGraph ist ein Dienstprogramm zum Archivieren und Suchen von Tabellenabhängigkeiten in PostgreSQL

Vorgeschichte

Wie zu verwenden

Installation und Konfiguration

Von der Konsole ausführen

Parameter

Befehlsbeispiele

Archivieren einer Tabelle

Suchen Sie nach Abhängigkeiten für eine bestimmte Tabelle

Suchen von Verweisen auf Zeichenfolgen mit dem angegebenen Primärschlüssel

Verwendung im Code

Archivieren einer Tabelle

Suchen Sie nach Abhängigkeiten für eine bestimmte Tabelle

Suchen von Verweisen auf Zeichenfolgen mit dem angegebenen Primärschlüssel

Kommentar hinzufügen Отменить ответ