🦊 🦂 ⏺️ PgGraph - utilidad para archivar y encontrar dependencias de tablas en PostgreSQL 🛶 🧖🏾 🤹🏾

Hoy quiero presentar a los lectores de Habr una utilidad escrita en Python para trabajar con dependencias de tabla en el DBMS de PostgreSQL.

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

archive_table - archivado / borrado recursivo de filas con claves primarias especificadas
get_table_references : busca dependencias para una tabla (mostrará las tablas referenciadas y referenciadas por la especificada)
get_rows_references : busca filas en otras tablas que hacen referencia a las filas especificadas en la tabla deseada

Antecedentes

Mi nombre es Oleg Borzov, soy desarrollador del equipo de CRM para administradores de préstamos hipotecarios en Domklik.

La base de datos principal de nuestro sistema CRM es una de las más grandes en términos de volumen de la empresa. También es uno de los más antiguos: apareció en el lanzamiento del proyecto, cuando los árboles eran grandes, Domclick era una startup, y en lugar de un microservicio en un marco asincrónico de Python, había un gran monolito en PHP.

La transición de PHP a Python fue muy larga 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 una gran cantidad de tablas altamente conectadas y enormes 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 tablas grandes y un montón de conexiones entre ellas, la complejidad de las consultas crece constantemente, 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 transfiriera los registros antiguos de las tablas más voluminosas y cargadas a las tablas de archivo (por ejemplo, desde taskc task_archive) todos los días .

Esta tarea se complica por una gran cantidad de relaciones entre tablas: simplemente transferir filas de taska task_archiveno es suficiente, antes de eso debe hacer lo mismo de forma recursiva con todas las tasktablas de referencia .

Demostraré con un ejemplobase de datos de demostración de postgrespro.ru :

Digamos que necesitamos eliminar registros de una tabla Flights. Postgres no nos permitirá hacer esto así: primero necesitamos eliminar los registros de todas las tablas de referencia, y de manera recursiva a las tablas a las que nadie se refiere.

En nuestro ejemplo, se Flightsrefiere a Ticket_flights, y a él - Boarding_passes.

Por lo tanto, debe eliminar en este orden:

Obtenga los valores de las Claves primarias (PK) de las filas Ticket_flights, que se refieren a las filas eliminadas en Flights.
Obtenemos el PK de las líneas Boarding_passesque se refieren Ticket_flights.
Eliminamos líneas por PK del elemento 2 en la tabla Boarding_passes.
Eliminamos líneas por PK de la cláusula 1 en Ticket_flights.
Eliminamos líneas de Flights.

El resultado es una utilidad llamada PgGraph, que decidimos hacer de código abierto.

Cómo utilizar

La utilidad admite dos modos de uso:

Llamar desde la línea de comando ( pggraph …).
Uso en código Python (clase PgGraphApi).

Instalacion y configuracion

Primero debe instalar la utilidad desde el repositorio de Pypi:

pip3 install pggraph

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

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

Corriendo desde la consola

Parámetros

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

Argumentos posicionales:

action- el efecto deseado: archive_table, get_table_referenceso get_rows_references.

Argumentos nombrados:

--config_path - ruta al archivo de configuración;
--table - la tabla con la que desea realizar la acción;
--ids- lista de identificación separada por comas, por ejemplo, 1,2,3(opcional);
--log_path - ruta a la carpeta para registros (parámetro opcional, por defecto - carpeta de inicio);
--log_level - nivel de registro (parámetro opcional, por defecto - INFO).

Ejemplos de comandos

Archivar una mesa

La función principal de la utilidad es el archivo de datos, es decir transferir filas de la tabla principal a la tabla de archivo (por ejemplo, de la tabla de libros a books_archive ).

También se admite la eliminación sin archivar: para esto, establezca el parámetro en_archivo = falso en config.ini ).

Los parámetros requeridos son config_path, table e ids .

Después del inicio, los registros idsen la tabla tabley en todas las tablas que se refieren a ella se eliminarán de forma recursiva .

$ 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

Encontrar dependencias para una tabla especificada

Una función para encontrar las dependencias de la tabla especificada table. Los parámetros requeridos son config_pathy table.

Después del lanzamiento, se mostrará un diccionario en la pantalla, donde:

in_refs- diccionario de tablas de referencia a la tabla dada, donde clave es el nombre de la tabla, el valor es la lista de objetos de clave externa ( pk_main- clave principal en la tabla principal, pk_ref- clave principal en la tabla de referencia, fk_ref- nombre de la columna que es la clave externa de la tabla fuente);
out_refs - el diccionario de tablas al que se refiere el dado.

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

Encontrar enlaces a cadenas con clave primaria especificada

Una función para buscar filas en otras tablas que hacen referencia a filas de idstabla a través de una clave externa table. Los parámetros requeridos son config_path, tabley ids.

Después del lanzamiento, se mostrará un diccionario con la siguiente estructura en la pantalla:

{
	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 código

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

Archivar una mesa

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

Encontrar dependencias para una 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')]}}

Encontrar enlaces a cadenas con clave primaria especificada

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

Comentarios, compromisos y sugerencias son bienvenidos.

Trataré de responder las preguntas lo más lejos posible aquí y en el repositorio.

PgGraph - utilidad para archivar y encontrar dependencias de tablas en PostgreSQL

Antecedentes

Cómo utilizar

Instalacion y configuracion

Corriendo desde la consola

Parámetros

Ejemplos de comandos

Archivar una mesa

Encontrar dependencias para una tabla especificada

Encontrar enlaces a cadenas con clave primaria especificada

Uso en código

Archivar una mesa

Encontrar dependencias para una tabla especificada

Encontrar enlaces a cadenas con clave primaria especificada

More articles: