ProHoster > Blogi > Haldamine > PgGraph — utiliit PostgreSQL-is tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks

PgGraph — utiliit PostgreSQL-is tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks

PgGraph — utiliit PostgreSQL-is tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks
TÀna tahan tutvustada Habra lugejatele Pythonis kirjutatud utiliiti, mis tegeleb PostgreSQL andmebaasi tabelite sÔltuvuste haldamisega.

Utiliidi API on lihtne ja koosneb kolmest meetodist:

  • archive_table — rekursiivne arhiveerimine/haldamine ridadega, millel on mÀÀratud esmased vĂ”tmed.
  • get_table_references — tabeli sĂ”ltuvuste leidmine (nĂ€itab, millistele tabelitele viidatakse ja kes viitavad sellele tabelile).
  • get_rows_references — leidmine, millised read teistes tabelites viitavad mÀÀratud ridadele soovitud tabelis.

Eellugu

Minu nimi on Oleg Borzov, olen arendaja CRM meeskonnas, kes tegeleb eluasemelaenude halduritega Domklikis.

Meie CRM-sĂŒsteemi peamine andmebaas on ĂŒks suurimaid mahult firmasse. See on ka ĂŒks kĂ”ige vanemaid: see loodi projekti kĂ€ivitamise ajal, kui puud olid suured, Domklik oli start-up ning moodsat Pythonis asĂŒnkroonset raamistikku asendas tohutu monoliit PHP-s.

Üleminek PHP-lt Pythonile oli vĂ€ga pikk ja nĂ”udis kahe sĂŒsteemi samaaegset toetust, mis avaldas mĂ”ju andmebaasi projekteerimisele.

Tulemusena on meil andmebaas, kus on palju tihedalt omavahel seotud ja tohutu suurusega tabeleid, millel on hulgaliselt indekseid erinevat tĂŒĂŒpi pĂ€ringute jaoks. KĂ”ik see mĂ”jutab negatiivselt andmebaasi jĂ”udlust: suurte tabelite ja hulgaliselt nende vaheliste seoste tĂ”ttu kasvab pidevalt pĂ€ringute keerukus, mis on eriti kriitiline kĂ”ige koormatud tabelite puhul.

Kuna soovime andmebaasi koormust vĂ€hendada, otsustasime kirjutada skripti, mis igapĂ€evaselt cron’i abil teisaldab vanad kirjed kĂ”ige mahukamatest ja koormatud tabelitest arhiivi (nĂ€iteks tabelist task ja task_archive).

See ĂŒlesanne on keeruline, kuna tabelite vahel on palju seoseid: lihtsalt ridade teisaldamine task ja task_archive ei piisa, tuleb enne seda teha sama rekursiivselt kĂ”igi viitavatega task tabelitega.

Demonstrin nÀiteks demonstratsioonilised andmebaasid saidilt postgrespro.ru:

PgGraph — utiliit PostgreSQL-is tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks
Oletame, et peame kustutama kirjeid tabelist Flights. Lihtsalt nii ei luba Postgres seda teha: eelnevalt tuleb kustutada kirjed kÔigist viitavatest tabelitest, ja nii rekursiivselt tabelitelt, millele ei viidata.

Meie nĂ€ites tabelil Flights viidatakse Ticket_flights, ja sellel — Boarding_passes.

SeetÔttu tuleb kustutada sellises jÀrjekorras:

  1. Saame primaarsete vÔtmete (Primary Keys, PK) vÀÀrtused ridadest Ticket_flights, mis viitavad kustutatavatele ridadele Flights.
  2. Saame PK ridadest Boarding_passes, mis viitavad Ticket_flights.
  3. Eemime PK-de alusel read punktist 2 tabelist Boarding_passes.
  4. Eemime PK-de alusel read punktist 1 Ticket_flights.
  5. Eemime read tabelist Flights.

LÔpuks saime valmis utiliidi nimega PgGraph, mille otsustasime teha avatud lÀhtekoodiga.

Kuidas kasutada

