Fragen zur Konfiguration meiner PostgreSQL-Umgebung

Hallo erstmal,
ich habe in den letzten Wochen eine PostgreSQL-Umgebung aufgebaut, unzählige Tutorials und Handbücher gelesen - aber einige Frage bleiben dennoch offen.
Zunächst mal die Umgebung:
Windows Server 2012 R2 Server (virtualisiert per VMWARE) - bitte nicht spotten… das ist nunmal unser BS
PostgreSQL 9.3.4
Keine Replikation, Cluster oder ähnliches

Ich habe es per “normaler” Installation nicht geschafft, mehrere “Instanzen” auf einen Server zu bringen, da bei dem 2. Aufruf der Installationsroutine die Meldung kam, dass PostgreSQL bereits installiert sei. Somit habe ich mir 9.3.4-Binary runtergeladen und damit laufen mehrere Instanzen problemlos auf einem Server

Backup:
Aktuell fahre ich übungshalber noch 2 Backupsysteme:
Allabendlich läuft skriptgesteuert …
…zunächst ein Dump
D:\PostgreSQL_5432\bin\pg_dumpall --port=5432 --username=postgres --no-password --oids --clean --file=E:\Backup\dump5432.sql
…und anschließend ein Basebackup
D:\PostgreSQL_5432\bin\pg_basebackup.exe --host=127.0.0.1 --port=5432 --username=postgres --no-password --pgdata=E:\Backup\Base_Datum --format=t --xlog --gzip --compress=1
Letzteres tue ich, um Point in Time Recovery anbieten zu können. (bin für Verbesserungsvorschläge offen)

Hier meine erste Frage:
In einer “Instanz” mit z.B. dem Port 5432 kann ich mehrere Datenbanken anlegen - mit der Dumpsicherung kann ich angeblich einzelne Datenbanken auch zurücksichern - mit dem Basebackup scheint dieses nicht möglich zu sein, sondern ich kann nur alle Datenbanken dieser Instanz auf einmal auf einen minutengenauen Stand zurücksichern. Ist das richtig?

Archive:
Über die postgresql-conf habe ich folgendes konfiguriert
wal_level = hot_standby
archive_mode = on
archive_command = ‘copy “%p” “E:\Archivelogs\5432\%f”’
archive_timeout = 3600
In diesem Ordner türmen sich nun die Archivelogs - stündlich eine 16MB-Datei… ich würde gerne abends nach dem Basebackup Archivelogs, die z.B. älter als 2 Tage sind, löschen. Geht das irgendwie?

pg_dumpall ist PLAIN-Text, und zwar alle Datenbanken. Um einzelne Objekte daraus zu popeln brauchst erst mal einen Editor. Besser ist hier oft, pg_dump im custom - Format zu verwenden, und extra mit pg_dumpall die globalen Objekte (User) zu sichern.

  • mit dem Basebackup scheint dieses nicht möglich zu sein, sondern ich kann nur alle Datenbanken dieser Instanz auf einmal auf einen minutengenauen Stand zurücksichern. Ist das richtig?

Absolut korrekt.

Archive:
Über die postgresql-conf habe ich folgendes konfiguriert
wal_level = hot_standby
archive_mode = on
archive_command = ‘copy “%p” “E:\Archivelogs\5432\%f”’
archive_timeout = 3600
In diesem Ordner türmen sich nun die Archivelogs - stündlich eine 16MB-Datei… ich würde gerne abends nach dem Basebackup Archivelogs, die z.B. älter als 2 Tage sind, löschen. Geht das irgendwie?

Das ist ja wenig :wink:

Ja, kannst Du dann löschen, Cron unter *NIX oder halt irgend was unter Kleinweich. Das ist nicht Sache von PG.

Vielen Dank für die Antworten.

Zum Backup:
Heißt für mich, wenn ich Point-in-tine-recovery anbieten möchte, dass ich nur eine Datenbank pro Instanz laufen lassen kann… denn sonst würde ich bei jeder Rücksicherung ja die Veränderungen der anderen DBs verlieren. Hmm… ernüchternd - aber nicht zu ändern. Bin von SQL-Server verwöhnt…
PG_DUMP und PG_DUMPALL macht im Fall dieser “1 DB pro Instanz”-Lösung somit keinen Unterschied?

zum Löschen des Archivelogs:
Hatte gehofft, dass es da eine interne Lösung gibt - ist doch ein bisschen aufwändig, alle Logs vor dem letzten Basebackup manuell zu löschen. Hatte was über “archive_cleanup_command & pg_archivecleanup” gelesen, aber das scheint dann wohl nicht meine gewünschte Funktionalität abzubilden. Das Löschen der Archivelogs werde ich nun mit dem PostgreSQL-fremden Produkt delage.exe erledigen. (delage32.exe E:\Archivelogs*.* 2 /recurse /rd /recurse)

Datenjbankpflegejobs:
Gibt es irgendwelche regelmäßigen Datenbankenpflegejobs, die man konfigurieren sollte. Habe irgendwas über Autovacuum gelesen - bin mir aber nicht sicher… Kann man sowas nachts über nen Skript konfigurieren?

