Erstellen von Rollen Verwalten des Zugriffs auf eine PostgreSQL-Datenbank Arbeiten mit PostgreSQL-Parametern Arbeiten mit der PostgreSQL-Selbstbereinigung 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

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 Befehls pg_stat_activity und Beenden ihrer Verbindungen mithilfe der Befehle pg_terminate_backend und pg_cancel_backend.
Erteilen und Widerrufen der Rolle rds_replication für alle Rollen außer der Rolle rds_superuser. Weitere Informationen dazu finden Sie im Abschnitt GRANT der 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 in der PostgreSQL-Dokumentation.

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 in der PostgreSQL-Dokumentation.

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
`application_name`	Dynamisch	Legt den Namen der Anwendung fest, der in Statistiken und Protokollen verwendet werden soll.
`array_nulls`	Dynamisch	Ermöglicht die Eingabe von NULL-Elementen in Arrays.
`authentication_timeout`	Dynamisch	Legt die Zeit fest, die maximal zulässig ist, um die Client-Authentifizierung durchzuführen.
`autovacuum`	Dynamisch	Startet den Untervorgang der Selbstbereinigung.
`autovacuum_analyze_scale_factor`	Dynamisch	Anzahl von Tupel-Einfügungen, -Aktualisierungen oder -Löschungen vor der Analyse als Bruchteil von Reltupeln.
`autovacuum_analyze_threshold`	Dynamisch	Mindestanzahl von Tupel-Einfügungen, -Aktualisierungen oder -Löschungen vor der Analyse.
`autovacuum_naptime`	Dynamisch	Inaktivitätszeit zwischen Selbstbereinigungen.
`autovacuum_vacuum_cost_delay`	Dynamisch	Bereinigungskostenverzögerung (in Millisekunden) für die Selbstbereinigung.
`autovacuum_vacuum_cost_limit`	Dynamisch	Bereinigungskostenbetrag für die Selbstbereinigung, der vor der Inaktivität verfügbar ist.
`autovacuum_vacuum_scale_factor`	Dynamisch	Anzahl von Tupel-Aktualisierungen oder -Löschungen vor der Bereinigung als Bruchteil von Reltupeln.
`autovacuum_vacuum_threshold`	Dynamisch	Mindestanzahl von Tupel-Aktualisierungen oder -Löschungen vor der Bereinigung.
`backslash_quote`	Dynamisch	Legt fest, ob in Zeichenfolgeliteralen ein Backslash (\) zulässig ist.
`bgwriter_delay`	Dynamisch	Inaktivitätszeit des Hintergrundschreibers zwischen Runden.
`bgwriter_lru_maxpages`	Dynamisch	Maximale Anzahl von LRU-Seiten eines Hintergrundschreibers, für die pro Runde ein Flush ausgeführt werden kann.
`bgwriter_lru_multiplier`	Dynamisch	Mehrfaches der durchschnittlichen Puffernutzung, die pro Runde freigegeben werden soll.
`bytea_output`	Dynamisch	Legt das Ausgabeformat für Bytes fest.
`check_function_bodies`	Dynamisch	Überprüft die Funktionstexte während CREATE FUNCTION.
`checkpoint_completion_target`	Dynamisch	Zeit für den Flush ungültiger Puffer während des Prüfpunkts als Bruchteil des Prüfpunktintervalls.
`checkpoint_segments`	Dynamisch	Legt die maximale Entfernung in Protokollsegmenten zwischen automatischen Write-Ahead Log (WAL)-Prüfpunkten fest.
`checkpoint_timeout`	Dynamisch	Legt die maximale Zeit zwischen automatischen WAL-Prüfpunkten fest.
`checkpoint_warning`	Dynamisch	Ermöglicht Warnungen, wenn Prüfpunktsegmente häufiger als hierdurch angegeben gefüllt werden.
`client_encoding`	Dynamisch	Legt die Zeichensatzkodierung des Client fest.
`client_min_messages`	Dynamisch	Legt die Nachrichtenebenen fest, die an den Client gesendet werden.
`commit_delay`	Dynamisch	Legt die Verzögerung (in Mikrosekunden) zwischen dem Transaktions-Commit und dem Flush von WAL zum Datenträger fest.
`commit_siblings`	Dynamisch	Legt die Mindestzahl gleichzeitiger offener Transaktionen fest, bevor eine Commit-Verzögerung ausgeführt wird.
`constraint_exclusion`	Dynamisch	Ermöglicht dem Planer die Verwendung von Einschränkungen, um Abfragen zu optimieren.
`cpu_index_tuple_cost`	Dynamisch	Legt die Schätzung des Planers für die Kosten der Verarbeitung der einzelnen Indexeinträge während einer Indexprüfung fest.
`cpu_operator_cost`	Dynamisch	Legt die Schätzung des Planers für die Kosten der Verarbeitung der einzelnen Operator- oder Funktionsaufrufe fest.
`cpu_tuple_cost`	Dynamisch	Legt die Schätzung des Planers für die Kosten der Verarbeitung der einzelnen Tupeln (Zeilen) fest.
`cursor_tuple_fraction`	Dynamisch	Legt die Schätzung des Planers für den Bruchteil der Zeilen eines Cursors fest, die abgerufen werden.
`datestyle`	Dynamisch	Legt das Anzeigeformat für Datum- und Uhrzeitwerte fest.
`deadlock_timeout`	Dynamisch	Legt die Zeit fest, die während einer Sperre gewartet wird, bevor auf einen Deadlock geprüft wird.
`debug_pretty_print`	Dynamisch	Erstellt Einschübe für Analyse- und Planstrukturanzeigen.
`debug_print_parse`	Dynamisch	Protokolliert die Analysestruktur der einzelnen Abfragen.
`debug_print_plan`	Dynamisch	Protokolliert den Ausführungsplan der einzelnen Abfragen.
`debug_print_rewritten`	Dynamisch	Protokolliert die neu geschriebene Analysestruktur der einzelnen Abfragen.
`default_statistics_target`	Dynamisch	Legt das Standardstatistikziel fest.
`default_tablespace`	Dynamisch	Legt den Standardtabellenraum fest, in dem Tabellen und Indexe erstellt werden.
`default_transaction_deferrable`	Dynamisch	Legt den Standardaufschiebbarkeitsstatus neuer Transaktionen fest.
`default_transaction_isolation`	Dynamisch	Legt die Transaktionsisolierungsstufe jeder neuen Transaktion fest.
`default_transaction_read_only`	Dynamisch	Legt den Standardschreibschutzstatus neuer Transaktionen fest.
`default_with_oids`	Dynamisch	Erstellt neue Tabellen standardmäßig mit OIDs.
`effective_cache_size`	Dynamisch	Legt die Annahme des Planers hinsichtlich der Größe des Datenträger-Caches fest.
`effective_io_concurrency`	Dynamisch	Die Anzahl der gleichzeitigen Anfragen, die durch das Datenträgersubsystem effizient bearbeitet werden können.
`enable_bitmapscan`	Dynamisch	Ermöglicht die Verwendung von Bitmap-Prüfungsplänen durch den Planer.
`enable_hashagg`	Dynamisch	Ermöglicht die Verwendung von Hash-Aggregationsplänen durch den Planer.
`enable_hashjoin`	Dynamisch	Ermöglicht die Verwendung von Hash-Join-Plänen durch den Planer.
`enable_indexscan`	Dynamisch	Ermöglicht die Verwendung von Indexprüfungsplänen durch den Planer.
`enable_material`	Dynamisch	Ermöglicht die Verwendung von Materialisierung durch den Planer.
`enable_mergejoin`	Dynamisch	Ermöglicht die Verwendung von Zusammenführungs-Join-Plänen durch den Planer.
`enable_nestloop`	Dynamisch	Ermöglicht die Verwendung von Join-Plänen mit verschachtelten Schleifen durch den Planer.
`enable_seqscan`	Dynamisch	Ermöglicht die Verwendung von sequenziellen Prüfungsplänen durch den Planer.
`enable_sort`	Dynamisch	Ermöglicht die Verwendung von expliziten Sortierschritten durch den Planer.
`enable_tidscan`	Dynamisch	Ermöglicht die Verwendung von TID-Prüfungsplänen durch den Planer.
`escape_string_warning`	Dynamisch	Gibt Warnungen zu Escape-Notierungen mit Backslash (\) in gewöhnlichen Zeichenfolgeliteralen aus.
`extra_float_digits`	Dynamisch	Legt die Anzahl der Stellen fest, die für Gleitkommawerte angezeigt werden.
`from_collapse_limit`	Dynamisch	Legt die Größe der FROM-Liste fest, jenseits der Unterabfragen nicht ausgeblendet werden.
`fsync`	Dynamisch	Erzwingt die Synchronisierung von Aktualisierungen zum Datenträger.
`full_page_writes`	Dynamisch	Schreibt bei der ersten Änderung nach einem Prüfpunkt vollständige Seiten zu WAL.
`geqo`	Dynamisch	Ermöglicht die genetische Abfrageoptimierung.
`geqo_effort`	Dynamisch	GEQO: Der Aufwand, der verwendet wird, um den Standard für andere GEQO-Parameter festzulegen.
`geqo_generations`	Dynamisch	GEQO: Die Zahl der Iterationen des Algorithmus.
`geqo_pool_size`	Dynamisch	GEQO: Die Anzahl der Personen in der Population.
`geqo_seed`	Dynamisch	GEQO: Der Seed für die zufällige Pfadauswahl.
`geqo_selection_bias`	Dynamisch	GEQO: Selektiver Druck innerhalb der Population.
`geqo_threshold`	Dynamisch	Legt den Schwellenwert für FROM-Elemente fest, jenseits derer GEQO verwendet wird.
`gin_fuzzy_search_limit`	Dynamisch	Legt das maximal zulässige Ergebnis für die exakte Suche durch GIN fest.
`hot_standby_feedback`	Dynamisch	Legt fest, ob ein Hot Standby Rückmeldungen an den primären oder Upstream Standby sendet.
`intervalstyle`	Dynamisch	Legt das Anzeigeformat für Intervallwerte fest.
`join_collapse_limit`	Dynamisch	Legt die Größe der FROM-Liste fest, jenseits der JOIN-Konstrukte nicht auf eine Ebene gebracht werden.
`lc_messages`	Dynamisch	Legt die Sprache fest, in der Nachrichten angezeigt werden.
`lc_monetary`	Dynamisch	Legt das Gebietsschema für die Formatierung von monetären Beträgen fest.
`lc_numeric`	Dynamisch	Legt das Gebietsschema für die Formatierung von Zahlen fest.
`lc_time`	Dynamisch	Legt das Gebietsschema für die Formatierung von Datum- und Uhrzeitwerten fest.
`log_autovacuum_min_duration`	Dynamisch	Legt die Mindestausführungszeit fest, ab der Aktionen für die Selbstbereinigung protokolliert werden.
`log_checkpoints`	Dynamisch	Protokolliert jeden Prüfpunkt.
`log_connections`	Dynamisch	Protokolliert jede erfolgreiche Verbindung.
`log_disconnections`	Dynamisch	Protokolliert das Ende einer Sitzung einschließlich der Dauer.
`log_duration`	Dynamisch	Protokolliert die Dauer jeder abgeschlossenen SQL-Anweisung.
`log_error_verbosity`	Dynamisch	Legt die Ausführlichkeit protokollierter Nachrichten fest.
`log_executor_stats`	Dynamisch	Schreibt die Leistungsstatistik des Executors in das Serverprotokoll.
`log_filename`	Dynamisch	Legt das Dateinamenmuster für Protokolldateien fest.
`log_hostname`	Dynamisch	Protokolliert den Hostnamen in den Verbindungsprotokollen.
`log_lock_waits`	Dynamisch	Protokolliert lange Sperrenwartezeiten.
`log_min_duration_statement`	Dynamisch	Legt die Mindestausführungszeit fest, ab der Anweisungen protokolliert werden.
`log_min_error_statement`	Dynamisch	Veranlasst, dass alle Anweisungen, die einen Fehler auf oder jenseits dieser Stufe generieren, protokolliert werden.
`log_min_messages`	Dynamisch	Legt die Nachrichtenebenen fest, die protokolliert werden.
`log_parser_stats`	Dynamisch	Schreibt die Leistungsstatistik des Parsers in das Serverprotokoll.
`log_planner_stats`	Dynamisch	Schreibt die Leistungsstatistik des Planers in das Serverprotokoll.
`log_rotation_age`	Dynamisch	Die automatische Protokolldateirotation wird nach N Minuten ausgeführt.
`log_rotation_size`	Dynamisch	Die automatische Protokolldateirotation wird nach N Kilobytes ausgeführt.
`log_statement`	Dynamisch	Legt den Typ der protokollierten Anweisungen fest.
`log_statement_stats`	Dynamisch	Schreibt kumulative Leistungsstatistiken in das Serverprotokoll.
`log_temp_files`	Dynamisch	Protokolliert die Verwendung temporärer Dateien, die größer als diese Zahl von Kilobytes sind.
`maintenance_work_mem`	Dynamisch	Legt den maximalen Arbeitsspeicher fest, der für Wartungsoperationen verwendet werden darf.
`max_stack_depth`	Dynamisch	Legt die maximale Stack-Tiefe in Kilobytes fest.
`max_standby_archive_delay`	Dynamisch	Legt die maximale Verzögerung fest, bevor Abfragen storniert werden, wenn ein Hot Standby-Server archivierte WAL-Daten verarbeitet.
`max_standby_streaming_delay`	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.
`quote_all_identifiers`	Dynamisch	Fügt beim Generieren von SQL-Fragmenten allen Bezeichnern Anführungszeichen (") hinzu.
`random_page_cost`	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.
`rds.log_retention_period`	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.
`search_path`	Dynamisch	Legt die Schemasuchreihenfolge für Namen fest, die nicht schemaqualifiziert sind.
`seq_page_cost`	Dynamisch	Legt die Schätzung des Planers für die Kosten einer sequenziell abgerufenen Datenträgerseite fest.
`session_replication_role`	Dynamisch	Legt das Sitzungsverhalten für Auslöser und Neuschreibungsregeln fest.
`sql_inheritance`	Dynamisch	Veranlasst den standardmäßigen Einschluss von Untertabellen in verschiedene Befehle.
`ssl_renegotiation_limit`	Dynamisch	Legt die Menge des Datenverkehrs fest, der gesendet und empfangen werden soll, bevor die Verschlüsselungsschlüssel neu verhandelt werden.
`standard_conforming_strings`	Dynamisch	Veranlasst Zeichenfolgen „...“, Backslashes als Zeichen zu behandeln.
`statement_timeout`	Dynamisch	Legt die maximal zulässige Dauer von Anweisungen fest.
`synchronize_seqscans`	Dynamisch	Ermöglicht synchronisierte sequenzielle Prüfungen.
`synchronous_commit`	Dynamisch	Legt die Synchronisierungsstufe aktueller Transaktionen fest.
`tcp_keepalives_count`	Dynamisch	Maximale Anzahl von TCP-Keepalive-Neuübertragungen.
`tcp_keepalives_idle`	Dynamisch	Zeit zwischen der Ausgabe von TCP-Keepalives.
`tcp_keepalives_interval`	Dynamisch	Zeit zwischen der Ausgabe von TCP-Keepalive-Neuübertragungen.
`temp_buffers`	Dynamisch	Legt die maximale Anzahl der temporären Puffer fest, die von den einzelnen Sitzungen verwendet werden.
`temp_tablespaces`	Dynamisch	Legt die Tabellenräume fest, die für temporäre Tabellen und Sortierdateien verwendet werden sollen.
`timezone`	Dynamisch	Legt die Zeitzone für die Anzeige und Interpretation von Zeitstempeln fest.
`track_activities`	Dynamisch	Sammelt Informationen zu Befehlen, die ausgeführt werden.
`track_counts`	Dynamisch	Sammelt Statistiken zur Datenbankaktivität.
`track_functions`	Dynamisch	Sammelt Statistiken auf Funktionsebene zur Datenbankaktivität.
`track_io_timing`	Dynamisch	Sammelt Zeitpunktstatistiken zur Datenbank-E/A-Aktivität.
`transaction_deferrable`	Dynamisch	Gibt an, ob eine schreibgeschützte serialisierbare Transaktion aufgeschoben werden kann, bis sie ohne mögliche Serialisierungsfehler gestartet werden kann.
`transaction_isolation`	Dynamisch	Legt die Isolierungsstufe aktueller Transaktionen fest.
`transaction_read_only`	Dynamisch	Legt den Schreibschutzstatus aktueller Transaktionen fest.
`transform_null_equals`	Dynamisch	Behandelt expr=NULL als expr IS NULL.
`update_process_title`	Dynamisch	Aktualisiert den Titel des Vorgangs, um den aktiven SQL-Befehl anzuzeigen.
`vacuum_cost_delay`	Dynamisch	Bereinigungskostenverzögerung (in Millisekunden).
`vacuum_cost_limit`	Dynamisch	Bereinigungskostenbetrag, der vor der Inaktivität verfügbar ist.
`vacuum_cost_page_dirty`	Dynamisch	Bereinigungskosten für eine Seite, die durch eine Bereinigung ungültig wurde.
`vacuum_cost_page_hit`	Dynamisch	Bereinigungskosten für eine Seite, die im Puffer-Cache gefunden wurde.
`vacuum_cost_page_miss`	Dynamisch	Bereinigungskosten für eine Seite, die nicht im Puffer-Cache gefunden wurde.
`vacuum_defer_cleanup_age`	Dynamisch	Anzahl der Transaktionen, um die Bereinigung und Hot Cleanup aufgeschoben werden sollen, wenn vorhanden.
`vacuum_freeze_min_age`	Dynamisch	Mindestalter, bei dem die Bereinigung eine Tabellenzeile eingefroren werden sollte.
`vacuum_freeze_table_age`	Dynamisch	Alter, bei dem die Bereinigung eine Tabelle vollständig scannen sollte, um Tupel einzufrieren.
`wal_writer_delay`	Dynamisch	Inaktivitätszeit des WAL-Schreibers zwischen WAL-Flushes.
`work_mem`	Dynamisch	Legt den maximalen Arbeitsspeicher fest, der für Abfrage-Workspaces verwendet werden darf.
`xmlbinary`	Dynamisch	Legt die Kodierung von Binärwerten in XML fest.
`xmloption`	Dynamisch	Legt fest, ob XML-Daten in impliziten Parsing- und Serialisierungsoperationen als Dokumente oder Inhaltsfragmente betrachtet werden sollen.
`autovacuum_freeze_max_age`	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.
`autovacuum_max_workers`	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.
`max_connections`	Um eine statische IP-Adresse zu verwenden, tippen Sie auf	Legt die maximale Anzahl gleichzeitiger Verbindungen fest.
`max_files_per_process`	Um eine statische IP-Adresse zu verwenden, tippen Sie auf	Legt die maximale Anzahl gleichzeitig geöffneter Dateien für die einzelnen Serverprozesse fest.
`max_locks_per_transaction`	Um eine statische IP-Adresse zu verwenden, tippen Sie auf	Legt die maximale Anzahl von Sperren pro Transaktion fest.
`max_pred_locks_per_transaction`	Um eine statische IP-Adresse zu verwenden, tippen Sie auf	Legt die maximale Anzahl von Prädikatssperren pro Transaktion fest.
`max_prepared_transactions`	Um eine statische IP-Adresse zu verwenden, tippen Sie auf	Legt die maximale Anzahl gleichzeitig vorbereiteter Transaktionen fest.
`shared_buffers`	Um eine statische IP-Adresse zu verwenden, tippen Sie auf	Legt die maximale Anzahl freigegebener Arbeitsspeicherpuffer fest, die vom Server verwendet werden.
`ssl`	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.
`track_activity_query_size`	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.
`wal_buffers`	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
`effective_cache_size`	8 KB
`segment_size`	8 KB
`shared_buffers`	8 KB
`temp_buffers`	8 KB
`wal_buffers`	8 KB
`wal_segment_size`	8 KB
`log_rotation_size`	KB
`log_temp_files`	KB
`maintenance_work_mem`	KB
`max_stack_depth`	KB
`ssl_renegotiation_limit`	KB
`temp_file_limit`	KB
`work_mem`	KB
`log_rotation_age`	Minuten
`autovacuum_vacuum_cost_delay`	ms
`bgwriter_delay`	ms
`deadlock_timeout`	ms
`lock_timeout`	ms
`log_autovacuum_min_duration`	ms
`log_min_duration_statement`	ms
`max_standby_archive_delay`	ms
`max_standby_streaming_delay`	ms
`statement_timeout`	ms
`vacuum_cost_delay`	ms
`wal_receiver_timeout`	ms
`wal_sender_timeout`	ms
`wal_writer_delay`	ms
`archive_timeout`	S
`authentication_timeout`	S
`autovacuum_naptime`	S
`checkpoint_timeout`	S
`checkpoint_warning`	S
`post_auth_delay`	S
`pre_auth_delay`	S
`tcp_keepalives_idle`	S
`tcp_keepalives_interval`	S
`wal_receiver_status_interval`	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. Dieser Parameter legt fest, wie viel Arbeitsspeicher Sie der Selbstbereinigung für das Scannen einer Datenbanktabelle und das Speichern aller Zeilen-IDs zuteilen, für die eine Bereinigung ausgeführt werden soll. Wenn Sie den Wert des Parameters maintenance_work_mem zu niedrig einstellen, muss die Tabelle während des Bereinigungsvorgangs möglicherweise mehrmals gescannt werden, um die Bereinigung auszuführen. Solche wiederholten Scans können negative Auswirkungen auf die Leistung haben.

Sie müssen bei der Berechnung des Werts für den Parameter maintenance_work_mem zwei Dinge berücksichtigen:

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 auf einen Wert zwischen einem und zwei Gigabyte (zwischen 1.048.576 und 2.097.152 KB) festlegen. Im Fall extrem großer Hosts sollten Sie den Parameter auf einen Wert zwischen zwei und vier Gigabyte (zwischen 2.097.152 und 4.194.304 KB) festlegen. Der Wert, den Sie für diesen Parameter festlegen, muss von der Workload abhängen. Amazon RDS hat den Standardwert für diesen Parameter auf Kilobyte aktualisiert. Er wird wie folgt berechnet:

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/) und über die RDS-API angezeigt werden kann. Nachdem der Schwellenwert der zurückgegebenen CloudWatch-Metrik 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 ist definiert als:


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 im Verhältnis zur Größe der Tabelle oder zur Häufigkeit der Aktualisierungen zu niedrig festgelegt wurde.

