ProHoster > Blog > Administration > PgGraph er et værktøj til arkivering og søgning af tabelafhængigheder i PostgreSQL.

PgGraph er et værktøj til arkivering og søgning af tabelafhængigheder i PostgreSQL.

PgGraph er et værktøj til arkivering og søgning af tabelafhængigheder i PostgreSQL.
I dag vil jeg præsentere læserne af Habr et værktøj skrevet i Python til håndtering af tabelafhængigheder i PostgreSQL-databaser.

API'et for værktøjet er enkelt og består af tre metoder:

  • archive_table — rekursiv arkivering/fjernelse af rækker med angivne primære nøgler
  • get_table_references — søgning af afhængigheder for en tabel (viser tabeller, som den angivne tabel refererer til, samt tabeller, der refererer til den)
  • get_rows_references — søgning af rækker i andre tabeller, der refererer til de angivne rækker i den ønskede tabel

Background

Mit navn er Oleg Borzov, jeg er udvikler i CRM-teamet for ejendomslånesælgere hos Domklik.

Den primære database i vores CRM-system er en af de største i virksomheden. Den er også en af de ældste: den blev oprettet ved projektets opstart, da træerne var høje, Domklik var en opstart, og i stedet for en mikrotjeneste baseret på et populært asynkront Python-framework, havde vi et kæmpe monolitisk system på PHP.

Overgangen fra PHP til Python var meget lang og krævede samtidig støtte til begge systemer, hvilket påvirkede databaseudformningen.

Som et resultat har vi en database med et stort antal stærkt sammenkoblede og enormt store tabeller med mange indekser til forskellige typer forespørgsler. Alt dette påvirker databaseydelsen negativt: på grund af de store tabeller og mange relationer mellem dem vokser kompleksiteten af forespørgslerne konstant, hvilket er særligt kritisk for de mest belastede tabeller.

For at reducere belastningen på databasen besluttede vi at skrive et script, der dagligt via cron ville overføre gamle poster fra de største og mest belastede tabeller til arkivtabeller (for eksempel fra task i task_archive).

Denne opgave kompliceres af det store antal relationer mellem tabellerne: det er ikke tilstrækkeligt blot at overføre rækker fra task i task_archive , før det samme skal gøres rekursivt med alle refererende task tabeller.

Jeg vil demonstrere med et eksempel fra den demonstrerende database på postgrespro.ru:

PgGraph er et værktøj til arkivering og søgning af tabelafhængigheder i PostgreSQL.
Antag, at vi skal slette poster fra tabellen Flights. Det kan Postgres ikke gøre uden videre: først skal vi slette poster fra alle refererende tabeller, og således rekursivt ned til de tabeller, som ingen henviser til.

I vores eksempel til Flights refereres der til Ticket_flights, og til den Boarding_passes.

Derfor skal sletningen ske i følgende rækkefølge:

  1. Vi får de primære nøgleværdier (Primary Keys, PK) for rækkerne i Ticket_flights, der refererer til de slettede rækker i Flights.
  2. Vi får PK for rækkerne Boarding_passes, der refererer til Ticket_flights.
  3. Fjerner rækker efter PK fra punkt 2 i tabellen Boarding_passes.
  4. Fjerner rækker efter PK fra punkt 1 i Ticket_flights.
  5. Fjerner rækker fra Flights.

Resultatet blev et værktøj kaldet PgGraph, som vi har besluttet at gøre open source.

Sådan bruger du

Værktøjet understøtter to brugsformer:

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

Installation og konfiguration

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

pip3 install pggraph

Derefter skal du oprette en fil config.ini på din lokale maskine med databasen og arkiveringsscriptets konfiguration:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Valgfri parameter, standardværdi angivet

[archive]  ; Dette afsnit er valgfrit, nedenfor angives standardværdier
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Kørsel fra konsollen

Parametre

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        krævet handling: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    vis denne hjælp og afslut
  --table TABLE                 tabelnavn
  --ids IDS                     primære nøgler ids, adskilt med komma, f.eks. 1,2,3
  --config_path CONFIG_PATH     sti til config.ini
  --log_path LOG_PATH           sti til log-mappen
  --log_level LOG_LEVEL         logniveau (debug, info, error)

Positionelle argumenter:

  • action — krævet handling: archive_table, get_table_references eller get_rows_references.

Navngivne argumenter:

  • --config_path — sti til konfigurationsfilen;
  • --table — tabel, hvortil der skal udføres en handling;
  • --ids — liste over id'er adskilt med komma, f.eks. 1,2,3 (valgfri parameter);
  • --log_path — sti til logmappen (valgfri parameter, standard er hjemme-mappen);
  • --log_level — logniveau (valgfri parameter, standard er INFO).

Eksempler på kommandoer

Arkivering af tabel

Hovedfunktionen ved værktøjet er arkivering af data, dvs. overførsel af rækker fra hovedtabellen til arkivtabellen (f.eks. fra tabellen books i books_archive).

Slettelse uden arkivering understøttes også: for dette skal parameteren to_archive = false).

Vigtige parametre — config_path, table og ids.

Efter kørsel vil posterne i ids i tabellen table blive rekursivt slettet fra alle relaterede tabeller.

$ 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

Søgning efter afhængigheder for den angivne tabel

Funktion til at finde afhængigheder for den angivne tabel table. Obligatoriske parametre — config_path og table.

Efter kørsel vil der blive vist et kort over, hvor:

  • in_refs — en ordbog over henvisende tabeller til denne tabel, hvor nøglen er tabelnavnet, og værdien er en liste over Foreign Key objekter (pk_main — primærnøgle i hovedtabellen, pk_ref — primærnøgle i den henvisende tabel, fk_ref — navnet på kolonnen, der fungerer som foreign key til hovedtabellen);
  • out_refs — en ordbog over tabeller, som denne tabel 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')]}}

Søgning efter links til rækker med angivne primære nøgler

Funktion til at finde rækker i andre tabeller, der henviser til rækker ids tabel table. Obligatoriske parametre — config_path, table og ids.

Når programmet er startet, vil der blive vist en ordbog med følgende struktur:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: værdi, row_pk_2: værdi},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Eksempel på kald:

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

Udover at blive kørt i konsollen, kan biblioteket også anvendes i Python-kode. Nedenfor vises eksempler på kald i det interaktive miljø iPython.

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

Søgning efter afhængigheder for den angivne 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')]}}

Søgning efter links til rækker med angivne primære nøgler

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

Kildekoden til biblioteket er tilgængelig på GitHub under MIT-licensen, samt i repository'et PyPI.

Jeg ser frem til kommentarer, commits og forslag.

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

Kilde: habr.com

Køb pålidelig webhosting med DDoS-beskyttelse, VPS VDS-servere 🔥 Køb pålidelig webhosting med DDoS-beskyttelse, VPS VDS-servere | ProHoster