🥇PgGraph - un utilitar pentru arhivarea și căutarea dependențelor de tabel în PostgreSQL

Astăzi vreau să prezint cititorilor Habr un utilitar scris în Python pentru a lucra cu dependențe de tabel în SGBD-ul PostgreSQL.

API-ul utilitarului este simplu și constă din trei metode:

tabel_arhivă - arhivare recursivă/ștergere rânduri cu chei primare specificate
get_table_references — căutați dependențe pentru un tabel (va afișa tabelele la care se face referire de cel specificat și cele care îl fac referire)
get_rows_references - căutați rânduri în alte tabele care fac referire la rândurile specificate în tabelul dorit

preistorie

Numele meu este Oleg Borzov, sunt dezvoltator în echipa CRM pentru managerii de credite ipotecare din Domklik.

Baza de date principală a sistemului nostru CRM este una dintre cele mai mari din punct de vedere al volumului din companie. Este și una dintre cele mai vechi: a apărut chiar la lansarea proiectului, când copacii erau mari, Domklik era un startup, iar în loc de un microserviciu pe un cadru asincron Python la modă a existat un monolit uriaș în PHP.

Tranziția de la PHP la Python a fost foarte lungă și a necesitat suport simultan al ambelor sisteme, ceea ce a afectat designul bazei de date.

Ca rezultat, avem o bază de date cu un număr mare de tabele foarte conectate și uriașe, cu o mulțime de indici pentru diferite tipuri de interogări. Toate acestea afectează negativ performanța bazei de date: datorită tabelelor mari și a multor relații între ele, complexitatea interogărilor este în continuă creștere, ceea ce este deosebit de critic pentru cele mai încărcate tabele.

Pentru a reduce încărcarea bazei de date, am decis să scriem un script care să transfere înregistrările vechi din tabele cele mai voluminoase și încărcate în cele arhivate (de exemplu, din task в task_archive).

Această sarcină este complicată de numărul mare de relații dintre tabele: pur și simplu mutați rândurile din task в task_archive nu este suficient, înainte de asta trebuie să faceți același lucru recursiv cu toate acele referințe task Mese.

Voi demonstra cu un exemplu baza de date demo de pe site-ul postgrespro.ru:

Să presupunem că trebuie să ștergem înregistrările dintr-un tabel Flights. Postgres nu ne va permite să facem asta doar așa: mai întâi trebuie să ștergem înregistrările din toate tabelele de referință și așa mai departe recursiv până la tabelele care nu sunt referite de nimeni.

În exemplul nostru la Flights se referă Ticket_flights, iar pe ea - Boarding_passes.

Prin urmare, trebuie să-l ștergeți în această ordine:

Obținem valorile cheilor primare (PK) ale rândurilor în Ticket_flights, care se referă la rândurile în care urmează să fie șterse Flights.
Primim rânduri PK Boarding_passes, care se referă la Ticket_flights.
Ștergem rândurile după PK de la pasul 2 din tabel Boarding_passes.
Ștergeți linii după PK de la pasul 1 în Ticket_flights.
Eliminarea liniilor din Flights.

Rezultatul a fost un utilitar numit PgGraph, pe care am decis să îl facem open source.

Cum să utilizați

Utilitarul acceptă două moduri de utilizare:

Apel din linia de comandă (pggraph …).
Utilizare în codul Python (clasa PgGraphApi).

Instalare și configurare

Mai întâi trebuie să instalați utilitarul din depozitul Pypi:

pip3 install pggraph

Apoi creați un fișier config.ini pe mașina locală cu configurația bazei de date și scriptul de arhivare:

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

Rulați de pe consolă

Parametrii

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

Argumente poziționale:

action - actul necesar: archive_table, get_table_references sau get_rows_references.

Argumente numite:

--config_path — calea către fișierul de configurare;
--table — un tabel cu care trebuie să efectuați o acțiune;
--ids — lista de id-uri separate prin virgule, de exemplu, 1,2,3 (parametru optional);
--log_path — calea către folderul pentru jurnalele (parametru opțional, implicit — folderul de acasă);
--log_level — nivelul de înregistrare (parametru opțional, implicit este INFO).

Exemple de comenzi

Arhivarea unui tabel

Funcția principală a utilitarului este arhivarea datelor, adică. transferul de rânduri din tabelul principal în tabelul de arhivă (de exemplu, din tabel cărţi в arhiva_carti).

De asemenea, este acceptată ștergerea fără arhivare: pentru aceasta trebuie să setați parametrul în config.ini to_archive = false).

Parametri necesari - config_path, tabel și ID-uri.

După lansare, înregistrările vor fi șterse recursiv ids in masa table și în toate tabelele care se referă la acesta.

$ 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

Găsiți dependențe pentru un tabel specificat

Funcție pentru a găsi dependențe ale unui tabel specificat table. Parametri necesari - config_path и table.

După lansare, pe ecran va fi afișat un dicționar, unde:

in_refs — un dicționar de tabele care face referire la unul dat, unde cheia este numele tabelului, valoarea este o listă de obiecte cheie străină (pk_main - cheie primară în tabelul principal, pk_ref - cheia primară în tabelul de referință, fk_ref — numele coloanei care este cheia externă a tabelului sursă);
out_refs — un dicționar de tabele la care se referă acesta.

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

Găsirea referințelor la șiruri cu cheia primară specificată

Funcție de căutare a rândurilor în alte tabele care se referă la rânduri prin cheia externă ids tabele table. Parametri necesari - config_path, table и ids.

După lansare, pe ecran va fi afișat un dicționar cu următoarea structură:

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

Exemplu de apel:

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

Utilizare în cod

Pe lângă rularea acesteia în consolă, biblioteca poate fi folosită în codul Python. Mai jos sunt prezentate exemple de apeluri în mediul interactiv iPython.

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

Găsiți dependențe pentru un tabel specificat

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

Găsirea referințelor la șiruri cu cheia primară specificată

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

Codul sursă al bibliotecii este disponibil la GitHub sub licență MIT, precum și în depozit PyPI.

Voi fi bucuros să fac comentarii, angajamente și sugestii.

Voi încerca să răspund la întrebări cât pot de bine aici și în depozit.

Sursa: www.habr.com

PgGraph este un utilitar pentru arhivarea și găsirea dependențelor de tabel în PostgreSQL

preistorie

Cum să utilizați

Instalare și configurare

Rulați de pe consolă

Parametrii

Exemple de comenzi

Arhivarea unui tabel

Găsiți dependențe pentru un tabel specificat

Găsirea referințelor la șiruri cu cheia primară specificată

Utilizare în cod

Arhivarea unui tabel

Găsiți dependențe pentru un tabel specificat

Găsirea referințelor la șiruri cu cheia primară specificată

Adauga un comentariu Anula răspuns