In der Datenbankwelt ist PostgreSQL ein leistungsstarkes und zuverlässiges relationales Open-Source-Datenbanksystem. Wenn jedoch die Arbeitslast der Datenbank wächst, wird eine effiziente Verbindungsverwaltung entscheidend für die Aufrechterhaltung der Leistung und Skalierbarkeit. An dieser Stelle kommen Connection Pooler wie Pgbouncer ins Spiel.
In diesem Blogbeitrag werden wir die Einrichtung und Konfiguration von Pgbouncerr, einem leichtgewichtigen Connection Pooler für PostgreSQL, auf Amazon EC2-Instanzen untersuchen. Pgbouncer agiert als Vermittler zwischen Ihrer Anwendung und der Datenbank und managt effizient Datenbankverbindungen.
Voraussetzungen
Vergewissern Sie sich, dass Sie die Schritte in unserem vorherigen Blog-Beitrag, Einrichten eines hochverfügbaren PostgreSQL-Clusters mit Patroni unter Verwendung eines Spilo-Images ausgeführt haben, um PostgreSQL auf Ihren EC2-Instanzen einzurichten. Wir werden die gleichen Instanzen für diesen Blogpost verwenden.
Warum Pgbouncer verwenden?
Die Verwaltung von Datenbankverbindungen kann sehr ressourcenintensiv sein. Jede Verbindung verbraucht Speicher auf dem Datenbankserver. Wenn Ihre Anwendung zahlreiche Verbindungen benötigt, kann dies zu Leistungsengpässen führen. Pgbouncer löst dieses Problem durch Pooling und Wiederverwendung von Datenbankverbindungen, was zu erheblichen Verbesserungen der Effizienz und Skalierbarkeit führt.
Bevor wir in die technischen Details eintauchen, lassen Sie uns einen Blick auf die Konfigurationsdatei und die systemd-Unit-Datei werfen, die es uns ermöglichen, Pgbouncer für PostgreSQL auf Fedora CoreOS einzurichten.
Die Konfigurationsdatei
Im Folgenden finden Sie die systemd-Unit-Konfiguration für unseren Pgbouncer-Dienst, die der Butane-Konfigurationsdatei aus dem vorherigen Blogpost hinzugefügt werden kann:
Die obige Konfiguration führt den Pgbouncer-Container aus und stellt den Pgbouncer-Dienst auf Port 6432 bereit. Der Pgbouncer selbst verbindet sich mit der PostgreSQL DB mit den Parametern, die wir in DB_HOST, DB_NAME und DB_PORT angegeben haben. Die Verbindungen vom Endbenutzer zum Pgbouncer werden über TLS mit selbstsignierten Zertifikaten gesichert, die mit einem Skript generiert wurden, das in unserem Github-Repository zu finden ist.
Im ersten Schritt führen wir ein Skript namens prepare_pgbouncer_auth.sh aus. Hier erstellen wir einen Datenbankbenutzer, der von Pgbouncer verwendet wird, um zu überprüfen, ob eingehende Verbindungen erlaubt sind oder nicht. Das Skript sieht wie folgt aus:
Zuerst wird gewartet, bis die PostgreSQL-Instanz bereit ist, Verbindungen zu akzeptieren. Dann wird ein Datenbankbenutzer namens pgbouncer angelegt. Der Benutzer pgbouncer ist mit einer sehr begrenzten Anzahl von Rechten ausgestattet. Er kann sich nur mit der Datenbank verbinden und die Funktion get_auth ausführen. Diese Funktion wird von Pgbouncer verwendet, um zu überprüfen, ob eingehende Verbindungen erlaubt sind oder nicht.
Nachdem das Skript erfolgreich ausgeführt wurde, können wir den Benutzer pgbouncer als AUTH_USER in der systemd-Unit-Konfiguration angeben.
In der systemd-Unit-Konfiguration geben wir die AUTH_QUERY mit dem Wert SELECT username, password FROM pgbouncer.get_auth($1) an. Das bedeutet, dass Pgbouncer die Funktion pgbouncer.get_auth mit dem vom Client angegebenen Benutzernamen ausführen wird. Wenn die Funktion eine Zeile zurückgibt, ist die Verbindung erlaubt. Andernfalls wird die Verbindung abgelehnt.
Dann haben wir den Parameter STATS_USERS. Dieser Parameter gibt die Benutzer an, die Statistiken über die Pgbouncer-Instanz abrufen dürfen, z. B. den Befehl SHOW STATS. Die zurückgegebenen Informationen sind nützlich und können zur Überwachung des Pgbouncer-Dienstes verwendet werden. In unserem Fall verwenden wir einen Benutzer namens stats_collector.
Schließlich haben wir AUTH_FILE. Dieser Parameter gibt den Speicherort der Datei userlist.txt an. Diese Datei enthält normalerweise die Liste der Benutzer und ihrer Passwörter, die eine Verbindung zur Datenbank herstellen dürfen. In unserem Fall (der sehr einfach ist) enthält die Datei Folgendes:
Benutzer in dieser Datei dürfen sich mit der internen Pgbouncer-Datenbank pgbouncer verbinden und werden nicht von der Funktion pgbouncer.get_auth abgefragt. Darüber hinaus wird der Benutzer pgbouncer zur Authentifizierung gegenüber der Datenbank verwendet.
Nachdem wir nun die Konfiguration der systemd-Einheit behandelt haben, sind wir bereit, die EC2-Instanzen mit unserer neuen Konfigurationsdatei bereitzustellen. Allerdings müssen wir noch einen Eintrag in unserer Security Group task ändern. Da wir jetzt Pgbouncer auf Port 6432 verwenden, möchten wir Port 5432, der von der Postgres-Datenbank verwendet wird, nicht freigeben. Stattdessen werden wir den Port 6432 freigeben. Hier ist die aktualisierte Version der Sicherheitsgruppenaufgabe in der Datei network_conf-task.yml:
Wir haben nun alles eingerichtet und sind startklar. Stellen wir die Instanzen bereit, indem wir dasselbe Ansible-Skript aus dem vorherigen Blogbeitrag verwenden, um die Instanzen bereitzustellen.
Überprüfen des Connection Poolers
Sobald die Instanzen in Betrieb sind, können wir uns per SSH in eine der Instanzen einwählen und den Status des Pgbouncer- Service überprüfen, indem wir den folgenden Befehl ausführen:
Sie sollten eine ähnliche Antwort wie die folgende erhalten:
Nachdem wir nun sichergestellt haben, dass der Pgbouncer- Service läuft, können wir die Verbindung testen, indem wir uns mit dem Befehl psql mit postgres als Benutzernamen und zalando als (Standard-) Passwort mit der Datenbank verbinden:
Sie können sich auch als Benutzer stats_collector mit der Datenbank pgbouncer verbinden, um die Statistiken von Pgbouncer zu überwachen. Das Passwort dieses Benutzers ist in der Datei userlist.txt definiert (in unserem Fall ist es collecter_stats). Dies können wir mit folgendem Befehl erreichen:
Nach Eingabe des richtigen Passworts sollten Sie eine Antwort ähnlich der folgenden erhalten:
Damit haben wir überprüft, dass der Pgbouncer-Dienst läuft und dass wir uns über den Pgbouncer-Dienst mit der Datenbank verbinden können.
Zusammenfassung
In diesem Blogbeitrag haben wir das grundlegende Konzept der Nutzung von Pgbouncer, einem Verbindungs-Pooler, untersucht, um die Effizienz und Skalierbarkeit Ihrer PostgreSQL-Datenbank auf EC2-Instanzen zu verbessern. Wir haben gelernt, wie man Pgbouncer einrichtet und konfiguriert, damit es als Zwischenschicht zwischen Ihrer Anwendung und dem Datenbankserver fungiert und Verbindungen nahtlos verwaltet.
Durch das Befolgen der detaillierten Schritte und Konfigurationen, die in diesem Blogbeitrag beschrieben wurden, haben Sie einen wichtigen Schritt zur Optimierung Ihrer PostgreSQL-Umgebung für hohe Leistung und Skalierbarkeit unternommen. Mit Pgbouncer können Sie Ihre Datenbankverbindungen sicher und effizient verwalten, während Sie eine stabile PostgreSQL-Umgebung aufrechterhalten.
Wir hoffen, dass dieser Leitfaden informativ war und Ihnen den Weg erleichtert, das volle Potenzial von PostgreSQL auszuschöpfen!
Farouq Abdurrahman ist Praktikant bei der Proventa AG und studiert Informatik. Sein Schwerpunkt liegt auf Cloud Computing. Er hat großes Interesse an der Digitalen Transformation und Cloud Computing. Aktuell beschäftigt er sich mit PostgreSQL Datenbanken in der Cloud.