🎿 👨🏽‍💻 👇🏼 PgGraph - Dienstprogramm zum Archivieren und Auffinden von Tabellenabhängigkeiten in PostgreSQL 🕺🏼 ☕️ 👩‍❤️‍💋‍👩

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

Die Utility-API ist einfach und besteht aus drei Methoden:

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

Hintergrund

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

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

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

Infolgedessen verfügen wir über eine Datenbank mit einer großen Anzahl stark verbundener und großer 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 Verbindungen zwischen ihnen nimmt die Komplexität von Abfragen ständig zu, was insbesondere für die am meisten geladenen Tabellen von entscheidender Bedeutung ist.

Um die Belastung der Datenbank zu verringern, haben wir beschlossen, ein Skript zu schreiben, das jeden Tag alte Datensätze von den umfangreichsten und geladensten Tabellen in Archivtabellen (z. B. von taskc task_archive) überträgt .

Diese Aufgabe wird durch eine große Anzahl von Beziehungen zwischen Tabellen kompliziert: Es reicht nicht aus, nur Zeilen von tasknach zu übertragen task_archive, bevor Sie dasselbe rekursiv mit allen verweisenden taskTabellen tun müssen.

Ich werde mit einem Beispiel demonstrierenDemo-Datenbank von postgrespro.ru :

Angenommen, wir müssen Datensätze aus einer Tabelle löschen Flights. Postgres erlaubt uns dies nicht einfach so: Wir müssen zuerst Datensätze aus allen verweisenden Tabellen löschen und so rekursiv auf Tabellen, auf die sich niemand bezieht.

In unserem Beispiel Flightsbezieht es sich auf Ticket_flightsund darauf - Boarding_passes.

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

Rufen Sie die Werte der Primärschlüssel (PK) der Zeilen in ab Ticket_flights, die sich auf die gelöschten Zeilen in beziehen Flights.
Wir erhalten die PK der Zeilen Boarding_passes, auf die verwiesen wird Ticket_flights.
Wir löschen Zeilen von PK aus Punkt 2 in der Tabelle Boarding_passes.
Wir löschen Zeilen von PK aus Abschnitt 1 in Ticket_flights.
Wir löschen Zeilen aus Flights.

Das Ergebnis ist ein Dienstprogramm namens PgGraph, das wir für Open Source entschieden haben.

Wie benutzt man

Das Dienstprogramm unterstützt zwei Verwendungsmodi:

Aufruf von der Kommandozeile ( pggraph …).
Verwendung in Python-Code (Klasse PgGraphApi).

Installation und Konfiguration

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

pip3 install pggraph

Erstellen Sie dann auf dem lokalen Computer eine Datei config.ini mit der Datenbankkonfiguration und 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'

Laufen von der Konsole

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- der gewünschte Effekt: archive_table, get_table_referencesoder get_rows_references.

Benannte Argumente:

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

Befehlsbeispiele

Eine Tabelle archivieren

Die Hauptfunktion des Dienstprogramms ist die Datenarchivierung, d.h. Übertragen von Zeilen aus der Haupttabelle zum Archivtabelle (zum Beispiel aus der Bücher - Tabelle zu books_archive ).

Das Löschen ohne Archivierung wird ebenfalls unterstützt: Setzen Sie dazu den Parameter in der Datei config.ini auf_archive = false .

Erforderliche Parameter sind config_path, table und ids .

Nach dem Start werden Datensätze idsin der Tabelle tableund 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: 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 von Abhängigkeiten für eine angegebene Tabelle

Eine Funktion zum Ermitteln der Abhängigkeiten der angegebenen Tabelle table. Erforderliche Parameter sind config_pathund table.

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

in_refs- Wörterbuch der Verweise auf die angegebene Tabelle, wobei Schlüssel der Name der Tabelle ist, Wert eine Liste von pk_mainFremdschlüsselobjekten ( - Primärschlüssel in der Haupttabelle, pk_ref- Primärschlüssel in der verweisenden Tabelle, fk_ref- Name der Spalte, die ein Fremdschlüssel für die Quelltabelle ist);
out_refs - das Wörterbuch der Tabellen, auf die sich die angegebene 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 Links zu Zeichenfolgen mit dem angegebenen Primärschlüssel

Eine Funktion für die Zeilen in anderen Tabellen zu finden , den Bezug zu nehmen idsTabellenzeilen table. Erforderliche Parameter sind config_path, tableund ids.

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

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

Beispiel aufrufen:

$ 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 wird nicht nur in der Konsole ausgeführt, sondern kann auch in Python-Code verwendet werden. Im Folgenden finden Sie Beispiele für Anrufe in der interaktiven iPython-Umgebung.

Eine Tabelle archivieren

>>> 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 von Abhängigkeiten für eine angegebene 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 Links zu 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 auf GitHub unter der MIT-Lizenz sowie im PyPI- Repository verfügbar .

Kommentare, Commits und Vorschläge sind willkommen.

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

PgGraph - Dienstprogramm zum Archivieren und Auffinden von Tabellenabhängigkeiten in PostgreSQL

Hintergrund

Wie benutzt man

Installation und Konfiguration

Laufen von der Konsole

Parameter

Befehlsbeispiele

Eine Tabelle archivieren

Suchen von Abhängigkeiten für eine angegebene Tabelle

Suchen von Links zu Zeichenfolgen mit dem angegebenen Primärschlüssel

Verwendung im Code

Eine Tabelle archivieren

Suchen von Abhängigkeiten für eine angegebene Tabelle

Suchen von Links zu Zeichenfolgen mit dem angegebenen Primärschlüssel

More articles: