ProHoster > Blog > Administração > PgGraph — uma ferramenta para arquivar e buscar dependências de tabelas no PostgreSQL

PgGraph — uma ferramenta para arquivar e buscar dependências de tabelas no PostgreSQL

PgGraph — uma ferramenta para arquivar e buscar dependências de tabelas no PostgreSQL
Hoje, quero apresentar aos leitores do Habr uma ferramenta escrita em Python para gerenciar dependências de tabelas no SGBD PostgreSQL.

A API da ferramenta é simples e consiste em três métodos:

  • archive_table — arquivamento/removal recursivo de linhas com as chaves primárias especificadas
  • get_table_references — busca por dependências da tabela (mostra tabelas que referenciam a tabela especificada e aquelas que são referenciadas por ela)
  • get_rows_references — busca por linhas em outras tabelas que referenciam as linhas especificadas na tabela desejada

Antecedentes

Meu nome é Oleg Borzov, sou desenvolvedor na equipe de CRM para gerentes de hipoteca na Domklik.

O banco de dados principal do nosso sistema de CRM é um dos maiores em termos de volume na empresa. É também um dos mais antigos: surgiu desde o lançamento do projeto, quando as árvores eram grandes, a Domklik era uma startup, e em vez de um microsserviço em um popular framework assíncrono em Python, havia um enorme monólito em PHP.

A transição de PHP para Python foi muito longa e exigiu o suporte simultâneo de ambos os sistemas, o que impactou no design do banco de dados.

Como resultado, temos um banco de dados com um grande número de tabelas altamente interconectadas e enormes em tamanho, com uma série de índices para diferentes tipos de consultas. Tudo isso afeta negativamente o desempenho do banco de dados: devido às grandes tabelas e à complexidade das relações entre elas, a complexidade das consultas aumenta constantemente, o que é especialmente crítico para as tabelas mais carregadas.

Para reduzir a carga no banco de dados, decidimos escrever um script que, diariamente, transferisse registros antigos das tabelas mais volumosas e carregadas para tabelas de archive (por exemplo, de task em task_archive).

Essa tarefa é complicada pelo grande número de relações entre as tabelas: simplesmente transferir linhas de task em task_archive não é suficiente, é necessário realizar o mesmo de forma recursiva em todas as tabelas que referenciam task as tabelas.

Vou demonstrar com um exemplo de um banco de dados de demonstração do site postgrespro.ru.:

PgGraph — uma ferramenta para arquivar e buscar dependências de tabelas no PostgreSQL
Suponha que precisamos excluir registros da tabela Flights. Não podemos fazer isso diretamente no Postgres: primeiro, precisamos excluir os registros de todas as tabelas que fazem referência, e assim recursivamente até chegar às tabelas que não são referenciadas por ninguém.

No nosso exemplo, Flights referencia Ticket_flights, e para ela, há Boarding_passes..

Portanto, devemos excluir na seguinte ordem:

  1. Obtemos os valores das chaves primárias (Primary Keys, PK) das linhas em Ticket_flights, que referenciam as linhas a serem excluídas em Flights.
  2. Obtemos as PK das linhas. Boarding_passes., que se referem a Ticket_flights.
  3. Removemos linhas por PK do ponto 2 na tabela Boarding_passes..
  4. Removemos linhas por PK do ponto 1 na Ticket_flights.
  5. Removemos linhas de Flights.

No final, criamos uma ferramenta chamada PgGraph, que decidimos tornar open source.

Como usar

A ferramenta suporta dois modos de uso:

  • Chamada a partir da linha de comando (pggraph …).
  • Uso em código Python (classe PgGraphApi).

Instalação e configuração

Primeiro, você precisa instalar a ferramenta do repositório Pypi:

pip3 install pggraph

Depois, crie um arquivo config.ini na máquina local com a configuração do banco de dados e do script de arquivamento:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Parâmetro opcional, valor padrão especificado

[archive]  ; Esta seção é opcional, abaixo estão os valores padrão
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Execução a partir do console

Parâmetros

$ pggraph -h
uso: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
argumentos posicionais:
  action        ação obrigatória: archive_table, get_table_references, get_rows_references

argumentos opcionais:
  -h, --help                    mostrar esta mensagem de ajuda e sair
  --table TABLE                 nome da tabela
  --ids IDS                     ids de chave primária, separados por vírgula, ex. 1,2,3
  --config_path CONFIG_PATH     caminho para config.ini
  --log_path LOG_PATH           caminho para o diretório de logs
  --log_level LOG_LEVEL         nível de log (debug, info, error)

Argumentos posicionais:

  • ação — ação requerida: archive_table, get_table_references ou get_rows_references.

Argumentos nomeados:

  • --config_path — caminho para o arquivo de configuração;
  • --table — tabela na qual a ação deve ser realizada;
  • --ids — lista de ids separados por vírgula, por exemplo, 1,2,3 (opcional);
  • --log_path — caminho para a pasta de logs (parâmetro opcional, padrão — pasta home);
  • --log_level — nível de registro (parâmetro opcional, padrão — INFO).

Exemplos de comandos

Arquivamento de tabela

A função principal da ferramenta é o arquivamento de dados, ou seja, a transferência de linhas da tabela principal para a tabela de arquivamento (por exemplo, da tabela books em books_archive).

A exclusão sem arquivamento também é suportada: para isso, o parâmetro no config.ini deve ser definido como to_archive = false).

