Häufige DBA-Aufgaben für PostgreSQL
In diesem Abschnitt werden die Amazon RDS-Implementierungen einiger häufiger DBA-Aufgaben für DB-Instances beschrieben, auf denen die PostgreSQL-Datenbank-Engine ausgeführt wird. Zum Bereitstellen einer verwalteten Service-Erfahrung, bietet Amazon RDS keinen Shell-Zugriff auf DB-Instances und schränkt den Zugriff auf bestimmte Verfahren Tabellen ein, die erweiterte Berechtigungen erfordern.
Weitere Informationen zur Arbeit mit PostgreSQL-Protokolldateien in Amazon RDS finden Sie unter PostgreSQL-Datenbankprotokolldateien.
Themen
- Erstellen von Rollen
- Verwalten des Zugriffs auf eine PostgreSQL-Datenbank
- Arbeiten mit PostgreSQL-Parametern
- Arbeiten mit der PostgreSQL-Selbstbereinigung in Amazon RDS
- Auditprotokollierung für eine PostgreSQL-DB-Instance
- Arbeiten mit der pgaudit-Erweiterung
- Arbeiten mit der pg_repack-Erweiterung
- Arbeiten mit PostGIS
- Verwenden von pgBadger für die Protokollanalyse mit PostgreSQL
- Anzeigen des Inhalts von pg_config
- Arbeiten mit der orafce-Erweiterung
- Zugreifen auf externe Daten mit der Erweiterung postgres_fdw
- Verwenden eines benutzerdefinierten DNS-Servers für ausgehenden Netzwerkzugriff.
- Beschränken der Passwortverwaltung
Erstellen von Rollen
Wenn Sie eine DB-Instance erstellen, ist das Systemkonto des Masterbenutzers, das
Sie erstellen, der rds_superuser
-Rolle zugewiesen. Die Rolle rds_superuser
ist eine vordefinierte Amazon RDS-Rolle ähnlich der PostgreSQL-Superuser-Rolle (üblicherweise
in lokalen Instances als postgres
bezeichnet), jedoch mit einigen Einschränkungen. Ähnlich der PostgreSQL-Superuser-Rolle
bietet die Rolle rds_superuser
für Ihre DB-Instance die meisten Vorteile. Diese Rolle darf nur den Benutzern zugewiesen
werden, die am häufigsten auf die DB-Instance zugreifen.
Mit der rds_superuser
-Rolle kann Folgendes ausgeführt werden:
-
Hinzufügen von Erweiterungen für die Verwendung mit Amazon RDS. Weitere Informationen dazu finden Sie unter Unterstützte PostgreSQL-Funktionen und in der PostgreSQL-Dokumentation
. -
Verwalten (einschließlich Erstellen und Löschen) von Tabellenräumen. Weitere Informationen dazu finden Sie im Abschnitt Tablespaces
der PostgreSQL-Dokumentation. -
Anzeigen aller Benutzer, denen nicht die Rolle
rds_superuser
zugewiesen ist, mithilfe des Befehlspg_stat_activity
und Beenden ihrer Verbindungen mithilfe der Befehlepg_terminate_backend
undpg_cancel_backend
. -
Erteilen und Widerrufen der Rolle
rds_replication
für alle Rollen außer der Rollerds_superuser
. Weitere Informationen dazu finden Sie im Abschnitt GRANTder PostgreSQL-Dokumentation.
Das folgende Beispiel zeigt die Erstellung eines Benutzers und die anschließende Gewährung
der Rolle rds_superuser
für den Benutzer. Benutzerdefinierte Rollen wie rds_superuser
müssen gewährt werden.
create role testuser with password 'testuser' login; CREATE ROLE grant rds_superuser to testuser; GRANT ROLE
Verwalten des Zugriffs auf eine PostgreSQL-Datenbank
In Amazon RDS für PostgreSQL verwalten Sie, welche Benutzer eine Verbindung zu welcher
Datenbank aufbauen dürfen. In anderen PostgreSQL-Umgebungen führen Sie diese Art der
Verwaltung manchmal durch, indem Sie die Datei pg_hba.conf
ändern. In Amazon RDS verwenden Sie stattdessen Datenbankberechtigungen.
Neue Datenbanken in PostgreSQL werden immer mit Standardberechtigungen erstellt. Standardberechtigungen
ermöglichen es PUBLIC
(allen Benutzern), eine Verbindung zur Datenbank aufzubauen und während der Verbindung
temporäre Tabellen zu erstellen.
Um festzulegen, welche Benutzer eine Verbindung zu einer bestimmten Datenbank in Amazon
RDS herstellen dürfen, müssen Sie zunächst die Standardberechtigungen PUBLIC
aufheben. Anschließend erteilen Sie die Berechtigungen auf differenzierter Ebene
erneut. Dieses Verfahren ist im folgenden Beispielcode dargestellt.
psql> revoke all on database <database-name> from public; psql> grant connect, temporary on database <database-name> to <user/role name>;
Weitere Informationen zu Berechtigungen in PostgreSQL-Datenbanken finden Sie unter
dem Befehl GRANT
Arbeiten mit PostgreSQL-Parametern
PostgreSQL-Parameter, die Sie für eine lokale PostgreSQL-Instance in der Datei postgresql.conf festlegen, werden in der DB-Parametergruppe für Ihre DB-Instance gespeichert. Wenn Sie eine DB-Instance unter Verwendung der Standardparametergruppe erstellen, befinden sich die Parametereinstellungen in der Parametergruppe namens default.postgres9.6.
Wenn Sie eine DB-Instance erstellen, werden die Parameter in der verknüpften DB-Parametergruppe
geladen. Sie können Parameterwerte ändern, indem Sie Werte in der Parametergruppe
ändern. Sie können Parameterwerte auch ändern, indem Sie die Befehle ALTER DATABASE,
ALTER ROLE und SET verwenden, wenn Sie über die entsprechenden Sicherheitsrechte verfügen.
Sie können weder den Befehlszeilenbefehl postgres
noch den Befehlszeilenbefehl env PGOPTIONS
verwenden können, da Sie keinen Zugriff auf den Host haben.
Die Nachverfolgung von PostgreSQL-Parametereinstellungen kann gelegentlich schwierig sein. Verwenden Sie den folgenden Befehl, um aktuelle Parametereinstellungen und den Standardwert aufzulisten.
select name, setting, boot_val, reset_val, unit from pg_settings order by name;
Eine Erklärung der Ausgabewerte finden Sie unter dem Thema pg_settings
Wenn Sie die Arbeitsspeichereinstellungen für max_connections
oder shared_buffers
zu hoch einstellen, verhindern Sie den Start der PostgreSQL-Instance. Einige Parameter
verwenden Einheiten, mit denen Sie möglicherweise nicht vertraut sind. Beispielsweise
legt shared_buffers
die Zahl von 8 KB freigegebenem Arbeitsspeicherpuffer fest, die vom Server verwendet
werden.
Der folgende Fehler wird zur Datei postgres.log geschrieben, wenn die Instance versucht zu starten, durch falsche Parametereinstellungen jedoch am Starten gehindert wird.
2013-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory 2013-09-18 21:13:15 UTC::@:[8097]:HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
Es gibt zwei Arten von PostgreSQL-Parametern, statisch und dynamisch. Statische Parameter erfordern einen Neustart der DB-Instance, bevor sie angewendet werden. Dynamische Parameter können sofort angewendet werden. Die folgende Tabelle zeigt Parameter, die Sie für eine PostgreSQL-DB-Instance ändern können, und den jeweiligen Parametertyp.
Parametername |
Apply_Type |
Beschreibung |
---|---|---|
|
Dynamisch | Legt den Namen der Anwendung fest, der in Statistiken und Protokollen verwendet werden soll. |
|
Dynamisch | Ermöglicht die Eingabe von NULL-Elementen in Arrays. |
|
Dynamisch | Legt die Zeit fest, die maximal zulässig ist, um die Client-Authentifizierung durchzuführen. |
|
Dynamisch | Startet den Untervorgang der Selbstbereinigung. |
|
Dynamisch | Anzahl von Tupel-Einfügungen, -Aktualisierungen oder -Löschungen vor der Analyse als Bruchteil von Reltupeln. |
|
Dynamisch | Mindestanzahl von Tupel-Einfügungen, -Aktualisierungen oder -Löschungen vor der Analyse. |
|
Dynamisch | Inaktivitätszeit zwischen Selbstbereinigungen. |
|
Dynamisch | Bereinigungskostenverzögerung (in Millisekunden) für die Selbstbereinigung. |
|
Dynamisch | Bereinigungskostenbetrag für die Selbstbereinigung, der vor der Inaktivität verfügbar ist. |
|
Dynamisch | Anzahl von Tupel-Aktualisierungen oder -Löschungen vor der Bereinigung als Bruchteil von Reltupeln. |
|
Dynamisch | Mindestanzahl von Tupel-Aktualisierungen oder -Löschungen vor der Bereinigung. |
|
Dynamisch | Legt fest, ob in Zeichenfolgeliteralen ein Backslash (\) zulässig ist. |
|
Dynamisch | Inaktivitätszeit des Hintergrundschreibers zwischen Runden. |
|
Dynamisch | Maximale Anzahl von LRU-Seiten eines Hintergrundschreibers, für die pro Runde ein Flush ausgeführt werden kann. |
|
Dynamisch | Mehrfaches der durchschnittlichen Puffernutzung, die pro Runde freigegeben werden soll. |
|
Dynamisch | Legt das Ausgabeformat für Bytes fest. |
|
Dynamisch | Überprüft die Funktionstexte während CREATE FUNCTION. |
|
Dynamisch | Zeit für den Flush ungültiger Puffer während des Prüfpunkts als Bruchteil des Prüfpunktintervalls. |
|
Dynamisch | Legt die maximale Entfernung in Protokollsegmenten zwischen automatischen Write-Ahead Log (WAL)-Prüfpunkten fest. |
|
Dynamisch | Legt die maximale Zeit zwischen automatischen WAL-Prüfpunkten fest. |
|
Dynamisch | Ermöglicht Warnungen, wenn Prüfpunktsegmente häufiger als hierdurch angegeben gefüllt werden. |
|
Dynamisch | Legt die Zeichensatzkodierung des Client fest. |
|
Dynamisch | Legt die Nachrichtenebenen fest, die an den Client gesendet werden. |
|
Dynamisch | Legt die Verzögerung (in Mikrosekunden) zwischen dem Transaktions-Commit und dem Flush von WAL zum Datenträger fest. |
|
Dynamisch | Legt die Mindestzahl gleichzeitiger offener Transaktionen fest, bevor eine Commit-Verzögerung ausgeführt wird. |
|
Dynamisch | Ermöglicht dem Planer die Verwendung von Einschränkungen, um Abfragen zu optimieren. |
|
Dynamisch | Legt die Schätzung des Planers für die Kosten der Verarbeitung der einzelnen Indexeinträge während einer Indexprüfung fest. |
|
Dynamisch | Legt die Schätzung des Planers für die Kosten der Verarbeitung der einzelnen Operator- oder Funktionsaufrufe fest. |
|
Dynamisch | Legt die Schätzung des Planers für die Kosten der Verarbeitung der einzelnen Tupeln (Zeilen) fest. |
|
Dynamisch | Legt die Schätzung des Planers für den Bruchteil der Zeilen eines Cursors fest, die abgerufen werden. |
|
Dynamisch | Legt das Anzeigeformat für Datum- und Uhrzeitwerte fest. |
|
Dynamisch | Legt die Zeit fest, die während einer Sperre gewartet wird, bevor auf einen Deadlock geprüft wird. |
|
Dynamisch | Erstellt Einschübe für Analyse- und Planstrukturanzeigen. |
|
Dynamisch | Protokolliert die Analysestruktur der einzelnen Abfragen. |
|
Dynamisch | Protokolliert den Ausführungsplan der einzelnen Abfragen. |
|
Dynamisch | Protokolliert die neu geschriebene Analysestruktur der einzelnen Abfragen. |
|
Dynamisch | Legt das Standardstatistikziel fest. |
|
Dynamisch | Legt den Standardtabellenraum fest, in dem Tabellen und Indexe erstellt werden. |
|
Dynamisch | Legt den Standardaufschiebbarkeitsstatus neuer Transaktionen fest. |
|
Dynamisch | Legt die Transaktionsisolierungsstufe jeder neuen Transaktion fest. |
|
Dynamisch | Legt den Standardschreibschutzstatus neuer Transaktionen fest. |
|
Dynamisch | Erstellt neue Tabellen standardmäßig mit OIDs. |
|
Dynamisch | Legt die Annahme des Planers hinsichtlich der Größe des Datenträger-Caches fest. |
|
Dynamisch | Die Anzahl der gleichzeitigen Anfragen, die durch das Datenträgersubsystem effizient bearbeitet werden können. |
|
Dynamisch | Ermöglicht die Verwendung von Bitmap-Prüfungsplänen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von Hash-Aggregationsplänen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von Hash-Join-Plänen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von Indexprüfungsplänen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von Materialisierung durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von Zusammenführungs-Join-Plänen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von Join-Plänen mit verschachtelten Schleifen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von sequenziellen Prüfungsplänen durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von expliziten Sortierschritten durch den Planer. |
|
Dynamisch | Ermöglicht die Verwendung von TID-Prüfungsplänen durch den Planer. |
|
Dynamisch | Gibt Warnungen zu Escape-Notierungen mit Backslash (\) in gewöhnlichen Zeichenfolgeliteralen aus. |
|
Dynamisch | Legt die Anzahl der Stellen fest, die für Gleitkommawerte angezeigt werden. |
|
Dynamisch | Legt die Größe der FROM-Liste fest, jenseits der Unterabfragen nicht ausgeblendet werden. |
|
Dynamisch | Erzwingt die Synchronisierung von Aktualisierungen zum Datenträger. |
|
Dynamisch | Schreibt bei der ersten Änderung nach einem Prüfpunkt vollständige Seiten zu WAL. |
|
Dynamisch | Ermöglicht die genetische Abfrageoptimierung. |
|
Dynamisch | GEQO: Der Aufwand, der verwendet wird, um den Standard für andere GEQO-Parameter festzulegen. |
|
Dynamisch | GEQO: Die Zahl der Iterationen des Algorithmus. |
|
Dynamisch | GEQO: Die Anzahl der Personen in der Population. |
|
Dynamisch | GEQO: Der Seed für die zufällige Pfadauswahl. |
|
Dynamisch | GEQO: Selektiver Druck innerhalb der Population. |
|
Dynamisch | Legt den Schwellenwert für FROM-Elemente fest, jenseits derer GEQO verwendet wird. |
|
Dynamisch | Legt das maximal zulässige Ergebnis für die exakte Suche durch GIN fest. |
|
Dynamisch | Legt fest, ob ein Hot Standby Rückmeldungen an den primären oder Upstream Standby sendet. |
|
Dynamisch | Legt das Anzeigeformat für Intervallwerte fest. |
|
Dynamisch | Legt die Größe der FROM-Liste fest, jenseits der JOIN-Konstrukte nicht auf eine Ebene gebracht werden. |
|
Dynamisch | Legt die Sprache fest, in der Nachrichten angezeigt werden. |
|
Dynamisch | Legt das Gebietsschema für die Formatierung von monetären Beträgen fest. |
|
Dynamisch | Legt das Gebietsschema für die Formatierung von Zahlen fest. |
|
Dynamisch | Legt das Gebietsschema für die Formatierung von Datum- und Uhrzeitwerten fest. |
|
Dynamisch | Legt die Mindestausführungszeit fest, ab der Aktionen für die Selbstbereinigung protokolliert werden. |
|
Dynamisch | Protokolliert jeden Prüfpunkt. |
|
Dynamisch | Protokolliert jede erfolgreiche Verbindung. |
|
Dynamisch | Protokolliert das Ende einer Sitzung einschließlich der Dauer. |
|
Dynamisch | Protokolliert die Dauer jeder abgeschlossenen SQL-Anweisung. |
|
Dynamisch | Legt die Ausführlichkeit protokollierter Nachrichten fest. |
|
Dynamisch | Schreibt die Leistungsstatistik des Executors in das Serverprotokoll. |
|
Dynamisch | Legt das Dateinamenmuster für Protokolldateien fest. |
|
Dynamisch | Protokolliert den Hostnamen in den Verbindungsprotokollen. |
|
Dynamisch | Protokolliert lange Sperrenwartezeiten. |
|
Dynamisch | Legt die Mindestausführungszeit fest, ab der Anweisungen protokolliert werden. |
|
Dynamisch | Veranlasst, dass alle Anweisungen, die einen Fehler auf oder jenseits dieser Stufe generieren, protokolliert werden. |
|
Dynamisch | Legt die Nachrichtenebenen fest, die protokolliert werden. |
|
Dynamisch | Schreibt die Leistungsstatistik des Parsers in das Serverprotokoll. |
|
Dynamisch | Schreibt die Leistungsstatistik des Planers in das Serverprotokoll. |
|
Dynamisch | Die automatische Protokolldateirotation wird nach N Minuten ausgeführt. |
|
Dynamisch | Die automatische Protokolldateirotation wird nach N Kilobytes ausgeführt. |
|
Dynamisch | Legt den Typ der protokollierten Anweisungen fest. |
|
Dynamisch | Schreibt kumulative Leistungsstatistiken in das Serverprotokoll. |
|
Dynamisch | Protokolliert die Verwendung temporärer Dateien, die größer als diese Zahl von Kilobytes sind. |
|
Dynamisch | Legt den maximalen Arbeitsspeicher fest, der für Wartungsoperationen verwendet werden darf. |
|
Dynamisch | Legt die maximale Stack-Tiefe in Kilobytes fest. |
|
Dynamisch | Legt die maximale Verzögerung fest, bevor Abfragen storniert werden, wenn ein Hot Standby-Server archivierte WAL-Daten verarbeitet. |
|
Dynamisch | Legt die maximale Verzögerung fest, bevor Abfragen storniert werden, wenn ein Hot Standby-Server gestreamte WAL-Daten verarbeitet. |
max_wal_size |
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die WAL-Größe fest, die den Prüfpunkt auslöst. Für PostgreSQL-Version 9.6 und
früher liegt max_wal_size in Einheiten von 16 MB vor. Für PostgreSQL-Version 10 und höher liegt max_wal_size in Einheiten von 1 MB vor.
|
min_wal_size |
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die Mindestgröße fest, auf die das WAL verkleinert werden soll. Für PostgreSQ-Version
9.6 und früher liegt min_wal_size in Einheiten von 16 MB vor. Für PostgreSQ-Version 10 und höher liegt min_wal_size in Einheiten von 1 MB vor.
|
|
Dynamisch | Fügt beim Generieren von SQL-Fragmenten allen Bezeichnern Anführungszeichen (") hinzu. |
|
Dynamisch | Legt die Schätzung des Planers für die Kosten einer nicht sequenziell abgerufenen Datenträgerseite fest. |
rds.adaptive_autovacuum |
Dynamisch | Optimiert die Selbstbereinigungsparameter automatisch, wenn die Transaktions-ID-Schwellenwerte überschritten werden. |
|
Dynamisch | Legt die Protokollaufbewahrung so fest, dass Amazon RDS PostgreSQL-Protokolle löscht, die älter als N Minuten sind. |
rds.restrict_password_commands |
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Schränkt ein, wer Passwörter für Benutzer mit der Rolle rds_password verwalten darf. Setzen Sie diesen Parameter auf 1, um die Passwortbeschränkung zu
aktivieren. Der Standardwert ist 0.
|
|
Dynamisch | Legt die Schemasuchreihenfolge für Namen fest, die nicht schemaqualifiziert sind. |
|
Dynamisch | Legt die Schätzung des Planers für die Kosten einer sequenziell abgerufenen Datenträgerseite fest. |
|
Dynamisch | Legt das Sitzungsverhalten für Auslöser und Neuschreibungsregeln fest. |
|
Dynamisch | Veranlasst den standardmäßigen Einschluss von Untertabellen in verschiedene Befehle. |
|
Dynamisch | Legt die Menge des Datenverkehrs fest, der gesendet und empfangen werden soll, bevor die Verschlüsselungsschlüssel neu verhandelt werden. |
|
Dynamisch | Veranlasst Zeichenfolgen „...“, Backslashes als Zeichen zu behandeln. |
|
Dynamisch | Legt die maximal zulässige Dauer von Anweisungen fest. |
|
Dynamisch | Ermöglicht synchronisierte sequenzielle Prüfungen. |
|
Dynamisch | Legt die Synchronisierungsstufe aktueller Transaktionen fest. |
|
Dynamisch | Maximale Anzahl von TCP-Keepalive-Neuübertragungen. |
|
Dynamisch | Zeit zwischen der Ausgabe von TCP-Keepalives. |
|
Dynamisch | Zeit zwischen der Ausgabe von TCP-Keepalive-Neuübertragungen. |
|
Dynamisch | Legt die maximale Anzahl der temporären Puffer fest, die von den einzelnen Sitzungen verwendet werden. |
|
Dynamisch | Legt die Tabellenräume fest, die für temporäre Tabellen und Sortierdateien verwendet werden sollen. |
|
Dynamisch | Legt die Zeitzone für die Anzeige und Interpretation von Zeitstempeln fest. |
|
Dynamisch | Sammelt Informationen zu Befehlen, die ausgeführt werden. |
|
Dynamisch | Sammelt Statistiken zur Datenbankaktivität. |
|
Dynamisch | Sammelt Statistiken auf Funktionsebene zur Datenbankaktivität. |
|
Dynamisch | Sammelt Zeitpunktstatistiken zur Datenbank-E/A-Aktivität. |
|
Dynamisch | Gibt an, ob eine schreibgeschützte serialisierbare Transaktion aufgeschoben werden kann, bis sie ohne mögliche Serialisierungsfehler gestartet werden kann. |
|
Dynamisch | Legt die Isolierungsstufe aktueller Transaktionen fest. |
|
Dynamisch | Legt den Schreibschutzstatus aktueller Transaktionen fest. |
|
Dynamisch | Behandelt expr=NULL als expr IS NULL. |
|
Dynamisch | Aktualisiert den Titel des Vorgangs, um den aktiven SQL-Befehl anzuzeigen. |
|
Dynamisch | Bereinigungskostenverzögerung (in Millisekunden). |
|
Dynamisch | Bereinigungskostenbetrag, der vor der Inaktivität verfügbar ist. |
|
Dynamisch | Bereinigungskosten für eine Seite, die durch eine Bereinigung ungültig wurde. |
|
Dynamisch | Bereinigungskosten für eine Seite, die im Puffer-Cache gefunden wurde. |
|
Dynamisch | Bereinigungskosten für eine Seite, die nicht im Puffer-Cache gefunden wurde. |
|
Dynamisch | Anzahl der Transaktionen, um die Bereinigung und Hot Cleanup aufgeschoben werden sollen, wenn vorhanden. |
|
Dynamisch | Mindestalter, bei dem die Bereinigung eine Tabellenzeile eingefroren werden sollte. |
|
Dynamisch | Alter, bei dem die Bereinigung eine Tabelle vollständig scannen sollte, um Tupel einzufrieren. |
|
Dynamisch | Inaktivitätszeit des WAL-Schreibers zwischen WAL-Flushes. |
|
Dynamisch | Legt den maximalen Arbeitsspeicher fest, der für Abfrage-Workspaces verwendet werden darf. |
|
Dynamisch | Legt die Kodierung von Binärwerten in XML fest. |
|
Dynamisch | Legt fest, ob XML-Daten in impliziten Parsing- und Serialisierungsoperationen als Dokumente oder Inhaltsfragmente betrachtet werden sollen. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Alter, bei dem eine Selbstbereinigung für eine Tabelle ausgeführt werden soll, um einen Transaktions-ID-Wraparound zu verhindern. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl gleichzeitig ausgeführter Worker-Vorgänge für die Selbstbereinigung fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl gleichzeitiger Verbindungen fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl gleichzeitig geöffneter Dateien für die einzelnen Serverprozesse fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl von Sperren pro Transaktion fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl von Prädikatssperren pro Transaktion fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl gleichzeitig vorbereiteter Transaktionen fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Anzahl freigegebener Arbeitsspeicherpuffer fest, die vom Server verwendet werden. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Ermöglicht SSL-Verbindungen. |
temp_file_limit |
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die maximale Größe in KB fest, temporären Dateien maximal annehmen können. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die für pg_stat_activity.current_query reservierte Größe in Bytes fest. |
|
Um eine statische IP-Adresse zu verwenden, tippen Sie auf | Legt die Anzahl von Datenträgerseitenpuffern im freigegebenen Arbeitsspeicher für WAL fest. |
Amazon RDS verwendet die PostgreSQL-Standardeinheiten für alle Parameter. Die folgende Tabelle zeigt die PostgreSQL-Standardeinheit und den Wert für die einzelnen Parameter.
Parametername |
Einheit |
---|---|
|
8 KB |
|
8 KB |
|
8 KB |
|
8 KB |
|
8 KB |
|
8 KB |
|
KB |
|
KB |
|
KB |
|
KB |
|
KB |
temp_file_limit |
KB |
|
KB |
|
Minuten |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
ms |
|
S |
|
S |
|
S |
|
S |
|
S |
|
S |
|
S |
|
S |
|
S |
|
S |
Arbeiten mit der PostgreSQL-Selbstbereinigung in Amazon RDS
Die Selbstbereinigungsfunktion für PostgreSQL-Datenbanken ist eine Funktion, deren Verwendung nachdrücklich empfohlen wird, um die Integrität Ihrer PostgreSQL-DB-Instance zu wahren. Die Selbstbereinigung automatisiert den Start der Befehle VACUUM und ANALYZE. Die Selbstbereinigung prüft auf Tabellen mit einer großen Zahl von eingefügten, aktualisierten oder gelöschten Tupeln. Die Selbstbereinigung gewinnt Speicher durch Entfernen von überflüssigen Daten oder Tupeln aus der PostgreSQL-Datenbank.
Die Selbstbereinigung ist für alle neuen Amazon RDS-PostgreSQL-DB-Instances standardmäßig aktiviert. Die zugehörigen Konfigurationsparameter werden standardmäßig entsprechend festgelegt. Da die Standardwerte gewissermaßen generisch sind, erzielen Sie Vorteile, wenn Sie die Parameter für Ihre spezifische Workload optimieren. Im folgenden Abschnitt finden Sie Informationen, die Sie bei der notwendigen Optimierung der Selbstbereinigung unterstützen können.
Themen
- Zuweisen von Arbeitsspeicher für die Selbstbereinigung
- Verringern der Wahrscheinlichkeit von Transaktions-ID-Wraparounds
- Ermittlung, ob die Tabellen in Ihrer Datenbank bereinigt werden müssen
- Ermittlung, für welche Tabellen derzeit eine Selbstbereinigung nötig ist
- Ermittlung, ob die Selbstbereinigung derzeit ausgeführt wird und wie lange sie dauert
- Ausführen einer manuellen Bereinigungseinfrierung
- Neuindizierung einer Tabelle während der Ausführung einer Selbstbereinigung
- Weitere Parameter, die sich auf die Selbstbereinigung auswirken
- Festlegen von Selbstbereinigungsparametern auf Tabellenebene
- Protokollierung der Selbstbereinigung
Zuweisen von Arbeitsspeicher für die Selbstbereinigung
Einer der wichtigsten Parameter, der sich auf die Leistung der Selbstbereinigung auswirkt,
ist der Parameter maintenance_work_mem
maintenance_work_mem
Sie müssen bei der Berechnung des Werts für den Parameter maintenance_work_mem
-
Die Standardeinheit für diesen Parameter ist Kilobyte (KB).
-
Der Parameter
maintenance_work_mem
funktioniert in Verbindung mit dem Parameter autovacuum_max_workers
. Wenn Sie viele kleine Tabellen haben, müssen Sie mehr autovacuum_max_workers
und weniger maintenance_work_mem
zuteilen. Wenn Sie große Tabellen haben (beispielsweise größer als 100 GB), sollten Sie mehr Arbeitsspeicher und weniger Worker-Prozesse zuteilen. Sie müssen genügend Arbeitsspeicher zuweisen, um den Vorgang für Ihre größte Tabelle erfolgreich ausführen zu können. Jeder autovacuum_max_workers
-Parameter kann den von Ihnen zugeteilten Arbeitsspeicher nutzen. Daher muss die Kombination von Worker-Prozessen und Arbeitsspeicher dem gesamten Arbeitsspeicher entsprechen, den Sie zuteilen möchten.
Allgemein ausgedrückt, müssen Sie für große Hosts den Parameter maintenance_work_mem
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
aus.
Verringern der Wahrscheinlichkeit von Transaktions-ID-Wraparounds
In einigen Fällen sind Parametergruppen-Einstellungen, die sich auf die Selbstbereinigung
beziehen, möglicherweise nicht aggressiv genug, um Transaktions-ID-Wraparounds zu
verhindern. Um dieses Problem zu beheben, stellt Amazon RDS for PostgreSQL eine Methode
bereit, mit der die Selbstbereinigungsparameter automatisch angepasst werden. Adaptive Optimierung der Selbstbereinigungsparameter ist eine Funktion für RDS for PostgreSQL. In der PostgreSQL-Dokumentation finden
Sie eine sehr detaillierte Beschreibung von Transaktions-ID-Wraparounds
Die adaptive Optimierung von Selbstbereinigungsparametern ist für RDS PostgreSQL-Instances,
bei denen der Parameter rds.adaptive_autovacuum
auf ON gesetzt ist, automatisch aktiviert. Wir raten dringend dazu, diese Option
aktiviert zu lassen. Um die adaptive Optimierung der Selbstbereinigungsparameter zu
deaktivieren, stellen Sie den Parameter rds.adaptive_autovacuum
jedoch auf 0 oder OFF ein.
Transaktions-ID-Wraparounds können selbst dann noch auftreten, wenn RDS die Selbstbereinigungsparameter
optimiert. Wir raten Ihnen dazu, einen Amazon CloudWatch-Alarm für Transaktions-ID-Wraparounds
zu implementieren. Weitere Informationen finden Sie im Blogbeitrag Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for
PostgreSQL
Wenn die adaptive Optimierung der Selbstbereinigungsparameter aktiviert ist, beginnt
RDS mit dem Anpassen der Selbstbereinigungsparameter, wenn die CloudWatch-Metrik MaximumUsedTransactionIDs
den Wert des autovacuum_freeze_max_age
-Parameters oder 500.000.000 erreicht, je nachdem, welcher Wert größer ist.
RDS fährt mit dem Anpassen der Parameter für die Selbstbereinigung fort, wenn eine Tabelle weiterhin zu Transaktions-ID-Wraparounds tendiert. Jede dieser Anpassungen stellt weitere Ressourcen für die Selbstbereinigung bereit, um Wraparounds zu vermeiden. RDS aktualisiert die folgenden Parameter, die sich auf die Selbstbereinigung beziehen:
RDS ändert diese Parameter nur, wenn der neue Wert die Selbstbereinigung aggressiver
macht. Die Parameter werden im Arbeitsspeicher auf der DB-Instance geändert. Die Werte
in der Parametergruppe werden nicht geändert. Um die aktuellen Arbeitsspeichereinstellungen
anzuzeigen, verwenden Sie den PostgreSQL-SQL-Befehl SHOW
Sobald RDS einen dieser Selbstbereinigungsparameter ändert, wird ein Ereignis für
die betroffene DB-Instance erzeugt, das in der AWS Management Console (https://console.aws.amazon.com/rds/MaximumUsedTransactionIDs
unterschritten wurde, setzt RDS die sich auf die Selbstbereinigung beziehenden Parameter
auf die in der Parametergruppe angegebenen Werte zurück. Auch wird ein weiteres Ereignis
entsprechend dieser Änderung erzeugt.
Ermittlung, ob die Tabellen in Ihrer Datenbank bereinigt werden müssen
Sie können die folgende Abfrage verwenden, um die Anzahl der nicht bereinigten Transaktionen
in einer Datenbank anzuzeigen. Die Spalte datfrozenxid
einer pg_database
-Zeile der Datenbank ist eine Untergrenze für die normalen Transaktions-IDs, die in
dieser Datenbank erscheinen. Diese Spalte ist der Mindestwert der relfrozenxid
-Werte pro Tabelle in der Datenbank.
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;
Beispielsweise könnten die Ergebnisse der Ausführung der oben gezeigten Abfrage wie folgt aussehen.
datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)
Wenn das Alter einer Datenbank 2 Milliarden Transaktions-IDs erreicht, treten Transaktions-ID
(XID)-Wraparounds auf und die Datenbank wird als schreibgeschützt festgelegt. Diese
Abfrage kann verwendet werden, um eine Metrik zu generieren und mehrmals am Tag ausgeführt
werden. Standardmäßig ist die Selbstbereinigung so festgelegt, dass das Alter der
Transaktionen 200 000 000 nicht überschreitet (autovacuum_freeze_max_age
Eine Überwachungsstrategie kann beispielsweise wie folgt aussehen:
-
Stellen Sie den
autovacuum_freeze_max_age
-Wert auf 200 Millionen Transaktionen ein. -
Wenn eine Tabelle 500 Millionen nicht bereinigter Transaktionen erreicht, wird ein Alarm mit niedrigem Schweregrad ausgelöst. Dies ist kein unangemessener Wert. Er könnte jedoch zu erkennen geben, dass die Selbstbereinigung nicht Schritt hält.
-
Wenn eine Tabelle 1 Milliarde nicht bereinigter Transaktionen aufweist, sollte dies als ein Alarm behandelt werden, für den Maßnahmen zu ergreifen sind. Im Allgemeinen sollte aus Leistungsgründen das Alter möglichst nahe zu
autovacuum_freeze_max_age
liegen. Wir empfehlen, dass Sie eine Untersuchung unter Beachtung der folgenden Empfehlungen durchführen. -
Wenn eine Tabelle 1,5 Milliarden nicht bereinigter Transaktionen erreicht, wird ein Alarm mit hohem Schweregrad ausgelöst. Abhängig davon, wie schnell Ihre Datenbank Transaktions-IDs verwendet, kann dieser Alarm zu erkennen geben, dass die Zeit für eine Selbstbereinigung des Systems abläuft. In diesem Fall empfehlen wir, eine unmittelbare Lösung in Betracht zu ziehen.
Wenn eine Tabelle diese Schwellenwerte konstant überschreitet, müssen Sie die Selbstbereinigungsparameter weiter verändern. Standardmäßig ist die manuelle Verwendung von VACUUM (für den kostenbasierte Verzögerungen deaktiviert sind) aggressiver als die Standardselbstbereinigung. Der Befehl hat jedoch insgesamt auch größere Auswirkungen auf das System.
Wir empfehlen Folgendes:
-
Aktivieren Sie einen Überwachungsmechanismus, damit Sie über das Alter der ältesten Transaktionen informiert sind.
Informationen zum Erstellen eines Prozesses, der Sie über Transaktions-ID-Wraparounds benachrichtigt, finden Sie im AWS-Datenbank-Blogbeitrag Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL
. -
Führen Sie für häufiger verwendete Tabellen zusätzlich zur Selbstbereinigung während Wartungsfenstern regelmäßig eine manuelle Bereinigungseinfrierung aus. Informationen zur Ausführung manueller Bereinigungseinfrierungen finden Sie unter Ausführen einer manuellen Bereinigungseinfrierung.
Ermittlung, für welche Tabellen derzeit eine Selbstbereinigung nötig ist
Häufig benötigen eine oder zwei Tabellen eine Bereinigung. Tabellen, deren relfrozenxid
-Wert größer als die Anzahl von Transaktionen in autovacuum_freeze_max_age
ist, sind stets Ziel der Selbstbereinigung. Wenn andernfalls die Anzahl der Tupeln,
die seit dem letzten VACUUM-Befehl veraltet sind, den Bereinigungsschwellenwert überschreitet,
wird die Tabelle bereinigt.
Der Selbstbereinigungsschwellenwert
Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples
Führen Sie während der Herstellung der Verbindung mit Ihrer Datenbank die folgende Abfrage aus, um eine Liste der Tabellen anzuzeigen, für die Selbstbereinigungsfunktion eine Bereinigung als notwendig betrachtet:
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') , fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') , sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation , pg_size_pretty(pg_table_size(c.oid)) as table_size , age(relfrozenxid) as xid_age , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples , n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' and ( age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) or coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC LIMIT 50;
Ermittlung, ob die Selbstbereinigung derzeit ausgeführt wird und wie lange sie dauert
Wenn Sie eine Tabelle manuell bereinigen müssen, müssen Sie ermitteln, ob zurzeit eine Selbstbereinigung ausgeführt wird. Wenn dies der Fall ist, müssen Sie die Parameter anpassen, damit sie effizienter ausgeführt wird, oder die Selbstbereinigung beenden, damit Sie den Befehl VACUUM manuell ausführen können.
Verwenden Sie die folgende Abfrage, um zu ermitteln, ob die Selbstbereinigung ausgeführt wird, wie lange diese bereits dauert und ob diese auf eine andere Sitzung wartet.
Wenn Sie mit Amazon RDS PostgreSQL 9.6 oder höher arbeiten, verwenden Sie folgende Abfrage:
SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
Nach dem Ausführen der Abfrage wird Ihnen eine Ausgabe ähnlich der folgenden angezeigt.
datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +
Bei einer Amazon RDS for PostgreSQL-Version kleiner als 9.6 verwenden Sie die folgende Abfrage.
SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
Nach dem Ausführen der Abfrage wird Ihnen eine Ausgabe ähnlich der folgenden angezeigt.
datname | usename | pid | waiting | xact_runtime | query --------+----------+-------+---------+-------------------------+---------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | f | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | f | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | f | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | f | 00:00:00 | SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query+ | | | | | FROM pg_stat_activity + | | | | | WHERE query like '%VACUUM%' + | | | | | ORDER BY xact_start; +
Verschiedene Probleme können dazu führen, dass eine Selbstbereinigungssitzung über
eine lange Zeit (mehrere Tage) ausgeführt wird. Das häufigste Problem besteht jedoch
darin, dass der Wert Ihres Parameters maintenance_work_mem
Es wird empfohlen, dass Sie die folgende Formel verwenden, um den Wert des Parameters
maintenance_work_mem
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
Selbstbereinigungssitzungen, die nur kurze Zeit ausgeführt werden, können ebenfalls auf Probleme hinweisen:
-
Es kann bedeuten, dass es für Ihre Arbeitslast nicht genügend
autovacuum_max_workers
gibt. In diesem Fall müssen Sie die Anzahl der Worker angeben. -
Es kann bedeuten, dass es eine Indexbeschädigung gibt (die Selbstbereinigung stürzt ab und startet bei derselben Relation neu, es gibt jedoch keine Fortschritte). In diesem Fall müssen Sie eine ausführliche ___table___ für die manuelle Bereinigungseinfrierung ausführen, um den genauen Grund anzuzeigen.
Ausführen einer manuellen Bereinigungseinfrierung
Möglicherweise möchten Sie eine manuelle Bereinigung für eine Tabelle ausführen, für die bereits ein Bereinigungsvorgang ausgeführt wird. Dies ist nützlich, wenn Sie eine Tabelle mit einem Alter identifiziert haben, das 2 Milliarden Transaktionen (oder einen Wert oberhalb eines von Ihnen beobachteten Schwellenwerts) erreicht.
Die folgenden Schritte stellen eine Anleitung dar. Es gibt mehrere mögliche Varianten
für den Prozess. Angenommen, Sie stellen während der Überprüfung fest, dass der Wert
für den Parameter maintenance_work_mem
maintenance_work_mem
So führen Sie manuell eine Bereinigungseinfrierung aus
-
Öffnen Sie zwei Sitzungen für die Datenbank, die Tabelle enthält, die Sie bereinigen möchten. Verwenden Sie für die zweite Sitzung „screen“ oder ein anderes Hilfsprogramm, das die Sitzung beibehält, wenn die Verbindung verloren geht.
-
Rufen Sie in der ersten Sitzung die PID der Selbstbereinigungssitzung ab, die für die Tabelle ausgeführt wird.
Führen Sie die folgende Abfrage aus, um die PID der Selbstbereinigungssitzung abzurufen.
SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
-
Berechnen Sie in der zweiten Sitzung die Menge des Arbeitsspeichers, den Sie für diese Operation benötigen. In diesem Beispiel ermitteln Sie, dass für diese Operation bis zu 2 GB Arbeitsspeicher verwendet werden können. Daher wird für die aktuelle Sitzung
maintenance_work_mem
auf 2 GB festgelegt. set maintenance_work_mem='2 GB'; SET
-
Geben Sie in der zweiten Sitzung einen
vacuum freeze verbose
-Befehl für die Tabelle aus. Die Verbose-Einstellung ist nützlich, da Sie Aktivität sehen können, auch wenn in PostgreSQL hierfür zurzeit kein Fortschrittsbericht verfügbar ist.\timing on Timing is on. vacuum freeze verbose pgbench_branches;
INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms
-
Wenn die Selbstbereinigung gesperrt war, wird Ihnen in der ersten Sitzung in
pg_stat_activity
angezeigt, dass das Warten für Ihre Bereinigungssitzung "T" ist. In diesem Fall müssen Sie den Selbstbereinigungsvorgang wie folgt beenden.SELECT pg_terminate_backend('the_pid');
-
An diesem Punkt beginnt Ihre Sitzung. Es ist wichtig, zu beachten, dass die Selbstbereinigung sofort neu gestartet wird, da diese Tabelle in der Liste der Aufgaben wahrscheinlich ganz oben steht. Initiieren Sie Ihren
vacuum freeze verbose
-Befehl in Sitzung 2 und beenden Sie dann den Selbstbereinigungsvorgang in Sitzung 1.
Neuindizierung einer Tabelle während der Ausführung einer Selbstbereinigung
Wenn ein Index beschädigt wurde, verarbeitet die Selbstbereinigung die Tabelle weiter und schlägt fehl. Wenn Sie in dieser Situation eine manuelle Bereinigung versuchen, erhalten Sie eine Fehlermeldung ähnlich der folgenden:
mydb=# vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.
Wenn der Index beschädigt ist und eine Selbstbereinigung für die Tabelle versucht
wird, konkurrieren Sie mit einer Selbstbereinigungssitzung, die bereits ausgeführt
wird. Wenn Sie einen "REINDEX
So führen Sie eine Neuindizierung für eine Tabelle aus, wenn eine Selbstbereinigung für die Tabelle ausgeführt wird
-
Öffnen Sie zwei Sitzungen für die Datenbank, die Tabelle enthält, die Sie bereinigen möchten. Verwenden Sie für die zweite Sitzung „screen“ oder ein anderes Hilfsprogramm, das die Sitzung beibehält, wenn die Verbindung verloren geht.
-
Rufen Sie in der ersten Sitzung die PID der Selbstbereinigungssitzung ab, die für die Tabelle ausgeführt wird.
Führen Sie die folgende Abfrage aus, um die PID der Selbstbereinigungssitzung abzurufen.
SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;
-
Führen Sie in der zweiten Sitzung den Neuindizierungsbefehl aus.
\timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms
-
Wenn die Selbstbereinigung gesperrt war, wird Ihnen in der ersten Sitzung in
pg_stat_activity
angezeigt, dass das Warten für Ihre Bereinigungssitzung "T" ist. In diesem Fall müssen Sie den Selbstbereinigungsvorgang beenden.select pg_terminate_backend('the_pid');
-
An diesem Punkt beginnt Ihre Sitzung. Es ist wichtig, zu beachten, dass die Selbstbereinigung sofort neu gestartet wird, da diese Tabelle in der Liste der Aufgaben wahrscheinlich ganz oben steht. Initiieren Sie Ihren Befehl in Sitzung 2 und beenden Sie dann den Selbstbereinigungsvorgang in Sitzung 1.
Weitere Parameter, die sich auf die Selbstbereinigung auswirken
Die folgende Abfrage zeigt die Werte einiger Parameter an, die sich direkt auf die
Selbstbereinigung und ihr Verhalten auswirken. Die Selbstbereinigungsparameter
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');
All diese Parameter wirken sich auf die Selbstbereinigung aus. Die wichtigsten unter ihnen sind jedoch:
Festlegen von Selbstbereinigungsparametern auf Tabellenebene
Speicherparameter
Die folgende Abfrage zeigt, für welche Tabellen zurzeit Optionen auf Tabellenebene festgelegt wurden.
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;
Ein Beispiel, in dem dies nützlich sein kann, sind Tabellen, die sehr viel größer als Ihre restlichen Tabellen sind. Angenommen, es gibt eine Tabelle mit 300 GB und 30 weitere Tabellen mit weniger als 1 GB. In diesem Fall würde es sich anbieten, einige spezifische Parameter nur für die große Tabelle festzulegen, um nicht das Verhalten des gesamten Systems zu ändern.
ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);
Hierdurch wird die kostenbasierte Selbstbereinigungsverzögerung für diese Tabelle
auf Kosten einer größeren Ressourcennutzung in Ihrem System deaktiviert. Normalerweise
wird die Selbstbereinigung jedes Mal für autovacuum_vacuum_cost_delay unterbrochen,
wenn autovacuum_cost_limit erreicht wird. Weitere Details finden Sie in der PostgreSQL-Dokumentation
zum Thema kostenbasierte Bereinigung
Protokollierung der Selbstbereinigung
Standardmäßig enthält postgresql.log keine Informationen zum Selbstbereinigungsvorgang. Sie können im PostgreSQL-Fehlerprotokoll
die Ausgabe der Worker-Operationen der Selbstbereinigung anzeigen, indem Sie den Parameter
rds.force_autovacuum_logging_level
festlegen. Zulässige Werte sind disabled, debug5, debug4, debug3, debug2, debug1, info, notice,
warning, error, log, fatal,
und panic
. Der Standardwert ist disabled
, da die übrigen zulässigen Werte Ihren Protokollen eine erhebliche Menge an Informationen
hinzufügen können.
Es wird empfohlen, den Wert des Parameters rds.force_autovacuum_logging_level
auf log
festzulegen und den Parameter log_autovacuum_min_duration
auf einen Wert zwischen 1.000 oder 5.000 Millisekunden festzulegen. Wenn Sie diesen
Wert auf 5.000 festlegen, schreibt Amazon RDS Aktivitäten in das Protokoll, die länger
als fünf Sekunden dauern. Es wird auch die Meldung "vacuum skipped (Bereinigung übersprungen)"
angezeigt, wenn die Anwendungssperrung dazu führt, dass die Selbstbereinigung Tabellen
absichtlich überspringt. Wenn Sie ein Problem beheben möchten und weitere Details
benötigen, können Sie einen anderen Protokollierungsstufenwert verwenden, beispielsweise
debug1
oder debug3
. Sie sollten diese Debug-Parameter nur über einen kurzen Zeitraum verwenden, da diese
Einstellungen dazu führen, dass extrem ausführliche Inhalte in die Fehlerprotokolldatei
geschrieben werden. Weitere Informationen zu diesen Debug-Einstellungen finden Sie
in der PostgreSQL-Dokumentation
PostgreSQL ermöglicht es dem Konto rds_superuser
, Autovakuum-Sitzungen in pg_stat_activity
anzuzeigen. Sie können beispielsweise eine Selbstbereinigungssitzung identifizieren
und beenden, die die Ausführung eines Befehls blockiert oder langsamer als ein manuell
ausgegebener Bereinigungsbefehl ausgeführt wird.
Auditprotokollierung für eine PostgreSQL-DB-Instance
Es gibt verschiedene Parameter, die Sie einrichten können, um Aktivitäten in Ihrer PostgreSQL-DB-Instance zu protokollieren. Zu diesen Parametern gehören unter anderem Folgende:
-
Der Parameter
log_statement
kann verwendet werden, um in Ihrer PostgreSQL-Datenbank Benutzeraktivitäten zu protokollieren. Weitere Informationen finden Sie unter PostgreSQL-Datenbankprotokolldateien. -
Der Parameter
rds.force_admin_logging_level
protokolliert Aktionen durch den internen RDS-Benutzer (rdsadmin) in den Datenbanken in der DB-Instance und schreibt die Ausgabe in das PostgreSQL-Fehlerprotokoll. Zulässige Werte sind disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal und panic. Der Standardwert ist disabled. -
Der Parameter
rds.force_autovacuum_logging_level
protokolliert Worker-Operationen für die Selbstbereinigung in allen Datenbanken in der DB-Instance und schreibt die Ausgabe zum PostgreSQL-Fehlerprotokoll. Zulässige Werte sind disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal und panic. Der Standardwert ist disabled. Die empfohlene Amazon RDS-Einstellung für rds.force_autovacuum_logging_level: ist LOG. Legen Sie log_autovacuum_min_duration auf einen Wert zwischen 1000 und 5000 fest. Wenn Sie diesen Wert auf 5.000 einstellen, werden Aktivitäten in das Protokoll geschrieben, die länger als 5 Sekunden dauern, und die Meldung "vacuum skipped (Bereinigung übersprungen)" wird angezeigt. Weitere Informationen zu diesem Parameter finden Sie unter Bewährte Methoden für die Arbeit mit PostgreSQL.
Arbeiten mit der pgaudit-Erweiterung
Die Erweiterung pgaudit
stellt eine detaillierte Sitzungs- und Objektauditprotokollierung für Amazon RDS
für PostgreSQL 9.6.3 und höher und 9.5.7 und höher bereit. Sie können mittels dieser
Erweiterung die Sitzungs- oder Objektauditierung aktivieren.
Mithilfe der Sitzungsauditierung können Sie Auditereignisse aus verschiedenen Quellen protokollieren und den vollständig qualifizierten Befehlstext einschließen, wenn verfügbar. Sie können die Sitzungsauditierung beispielsweise verwenden, um alle READ-Anweisungen zu protokollieren, die mit einer Datenbank verknüpft sind, indem Sie pgaudit.log auf READ festlegen.
Mithilfe der Sitzungsauditierung können Sie die Auditprotokollierung so anpassen, dass sie mit spezifischen Befehlen funktioniert. Sie können beispielsweise angeben, dass die Auditprotokollierung für READ-Operationen für eine spezifische Anzahl von Tabellen ausgeführt wird.
So verwenden Sie die objektbasierte Protokollierung mit der Erweiterung pgaudit
-
Erstellen Sie eine spezifische Datenbankrolle namens
rds_pgaudit
. Verwenden Sie den folgenden Befehl, um die Rolle zu erstellen.CREATE ROLE rds_pgaudit; CREATE ROLE
-
Ändern Sie die Parametergruppe, die mit Ihrer DB-Instance verknüpft ist, sodass die freigegebenen vorab geladenen Bibliotheken verwendet werden, die
pgaudit
enthalten, und legen Sie den Parameterpgaudit.role
fest. Der Parameterpgaudit.role
muss auf die Rollerds_pgaudit
festgelegt werden.Der folgende Befehl ändert eine benutzerdefinierte Parametergruppe.
aws rds modify-db-parameter-group --db-parameter-group-name rds-parameter-group-96 --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" --region us-west-2
-
Starten Sie die Instance neu, sodass die DB-Instance die Änderungen für die Parametergruppe übernimmt. Der folgende Befehl startet eine DB-Instance neu.
aws rds reboot-db-instance --db-instance-identifier rds-test-instance --region us-west-2
-
Führen Sie den folgenden Befehl aus, um zu bestätigen, dass
pgaudit
initialisiert wurde.show shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
-
Führen Sie den folgenden Befehl aus, um die Erweiterung
pgaudit
zu erstellen.CREATE EXTENSION pgaudit; CREATE EXTENSION
-
Führen Sie den folgenden Befehl aus, um zu bestätigen, dass
pgaudit.role
auf rds_pgaudit festgelegt ist.show pgaudit.role; pgaudit.role ------------------ rds_pgaudit
Um die Auditprotokollierung zu testen, führen Sie verschiedene Befehle aus, die Sie auditieren möchten. Sie könnten beispielsweise die folgenden Befehle ausführen.
CREATE TABLE t1 (id int); CREATE TABLE GRANT SELECT ON t1 TO rds_pgaudit; GRANT select * from t1; id ---- (0 rows)
Die Datenbankprotokolle sollten dann einen Eintrag ähnlich dem folgenden enthalten.
... 2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1; ...
Weitere Informationen zur Anzeige der Protokolle finden Sie unter Amazon RDS-Datenbank-Protokolldateien.
Arbeiten mit der pg_repack-Erweiterung
Sie können die Erweiterung pg_repack
verwenden, um überflüssige Daten aus Tabellen und Indizes zu entfernen. Diese Erweiterung
wird in Amazon RDS für PostgreSQL 9.6.3 und höher unterstützt. Weitere Informationen
zur Erweiterung pg_repack
finden Sie in der GitHub-Projektdokumentation
Verwenden der pg-repack-Erweiterung
-
Installieren Sie die Erweiterung
pg_repack
für die PostgreSQL-DB-Instance in Amazon RDS, indem Sie den folgenden Befehl ausführen.CREATE EXTENSION pg_repack;
-
Verwenden Sie das Client-Hilfsprogramm pg_repack, um eine Verbindung zu einer Datenbank herzustellen. Verwenden Sie eine Datenbankrolle mit rds_superuser-Rechten, um die Verbindung zur Datenbank herzustellen. Im folgenden Beispiel für eine Verbindung besitzt die Rolle rds_test rds_superuser-Rechte und der verwendete Datenbankendpunkt ist rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com.
pg_repack -h rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com -U rds_test -k postgres
Stellen Sie die Verbindung mittels der Option -k her. Die Option -a wird nicht unterstützt.
-
Die Antwort des pg_repack-Client stellt Informationen zu den Tabellen in der DB-Instance bereit, die neu verpackt werden.
INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"
Arbeiten mit PostGIS
PostGIS ist eine Erweiterung von PostgreSQL zur Speicherung und Verwaltung von Geodaten.
Wenn Sie nicht mit PostGIS nicht vertraut, informieren Sie sich unter PostGIS.net
Sie müssen einige Einrichtungsschritte ausführen, bevor Sie die PostGIS-Erweiterung verwenden können. Die folgende Liste beschreibt, was zu tun ist. Die einzelnen Schritte werden an späterer Stelle in diesem Abschnitt detaillierter beschrieben.
-
Stellen Sie unter Verwendung des Masterbenutzernamens, der für die Erstellung der DB-Instance verwendet wurde, eine Verbindung mit der DB-Instance her.
-
Laden Sie die PostGIS-Erweiterungen.
-
Übertragen Sie den Besitz der Erweiterungen auf die Rolle
rds_superuser
. -
Übertragen Sie den Besitz der Objekte auf die Rolle
rds_superuser
. -
Testen Sie die Erweiterungen.
Schritt 1: Herstellen einer Verbindung mit der DB-Instance unter Verwendung des Masterbenutzernamens, der für die Erstellung der DB-Instance verwendet wurde.
Zunächst stellen Sie unter Verwendung des Masterbenutzernamens, der für die Erstellung
der DB-Instance verwendet wurde, eine Verbindung mit der DB-Instance her. Dieser Name
wird der Rolle rds_superuser
automatisch zugewiesen. Sie benötigen die Rolle rds_superuser
, um die verbleibenden Schritte auszuführen.
Im folgenden Beispiel wird SELECT verwendet, um den aktuellen Benutzer anzuzeigen. In diesem Fall sollte der aktuelle Benutzer der Masterbenutzername sein, den Sie bei der Erstellung der DB-Instance gewählt haben.
select current_user; current_user ------------- myawsuser (1 row)
Schritt 2: Laden der PostGIS-Erweiterungen.
Verwenden Sie die CREATE EXTENSION-Anweisungen, um die PostGIS-Erweiterungen zu laden.
Sie müssen auch die Erweiterung laden. Anschließend verwenden Sie den Befehl
\dn
psql, um die Besitzer des PostGIS-Schemas aufzulisten.
create extension postgis; CREATE EXTENSION create extension fuzzystrmatch; CREATE EXTENSION create extension postgis_tiger_geocoder; CREATE EXTENSION create extension postgis_topology; CREATE EXTENSION \dn List of schemas Name | Owner --------------+----------- public | myawsuser tiger | rdsadmin tiger_data | rdsadmin topology | rdsadmin (4 rows)
Schritt 3: Übertragen der Eigentümerschaft an den Erweiterungen auf die Rolle rds_superuser
Verwenden Sie die ALTER SCHEMA-Anweisungen, um den Besitz der Schemas auf die Rolle
rds_superuser
zu übertragen.
alter schema tiger owner to rds_superuser; ALTER SCHEMA alter schema tiger_data owner to rds_superuser; ALTER SCHEMA alter schema topology owner to rds_superuser; ALTER SCHEMA \dn List of schemas Name | Owner --------------+--------------- public | myawsuser tiger | rds_superuser tiger_data | rds_superuser topology | rds_superuser (4 rows)
Schritt 4: Übertragen der Eigentümerschaft an den Objekten auf die Rolle rds_superuser
Verwenden Sie die folgende Funktion, um den Besitz der PostGIS-Objekte auf die Rolle
rds_superuser
zu übertragen. Führen Sie die folgende Anweisung aus der psql-Aufforderung aus, um
die Funktion zu erstellen.
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
Führen Sie als Nächstes diese Abfrage aus, um die exec-Funktion auszuführen, die wiederum die Anweisungen ausführt und die Berechtigungen ändert.
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;') FROM ( SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE nspname in ('tiger','topology') AND relkind IN ('r','S','v') ORDER BY relkind = 'S') s;
Schritt 5: Testen der Erweiterungen
Fügen Sie Ihrem Suchpfad tiger
unter Verwendung des folgenden Befehls hinzu.
SET search_path=public,tiger;
Testen Sie tiger
unter Verwendung der folgenden SELECT-Anweisung.
select na.address, na.streetname, na.streettypeabbrev, na.zip from normalize_address('1 Devonshire Place, Boston, MA 02109') as na; address | streetname | streettypeabbrev | zip ---------+------------+------------------+------- 1 | Devonshire | Pl | 02109 (1 row)
Testen Sie topology
unter Verwendung der folgenden SELECT-Anweisung.
select topology.createtopology('my_new_topo',26986,0.5); createtopology ---------------- 1 (1 row)
Verwenden von pgBadger für die Protokollanalyse mit PostgreSQL
Sie können einen Protokollanalysierer wie pgbadger
Beispielsweise formatiert der folgende Befehl eine Amazon RDS-PostgreSQL-Protokolldatei mit dem Datum 2014-02-04 korrekt unter Verwendung von pgbadger.
./pgbadger -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00
Anzeigen des Inhalts von pg_config
In PostgreSQL 9.6.1 können Sie die Konfigurationsparameter zur Kompilationszeit der aktuell installierten Version von PostgreSQL mithilfe der neuen Ansicht pg_config anzeigen. Sie können die Ansicht verwenden, indem Sie die Funktion pg_config wie im folgenden Beispiel gezeigt aufrufen.
select * from pg_config(); name | setting -------------------+--------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- BINDIR | /rdsdbbin/postgres-9.6.1.R1/bin DOCDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc HTMLDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc INCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include PKGINCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.1.R1/include/server LIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib PKGLIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib LOCALEDIR | /rdsdbbin/postgres-9.6.1.R1/share/locale MANDIR | /rdsdbbin/postgres-9.6.1.R1/share/man SHAREDIR | /rdsdbbin/postgres-9.6.1.R1/share SYSCONFDIR | /rdsdbbin/postgres-9.6.1.R1/etc PGXS | /rdsdbbin/postgres-9.6.1.R1/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE | '--prefix=/rdsdbbin/postgres-9.6.1.R1' '--with-openssl' '--with-perl' '--with-tcl' '--with-ossp-uuid' '--with-libxml' '--with-libraries=/rdsdbbin /postgres-9.6.1.R1/lib' '--with-includes=/rdsdbbin/postgres-9.6.1.R1/include' '--enable-debug' CC | gcc CPPFLAGS | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.1.R1/include CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict- aliasing -fwrapv -fexcess-precision=standard -g -O2 CFLAGS_SL | -fpic LDFLAGS | -L../../src/common -L/rdsdbbin/postgres-9.6.1.R1/lib -Wl,--as-needed -Wl, -rpath,'/rdsdbbin/postgres-9.6.1.R1/lib',--enable-new-dtags LDFLAGS_EX | LDFLAGS_SL | LIBS | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm VERSION | PostgreSQL 9.6.1 (23 rows)
Wenn Sie versuchen, die Ansicht direkt aufzurufen, schlägt die Anfrage fehl.
select * from pg_config; ERROR: permission denied for relation pg_config
Arbeiten mit der orafce-Erweiterung
Die orafce
-Erweiterung beinhaltet Funktionen, die in kommerziellen Datenbanken üblich sind und
mit denen es einfacher ist, eine kommerzielle Datenbank in PostgreSQL zu portieren.
Diese Erweiterung wird von Amazon RDS für PostgreSQL-Version 9.6.6 und höher unterstützt.
Weitere Informationen zu orafce
finden Sie im orafce-Projekt auf GitHub
Amazon RDS für PostgreSQL unterstützt das utl_file
-Paket nicht, das Teil der orafce
-Erweiterung ist. Dies liegt daran, dass die utl_file
-Schema-Funktionen Lese- und Schreiboperationen für Betriebssystem-Textdateien ermöglichen,
wofür ein Superuser-Zugriff auf den zugrundeliegenden Host erforderlich ist.
So verwenden Sie die orafce-Erweiterung
-
Stellen Sie unter Verwendung des Masterbenutzernamens, der für die Erstellung der DB-Instance verwendet wurde, eine Verbindung mit der DB-Instance her.
Anmerkung Wenn Sie
orafce
auf einer anderen Datenbank in derselben Instance aktivieren möchten, verwenden Sie den psql-Befehl/c dbname
, um von der primären Datenbank zu wechseln, nachdem Sie die Verbindung initiiert haben. -
Aktivieren Sie die orafce-Erweiterung mit der Anweisung
CREATE EXTENSION
.CREATE EXTENSION orafce;
-
Übertragen Sie den Besitz der oracle-Schemas mit der Anweisung
ALTER SCHEMA
auf die rds_superuser-Rolle.ALTER SCHEMA oracle OWNER TO rds_superuser;
Anmerkung Mit dem psql-Befehl
\dn
zeigen Sie die Liste der Eigentümer für das oracle-Schema an.
Zugreifen auf externe Daten mit der Erweiterung postgres_fdw
Auf die Daten in einer Tabelle auf einem Remote-Datenbank-Server können Sie mit der
Erweiterung postgres_fdw
Verwenden Sie postgres_fdw wie folgt für den Zugriff auf einen Remote-Datenbank-Server:
-
Installieren Sie die Erweiterung postgres_fdw.
CREATE EXTENSION postgres_fdw;
-
Erstellen Sie einen Fremddaten-Server mit CREATE SERVER.
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
-
Erstellen Sie eine Benutzerzuweisung, um die Rolle zu identifizieren, die auf dem Remote-Server verwendet werden soll.
CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
-
Erstellen Sie eine Tabelle, die der Tabelle auf dem Remote-Server zugewiesen ist.
CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');
Verwenden eines benutzerdefinierten DNS-Servers für ausgehenden Netzwerkzugriff.
Amazon RDS für PostgreSQL unterstützt den ausgehenden Netzwerkzugriff auf Ihre DB-Instances und erlaubt Domain Name Service (DNS)-Auflösung aus einem benutzerdefinierten DNS-Server, der im Besitz des Kunden ist. Sie können nur vollständig geeignete Domänenamen aus Ihrer Amazon RDS-DB-Instance über Ihren benutzerdefinierten DNS-Server auflösen.
Themen
Aktivieren der benutzerdefinierten DNS-Auflösung
Um die DNS-Auflösung in Ihrer Kunden-VPC zu aktivieren, weisen Sie Ihrer RDS-PostgreSQL-Instance
eine benutzerdefinierte DB-Parametergruppe zu, schalten Sie den Parameter rds.custom_dns_resolution
ein, indem Sie ihn auf 1 setzen, und starten Sie dann die DB-Instance neu, damit
die Änderungen durchgeführt werden können.
Deaktivieren der benutzerdefinierten DNS-Auflösung
Um die DNS-Auflösung in Ihrer Kunden-VPC zu deaktivieren, deaktivieren Sie den Parameter
rds.custom_dns_resolution
Ihrer benutzerdefinierten DB-Parametergruppe, indem Sie ihn auf 0 setzen, und starten
Sie dann die DB-Instance neu, damit die Änderungen durchgeführt werden können.
Einrichten eines benutzerdefinierten DNS-Servers
Nachdem Sie Ihren benutzerdefinierten DNS-Namensserver eingerichtet haben, dauert es bis zu 30 Minuten, um die Änderungen an Ihre DB-Instance zu übertragen. Nachdem die Änderungen an Ihre DB-Instance übertragen wurden, wird ausgehender Datenverkehr, der eine DNS-Abfrage tätigen muss, Ihren DNS-Server über Port 53 abrufen.
Wenn Sie keinen benutzerdefinierten DNS-Server einrichten und rds.custom_dns_resolution
auf 1 festgelegt ist, werden Hosts mithilfe einer privaten Route 53-Zone aufgelöst.
Weitere Informationen finden Sie unter Arbeiten mit privat gehosteten Zonen.
So richten Sie einen benutzerdefinierten DNS-Server für Ihre Amazon RDS-PostgreSQL-DB-Instance ein
-
Legen Sie in dem Ihrer VPC beigefügten DHCP-Optionsset die Option
domain-name-servers
für die IP-Adresse Ihres DNS-Namensservers fest. Weitere Informationen finden Sie unter DHCP-Optionssets.Anmerkung Die Option
domain-name-servers
akzeptiert bis zu vier Werte, Ihre Amazon RDS-DB-Instance verwendet jedoch nur den ersten Wert. -
Stellen Sie sicher, dass Ihr DNS-Server die Suchabfragen auflösen kann, einschließlich DNS-Namen, Amazon EC2-private-DNS-Namen und benutzerspezifischen DNS-Namen. Wenn der ausgehende Datenverkehr DNS-Abfragen beinhaltet, die Ihr DNS-Server nicht handhaben kann, müssen für Ihren DNS-Server angemessene DNS-Provider für einen Upstream konfiguriert sein.
-
Konfigurieren Sie Ihren DNS-Server, um User Datagram Protocol (UDP)-Antworten in der Größenordnung von 512 Bytes oder weniger zu erhalten.
-
Konfigurieren Sie Ihren DNS-Server, um Transmission Control Protocol (TCP)-Antworten in der Größenordnung von 1 024 Bytes oder weniger zu erhalten.
-
Konfigurieren Sie Ihren DNS-Server, um eingehenden Datenverkehr aus Ihrer Amazon RDS-DB-Instance über Port 53 zu erlauben. Wenn sich Ihr DNS-Server in einer Amazon VPC befindet, muss die VPC über eine Sicherheitsgruppe verfügen, die eingehende Regeln für das Erlauben von UDP und TCP über Port 53 beinhaltet. Wenn sich Ihr DNS-Server nicht in einer Amazon VPC befindet, muss er geeignete Firewall-Einstellungen besitzen, die eingehenden UDP- und TCP-Datenverkehr auf Port 53 zulassen.
Weitere Informationen finden Sie unter Sicherheitsgruppen für Ihre VPC und unter Hinzufügen und Entfernen von Regeln.
-
Konfigurieren Sie die VPC Ihrer Amazon RDS-DB-Instance, um ausgehenden Datenverkehr über Port 53 zu erlauben. Ihre VPC muss über eine Sicherheitsgruppe mit ausgehenden Regeln verfügen, die UDP- und TCP-Übertragungen über Port 53 erlauben.
Weitere Informationen finden Sie unter Sicherheitsgruppen für Ihre VPC und unter Hinzufügen und Entfernen von Regeln.
-
Der Routing-Pfad zwischen der Amazon RDS-DB-Instance und dem DNS-Server muss korrekt konfiguriert werden, um DNS-Datenverkehr zu erlauben.
Wenn sich die Amazon RDS-DB-Instance und der DNS-Server nicht in der selben VPC befinden, muss zwischen ihnen eine Peer-to-Peer-Verbindung eingerichtet werden. Weitere Informationen finden Sie unter Was ist VPC Peering?
Beschränken der Passwortverwaltung
Sie können die Verwaltung von Datenbankbenutzerpasswörtern auf eine bestimmte Rolle einschränken. Auf diese Weise können Sie die Passwortverwaltung auf Kundenseite besser kontrollieren.
Sie aktivieren die eingeschränkte Passwortverwaltung mit dem statischen Parameter
rds.restrict_password_commands
und verwenden eine Rolle namens rds_password
. Wenn der Parameter rds.restrict_password_commands
auf 1 gesetzt ist, können nur Benutzer, die Mitglieder der Rolle rds_password
sind, bestimmte SQL-Befehle ausführen. Die eingeschränkten SQL-Befehle sind Befehle,
die Datenbankbenutzerpasswörter und die Ablaufzeit des Passworts ändern.
Um die eingeschränkte Passwortverwaltung nutzen zu können, muss Ihre DB-Instance Amazon
RDS für PostgreSQL 10.6 oder höher ausführen. Da der Parameter rds.restrict_password_commands
statisch ist, erfordert das Ändern dieses Parameters einen Neustart der Datenbank.
Wenn für eine Datenbank die eingeschränkte Passwortverwaltung aktiviert ist, wird
beim Versuch, eingeschränkte SQL-Befehle auszuführen, der folgende Fehler angezeigt:
FEHLER: Muss Mitglied von rds_password sein, um Passwörter zu ändern
.
Im Folgenden finden Sie einige Beispiele für SQL-Befehle, die bei aktivierter eingeschränkter Passwortverwaltung eingeschränkt sind.
postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword'; postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword'; postgres=> ALTER ROLE myrole VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole RENAME TO myrole2;
Einige ALTER ROLE
-Befehle, die RENAME TO
enthalten, sind möglicherweise auch eingeschränkt. Sie können eingeschränkt sein,
da das Umbenennen einer PostgreSQL-Rolle mit einem MD5-Passwort das Passwort löscht.
Die Rolle rds_superuser
hat standardmäßig eine Mitgliedschaft für die Rolle rds_password
, was nicht geändert werden kann. Sie können anderen Rollen die Mitgliedschaft für
die Rolle rds_password
mit dem SQL-Befehl GRANT
erteilen. Wir empfehlen, dass Sie eine Mitgliedschaft für rds_password
nur wenigen Rollen zuweisen, die Sie ausschließlich für die Passwortverwaltung verwenden.
Diese Rollen erfordern zum Ändern anderer Rollen das Attribut CREATEROLE
.
Stellen Sie sicher, dass Sie die Passwortanforderungen wie Ablaufdatum und erforderliche
Komplexität auf Kundenseite überprüfen. Wir empfehlen, passwortbedingte Änderungen
mithilfe eines eigenen clientseitigen Dienstprogramms einzuschränken. Dieses Dienstprogramm
sollte über eine Rolle verfügen, die ein Mitglied von rds_password
ist und das Rollenattribut CREATEROLE
hat.