ProHoster > Blog > Administración > PgGraph — herramienta para archivar y buscar dependencias de tablas en PostgreSQL

PgGraph — herramienta para archivar y buscar dependencias de tablas en PostgreSQL

PgGraph — herramienta para archivar y buscar dependencias de tablas en PostgreSQL
Hoy quiero presentar a los lectores de Habr una utilidad escrita en Python para gestionar las dependencias de las tablas en la base de datos PostgreSQL.

La API de la utilidad es simple y consta de tres métodos:

  • archive_table — archivación/eliminación recursiva de filas con las claves primarias especificadas
  • get_table_references — búsqueda de dependencias para una tabla (mostrará las tablas a las que se refiere la especificada y las que se refieren a ella)
  • get_rows_references — búsqueda de filas en otras tablas que hacen referencia a las filas especificadas en la tabla deseada

Antecedentes

Me llamo Oleg Borzov, soy desarrollador en el equipo de CRM para gerentes de hipotecas en Domklik.

La base de datos principal de nuestro sistema CRM es una de las más grandes de la empresa. También es una de las más antiguas: se creó al inicio del proyecto, cuando los árboles eran grandes, Domklik era una startup, y en lugar de un microservicio en un moderno marco asíncrono de Python, había un gran monolito en PHP.

La transición de PHP a Python fue muy prolongada y requirió el soporte simultáneo de ambos sistemas, lo que afectó el diseño de la base de datos.

Como resultado, tenemos una base de datos con un gran número de tablas muy interconectadas y enormes en tamaño con un montón de índices para diferentes tipos de consultas. Todo esto afecta negativamente el rendimiento de la base de datos: debido a las grandes tablas y a la cantidad de relaciones entre ellas, la complejidad de las consultas sigue aumentando, lo que es especialmente crítico para las tablas más cargadas.

Para reducir la carga en la base de datos, decidimos escribir un script que diariamente, mediante cron, trasladara registros antiguos de las tablas más voluminosas y más solicitadas a tablas de archivo (por ejemplo, de tarea en task_archive).

Esta tarea se complica por la gran cantidad de relaciones entre tablas: simplemente mover filas de tarea en task_archive no es suficiente, antes de eso, se debe hacer lo mismo recursivamente con todas las tablas referenciadas a tarea .

Voy a demostrarlo con un ejemplo de una base de datos de demostración del sitio postgrespro.ru:

PgGraph — herramienta para archivar y buscar dependencias de tablas en PostgreSQL
Supongamos que necesitamos eliminar registros de la tabla Flights. Simplemente no podemos hacer esto, Postgres no nos lo permitirá: primero debemos eliminar registros de todas las tablas que hacen referencia a ella, y así, recursivamente, hasta llegar a las tablas que no tienen referencias.

En nuestro ejemplo, a Flights se refiere Ticket_flights, y esta a Boarding_passes.

Por lo tanto, hay que eliminar en el siguiente orden:

  1. Obtenemos los valores de las claves primarias (Primary Keys, PK) de las filas en Ticket_flights, que hacen referencia a las filas que se van a eliminar en Flights.
  2. Obtenemos las PK de las filas Boarding_passes, que hacen referencia a Ticket_flights.
  3. Eliminamos filas por PK de p.2 en la tabla Boarding_passes.
  4. Eliminamos filas por PK de p.1 en Ticket_flights.
  5. Eliminamos filas de Flights.

Como resultado, se creó una herramienta llamada PgGraph, que decidimos hacer de código abierto.

Cómo usar

La herramienta soporta dos modos de uso:

  • Llamada desde la línea de comandos (pggraph …).
  • Uso en código Python (clase PgGraphApi).

Instalación y configuración

Primero, necesitas instalar la herramienta desde el repositorio de Pypi:

pip3 install pggraph

Luego, crea un archivo config.ini en la máquina local con la configuración de la base de datos y el script de archivado:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Parámetro opcional, se indica el valor por defecto

[archive]  ; Esta sección es opcional, se indican los valores por defecto a continuación
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Ejecución desde la consola

Parámetros

$ pggraph -h
uso: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
argumentos posicionales:
  action        acción requerida: archive_table, get_table_references, get_rows_references

argumentos opcionales:
  -h, --help                    muestra este mensaje de ayuda y sale
  --table TABLE                 nombre de la tabla
  --ids IDS                     ids de clave primaria, separados por comas, p. ej. 1,2,3
  --config_path CONFIG_PATH     ruta a config.ini
  --log_path LOG_PATH           ruta al directorio de logs
  --log_level LOG_LEVEL         nivel de log (debug, info, error)

Argumentos posicionales:

  • acción — acción requerida: archive_table, get_table_references o get_rows_references.

Argumentos nombrados:

  • --config_path — ruta al archivo de configuración;
  • --table — tabla sobre la que se debe realizar la acción;
  • --ids — lista de id separados por comas, por ejemplo, 1,2,3 (parámetro opcional);
  • --log_path — ruta a la carpeta de logs (parámetro opcional, por defecto — carpeta de inicio);
  • --log_level — nivel de registro (parámetro opcional, por defecto — INFO).

Ejemplos de comandos

Archivando la tabla

La función principal de la herramienta es la archivación de datos, es decir, mover filas de la tabla principal a la tabla de archivos (por ejemplo, de la tabla books en books_archive).

También se permite eliminación sin archivado: para eso, en config.ini debes establecer el parámetro to_archive = false).