Parâmetros obrigatórios — config_path, table e ids.

Após a execução, os registros com ids na tabela table e em todas as tabelas que referenciam ela.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - INÍCIO
2020-06-20 19:27:44 INFO: flights - iniciar archive_recursive 3 linhas (profundidade=0)
2020-06-20 19:27:44 INFO:       INÍCIO DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:       ticket_flights - iniciar archive_recursive 3 linhas (profundidade=1)
2020-06-20 19:27:44 INFO:               INÍCIO DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - iniciar archive_recursive 3 linhas (profundidade=2)
2020-06-20 19:27:44 INFO:                       INÍCIO DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIM DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 linhas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - iniciar archive_recursive 3 linhas (profundidade=2)
2020-06-20 19:27:44 INFO:                       INÍCIO DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIM DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 linhas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - iniciar archive_recursive 3 linhas (profundidade=2)
2020-06-20 19:27:44 INFO:                       INÍCIO DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIM DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 linhas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - iniciar archive_recursive 3 linhas (profundidade=2)
2020-06-20 19:27:44 INFO:                       INÍCIO DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:                       FIM DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 linhas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:               FIM DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 linhas por ticket_no, flight_id
2020-06-20 19:27:44 INFO:       FIM DO ARQUIVO DE TABELAS REFERENCIADAS
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 linhas por id
2020-06-20 19:27:44 INFO: flights - FIM

Busca por dependências da tabela especificada

Função para buscar dependências da tabela especificada table. Parâmetros obrigatórios — config_path e table.

Após a execução, um dicionário será exibido na tela, onde:

  • in_refs — dicionário de tabelas que referenciam esta, onde a chave é o nome da tabela e o valor é uma lista de objetos Foreign Key (pk_main — chave primária na tabela principal, pk_ref — chave primária na tabela que referencia, fk_ref — nome da coluna que é a chave estrangeira para a tabela original);
  • out_refs — dicionário de tabelas que esta tabela 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')]}}

Busca por referências a linhas com chaves primárias especificadas

Função para buscar linhas em outras tabelas que referenciam através de chave estrangeira as linhas ids da tabela table. Parâmetros obrigatórios — config_path, table e ids.

Após a execução, um dicionário será exibido na tela com a seguinte estrutura:

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

Exemplo de chamada:

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

Além da execução no console, a biblioteca pode ser utilizada no código Python. Abaixo estão exemplos de chamadas no ambiente interativo iPython.

Arquivamento de tabela

>>> 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 - INÍCIO
2020-06-20 23:12:08 INFO: flights - início archive_recursive 2 linhas (profundidade=0)
2020-06-20 23:12:08 INFO: 	INÍCIO ARQUIVAR TABELAS 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 - início archive_recursive 30 linhas (profundidade=1)
2020-06-20 23:12:08 INFO: 		INÍCIO ARQUIVAR TABELAS 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 - archive_by_fk 30 linhas 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 by FK flight_id, ticket_no - 30 linhas
2020-06-20 23:12:08 INFO: 		FIM ARQUIVAR TABELAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 linhas 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 by flight_id, ticket_no - 30 linhas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 linhas
2020-06-20 23:12:08 INFO: 	ticket_flights - início archive_recursive 30 linhas (profundidade=1)
2020-06-20 23:12:08 INFO: 		INÍCIO ARQUIVAR TABELAS 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 - archive_by_fk 30 linhas 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 by FK flight_id, ticket_no - 30 linhas
2020-06-20 23:12:08 INFO: 		FIM ARQUIVAR TABELAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 linhas 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 by flight_id, ticket_no - 30 linhas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 linhas
2020-06-20 23:12:08 INFO: 	ticket_flights - início archive_recursive 30 linhas (profundidade=1)
2020-06-20 23:12:08 INFO: 		INÍCIO ARQUIVAR TABELAS 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 - archive_by_fk 30 linhas 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 by FK flight_id, ticket_no - 30 linhas
2020-06-20 23:12:08 INFO: 		FIM ARQUIVAR TABELAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 linhas 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 by flight_id, ticket_no - 30 linhas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 linhas
2020-06-20 23:12:08 INFO: 	ticket_flights - início archive_recursive 3 linhas (profundidade=1)
2020-06-20 23:12:08 INFO: 		INÍCIO ARQUIVAR TABELAS 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 - archive_by_fk 3 linhas 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 by FK flight_id, ticket_no - 3 linhas
2020-06-20 23:12:08 INFO: 		FIM ARQUIVAR TABELAS REFERENCIADAS
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 linhas 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 by flight_id, ticket_no - 3 linhas
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 linhas
2020-06-20 23:12:08 INFO: 	FIM ARQUIVAR TABELAS REFERENCIADAS
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 linhas 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 by flight_id - 2 linhas
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 linhas
2020-06-20 23:12:09 INFO: flights - FIM

Busca por dependências da tabela 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')]}}

Busca por referências a linhas com chaves primárias especificadas

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

O código-fonte da biblioteca está disponível em GitHub sob a licença MIT, bem como no repositório PyPI.

Fico feliz com comentários, commits e sugestões.

Tentarei responder às perguntas conforme possível aqui e no repositório.

Fonte: habr.com

Compre hospedagem confiável para sites com proteção contra DDoS, servidores VPS VDS 🔥 Compre hospedagem confiável para sites com proteção contra DDoS, servidores VPS VDS | ProHoster