Utiliid toetab kahe kasutusreĆŸiimi:

  • KĂ€skude rida (pggraph 
).
  • Kasutamine Python koodis (klass PgGraphApi).

Paigaldamine ja seadistamine

Esmalt tuleb installida utiliid Pypi-repositooriumist:

pip3 install pggraph

SeejÀrel tuleb luua kohaliku masina jaoks fail config.ini, kus on andmebaasi ja arhiivimise skripti konfigureerimine:

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

[archive]  ; Selle jaotuse tÀitmine pole kohustuslik, allpool on antud vaikimisi vÀÀrtused
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

KĂ€ivitamine konsolist

Parameetrid

$ pggraph -h
kasutamine: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positsioonilised argumendid:
  action        nÔutav tegevus: archive_table, get_table_references, get_rows_references

valikulised argumendid:
  -h, --help                    nÀita seda abi ja lÔpeta
  --table TABLE                 tabeli nimi
  --ids IDS                     pÔhikoodide ids, eraldatud komadega, nt 1,2,3
  --config_path CONFIG_PATH     tee config.ini failini
  --log_path LOG_PATH           logikaustade tee
  --log_level LOG_LEVEL         logitase (debug, info, error)

Positsioonilised argumendid:

  • tegevus — nĂ”utav tegevus: archive_table, get_table_references vĂ”i get_rows_references.

Nimelised argumendid:

  • --config_path — konfig-faili tee;
  • --table — tabel, millega tuleb tegevus teostada;
  • --ids — id-de nimekiri, eraldatud komadega, nĂ€iteks, 1,2,3 (valikuline parameeter);
  • --log_path — logide kausta tee (valikuline parameeter, vaikimisi — kodukaust);
  • --log_level — logimisaste (valikuline parameeter, vaikimisi — INFO).

KÀskude nÀited

Tabeli arhiveerimine

Utiliidi peamine funktsioon on andmete arhiveerimine, st ridade ĂŒlekandmine pĂ”hitabelist arhiivi (nĂ€iteks tabelist books ja books_archive).

Toetatakse ka kustutamist ilma arhiveerimata: selleks tuleb config.ini-s seadistada parameeter to_archive = false).

Kohustuslikud parameetrid — config_path, table ja ids.

PÀrast kÀivitamist kustutatakse rekursiivselt kirjeldused ids tabelis table ja kÔikidesse viitavatesse tabelitesse.

$ 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

Otsing sÔltuvusi mÀÀratud tabelile

Funktsioon mÀÀratud tabeli sĂ”ltuvuste otsimiseks table. Kohustuslikud parameetrid — config_path ja table.

PÀrast kÀivitamist kuvatakse ekraanile sÔnastik, kus:

  • in_refs — sĂ”nastik viidatud tabelitest, kus vĂ”ti on tabeli nimi, vÀÀrtus on objektide Foreign Key loend (pk_main — peamine vĂ”ti pĂ”hietabelis, pk_ref — peamine vĂ”ti viidatud tabelis, fk_ref — veeru nimi, mis on foreign key algtabelile);
  • out_refs — sĂ”nastik tabelitest, mida see viitab.

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

Otsing viiteid ridadele, mille peamised vÔtmed on mÀÀratud

Funktsioon ridade otsimiseks teistes tabelites, mis viitavad ridadele ids tabelis table. Kohustuslikud parameetrid — config_path, table 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'}]}}}

Koodi kasutamine

Lisaks kÀivitamisele konsoolis, saab teeki kasutada ka Pythonis. Allpool on toodud nÀited kutsumisest 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 - 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

Otsing sÔltuvusi mÀÀratud tabelile

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

Otsing viiteid ridadele, mille peamised vÔtmed on mÀÀratud

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

Allikas on saadaval GitHub MIT litsentsi all, samuti hoidlas PyPI.

Olen huvitatud tagasisidest, panustamisest ja ettepanekutest.

KĂŒsimustele vastan vĂ”imaluste kohaselt siin ja hoidlas.

Allikas: habr.com

Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid | ProHoster