🥇PgGraph - een hulpprogramma voor het archiveren en doorzoeken van tabelafhankelijkheden in PostgreSQL

Vandaag wil ik Habr-lezers een in Python geschreven hulpprogramma presenteren voor het werken met tabelafhankelijkheden in het PostgreSQL DBMS.

De API van het hulpprogramma is eenvoudig en bestaat uit drie methoden:

archief_tabel - recursief archiveren/verwijderen van rijen met gespecificeerde primaire sleutels
get_table_references — zoeken naar afhankelijkheden voor een tabel (toont tabellen waarnaar wordt verwezen door de opgegeven tabel en de tabellen die ernaar verwijzen)
get_rows_references - zoek naar rijen in andere tabellen die verwijzen naar gespecificeerde rijen in de gewenste tabel

prehistorie

Mijn naam is Oleg Borzov, ik ben ontwikkelaar in het CRM-team voor hypotheekbeheerders in Domklik.

De hoofddatabase van ons CRM-systeem is qua volume een van de grootste in het bedrijf. Het is ook een van de oudste: het verscheen bij de lancering van het project, toen de bomen groot waren, Domklik was een startup, en in plaats van een microservice op een modieus asynchrone Python-framework was er een enorme monoliet in PHP.

De overgang van PHP naar Python duurde erg lang en vereiste gelijktijdige ondersteuning van beide systemen, wat van invloed was op het ontwerp van de database.

Als gevolg hiervan hebben we een database met een groot aantal sterk verbonden en enorme tabellen met een aantal indexen voor verschillende soorten zoekopdrachten. Dit alles heeft een negatieve invloed op de prestaties van de database: vanwege grote tabellen en een heleboel relaties daartussen neemt de complexiteit van zoekopdrachten voortdurend toe, wat vooral van cruciaal belang is voor de meest geladen tabellen.

Om de belasting van de database te verminderen, hebben we besloten een script te schrijven dat oude records van de meest omvangrijke en geladen tabellen naar gearchiveerde tabellen zou overbrengen (bijvoorbeeld van task в task_archive).

Deze taak wordt bemoeilijkt door het grote aantal relaties tussen tabellen: verplaats eenvoudigweg rijen van task в task_archive is niet genoeg, daarvoor moet je hetzelfde recursief doen met al die verwijzingen task tafels.

Ik zal het aantonen met een voorbeeld demodatabase van de site postgrespro.ru:

Stel dat we records uit een tabel moeten verwijderen Flights. Postgres staat ons niet toe dit zomaar te doen: we moeten eerst records verwijderen uit alle verwijzende tabellen, enzovoort, recursief tot aan tabellen waarnaar door niemand wordt verwezen.

In ons voorbeeld op Flights verwijst Ticket_flights, en op haar - Boarding_passes.

Daarom moet u het in deze volgorde verwijderen:

We krijgen de primaire sleutelwaarden (PK) van rijen binnen Ticket_flights, die verwijzen naar de rijen waarin moet worden verwijderd Flights.
We krijgen PK-rijen Boarding_passes, waarnaar verwezen wordt Ticket_flights.
We verwijderen rijen per PK uit stap 2 in de tabel Boarding_passes.
Verwijder regels per PK vanaf stap 1 in Ticket_flights.
Lijnen verwijderen uit Flights.

Het resultaat was een hulpprogramma genaamd PgGraph, waarvan we besloten om het open source te maken.

Hoe te gebruiken

Het hulpprogramma ondersteunt twee gebruiksmodi:

Bellen vanaf de opdrachtregel (pggraph …).
Gebruik in Python-code (class PgGraphApi).

Installatie en configuratie

Eerst moet je het hulpprogramma installeren vanuit de Pypi-repository:

pip3 install pggraph

Maak vervolgens een config.ini-bestand op de lokale machine met de configuratie van de database en het archiveringsscript:

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

Uitvoeren vanaf de console

Parameters

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

Positionele argumenten:

action - Vereiste actie: archive_table, get_table_references of get_rows_references.

Genoemde argumenten:

--config_path — pad naar het configuratiebestand;
--table — een tafel waarmee je een actie moet uitvoeren;
--ids — lijst met ID's gescheiden door komma's, bijvoorbeeld 1,2,3 (optionele parameter);
--log_path — pad naar de map voor logbestanden (optionele parameter, standaard: thuismap);
--log_level — logniveau (optionele parameter, standaard is INFO).

Commandovoorbeelden

Een tabel archiveren

De belangrijkste functie van het hulpprogramma is het archiveren van gegevens, d.w.z. het overbrengen van rijen van de hoofdtabel naar de archieftabel (bijvoorbeeld van de table boeken в boeken_archief).

Verwijdering zonder archivering wordt ook ondersteund: hiervoor moet u de parameter in config.ini instellen naar_archief = false).

Vereiste parameters - config_path, tabel en id's.

Na de lancering worden records recursief verwijderd ids in de tafel table en in alle tabellen die ernaar verwijzen.

$ 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

Zoek afhankelijkheden voor een opgegeven tabel

Functie om afhankelijkheden van een opgegeven tabel te vinden table. Vereiste parameters - config_path и table.

Na het starten wordt er een woordenboek op het scherm weergegeven, waarin:

in_refs — een woordenboek met tabellen die naar een bepaalde tabel verwijzen, waarbij de sleutel de naam van de tabel is en de waarde een lijst met Foreign Key-objecten is (pk_main - primaire sleutel in de hoofdtabel, pk_ref - primaire sleutel in de referentietabel, fk_ref — de naam van de kolom die de externe sleutel voor de brontabel is);
out_refs – een woordenboek met tabellen waarnaar deze verwijst.

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

Verwijzingen zoeken naar tekenreeksen met de opgegeven primaire sleutel

Functie om via Foreign Key naar rijen in andere tabellen te zoeken die naar rijen verwijzen ids tafels table. Vereiste parameters - config_path, table и ids.

Na het opstarten verschijnt er een woordenboek met de volgende structuur op het scherm:

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

Voorbeeld oproep:

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

Gebruik in code

Naast het uitvoeren in de console, kan de bibliotheek ook in Python-code worden gebruikt. Hieronder vindt u voorbeelden van oproepen in de interactieve iPython-omgeving.

Een tabel archiveren

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

Zoek afhankelijkheden voor een opgegeven 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')]}}

Verwijzingen zoeken naar tekenreeksen met de opgegeven primaire sleutel

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

De broncode van de bibliotheek is beschikbaar op GitHub onder MIT-licentie, evenals in de repository PyPI.

Ik zal blij zijn met opmerkingen, toezeggingen en suggesties.

Ik zal proberen de vragen hier en in de repository zo goed mogelijk te beantwoorden.

Bron: www.habr.com

PgGraph is een hulpprogramma voor het archiveren en vinden van tabelafhankelijkheden in PostgreSQL

prehistorie

Hoe te gebruiken

Installatie en configuratie

Uitvoeren vanaf de console

Parameters

Commandovoorbeelden

Een tabel archiveren

Zoek afhankelijkheden voor een opgegeven tabel

Verwijzingen zoeken naar tekenreeksen met de opgegeven primaire sleutel

Gebruik in code

Een tabel archiveren

Zoek afhankelijkheden voor een opgegeven tabel

Verwijzingen zoeken naar tekenreeksen met de opgegeven primaire sleutel

Voeg een reactie Reactie annuleren