🥇PgGraph - et værktøj til arkivering og søgning i tabelafhængigheder i PostgreSQL

I dag vil jeg præsentere Habr-læsere for et værktøj skrevet i Python til at arbejde med tabelafhængigheder i PostgreSQL DBMS.

Værktøjets API er enkel og består af tre metoder:

arkivtabel - rekursiv arkivering/sletning af rækker med specificerede primære nøgler
get_table_references — søg efter afhængigheder for en tabel (vil vise tabeller, der henvises til af den angivne, og dem, der refererer til den)
get_rows_references - søg efter rækker i andre tabeller, der refererer til specificerede rækker i den ønskede tabel

forhistorie

Mit navn er Oleg Borzov, jeg er udvikler i CRM-teamet for realkreditansvarlige i Domklik.

Hoveddatabasen i vores CRM-system er en af de største volumenmæssigt i virksomheden. Den er også en af de ældste: den dukkede op ved selve lanceringen af projektet, da træerne var store, Domklik var en startup, og i stedet for en mikroservice på en fashionabel Python asynkron ramme var der en enorm monolit i PHP.

Overgangen fra PHP til Python var meget lang og krævede samtidig understøttelse af begge systemer, hvilket påvirkede designet af databasen.

Som et resultat har vi en database med et stort antal stærkt forbundne og enorme tabeller med en masse indekser til forskellige typer forespørgsler. Alt dette påvirker databasens ydeevne negativt: På grund af store tabeller og en masse relationer mellem dem, øges kompleksiteten af forespørgsler konstant, hvilket er særligt kritisk for de mest indlæste tabeller.

For at reducere belastningen på databasen besluttede vi at skrive et script, der ville overføre gamle poster fra de mest omfangsrige og indlæste tabeller til arkiverede (f.eks. task в task_archive).

Denne opgave er kompliceret af det store antal relationer mellem tabeller: flyt blot rækker fra task в task_archive er ikke nok, før det skal du gøre det samme rekursivt med alle dem, der refererer task borde.

Jeg vil demonstrere med et eksempel demodatabase fra webstedet postgrespro.ru:

Lad os sige, at vi skal slette poster fra en tabel Flights. Postgres vil ikke tillade os at gøre dette bare sådan: Vi skal først slette poster fra alle referencetabeller og så videre rekursivt ned til tabeller, der ikke er refereret af nogen.

I vores eksempel kl Flights henviser Ticket_flights, og på hende - Boarding_passes.

Derfor skal du slette den i denne rækkefølge:

Vi får de primære nøgler (PK) værdier af rækker ind Ticket_flights, som henviser til de rækker, der skal slettes i Flights.
Vi får PK-rækker Boarding_passes, som henviser til Ticket_flights.
Vi sletter rækker efter PK fra trin 2 i tabellen Boarding_passes.
Slet linjer efter PK fra trin 1 in Ticket_flights.
Fjernelse af linjer fra Flights.

Resultatet var et hjælpeprogram kaldet PgGraph, som vi besluttede at gøre til open source.

Sådan bruges

Hjælpeprogrammet understøtter to brugsformer:

Ring fra kommandolinjen (pggraph …).
Brug i Python-kode (klasse PgGraphApi).

Installation og konfiguration

Først skal du installere værktøjet fra Pypi-depotet:

pip3 install pggraph

Opret derefter en config.ini-fil på den lokale maskine med konfigurationen af databasen og arkiveringsscriptet:

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

Kør fra konsollen

Parametre

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

Positionelle argumenter:

action - påkrævet handling: archive_table, get_table_references eller get_rows_references.

Navngivne argumenter:

--config_path — sti til konfigurationsfilen;
--table — en tabel, som du skal bruge til at udføre en handling;
--ids — liste over id adskilt af kommaer, f.eks. 1,2,3 (valgfri parameter);
--log_path — sti til mappen for logfiler (valgfri parameter, som standard — hjemmemappe);
--log_level — logningsniveau (valgfri parameter, standard er INFO).

Eksempler på kommandoer

Arkivering af en tabel

Hjælpeprogrammets hovedfunktion er dataarkivering, dvs. overførsel af rækker fra hovedtabellen til arkivtabellen (f.eks. fra tabellen bøger в bøger_arkiv).

Sletning uden arkivering understøttes også: til dette skal du indstille parameteren i config.ini to_archive = falsk).

Påkrævede parametre - config_path, tabel og id'er.

Efter lancering slettes poster rekursivt ids i bordet table og i alle tabeller, der henviser til det.

$ 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

Find afhængigheder for en specificeret tabel

Funktion til at finde afhængigheder af en specificeret tabel table. Påkrævede parametre - config_path и table.

Efter lanceringen vil en ordbog blive vist på skærmen, hvor:

in_refs — en ordbog over tabeller, der refererer til en given, hvor nøglen er navnet på tabellen, værdien er en liste over fremmednøgleobjekter (pk_main - primær nøgle i hovedtabellen, pk_ref - primær nøgle i referencetabellen, fk_ref — navnet på den kolonne, der er fremmednøglen til kildetabellen);
out_refs — en ordbog over tabeller, som denne henviser til.

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

Finde referencer til strenge med den angivne primære nøgle

Funktion til at søge efter rækker i andre tabeller, der refererer til rækker via fremmednøgle ids таблицы table. Påkrævede parametre - config_path, table и ids.

Efter lanceringen vil en ordbog med følgende struktur blive vist på skærmen:

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

Eksempel opkald:

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

Brug i kode

Udover at køre det i konsollen, kan biblioteket bruges i Python-kode. Eksempler på opkald i det interaktive iPython-miljø er vist nedenfor.

Arkivering af en tabel

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

Find afhængigheder for en specificeret tabel

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

Finde referencer til strenge med den angivne primære nøgle

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

Bibliotekets kildekode er tilgængelig på GitHub under MIT-licens, samt i depotet PyPI.

Jeg vil være glad for kommentarer, tilsagn og forslag.

Jeg vil forsøge at besvare spørgsmål efter bedste evne her og i depotet.

Kilde: www.habr.com

PgGraph er et værktøj til at arkivere og finde tabelafhængigheder i PostgreSQL

forhistorie

Sådan bruges

Installation og konfiguration

Kør fra konsollen

Parametre

Eksempler på kommandoer

Arkivering af en tabel

Find afhængigheder for en specificeret tabel

Finde referencer til strenge med den angivne primære nøgle

Brug i kode

Arkivering af en tabel

Find afhængigheder for en specificeret tabel

Finde referencer til strenge med den angivne primære nøgle

Tilføj en kommentar Annuller svar