Es wird empfohlen, dass Sie die folgende Formel verwenden, um den Wert des Parameters maintenance_work_mem festzulegen.


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 zu niedrig festgelegt wurde. Sie müssen sofort Maßnahmen für eine Tabelle einleiten. Sie möchten zum aktuellen Zeitpunkt jedoch keinen Bounce für die Instance auslösen. Ermitteln Sie mittels der Abfragen in vorherigen Abschnitten die Tabelle, die das Problem darstellt, und identifizieren Sie eine über einen langen Zeitraum ausgeführte Selbstbereinigungssitzung. Sie müssen die Einstellung für den Parameter maintenance_work_mem ändern. Sie müssen jedoch auch sofortige Maßnahmen einleiten und die betreffende Tabelle bereinigen. Das folgende Verfahren zeigt, was Sie in dieser Situation unternehmen:

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"-Befehl ausgeben, wird eine exklusive Sperre für die Tabelle ausgeführt. Schreiboperationen werden blockiert. Gleiches gilt für Lesevorgänge, die diesen spezifischen Index verwenden.

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 werden in der PostgreSQL-Dokumentation vollständig beschrieben.


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 sich auf die der Selbstbereinigung beziehen, lassen sich auf Tabellenebene festlegen. Dies kann im Vergleich zur Änderung des Verhaltens der gesamten Datenbank ein bevorzugtes Verfahren sein. Im Fall großer Tabellen müssen möglicherweise aggressive Einstellungen festgelegt werden, und Sie möchten vielleicht nicht, dass sich die Selbstbereinigung für alle Tabellen auf diese Weise verhält.

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.

