ProHoster > Blog > Amministrazione > PgGraph — uno strumento per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL

PgGraph — uno strumento per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL

PgGraph — uno strumento per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL
Oggi voglio presentare ai lettori di Habr un’utilità scritta in Python per gestire le dipendenze delle tabelle nel database PostgreSQL.

L’API dell’utilità è semplice e composta da tre metodi:

  • archive_table — archiviazione/rimozione ricorsiva delle righe con le chiavi primarie specificate
  • get_table_references — ricerca delle dipendenze per una tabella (mostrerà le tabelle a cui si riferisce quella specificata e che si riferiscono ad essa)
  • get_rows_references — ricerca delle righe in altre tabelle che fanno riferimento alle righe specificate nella tabella desiderata

Contesto

Mi chiamo Oleg Borzov, sono uno sviluppatore nel team CRM per i manager di mutui di Domklik.

Il database principale del nostro sistema CRM è uno dei più grandi per volume in azienda. È anche uno dei più antichi: è nato con il lancio del progetto, quando gli alberi erano alti, Domklik era una startup e invece di un microservizio su un moderno framework asincrono Python c'era un enorme monolite in PHP.

Il passaggio da PHP a Python è stato molto lungo e ha richiesto il supporto simultaneo di entrambi i sistemi, il che ha influito sulla progettazione del database.

Di conseguenza, abbiamo un database con un gran numero di tabelle altamente collegate e di grandi dimensioni, con molti indici per diverse tipologie di query. Tutto questo influisce negativamente sulle prestazioni del database: le grandi tabelle e le numerose relazioni tra di esse aumentano costantemente la complessità delle query, il che è particolarmente critico per le tabelle più sollecitate.

Per ridurre il carico sul database, abbiamo deciso di scrivere uno script che, tramite cron, trasferisca quotidianamente le vecchie registrazioni dalle tabelle più grandi e cariche in quelle di archivio (ad esempio, da task in task_archive).

Questa operazione è complicata dall'elevato numero di relazioni tra le tabelle: non basta semplice trasferire le righe da task in task_archive prima di farlo, è necessario eseguire la stessa operazione in modo ricorsivo su tutte le tabelle collegate a task tabelle.

Mostrerò un esempio utilizzando il database dimostrativo del sito postgrespro.ru:

PgGraph — uno strumento per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL
Supponiamo di dover eliminare le registrazioni dalla tabella Flights. PostgreSQL non ci permetterà di farlo direttamente: prima dobbiamo eliminare le registrazioni da tutte le tabelle collegate, e così via in modo ricorsivo fino a giungere alle tabelle a cui nessuno fa riferimento.

Nel nostro esempio, Flights fa riferimento a Ticket_flights, e questa da Boarding_passes.

Pertanto, dobbiamo eliminare nell'ordine seguente:

  1. Otteniamo i valori delle chiavi primarie (Primary Keys, PK) delle righe in Ticket_flights, che fanno riferimento a righe eliminate in Flights.
  2. Otteniamo le PK delle righe Boarding_passes, che fanno riferimento a Ticket_flights.
  3. Eliminiamo righe per PK da p.2 nella tabella Boarding_passes.
  4. Eliminiamo righe per PK da p.1 in Ticket_flights.
  5. Eliminiamo righe da Flights.

Alla fine abbiamo ottenuto uno strumento chiamato PgGraph, che abbiamo deciso di rendere open source.

Come utilizzare

Lo strumento supporta due modalità d'uso:

  • Chiamata dalla riga di comando (pggraph …).
  • Utilizzo nel codice Python (classe PgGraphApi).

Installazione e configurazione

Per prima cosa, è necessario installare lo strumento dal repository Pypi:

pip3 install pggraph

Poi creare un file config.ini locale con la configurazione del DB e dello script di archiviazione:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Parametro facoltativo, impostato al valore predefinito

[archive]  ; Questa sezione può essere compilata facoltativamente, sotto sono indicati i valori predefiniti
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Avvio dalla console

Parametri

$ pggraph -h
uso: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
argomenti posizionali:
  action        azione richiesta: archive_table, get_table_references, get_rows_references

argomenti opzionali:
  -h, --help                    mostra questo messaggio di aiuto e esci
  --table TABLE                 nome della tabella
  --ids IDS                     id della chiave primaria, separati da una virgola, ad esempio 1,2,3
  --config_path CONFIG_PATH     percorso del config.ini
  --log_path LOG_PATH           percorso della cartella dei log
  --log_level LOG_LEVEL         livello di log (debug, info, error)

Argomenti posizionali:

  • action — azione richiesta: archive_table, get_table_references o get_rows_references.

Argomenti nominativi:

  • --config_path — percorso del file di configurazione;
  • --table — tabella su cui eseguire l'azione;
  • --ids — elenco di id separati da virgole, ad esempio, 1,2,3 (argomento facoltativo);
  • --log_path — percorso della cartella per i log (argomento facoltativo, predefinito — cartella home);
  • --log_level — livello di registrazione (argomento facoltativo, predefinito — INFO).

Esempi di comandi

Archiviazione della tabella

La funzione principale dello strumento è l'archiviazione dei dati, cioè il trasferimento delle righe dalla tabella principale all'archivio (ad esempio, dalla tabella books in books_archive).

Supporta anche l'eliminazione senza archiviazione: per fare ciò, è necessario impostare nel config.ini il parametro to_archive = false).

Parametri obbligatori — config_path, table e ids.

Dopo l'esecuzione, verranno eliminati ricorsivamente i record ids nella tabella table e in tutte le tabelle che la citano.

$ pggraph archive_table --config_path config.hw.local.ini --table voli --ids 1,2,3
2020-06-20 19:27:44 INFO: voli - INIZIO
2020-06-20 19:27:44 INFO: voli - inizio archive_recursive 3 righe (profondità=0)
2020-06-20 19:27:44 INFO:       INIZIO ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:       biglietti_voli - inizio archive_recursive 3 righe (profondità=1)
2020-06-20 19:27:44 INFO:               INIZIO ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:               carte_imbarco - inizio archive_recursive 3 righe (profondità=2)
2020-06-20 19:27:44 INFO:                       INIZIO ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:                       FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:               carte_imbarco - archive_by_ids 3 righe per numero_biglietto, id_volo
2020-06-20 19:27:44 INFO:               carte_imbarco - inizio archive_recursive 3 righe (profondità=2)
2020-06-20 19:27:44 INFO:                       INIZIO ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:                       FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:               carte_imbarco - archive_by_ids 3 righe per numero_biglietto, id_volo
2020-06-20 19:27:44 INFO:               carte_imbarco - inizio archive_recursive 3 righe (profondità=2)
2020-06-20 19:27:44 INFO:                       INIZIO ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:                       FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:               carte_imbarco - archive_by_ids 3 righe per numero_biglietto, id_volo
2020-06-20 19:27:44 INFO:               carte_imbarco - inizio archive_recursive 3 righe (profondità=2)
2020-06-20 19:27:44 INFO:                       INIZIO ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:                       FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:               carte_imbarco - archive_by_ids 3 righe per numero_biglietto, id_volo
2020-06-20 19:27:44 INFO:               FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:       biglietti_voli - archive_by_ids 3 righe per numero_biglietto, id_volo
2020-06-20 19:27:44 INFO:       FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO: voli - archive_by_ids 3 righe per id
2020-06-20 19:27:44 INFO: voli - FINE

Ricerca delle dipendenze per la tabella specificata

Funzione per la ricerca delle dipendenze della tabella specificata table. Parametri obbligatori — config_path e table.

Dopo l'esecuzione, verrà visualizzato un dizionario in cui:

  • in_refs — dizionario delle tabelle che fanno riferimento a questa, dove la chiave è il nome della tabella e il valore è l'elenco degli oggetti Foreign Key (pk_main — chiave primaria nella tabella principale, pk_ref — chiave primaria nella tabella di riferimento, fk_ref — nome della colonna che è una foreign key verso la tabella originale);
  • out_refs — dizionario delle tabelle a cui fa riferimento questa.

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

Ricerca dei riferimenti alle righe con le chiavi primarie specificate

Funzione per cercare righe in altre tabelle che fanno riferimento tramite Foreign Key a righe ids della tabella table. Parametri obbligatori — config_path, table e ids.

Dopo l'esecuzione, verrà visualizzato un dizionario con la seguente struttura:

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

Esempio di chiamata:

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

Uso nel codice

Oltre a essere eseguita nella console, la libreria può essere utilizzata nel codice Python. Di seguito sono mostrati esempi di chiamata nell'ambiente interattivo iPython.

Archiviazione della tabella

> > > 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 - INIZIO
2020-06-20 23:12:08 INFO: flights - avvio archive_recursive 2 righe (profondità=0)
2020-06-20 23:12:08 INFO: 	INIZIO ARCHIVIAZIONE TABELLE RIFERITE
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 - avvio archive_recursive 30 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIAZIONE TABELLE RIFERITE
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 righe per 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 righe
2020-06-20 23:12:08 INFO: 		FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 righe per 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 righe
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 righe
2020-06-20 23:12:08 INFO: 	ticket_flights - avvio archive_recursive 30 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIAZIONE TABELLE RIFERITE
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 righe per 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 righe
2020-06-20 23:12:08 INFO: 		FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 righe per 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 righe
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 righe
2020-06-20 23:12:08 INFO: 	ticket_flights - avvio archive_recursive 30 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIAZIONE TABELLE RIFERITE
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 righe per 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 righe
2020-06-20 23:12:08 INFO: 		FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 righe per 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 righe
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 righe
2020-06-20 23:12:08 INFO: 	ticket_flights - avvio archive_recursive 3 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIAZIONE TABELLE RIFERITE
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 righe per 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 righe
2020-06-20 23:12:08 INFO: 		FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 righe per 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 righe
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 righe
2020-06-20 23:12:08 INFO: 	FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 righe per 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 righe
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 righe
2020-06-20 23:12:09 INFO: flights - FINE

Ricerca delle dipendenze per la tabella specificata

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

Ricerca dei riferimenti alle righe con le chiavi primarie specificate

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

Il codice sorgente della libreria è disponibile su GitHub sotto licenza MIT, così come nel repository PyPI.

Sarò felice di ricevere commenti, commit e suggerimenti.

Cercherò di rispondere alle domande qui e nel repository secondo le mie possibilità.

Fonte: habr.com

Acquista hosting affidabile per siti web con protezione DDoS, VPS VDS server 🔥 Acquista hosting affidabile per siti web con protezione DDoS, VPS VDS server | ProHoster