Parámetros obligatorios — config_path, table e ids.

Después de ejecutar, se eliminarán recursivamente los registros ids en la tabla tabla y en todas las tablas que hacen referencia a ella.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: vuelos - INICIO
2020-06-20 19:27:44 INFO: vuelos - comienza archive_recursive 3 filas (profundidad=0)
2020-06-20 19:27:44 INFO:       INICIO ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:       ticket_flights - comienza archive_recursive 3 filas (profundidad=1)
2020-06-20 19:27:44 INFO:               INICIO ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - comienza archive_recursive 3 filas (profundidad=2)
2020-06-20 19:27:44 INFO:                       INICIO ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 filas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - comienza archive_recursive 3 filas (profundidad=2)
2020-06-20 19:27:44 INFO:                       INICIO ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 filas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - comienza archive_recursive 3 filas (profundidad=2)
2020-06-20 19:27:44 INFO:                       INICIO ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 filas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - comienza archive_recursive 3 filas (profundidad=2)
2020-06-20 19:27:44 INFO:                       INICIO ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 filas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 filas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:       FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 19:27:44 INFO: vuelos - archive_by_ids 3 filas por id
2020-06-20 19:27:44 INFO: vuelos - FIN

Búsqueda de dependencias para la tabla especificada

Función para buscar dependencias de la tabla especificada tabla. Parámetros obligatorios — config_path y tabla.

Después de ejecutar, se mostrará un diccionario, donde:

  • in_refs — diccionario de tablas que hacen referencia a esta, donde la clave es el nombre de la tabla y el valor es una lista de objetos Foreign Key (pk_main — clave primaria en la tabla principal, pk_ref — clave primaria en la tabla referenciada, fk_ref — nombre de la columna que es foreign key a la tabla original);
  • out_refs — diccionario de tablas a las que esta tabla hace referencia.

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

Búsqueda de referencias a filas con Primary Key especificados

Función para buscar filas en otras tablas que hacen referencia a filas ids de la tabla tabla. Parámetros obligatorios — config_path, tabla y ids.

Una vez ejecutado, se mostrará un diccionario con la siguiente estructura:

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

Ejemplo de llamada:

$ 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 en el código

Además de ejecutarlo en la consola, la biblioteca se puede utilizar en código Python. A continuación se muestran ejemplos de llamadas en el entorno interactivo iPython.

Archivando la tabla

>>> 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 - INICIO
2020-06-20 23:12:08 INFO: flights - inicio archivo_recursivo 2 filas (profundidad=0)
2020-06-20 23:12:08 INFO: 	INICIO ARCHIVO TABLAS REFERENCIADAS
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 - inicio archivo_recursivo 30 filas (profundidad=1)
2020-06-20 23:12:08 INFO: 		INICIO ARCHIVO TABLAS REFERENCIADAS
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 - archivo_por_fk 30 filas por 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 por FK flight_id, ticket_no - 30 filas
2020-06-20 23:12:08 INFO: 		FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archivo_por_ids 30 filas por 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 por flight_id, ticket_no - 30 filas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 filas
2020-06-20 23:12:08 INFO: 	ticket_flights - inicio archivo_recursivo 30 filas (profundidad=1)
2020-06-20 23:12:08 INFO: 		INICIO ARCHIVO TABLAS REFERENCIADAS
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 - archivo_por_fk 30 filas por 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 por FK flight_id, ticket_no - 30 filas
2020-06-20 23:12:08 INFO: 		FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archivo_por_ids 30 filas por 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 por flight_id, ticket_no - 30 filas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 filas
2020-06-20 23:12:08 INFO: 	ticket_flights - inicio archivo_recursivo 30 filas (profundidad=1)
2020-06-20 23:12:08 INFO: 		INICIO ARCHIVO TABLAS REFERENCIADAS
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 - archivo_por_fk 30 filas por 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 por FK flight_id, ticket_no - 30 filas
2020-06-20 23:12:08 INFO: 		FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archivo_por_ids 30 filas por 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 por flight_id, ticket_no - 30 filas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 filas
2020-06-20 23:12:08 INFO: 	ticket_flights - inicio archivo_recursivo 3 filas (profundidad=1)
2020-06-20 23:12:08 INFO: 		INICIO ARCHIVO TABLAS REFERENCIADAS
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 - archivo_por_fk 3 filas por 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 por FK flight_id, ticket_no - 3 filas
2020-06-20 23:12:08 INFO: 		FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archivo_por_ids 3 filas por 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 por flight_id, ticket_no - 3 filas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 filas
2020-06-20 23:12:08 INFO: 	FIN ARCHIVO TABLAS REFERENCIADAS
2020-06-20 23:12:08 INFO: flights - archivo_por_ids 2 filas por 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 por flight_id - 2 filas
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 filas
2020-06-20 23:12:09 INFO: flights - FIN

Búsqueda de dependencias para la tabla especificada

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

Búsqueda de referencias a filas con Primary Key especificados

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

El código fuente de la biblioteca está disponible en GitHub bajo la licencia MIT, así como en el repositorio PyPI.

Agradezco los comentarios, commits y sugerencias.

Intentaré responder a las preguntas en la medida de mis posibilidades aquí y en el repositorio.

Fuente: habr.com

Compra un hosting fiable para sitios web con protección contra DDoS, servidores VPS VDS 🔥 Compra un hosting fiable para sitios web con protección contra DDoS, servidores VPS VDS | ProHoster