ProHoster > Blog > διαχείριση > Το PgGraph είναι ένα βοηθητικό πρόγραμμα για την αρχειοθέτηση και την εύρεση εξαρτήσεων πινάκων στο PostgreSQL

Το PgGraph είναι ένα βοηθητικό πρόγραμμα για την αρχειοθέτηση και την εύρεση εξαρτήσεων πινάκων στο PostgreSQL

Το PgGraph είναι ένα βοηθητικό πρόγραμμα για την αρχειοθέτηση και την εύρεση εξαρτήσεων πινάκων στο PostgreSQL
Σήμερα θέλω να παρουσιάσω στους αναγνώστες Habr ένα βοηθητικό πρόγραμμα γραμμένο σε Python για την εργασία με εξαρτήσεις πινάκων στο PostgreSQL DBMS.

Το API του βοηθητικού προγράμματος είναι απλό και αποτελείται από τρεις μεθόδους:

  • archive_table - αναδρομική αρχειοθέτηση/διαγραφή σειρών με καθορισμένα Πρωτεύοντα κλειδιά
  • get_table_references — αναζήτηση για εξαρτήσεις για έναν πίνακα (θα εμφανίσει πίνακες που αναφέρονται από τον καθορισμένο και αυτούς που αναφέρονται σε αυτόν)
  • get_rows_references - αναζήτηση για σειρές σε άλλους πίνακες που αναφέρονται σε καθορισμένες σειρές στον επιθυμητό πίνακα

Ιστορικό

Ονομάζομαι Oleg Borzov, είμαι προγραμματιστής στην ομάδα CRM για διαχειριστές στεγαστικών δανείων στο Domklik.

Η κύρια βάση δεδομένων του συστήματος CRM μας είναι από τις μεγαλύτερες σε όγκο στην εταιρεία. Είναι επίσης ένα από τα παλαιότερα: εμφανίστηκε στην ίδια την έναρξη του έργου, όταν τα δέντρα ήταν μεγάλα, η Domklik ήταν μια startup και αντί για μια microservice σε ένα μοντέρνο ασύγχρονο πλαίσιο Python υπήρχε ένας τεράστιος μονόλιθος στην PHP.

Η μετάβαση από την PHP στην Python ήταν πολύ μεγάλη και απαιτούσε ταυτόχρονη υποστήριξη και των δύο συστημάτων, κάτι που επηρέασε τον σχεδιασμό της βάσης δεδομένων.

Ως αποτέλεσμα, έχουμε μια βάση δεδομένων με μεγάλο αριθμό πολύ συνδεδεμένων και τεράστιων πινάκων με μια δέσμη ευρετηρίων για διαφορετικούς τύπους ερωτημάτων. Όλα αυτά επηρεάζουν αρνητικά την απόδοση της βάσης δεδομένων: λόγω των μεγάλων πινάκων και μιας δέσμης σχέσεων μεταξύ τους, η πολυπλοκότητα των ερωτημάτων αυξάνεται συνεχώς, κάτι που είναι ιδιαίτερα σημαντικό για τους πιο φορτωμένους πίνακες.

Για να μειώσουμε το φόρτο στη βάση δεδομένων, αποφασίσαμε να γράψουμε ένα σενάριο που θα μεταφέρει παλιές εγγραφές από τους πιο ογκώδεις και φορτωμένους πίνακες σε αρχειοθετημένους πίνακες (για παράδειγμα, από task в task_archive).

Αυτή η εργασία περιπλέκεται από τον μεγάλο αριθμό σχέσεων μεταξύ των πινάκων: απλώς μετακινήστε τις σειρές από task в task_archive δεν είναι αρκετό, πριν από αυτό πρέπει να κάνετε το ίδιο αναδρομικά με όλες αυτές τις αναφορές task τραπέζια.

Θα δείξω με ένα παράδειγμα βάση δεδομένων επίδειξης από τον ιστότοπο postgrespro.ru:

Το PgGraph είναι ένα βοηθητικό πρόγραμμα για την αρχειοθέτηση και την εύρεση εξαρτήσεων πινάκων στο PostgreSQL
Ας υποθέσουμε ότι πρέπει να διαγράψουμε εγγραφές από έναν πίνακα Flights. Η Postgres δεν μας επιτρέπει να το κάνουμε αυτό ακριβώς έτσι: πρέπει πρώτα να διαγράψουμε εγγραφές από όλους τους πίνακες αναφοράς και ούτω καθεξής αναδρομικά σε πίνακες που δεν αναφέρονται από κανέναν.

Στο παράδειγμά μας στο Flights αναφέρεται Ticket_flightsκαι πάνω της - Boarding_passes.

Επομένως, πρέπει να το διαγράψετε με αυτή τη σειρά:

  1. Λαμβάνουμε τις τιμές των πρωτευόντων κλειδιών (PK) των σειρών Ticket_flights, που αναφέρονται στις σειρές που θα διαγραφούν Flights.
  2. Λαμβάνουμε σειρές PK Boarding_passes, που αναφέρονται σε Ticket_flights.
  3. Διαγράφουμε σειρές ανά PK από το βήμα 2 στον πίνακα Boarding_passes.
  4. Διαγράψτε τις γραμμές κατά PK από το βήμα 1 Ticket_flights.
  5. Αφαίρεση γραμμών από 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

Προσθέστε ένα σχόλιο