🧔 🎪 🤳🏻 PgGraph - utilitas untuk pengarsipan dan menemukan dependensi tabel di PostgreSQL 🐶 ☢️ 📭

Hari ini saya ingin menyajikan kepada para pembaca Habr sebuah utilitas yang ditulis dengan Python untuk bekerja dengan dependensi tabel dalam DBMS PostgreSQL.

API utilitas sederhana dan terdiri dari tiga metode:

archive_table - baris pengarsipan / penghapusan rekursif dengan Kunci Utama yang ditentukan
get_table_references - temukan dependensi untuk tabel (akan menampilkan tabel yang direferensikan dan direferensikan oleh yang ditentukan)
get_rows_references - mencari baris di tabel lain yang merujuk ke baris yang ditentukan di tabel yang diinginkan

Latar Belakang

Nama saya Oleg Borzov, saya seorang pengembang di tim CRM untuk manajer pinjaman hipotek di Domklik.

Basis data utama sistem CRM kami adalah salah satu yang terbesar dalam hal volume di perusahaan. Ini juga salah satu yang tertua: muncul pada saat peluncuran proyek, ketika pohon-pohon besar, Domclick adalah startup, dan alih-alih layanan mikro pada kerangka asinkron Python modis, ada monolit besar di PHP.

Transisi dari PHP ke Python sangat panjang dan membutuhkan dukungan simultan dari kedua sistem, yang mempengaruhi desain database.

Sebagai hasilnya, kami memiliki database dengan sejumlah besar tabel yang sangat terhubung dan besar dengan sekelompok indeks untuk berbagai jenis kueri. Semua ini secara negatif mempengaruhi kinerja database: karena tabel besar dan banyak koneksi di antara mereka, kompleksitas kueri terus tumbuh, yang sangat penting untuk tabel paling banyak dimuat.

Untuk mengurangi beban pada basis data, kami memutuskan untuk menulis skrip yang akan mentransfer catatan lama dari tabel yang paling bervolume dan dimuat ke tabel arsip (misalnya, dari taskc task_archive) setiap hari .

Tugas ini rumit oleh sejumlah besar hubungan antara tabel: hanya mentransfer baris dari taskke task_archivetidak cukup, sebelum itu Anda perlu melakukan hal yang sama secara rekursif dengan semua tasktabel referensi .

Saya akan menunjukkan dengan sebuah contohdatabase demo dari postgrespro.ru :

Katakanlah kita perlu menghapus catatan dari sebuah tabel Flights. Postgres tidak akan mengizinkan kita melakukan ini begitu saja: pertama-tama kita perlu menghapus catatan dari semua tabel rujukan, dan dengan demikian secara rekursif ke tabel yang tidak ada yang merujuk.

Dalam contoh kita, ini Flightsmengacu pada Ticket_flights, dan untuk itu - Boarding_passes.

Karena itu, Anda perlu menghapus dalam urutan ini:

Dapatkan nilai Kunci Utama (PK) dari baris Ticket_flights, yang merujuk ke baris yang dihapus di Flights.
Kami mendapatkan PK dari garis Boarding_passesyang merujuk Ticket_flights.
Kami menghapus baris dengan PK dari item 2 di tabel Boarding_passes.
Kami menghapus baris dengan PK dari klausa 1 di Ticket_flights.
Kami menghapus baris dari Flights.

Hasilnya adalah sebuah utilitas bernama PgGraph, yang kami putuskan untuk membuat open source.

Cara Penggunaan

Utilitas mendukung dua mode penggunaan:

Panggilan dari baris perintah ( pggraph …).
Penggunaan dalam kode Python (kelas PgGraphApi).

Instalasi dan konfigurasi

Pertama, Anda perlu menginstal utilitas dari repositori Pypi:

pip3 install pggraph

Kemudian buat file config.ini di mesin lokal dengan konfigurasi database dan skrip arsip:

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

Berlari dari konsol

Parameter

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

Argumen posisi:

action- efek yang diinginkan: archive_table, get_table_referencesatau get_rows_references.

Argumen yang dinamai:

--config_path - path ke file konfigurasi;
--table - tabel yang dengannya Anda ingin melakukan tindakan;
--ids- daftar id yang dipisahkan oleh koma, misalnya, 1,2,3(opsional);
--log_path - path ke folder untuk log (parameter opsional, secara default - folder home);
--log_level - Level logging (parameter opsional, secara default - INFO).

Contoh perintah

Pengarsipan meja

Fungsi utama dari utilitas adalah pengarsipan data, mis. mentransfer baris dari tabel utama ke tabel arsip (misalnya, dari tabel buku ke books_archive ).

Penghapusan tanpa pengarsipan juga didukung: untuk melakukan ini, atur parameter to_archive = false di config.ini ).

Parameter yang diperlukan adalah config_path, tabel dan id .

Setelah startup, catatan idsdi tabel tabledan di semua tabel yang merujuknya akan dihapus secara rekursif .

$ 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

Menemukan Ketergantungan untuk Tabel yang Ditentukan

Fungsi untuk menemukan dependensi dari tabel yang ditentukan table. Parameter yang diperlukan adalah config_pathdan table.

Setelah peluncuran, kamus akan ditampilkan di layar, di mana:

in_refs- Kamus merujuk tabel ke yang diberikan, di mana kunci adalah nama tabel, nilai adalah daftar objek Kunci Asing ( pk_main- kunci primer di tabel utama, pk_ref- kunci utama dalam tabel referensi, fk_ref- nama kolom yang merupakan kunci asing ke tabel sumber);
out_refs - Kamus tabel yang merujuk pada yang diberikan.

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

Menemukan tautan ke string dengan Kunci Utama yang ditentukan

Fungsi untuk menemukan baris di tabel lain yang merujuk ke baris idstabel melalui Kunci Asing table. Parameter yang diperlukan adalah config_path, tabledan ids.

Setelah diluncurkan, kamus dengan struktur berikut akan ditampilkan di layar:

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

Contoh panggilan:

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

Penggunaan dalam kode

Selain berjalan di konsol, perpustakaan dapat digunakan dalam kode Python. Di bawah ini adalah contoh panggilan di lingkungan interaktif iPython.

Pengarsipan meja

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

Menemukan Ketergantungan untuk Tabel yang Ditentukan

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

Menemukan tautan ke string dengan Kunci Utama yang ditentukan

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

Kode sumber pustaka tersedia di GitHub di bawah lisensi MIT, serta dalam repositori PyPI .

Komentar, komitmen, dan saran dipersilahkan.

Saya akan mencoba menjawab pertanyaan sejauh mungkin di sini dan di repositori.

PgGraph - utilitas untuk pengarsipan dan menemukan dependensi tabel di PostgreSQL

Latar Belakang

Cara Penggunaan

Instalasi dan konfigurasi

Berlari dari konsol

Parameter

Contoh perintah

Pengarsipan meja

Menemukan Ketergantungan untuk Tabel yang Ditentukan

Menemukan tautan ke string dengan Kunci Utama yang ditentukan

Penggunaan dalam kode

Pengarsipan meja

Menemukan Ketergantungan untuk Tabel yang Ditentukan

Menemukan tautan ke string dengan Kunci Utama yang ditentukan

More articles: