ProHoster > blog > administrasi > PgGraph adalah utilitas untuk mengarsipkan dan menemukan dependensi tabel di PostgreSQL

PgGraph adalah utilitas untuk mengarsipkan dan menemukan dependensi tabel di PostgreSQL

PgGraph adalah utilitas untuk mengarsipkan dan menemukan dependensi tabel di PostgreSQL
Hari ini saya ingin memberi pembaca Habr sebuah utilitas yang ditulis dengan Python untuk bekerja dengan dependensi tabel di DBMS PostgreSQL.

API utilitasnya sederhana dan terdiri dari tiga metode:

  • arsip_tabel - pengarsipan/penghapusan baris secara rekursif dengan Kunci Utama yang ditentukan
  • dapatkan_tabel_referensi β€” mencari dependensi untuk suatu tabel (akan menampilkan tabel yang direferensikan oleh tabel tertentu dan orang yang mereferensikannya)
  • dapatkan_rows_references - mencari baris pada tabel lain yang mereferensikan baris tertentu pada tabel yang diinginkan

prasejarah

Nama saya Oleg Borzov, saya adalah 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 pohonnya masih besar, Domklik adalah sebuah startup, dan alih-alih layanan mikro pada kerangka asinkron Python yang modis, ada monolit besar di PHP.

Transisi dari PHP ke Python memakan waktu yang sangat lama dan memerlukan dukungan simultan dari kedua sistem, yang mempengaruhi desain database.

Hasilnya, kami memiliki database dengan sejumlah besar tabel yang sangat terhubung dan besar dengan banyak indeks untuk berbagai jenis kueri. Semua ini berdampak negatif pada kinerja database: karena tabel besar dan banyaknya hubungan di antara tabel tersebut, kompleksitas kueri terus meningkat, yang sangat penting untuk tabel yang paling banyak dimuat.

Untuk mengurangi beban pada database, kami memutuskan untuk menulis skrip yang akan mentransfer catatan lama dari tabel paling banyak dan paling banyak dimuat ke tabel yang diarsipkan (misalnya, dari task Π² task_archive).

Tugas ini menjadi rumit karena banyaknya hubungan antar tabel: cukup pindahkan baris dari task Π² task_archive saja tidak cukup, sebelum itu Anda perlu melakukan hal yang sama secara rekursif dengan semua referensi task tabel.

Saya akan mendemonstrasikannya dengan sebuah contoh basis data demo dari situs postgrespro.ru:

PgGraph adalah utilitas untuk mengarsipkan dan menemukan dependensi tabel di PostgreSQL
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 referensi, dan seterusnya secara rekursif hingga ke tabel yang tidak direferensikan oleh siapa pun.

Dalam contoh kita di Flights mengacu Ticket_flights, dan padanya - Boarding_passes.

Oleh karena itu, Anda perlu menghapusnya dengan urutan sebagai berikut:

  1. Kami mendapatkan nilai kunci utama (PK) dari baris-baris di dalamnya Ticket_flights, yang merujuk pada baris yang akan dihapus Flights.
  2. Kami mendapatkan baris PK Boarding_passes, yang mengacu pada Ticket_flights.
  3. Kami menghapus baris dengan PK dari langkah 2 di tabel Boarding_passes.
  4. Hapus baris dengan PK dari langkah 1 masuk Ticket_flights.
  5. Menghapus garis dari Flights.

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

Bagaimana cara menggunakan

Utilitas mendukung dua mode penggunaan:

  • Panggilan dari baris perintah (pggraph …).
  • Penggunaan dalam kode Python (class 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 pengarsipan:

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

Jalankan 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 - tindakan yang diperlukan: archive_table, get_table_references ΠΈΠ»ΠΈ get_rows_references.

Argumen bernama:

  • --config_path β€” jalur ke file konfigurasi;
  • --table β€” tabel yang Anda perlukan untuk melakukan suatu tindakan;
  • --ids β€” daftar id dipisahkan dengan koma, misalnya, 1,2,3 (parameter opsional);
  • --log_path β€” jalur ke folder untuk log (parameter opsional, secara default β€” folder utama);
  • --log_level β€” tingkat logging (parameter opsional, defaultnya adalah INFO).

Contoh perintah

Mengarsipkan tabel

Fungsi utama utilitas adalah pengarsipan data, mis. mentransfer baris dari tabel utama ke tabel arsip (misalnya, dari tabel buku-buku Π² arsip_buku).

Penghapusan tanpa pengarsipan juga didukung: untuk ini, Anda perlu mengatur parameter di config.ini ke_arsip = salah).

Parameter yang diperlukan - config_path, tabel dan id.

Setelah peluncuran, catatan akan dihapus secara rekursif ids di meja table dan di semua tabel yang merujuk padanya.

$ 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

Temukan dependensi untuk tabel tertentu

Berfungsi untuk menemukan dependensi dari tabel tertentu table. Parameter yang diperlukan - config_path ΠΈ table.

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

  • in_refs β€” kamus tabel yang mereferensikan tabel tertentu, dengan kuncinya adalah nama tabel, nilainya adalah daftar objek Kunci Asing (pk_main - kunci utama di tabel utama, pk_ref - kunci utama di tabel referensi, fk_ref β€” nama kolom yang merupakan kunci asing ke tabel sumber);
  • out_refs β€” kamus tabel yang dirujuk.

$ 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 referensi ke string dengan Kunci Utama yang ditentukan

Berfungsi untuk mencari baris pada tabel lain yang merujuk pada baris tersebut melalui Foreign Key ids meja table. Parameter yang diperlukan - config_path, table ΠΈ ids.

Setelah peluncuran, 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 menjalankannya di konsol, perpustakaan dapat digunakan dalam kode Python. Contoh panggilan di lingkungan interaktif iPython ditunjukkan di bawah ini.

Mengarsipkan tabel

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

Temukan dependensi untuk tabel tertentu

>>> 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 referensi 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 perpustakaan tersedia di GitHub di bawah lisensi MIT, serta di repositori PyPI.

Saya akan dengan senang hati memberikan komentar, komitmen, dan saran.

Saya akan mencoba menjawab pertanyaan dengan kemampuan terbaik saya di sini dan di repositori.

Sumber: www.habr.com

Tambah komentar