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

PgGraph — un'utility per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL

PgGraph — un'utility per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL
Oggi voglio presentare ai lettori di Habra un'utility scritta in Python per lavorare con le dipendenze delle tabelle nei database PostgreSQL.

L'API dell'utility è semplice e consiste in tre metodi:

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

Antefatti

Mi chiamo Oleg Borzov, sono uno sviluppatore nel team CRM per i gestori di prestiti ipotecari in Domklik.

Il database principale del nostro sistema CRM è uno dei più grandi in termini di volume nell'azienda. È anche uno dei più vecchi: è stato creato all'inizio del progetto, quando gli alberi erano alti, Domklik era una startup, e al posto di un microservizio su un framework asincrono Python di tendenza, c'era un enorme monolite in PHP.

La transizione da PHP a Python è stata molto lunga 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 fortemente collegate e di grandi dimensioni, con una miriade di indici per diversi tipi di query. Tutto ciò influisce negativamente sulle prestazioni del database: a causa delle grandi tabelle e delle numerose relazioni tra esse, la complessità delle query continua a crescere, il che è particolarmente critico per le tabelle più sovraccaricate.

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

Questo compito è complicato dal gran numero di relazioni tra le tabelle: semplicemente trasferire le righe da task in task_archive non è sufficiente, prima bisogna fare lo stesso ricorsivamente con tutte le tabelle che si riferiscono a task tabelle.

Mostrerò un esempio di un database di dimostrazione dal sito postgrespro.ru:

PgGraph — un'utility per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL
Supponiamo che dobbiamo eliminare le registrazioni dalla tabella Flights. Farlo così non sarà permesso da Postgres: prima bisogna eliminare le registrazioni da tutte le tabelle che si riferiscono, e così ricorsivamente fino alle tabelle che non sono referenziate da nessuno.

Nel nostro esempio la tabella Flights è referenziata da Ticket_flights, e su di essa — Boarding_passes.

Pertanto, è necessario eliminare nell'ordine seguente:

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

Alla fine è stata creata un'utilità chiamata PgGraph, che abbiamo deciso di rendere open source.

Come usare

L'utilità supporta due modalità d'uso:

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

Installazione e configurazione

Prima di tutto, è necessario installare l'utilità dal repository Pypi:

pip3 install pggraph

Poi creare un file config.ini sulla macchina 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, valore di default indicato

[archive]  ; Questo segmento può essere riempito facoltativamente, di seguito sono elencati i valori di default
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 facoltativi:
  -h, --help                    mostra questo messaggio d'aiuto e esce
  --table TABLE                 nome della tabella
  --ids IDS                     id chiave primaria, separati da virgola, es. 1,2,3
  --config_path CONFIG_PATH     percorso a config.ini
  --log_path LOG_PATH           percorso della directory di 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 nominati:

  • --config_path — percorso al file di configurazione;
  • --table — la tabella su cui eseguire l'azione;
  • --ids — elenco di id separati da virgola, ad esempio, 1,2,3 (parametro facoltativo);
  • --log_path — percorso alla cartella per i log (parametro facoltativo, di default — cartella home);
  • --log_level — livello di logging (parametro facoltativo, di default — INFO).

Esempi di comandi

Archiviazione della tabella

La funzione principale dell'utilità è l'archiviazione dei dati, cioè il trasferimento delle righe dalla tabella principale a quella di archiviazione (ad esempio, dalla tabella books in books_archive).

Supporta anche l'eliminazione senza archiviazione: per farlo, è necessario impostare il parametro in config.ini 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 fanno riferimento ad essa.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --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:       ticket_flights - 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:               boarding_passes - 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:               boarding_passes - archive_by_ids 3 righe per ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - 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:               boarding_passes - archive_by_ids 3 righe per ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - 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:               boarding_passes - archive_by_ids 3 righe per ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - 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:               boarding_passes - archive_by_ids 3 righe per ticket_no, flight_id
2020-06-20 19:27:44 INFO:               FINE ARCHIVIAZIONE TABELLE RIFERITE
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 righe per ticket_no, flight_id
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 nella tabella specificata table. Parametri obbligatori — config_path e table.

Dopo l'avvio 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 è un elenco di oggetti Foreign Key (pk_main — chiave primaria nella tabella principale, pk_ref — chiave primaria nella tabella di riferimento, fk_ref — nome della colonna che rappresenta la foreign key sulla tabella sorgente);
  • 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 per le righe con Primary Key specificati

Funzione per la ricerca delle 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'avvio, verrà visualizzato un dizionario con la seguente struttura:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	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'}]}}}

Utilizzo nel codice

Oltre all'esecuzione in 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: voli - INIZIO
2020-06-20 23:12:08 INFO: voli - inizio archivio_ricorsivo 2 righe (profondità=0)
2020-06-20 23:12:08 INFO: 	INIZIO ARCHIVIO 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 - inizio archivio_ricorsivo 30 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIO 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 - archivio_per_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 ARCHIVIO TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archivio_per_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 - inizio archivio_ricorsivo 30 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIO 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 - archivio_per_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 ARCHIVIO TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archivio_per_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 - inizio archivio_ricorsivo 30 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIO 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 - archivio_per_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 ARCHIVIO TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archivio_per_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 - inizio archivio_ricorsivo 3 righe (profondità=1)
2020-06-20 23:12:08 INFO: 		INIZIO ARCHIVIO 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 - archivio_per_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 ARCHIVIO TABELLE RIFERITE
2020-06-20 23:12:08 INFO: 	ticket_flights - archivio_per_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 ARCHIVIO TABELLE RIFERITE
2020-06-20 23:12:08 INFO: voli - archivio_per_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 voli 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: voli - 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 per le righe con Primary Key specificati

>>> 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 con licenza MIT, così come nel repository PyPI.

Sarò felice di ricevere commenti, commit e suggerimenti.

Cercherò di rispondere alle domande il prima possibile qui e nel repository.

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