ProHoster > Blogi > Haldamine > PgGraph — tööriist PostgreSQL tabelite sõltuvuste arhiveerimiseks ja otsimiseks

PgGraph — tööriist PostgreSQL tabelite sõltuvuste arhiveerimiseks ja otsimiseks

PgGraph — tööriist PostgreSQL tabelite sõltuvuste arhiveerimiseks ja otsimiseks
Täna tahan tutvustada Habra lugejatele Pythonis kirjutatud tööriista tabelite sõltuvuste haldamiseks PostgreSQL andmebaasis.

Tööriista API on lihtne ja koosneb kolmest meetodist:

  • archive_table — rekursiivne arhiveerimine/kustutamine antud Primary Key-dega ridade jaoks
  • get_table_references — tabeli sõltuvuste otsing (näitab tabeleid, millele antud viitab ja mis viitavad sellele)
  • get_rows_references — ridade otsing teistes tabelites, mis viitavad antud tabeli määratud ridadele

Eelalugu

Minu nimi on Oleg Borzov, olen CRM meeskonna arendaja, kes töötab kinnisvara laenude halduritele Domklikis.

Meie CRM-süsteemi peamine andmebaas on ettevõtte suurimaid oma mahu poolest. See on ka üks vanimaid: see loodi projekti käivitamise ajal, kui puud olid suured, Domklik oli start-up ja moodsa Python'i asünkroonse raamistikuna polnud veel mikroteenuseid, vaid suur monoliit PHP-s.

Üleminek PHP-lt Pythonile oli väga pikk ja nõudis kahe süsteemi samal ajal toe pakkumist, mis kajastus andmebaasi projekteerimises.

Kokkuvõttes on meil andmebaas, mis sisaldab suurt hulka tugevalt seotud ja suurte tabelitega, kus on palju indekseid erinevate päringute jaoks. See kõik avaldab negatiivset mõju andmebaasi jõudlusele: suurte tabelite ja nende vaheliste paljude seoste tõttu suureneb päringute keerukus, mis on eriti kriitiline kõige koormatumate tabelite puhul.

Andmebaasi koormuse vähendamiseks otsustasime kirjutada skripti, mis iga päev cron'i abil liiguks vanad kirjed kõige mahukamatest ja koormatud tabelitest arhiivi (näiteks tabelist task ühes task_archive).

See ülesanne muutub keeruliseks paljude tabelite vaheliste seoste tõttu: lihtsalt ridade üleviimine tabelist task ühes task_archive ei ole piisav, enne tuleb sama rekursiivselt teha kõigi ridade viitavatega task tabelitega.

Demonstrin näite abil demonstratiivse andmebaasi lehelt postgrespro.ru:

PgGraph — tööriist PostgreSQL tabelite sõltuvuste arhiveerimiseks ja otsimiseks
Oletame, et peame eemaldama kirjed tabelist Flights. Lihtsalt nii seda Postgres meile ei luba: eelnevalt on vaja eemaldada kirjed kõikidest viitavatest tabelitest, ja seda rekursiivselt, kuni tabeliteni, millele keegi ei viita.

Meie näites viitab Flights Ticket_flights , ja sellel onBoarding_passes Seetõttu tuleb eemaldada järgmisest järjekorras:.

Seetõttu tuleb kustutamine teha järgmises järjekorras:

  1. Saame primaarsete võtmete (Primary Keys, PK) väärtused ridadest , ja sellel on, mis viitavad kustutatavatele ridadele Flights.
  2. Saame ridade PK-d Seetõttu tuleb eemaldada järgmisest järjekorras:, mis viitavad , ja sellel on.
  3. Kustutame PK-dega read p.2 tabelist Seetõttu tuleb eemaldada järgmisest järjekorras:.
  4. Kustutame PK-dega read p.1-st , ja sellel on.
  5. Kustutame read Flights.

Tulemuseks sai utiliit nimega PgGraph, mille muutsime avatud lähtekoodiga.

Kuidas kasutada

Utiliit toetab kahte kasutusrežiimi:

  • Käivitus käsurealt (pggraph …).
  • Kasutamine Python koodis (klass PgGraphApi).

Installeerimine ja seadistamine

Esmalt tuleb installida utiliit Pypi-repositooriumist:

pip3 install pggraph

Seejärel tuleb luua lokaalsel masinal fail config.ini, mis sisaldab andmebaasi ja arhiveerimise skripti konfiguratsiooni:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Valikuline parameeter, vaikimisi väärtus

[archive]  ; Seda jaotust ei pea täitma, allpool on toodud vaikimisi väärtused
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Käivitus konsolist

Parameetrid

$ pggraph -h
kasutus: pggraph action [-h] --table TABEL [--ids ID-d] [--config_path CONFIG_PATH]
positiivsed argumendid:
  action        nõutav tegevus: archive_table, get_table_references, get_rows_references