Ja, derzeit gilt das für den kompletten Cluster. In 9.4 kommen neue Features, die wohl später mal selektives PITR einzelner Datenbanken ermöglichen könnten, mal sehen. PG_DUMP mit Custom-Format hat einen Vorteil: Du kannst beim Restore mehrere Jobs damit beauftragen. Da im Custom-Format auch steht, welche Abhängigkeiten zwischen den Objekten (ein View braucht erst mal die Table) können Objekte so parallel hergestellt werden, wenn möglich. Das geht mit einem Plain-Text-Dump nicht.

zum Löschen des Archivelogs:
Hatte gehofft, dass es da eine interne Lösung gibt - ist doch ein bisschen aufwändig, alle Logs vor dem letzten Basebackup manuell zu löschen. Hatte was über “archive_cleanup_command & pg_archivecleanup” gelesen, aber das scheint dann wohl nicht meine gewünschte Funktionalität abzubilden. Das Löschen der Archivelogs werde ich nun mit dem PostgreSQL-fremden Produkt delage.exe erledigen. (delage32.exe E:\Archivelogs*.* 2 /recurse /rd /recurse)

Ähm, ja, man kann wohl mit archive_cleanup_command oder so auch fertig verarbeitete WAL’s löschen, nachdem diese eingelesen wurden. Noch nie verwendet. Ich betreib PG nur unter Linux und da gibt es Cron.

Datenjbankpflegejobs:
Gibt es irgendwelche regelmäßigen Datenbankenpflegejobs, die man konfigurieren sollte. Habe irgendwas über Autovacuum gelesen - bin mir aber nicht sicher… Kann man sowas nachts über nen Skript konfigurieren?

Das war mal, daß man das extra aufrufen mußte. Heutzutage (seit Version 8.0 oder so) gibt es den Autovacuum-Daemon. Du kannst bei Bedarf in lastschwachen Zeiten wichtige Tabellen einem Analyse unterziehen oder via Cluster nach einem Index physisch sortieren, aber das ist dann schon echtes Feintuning.

Andreas

Vielen Dank, Andreas. Das war mir eine große Hilfe.

Wenn noch jemand eine kreative Idee zur Archivelog-Löschung unter Windows hat, würde ich dankbarer Abnehmer sein…

Hallo - ich suche nach wie vor noch eine Lösung, um selektives PITR einzelner Datenbanken eines Cluster-Basebackups durchführen zu können. Oben wurde für Version 9.4 die Hoffnung auf diese Funktionalität geweckt - nun ist 9.4.1 erschienen… ist dieses nun möglich?
Hintergrund ist, dass PostgreSQL bei uns beliebter und beliebter wird, ich aber für jede noch so kleine Datenbank einen eigenen DB-Cluster mit eigenem Port… aufbauen muss. Das wäre ein wirklicher Gewinn und noch könnte ich unsere Umgebung halbwegs einfach auf die neue Version “umbauen”…

Jein. Lies http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-basics-logical-decoding/ und mach Dir selber ein Bild.

In 9.4 wurden Features eingebaut die eine entsprechende Lösung möglich machen - z.B. durch externe Tools. Aber “out-of-the-box” geht es leider immer noch nicht.

Wenn es Dir aber nur um den Recory Teil geht, dann könntest Du doch im Fall eines Problems eine zweite Instanz hochziehen, dort einen PITR des ursprünglichen Clusters machen und aus dem dann die eigentliche DB extrahieren. Ist für den Recovery Fall natürlich umständlicher, aber nachdem der eher die Ausnahme ist (oder sein sollte), würdest Du Dir immerhin das aufsetzen der vielen Instanzen im “Regelbetrieb” sparen. Recovery/DB Dump/DB Restore könnte man vermutlich sogar scripten, so dass es dann automatisch abläuft.

"In 9.4 wurden Features eingebaut die eine entsprechende Lösung möglich machen - z.B. durch externe Tools. Aber “out-of-the-box” geht es leider immer noch nicht. " - ich kenne jemanden, der bei einer großen deutschen Firma sowas implementiert hat. Falls für den Fragesteller das wirklich sehr wichtig ist, könnte ich auch versuchen, da zu vermitteln…

Ich bin nach wie vor auf der Suche nach einer Lösung, die PIT-Recovery einzelner Datenbanken einer Instanz ermöglicht:
Meine Hoffnung auf zukünftige Versionen löst sich langsam in Luft auf – selbst in der jetzt angekündigten 9.5 habe ich eine solche Funktionalität nicht entdecken können. Bin ich der einzige, der diesen Bedarf hat?
Vielen Dank für das Angebot Kontakte/externe Tools zu vermitteln. Da ich aber aufgrund hoher Arbeitslast und der Notwendigkeit, meine Kollegen zu instruieren, eine einfache bzw. einfach zu vermittelnde Lösung suche, versuche ich mich nur „out-of-the-box“-Mitteln zu bedienen, wie castorp sie so schön nennt.
Die Lösung von castorp scheint mir momentan die sinnvollste – wir betreiben mittlerweile ca. 30 PostgreSQL-DBs (Tendenz klar steigend). Pro Datenbank einen Service ist bald nicht mehr zu leisten und auch unwirtschaftlich. Eine Produktiv-Instanz und eine Reproduktions-Instanz für den Fall eines Wiederherstellungs-Erfordernisses (dann pgdump-Export/Import) scheint mir aktuell der sinnvollste Weg.
Ich bin aber nach wie vor sehr an Tipps interessiert, die dieses Thema betreffen – also wenn jemand noch Ideen hat… ich schaue hier regelmäßig rein und bin für jeden Hinweis dankbar.