🥇PgGraph - помощна програма за архивиране и търсене на зависимости в таблици в PostgreSQL

Днес искам да представя на читателите на Habr помощна програма, написана на Python за работа със зависимости на таблици в СУБД PostgreSQL.

API на помощната програма е прост и се състои от три метода:

архивна_таблица - рекурсивно архивиране/изтриване на редове с определени първични ключове
get_table_references — търсене на зависимости за таблица (ще покаже таблици, към които се позовава определената и тези, които я препращат)
get_rows_references - търсене на редове в други таблици, които препращат към определени редове в желаната таблица

праистория

Казвам се Олег Борзов, аз съм разработчик в CRM екипа за мениджъри по ипотечно кредитиране в Domklik.

Основната база данни на нашата CRM система е една от най-големите по обем в компанията. Освен това е и един от най-старите: появи се при самото стартиране на проекта, когато дърветата бяха големи, Domklik беше стартъп и вместо микроуслуга на модерна Python асинхронна рамка имаше огромен монолит в PHP.

Преходът от PHP към Python беше много дълъг и изискваше едновременна поддръжка на двете системи, което се отрази на дизайна на базата данни.

В резултат на това имаме база данни с голям брой силно свързани и огромни таблици с куп индекси за различни типове заявки. Всичко това се отразява негативно на производителността на базата данни: поради големите таблици и куп връзки между тях, сложността на заявките непрекъснато се увеличава, което е особено критично за най-натоварените таблици.

За да намалим натоварването на базата данни, решихме да напишем скрипт, който да прехвърля стари записи от най-обемните и натоварени таблици в архивирани (например от task в task_archive).

Тази задача се усложнява от големия брой връзки между таблиците: просто преместете редове от task в task_archive не е достатъчно, преди това трябва да направите същото рекурсивно с всички тези препратки task маси.

Ще демонстрирам с пример демо база данни от сайта postgrespro.ru:

Да кажем, че трябва да изтрием записи от таблица Flights. Postgres няма да ни позволи да направим това просто така: първо трябва да изтрием записи от всички референтни таблици и така нататък рекурсивно до таблици, които не са реферирани от никого.

В нашия пример при Flights се отнася Ticket_flights, а върху нея - Boarding_passes.

Следователно трябва да го изтриете в следния ред:

Получаваме стойностите на първичните ключове (PK) на редовете в Ticket_flights, които се отнасят до редовете, в които трябва да се изтрият Flights.
Получаваме PK редове Boarding_passes, които се отнасят до Ticket_flights.
Изтриваме редове по PK от стъпка 2 в таблицата Boarding_passes.
Изтрийте редовете по PK от стъпка 1 в Ticket_flights.
Премахване на линии от Flights.

Резултатът беше помощна програма, наречена PgGraph, която решихме да направим с отворен код.

Как да използвате

Помощната програма поддържа два режима на използване:

Обаждане от командния ред (pggraph …).
Използване в код на Python (клас PgGraphApi).

Инсталиране и конфигуриране

Първо трябва да инсталирате помощната програма от хранилището на Pypi:

pip3 install pggraph

След това създайте файл config.ini на локалната машина с конфигурацията на базата данни и скрипта за архивиране:

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

Стартирайте от конзолата

Параметри

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

Позиционни аргументи:

action - необходимо действие: archive_table, get_table_references или get_rows_references.

Именувани аргументи:

--config_path — път до конфигурационния файл;
--table — таблица, с която трябва да извършите действие;
--ids — списък с идентификатори, разделени със запетаи, например, 1,2,3 (незадължителен параметър);
--log_path — път до папката за регистрационни файлове (незадължителен параметър, по подразбиране — домашна папка);
--log_level — ниво на регистриране (незадължителен параметър, по подразбиране е INFO).

Примери за команди

Архивиране на таблица

Основната функция на помощната програма е архивирането на данни, т.е. прехвърляне на редове от основната таблица към архивната таблица (например от таблицата книги в книги_архив).

Поддържа се и изтриване без архивиране: за това трябва да зададете параметъра в config.ini to_archive = невярно).

Необходими параметри - config_path, таблица и идентификатори.

След стартиране записите ще бъдат рекурсивно изтрити ids в таблицата table и във всички таблици, които се отнасят до него.

$ 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

Намерете зависимости за определена таблица

Функция за намиране на зависимости на определена таблица table. Необходими параметри - config_path и table.

След стартиране на екрана ще се покаже речник, където:

in_refs — речник на таблици, препращащи към дадена, където ключът е името на таблицата, стойността е списък с обекти на външен ключ (pk_main - първичен ключ в главната таблица, pk_ref - първичен ключ в референтната таблица, fk_ref — името на колоната, която е външен ключ към изходната таблица);
out_refs — речник на таблиците, към който се отнася.

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

Намиране на препратки към низове с посочения първичен ключ

Функция за търсене на редове в други таблици, които препращат към редове чрез външен ключ ids маси table. Необходими параметри - config_path, table и ids.

След стартиране на екрана ще се покаже речник със следната структура:

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

Примерно обаждане:

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

Употреба в кода

В допълнение към стартирането й в конзолата, библиотеката може да се използва в код на Python. Примери за повиквания в интерактивната среда на iPython са показани по-долу.

Архивиране на таблица

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

Намерете зависимости за определена таблица

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

Намиране на препратки към низове с посочения първичен ключ

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

Изходният код на библиотеката е достъпен на GitHub под лиценз на MIT, както и в хранилището PyPI.

Ще се радвам на коментари, ангажименти и предложения.

Ще се опитам да отговоря на въпроси, доколкото мога, тук и в хранилището.

Източник: www.habr.com

PgGraph е помощна програма за архивиране и намиране на зависимости на таблици в PostgreSQL

праистория

Как да използвате

Инсталиране и конфигуриране

Стартирайте от конзолата

Параметри

Примери за команди

Архивиране на таблица

Намерете зависимости за определена таблица

Намиране на препратки към низове с посочения първичен ключ

Употреба в кода

Архивиране на таблица

Намерете зависимости за определена таблица

Намиране на препратки към низове с посочения първичен ключ

Добавяне на нов коментар

PgGraph е помощна програма за архивиране и намиране на зависимости на таблици в PostgreSQL

праистория

Как да използвате

Инсталиране и конфигуриране

Стартирайте от конзолата

Параметри

Примери за команди

Архивиране на таблица

Намерете зависимости за определена таблица

Намиране на препратки към низове с посочения първичен ключ

Употреба в кода

Архивиране на таблица

Намерете зависимости за определена таблица

Намиране на препратки към низове с посочения първичен ключ

Добавяне на нов коментар Отказване на отговора

Добавяне на нов коментар