valikulised argumendid:
  -h, --help                    näita seda abiteadet ja välju
  --table TABEL                 tabeli nimi
  --ids ID-d                    peavad võtmed, eraldatud komaga, nt 1,2,3
  --config_path CONFIG_PATH     tee config.ini faili
  --log_path LOG_PATH           tee logi kausta
  --log_level LOG_LEVEL         logi tase (debug, info, error)

Positiivsed argumendid:

  • action — nõutav tegevus: archive_table, get_table_references või get_rows_references.

Nimetatud argumendid:

  • --config_path — tee konfigureerimise faili;
  • --table — tabel, millega toimingut teostada;
  • --ids — ID-de nimekiri, eraldatud komaga, näiteks, 1,2,3 (valikuline parameeter);
  • --log_path — tee logide kausta (valikuline parameeter, vaikimisi — kodukaust);
  • --log_level — logimise tase (valikuline parameeter, vaikimisi — INFO).

Käskude näidised

Tabeli arhiveerimine

Tööriista peamine funktsioon — andmete arhiveerimine, st ridade edasiviimine peamiseks tabelist arhiivi (näiteks tabelist books ühes books_archive).

Samuti toetatakse kustutamist ilma arhiveerimata: selleks tuleb config.ini-s seada parameeter to_archive = false).

Nõutavad parameetrid — config_path, table ja ids.

Pärast käivitamist eemaldatakse rekursiivselt kirjed ids tabelis tabel ja kõikidesse viidatud tabelitesse.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: lennud - ALUSTUS
2020-06-20 19:27:44 INFO: lennud - alustamine archive_recursive 3 rida (sügavus=0)
2020-06-20 19:27:44 INFO:       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:       piletid_lennud - alustamine archive_recursive 3 rida (sügavus=1)
2020-06-20 19:27:44 INFO:               ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sügavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id järgi
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sügavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id järgi
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sügavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id järgi
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sügavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id järgi
2020-06-20 19:27:44 INFO:               LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:       piletid_lennud - archive_by_ids 3 rida pilet_number, lennu_id järgi
2020-06-20 19:27:44 INFO:       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO: lennud - archive_by_ids 3 rida id järgi
2020-06-20 19:27:44 INFO: lennud - LÕPP

Määratud tabeli sõltuvuste otsimine

Sõltuvuste otsimise funktsioon määratud tabeli jaoks tabel. Kohustuslikud parameetrid — config_path ja tabel.

Pärast käivitamist kuvatakse ekraanile sõnastik, kus:

  • in_refs — tabel, mis viitab sellele tabelile, kus võti on tabeli nimi ja väärtus on Foreign Key objektide loend (pk_main — peamine võti põhitaabelis, pk_ref — peamine võti viitavas tabelis, fk_ref — veeru nimi, mis on välisvõti algsesse tabelisse);
  • out_refs — tabelite sõnastik, millele viidatakse antud tabelis.

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

Otsib viiteid ridadele, millel on määratud Primary Key

Funktsioon ridade leidmiseks teistes tabelites, mis viitavad ridadele, kasutades Foreign Key-d ids tabel tabel. Kohustuslikud parameetrid — config_path, tabel ja ids.

Pärast käivitamist kuvatakse ekraanile sõnastik järgmise struktuuriga:

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

Kutsumise näide:

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

Kasutamine koodis

Lisaks konsoolis käivitamisele saab raamatukogu kasutada ka Pythonis koodis. Allpool on näidatud kutsumise näited interaktiivses iPython keskkonnas.

Tabeli arhiveerimine

>>> 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 - ALUSTA
2020-06-20 23:12:08 INFO: flights - alustamine archive_recursive 2 rida (sügavus=0)
2020-06-20 23:12:08 INFO: 	ALUSTA ARCHIVE VIITATAVAD TABELID
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 - alustamine archive_recursive 30 rida (sügavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
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 rida 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: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rida 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: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rida
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 30 rida (sügavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
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 rida 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: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rida 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: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rida
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 30 rida (sügavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
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 rida 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: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rida 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: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rida
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 3 rida (sügavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
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 rida 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: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 3 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rida 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: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 3 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rida
2020-06-20 23:12:08 INFO: 	LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rida 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: KUSTUTADA FROM flights by flight_id - 2 rida
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rida
2020-06-20 23:12:09 INFO: flights - LÕPP

Määratud tabeli sõltuvuste otsimine

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

Otsib viiteid ridadele, millel on määratud Primary Key

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

Raamatukogu lähtekood on saadaval GitHub MIT litsentsi alusel, samuti hoidlas PyPI.

Ootan kommentaare, panuseid ja ettepanekuid.

Vastan küsimustele võimaluste piires siin ja hoidlas.

Allikas: habr.com

Osta usaldusväärne veebihosting DDoS kaitsega, VPS VDS serverid 🔥 Osta usaldusväärne veebihosting DDoS kaitsega, VPS VDS serverid | ProHoster