ProHoster > Blog > Administration > PgGraph – ein Tool zum Archivieren und Suchen von TabellenabhĂ€ngigkeiten in PostgreSQL

PgGraph – ein Tool zum Archivieren und Suchen von TabellenabhĂ€ngigkeiten in PostgreSQL

PgGraph – ein Tool zum Archivieren und Suchen von TabellenabhĂ€ngigkeiten in PostgreSQL
Heute möchte ich den Lesern von Habr ein Tool vorstellen, das in Python geschrieben wurde, um mit TabellenabhÀngigkeiten in der PostgreSQL-Datenbank umzugehen.

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

  • archive_table — rekursive Archivierung/Löschung von Zeilen mit angegebenen PrimĂ€rschlĂŒsseln
  • get_table_references — Suche nach AbhĂ€ngigkeiten fĂŒr eine Tabelle (zeigt die Tabellen an, auf die verwiesen wird, sowie die, die auf die angegebene verweisen)
  • get_rows_references — Suche nach Zeilen in anderen Tabellen, die auf die angegebenen Zeilen in der gewĂŒnschten Tabelle verweisen

Hintergrund

Ich heiße Oleg Borzov und bin Entwickler im CRM-Team fĂŒr Immobilienkreditmanager bei Domklik.

Die Hauptdatenbank unseres CRM-Systems gehört zu den grĂ¶ĂŸten in unserem Unternehmen. Sie ist auch eine der Ă€ltesten: sie wurde beim Start des Projekts eingerichtet, als die BĂ€ume groß waren, Domklik ein Startup war und anstelle eines Mikrodienstes auf einem angesagten Python-Async-Framework ein riesiges Monolith auf PHP lief.

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

Das Ergebnis ist eine Datenbank mit einer Vielzahl von stark verknĂŒpften und umfangreichen Tabellen, die zahlreiche Indizes fĂŒr verschiedene Abfragetypen aufweisen. All dies hat negative Auswirkungen auf die Datenbankleistung: Aufgrund der großen Tabellen und der Vielzahl an Beziehungen zwischen ihnen wĂ€chst die KomplexitĂ€t der Abfragen stĂ€ndig, was insbesondere fĂŒr die am stĂ€rksten belasteten Tabellen kritisch ist.

Um die Belastung der Datenbank zu verringern, haben wir uns entschieden, ein Skript zu schreiben, das tĂ€glich ĂŒber einen Cron-Job alte DatensĂ€tze aus den umfangreichsten und am stĂ€rksten belasteten Tabellen in Archivtabellen ĂŒbertrĂ€gt (zum Beispiel aus task in task_archive).

. Diese Aufgabe wird durch die Vielzahl der Beziehungen zwischen den Tabellen kompliziert: Es reicht nicht aus, einfach die Zeilen aus task in task_archive zu ĂŒbertragen, bevor dies erforderlich ist, muss dasselbe rekursiv fĂŒr alle abhĂ€ngigen task Tabellen durchgefĂŒhrt werden.

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

PgGraph – ein Tool zum Archivieren und Suchen von TabellenabhĂ€ngigkeiten in PostgreSQL
Angenommen, wir mĂŒssen DatensĂ€tze aus der Tabelle Flightslöschen. Einfach so erlaubt uns Postgres das nicht: Zuvor mĂŒssen die DatensĂ€tze aus allen verknĂŒpften Tabellen gelöscht werden, und dies rekursiv bis zu den Tabellen, auf die niemand verweist.

In unserem Beispiel wird auf Flights verwiesen, Ticket_flights,und auf sie – Boarding_passes..

Deshalb muss in folgender Reihenfolge gelöscht werden:

  1. Erhalten der PrimĂ€rschlĂŒssel (Primary Keys, PK) der Zeilen in Ticket_flights,, die auf zu löschende Zeilen verweisen in Flights.
  2. Erhalten der PK der Zeilen Boarding_passes., die verweisen auf Ticket_flights,.
  3. Löschen der Zeilen nach PK aus Punkt 2 in der Tabelle Boarding_passes..
  4. Löschen der Zeilen nach PK aus Punkt 1 in Ticket_flights,.
  5. Löschen von Zeilen aus Flights.

Am Ende entstand ein Tool namens PgGraph, das wir als Open Source bereitstellen möchten.

So verwenden Sie es

Das Tool unterstĂŒtzt zwei Nutzungsmodi:

  • Aufruf aus der Kommandozeile (pggraph 
).
  • Verwendung im Python-Code (Klasse PgGraphApi).

Installation und Konfiguration

Zuerst muss das Tool aus dem Pypi-Repository installiert werden:

pip3 install pggraph

Dann eine Datei config.ini auf dem lokalen Rechner erstellen mit der Datenbank- und Archivierungsskript-Konfiguration:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Optionaler Parameter, Standardwert angegeben

[archive]  ; Dieser Abschnitt kann optional ausgefĂŒllt werden, Standardwerte sind unten angegeben
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Start aus der Konsole

Einstellungen

$ pggraph -h
Nutzung: pggraph Aktion [-h] --table TABELLE [--ids IDS] [--config_path KONFIG_PATH]
positional arguments:
  action        erforderliche Aktion: archive_table, get_table_references, get_rows_references

