Häufige DBA-Aufgaben für PostgreSQL - Amazon Relational Database Service

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.

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.

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:

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

  1. Ö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.

  2. 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;
  3. 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
  4. 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
  5. 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');
  6. 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

  1. Ö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.

  2. 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;
  3. 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
  4. 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');
  5. 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

  1. Erstellen Sie eine spezifische Datenbankrolle namens rds_pgaudit. Verwenden Sie den folgenden Befehl, um die Rolle zu erstellen.

    CREATE ROLE rds_pgaudit; CREATE ROLE
  2. Ä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
  3. 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
  4. 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)
  5. Führen Sie den folgenden Befehl aus, um die Erweiterung pgaudit zu erstellen.

    CREATE EXTENSION pgaudit; CREATE EXTENSION
  6. 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

  1. 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;
  2. 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.

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

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

  2. Aktivieren Sie die orafce-Erweiterung mit der Anweisung CREATE EXTENSION.

    CREATE EXTENSION orafce;
  3. Ü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:

  1. Installieren Sie die Erweiterung postgres_fdw.

    CREATE EXTENSION postgres_fdw;
  2. 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');
  3. 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');
  4. 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.

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

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

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

  3. Konfigurieren Sie Ihren DNS-Server, um User Datagram Protocol (UDP)-Antworten in der Größenordnung von 512 Bytes oder weniger zu erhalten.

  4. Konfigurieren Sie Ihren DNS-Server, um Transmission Control Protocol (TCP)-Antworten in der Größenordnung von 1 024 Bytes oder weniger zu erhalten.

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

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

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