Anmerkung

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 Parameter pgaudit.role fest. Der Parameter pgaudit.role muss auf die Rolle rds_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 verwenden, um PostgreSQL-Protokolle zu analysieren. Die pgbadger-Dokumentation besagt, dass das Muster %l (Protokollzeile für Sitzung/Prozess) ein Teil des Präfixes sein muss. Wenn Sie jedoch das aktuelle rds log_line_prefix als Parameter für pgbadger angeben, wird dennoch ein Bericht erstellt.

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.

Anmerkung

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 zugreifen. Wenn Sie eine Remote-Verbindung ausgehend von einer PostgreSQL-DB-Instance einrichten, ist der Zugriff auch für das Lesereplikat verfügbar.

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
Deaktivieren der benutzerdefinierten DNS-Auflösung
Einrichten eines benutzerdefinierten DNS-Servers

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.

Anmerkung

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.

Warnung JavaScript ist in Ihrem Browser nicht verfügbar oder deaktiviert.

Zur Nutzung der AWS-Dokumentation muss JavaScript aktiviert sein. Weitere Informationen finden auf den Hilfe-Seiten Ihres Browsers.

Dokumentkonventionen

Exportieren von PostgreSQL-Daten zu Amazon S3-Dateien

Verwenden der Kerberos-Authentifizierung