optionale Argumente:
  -h, --help                    zeigt diese Hilfe an und beendet das Programm
  --table TABELLE               Tabellenname
  --ids IDS                     PrimĂ€rschlĂŒssel-IDs, durch Kommas getrennt, z. B. 1,2,3
  --config_path KONFIG_PATH     Pfad zur config.ini
  --log_path LOG_PATH           Pfad zum Log-Verzeichnis
  --log_level LOG_LEVEL         Protokollierungsstufe (debug, info, error)

Positionalargumente:

  • Aktion — erforderliche Aktion: archive_table, get_table_references oder get_rows_references.

Benannte Argumente:

  • --config_path — Pfad zur Konfigurationsdatei;
  • --table — Tabelle, auf die die Aktion angewendet werden soll;
  • --ids — Liste der IDs, durch Kommas getrennt, z. B. 1,2,3 (optional);
  • --log_path — Pfad zum Log-Verzeichnis (optional, Standardwert — Home-Verzeichnis);
  • --log_level — Protokollierungsstufe (optional, Standardwert — INFO).

Beispiele fĂŒr Befehle

Tabellenarchivierung

Die Hauptfunktion des Dienstprogramms besteht in der Archivierung von Daten, d. h. dem Verschieben von Zeilen aus der Haupttabelle in die Archivtabelle (z. B. von der Tabelle bĂŒcher in bĂŒcher_archiv).

Es wird auch das Löschen ohne Archivierung unterstĂŒtzt: Dazu muss in der config.ini der Parameter to_archive = false).

Erforderliche Parameter — config_path, table und ids.

Nach dem Start werden die EintrÀge ids in der Tabelle Tabelle und in allen darauf verweisenden Tabellen rekursiv gelöscht.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: FlĂŒge - START
2020-06-20 19:27:44 INFO: FlĂŒge - beginne archive_recursive mit 3 Zeilen (Tiefe=0)
2020-06-20 19:27:44 INFO:       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:       ticket_flights - beginne archive_recursive mit 3 Zeilen (Tiefe=1)
2020-06-20 19:27:44 INFO:               BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO: FlĂŒge - archive_by_ids 3 Zeilen nach id
2020-06-20 19:27:44 INFO: FlĂŒge - ENDE

AbhĂ€ngigkeiten fĂŒr die angegebene Tabelle suchen

Funktion zur Suche nach AbhĂ€ngigkeiten der angegebenen Tabelle Tabelle. Erforderliche Parameter — config_path und Tabelle.

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

  • in_refs — ein Wörterbuch der Tabellen, die auf diese verweisen, wobei der SchlĂŒssel der Name der Tabelle und der Wert eine Liste von Foreign Keys ist (pk_main — der PrimĂ€rschlĂŒssel in der Haupttabelle, pk_ref — der PrimĂ€rschlĂŒssel in der referenzierenden Tabelle, fk_ref — der Name der Spalte, die als Foreign Key auf die UrsprĂŒngliche Tabelle verweist);
  • out_refs — ein Wörterbuch der Tabellen, auf die diese verweist.

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

Suche nach Verweisen auf Zeilen mit angegebenen PrimĂ€rschlĂŒsseln

Funktion zur Suche nach Zeilen in anderen Tabellen, die ĂŒber Foreign Keys auf Zeilen der ids Tabelle verweisen Tabelle. Erforderliche Parameter — config_path, Tabelle und ids.

Nach dem Start wird 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

Neben dem Start in der Konsole kann die Bibliothek auch im Python-Code verwendet werden. Nachfolgend sind Beispiele fĂŒr Aufrufe in der interaktiven Umgebung iPython dargestellt.

Tabellenarchivierung

>>> 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: flĂŒge - START
2020-06-20 23:12:08 INFO: flĂŒge - beginne archive_recursive 2 Zeilen (tiefe=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVIEREN DER REFERENZTABELLEN
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 - beginne archive_recursive 30 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
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 Zeilen durch 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: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 Zeilen durch 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: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 30 Zeilen
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 30 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
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 Zeilen durch 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: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 Zeilen durch 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: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 30 Zeilen
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 30 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
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 Zeilen durch 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: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 Zeilen durch 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: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 30 Zeilen
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 3 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
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 Zeilen durch 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: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 3 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 Zeilen durch 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: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 3 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 3 Zeilen
2020-06-20 23:12:08 INFO: 	ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: flĂŒge - archive_by_ids 2 Zeilen durch 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: LÖSCHEN VON flĂŒge durch flight_id - 2 Zeilen
2020-06-20 23:12:09 DEBUG: EINFÜGEN IN flights_archive - 2 Zeilen
2020-06-20 23:12:09 INFO: flĂŒge - ENDE

AbhĂ€ngigkeiten fĂŒr die angegebene Tabelle suchen

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

Suche nach Verweisen auf Zeilen mit angegebenen PrimĂ€rschlĂŒsseln

>>> 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 und im Repository PyPI.

Ich freue mich ĂŒber Kommentare, Commits und VorschlĂ€ge.

Auf Fragen werde ich hier und im Repository möglichst antworten.

Quelle: habr.com

Erwerben Sie zuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz, VPS VDS-Server đŸ”„ Kaufen Sie zuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz, VPS VDS-Server | ProHoster