Ich habe ein kleines RSS Projekt, in dem ich fleißig Daten sammle. Und jetzt mit fast 7 Millionen Einträgen wird mir langsam die Platte auf dem Server zu klein. Was tun? In dieser Miniserie stelle ich vor, welche Schritte ich unternehme um diesem Problem zu begegnen. Ich denke, dass ich die Datenbank in Partitionen aufteilen werde und dann nur die aktuellsten auf dem Server belasse.
Dafür müssen wir als erstes die Daten bewegen können. Das zeige ich hier.
Vorteile von dblink
Wenn die Tabelle langsam 300gig erreicht, wird der Transport übers Netz zeitaufwendig. Es erinnert mich an die Zeiten, als ich mit meiner Leitung 3 Minuten für ein Megabyte benötigt hatte. Aber nicht nur die Übertragung dauert Zeit, alles wird langsamer. Das Dumpen des Inhaltes, Packen und das wieder Einlesen. Mit dblink sparen wir zwar nix an Zeit der Übertragung, die anderen Schritte fallen aber weg. dblink bietet die Möglichkeit Daten einfach per SQL Befehl zu kopieren. Was Zeit und Nerven spart.
Vor dblink, kannte ich nur das direkte Übertragen der postgresdaten oder das Dumpen der Daten ins SQL oder CSV Format als Sicherungs- und Übertragungsmöglichkeit. Beides nicht ideal für diesen Use case. Falls sich jemand fragt, warum nicht postgres_fdw zum Einsatz kommt, dass schneller als dblink sein soll. postgres_fdw kommt leider nicht gut mit composite keys aus, die ich später bei der partitionierten Datenbank nutzen will, aber dazu später mehr.
Voraussetzungen
dblink verlinkt ganz im Sinne des Wortes eine Datenbank einer entfernten postgresinstallation in das aktuell benutze postgres Programm. Damit das funktioniert müssen sich die zwei postgres Programme sehen, d.h. Zugriff über Netz soll möglich sein und auch keine Firewall im Wege stehen.
Dann benötigen beide die Erweitern dblink, was bei Standard Linux Installationen der Fall sein sollte. Im Zweifel Nachprüfen mit dem Postges Befehl
SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name = 'dblink'; name | default_version | installed_version --------+-----------------+------------------- dblink | 1.2 | (1 row)
Ich hab jetzt nicht getestet ob beide postgres auf der gleichen Version sein müssen, ich denke nein, bin mir aber da nicht ganz sicher, jedenfalls waren meine beiden auf Version 14.
In einer der Beiden wird Super-User genötigt um dblink aktivieren zu können. Es ist egal welche, solange der andere Nutzer auf der anderen Datenbank die jeweiligen Rechte hat. Da wir mit dblink Daten ziehen und schieben können.
Vorgehen
Zuerst aktivieren wir dblink:
CREATE EXTENSION IF NOT EXISTS dblink;
Bevor wir anfangen mit den Daten zu spielen, gehe ich davon aus das jeder brav ein Backup gemacht hat, so das nix kaputt gehen kann, solange wir hier üben?
Dann verbinden wir die beiden Datenbanken:
SELECT dblink_connect('myconn', 'dbname=rssdata host=192.168.5.22 user=rssuser password=jis9jsdjfdk port=5433');
Wenn der Port nicht angegeben wird, wird der Default von 5432 genommen, da ich aber nicht den Standartport hatte musste ich ihn mit angeben.
Der Schritt kann anscheinend auch übersprungen werden, dann muss jedoch überall wo wir myconn eintragen, die Verbindungsdaten eingetragen werden.
Der eigentliche Abruf ist etwas kompliziert, aber zum Glück logisch.
Wir wollen testen, ob wir die entfernte Datenbank erreichen und uns aus der Tabelle rssanswers den letzten Eintrag ausgeben lassen können.
select * from dblink('myconn','select created_at from rssanswers order by created_at desc limit 1') AS t1(created_at timestamp without time zone);
created_at
----------------------------
2026-04-27 07:12:43.540033
(1 row)
Der erste Teil ist für das postgres in dem wir es ausführen
Der Innenteil wird praktisch auf dem entfernten postgres ausgeführt. Über die Verbindung myconn die wir oben definiert haben. Wenn wir später dann nur bestimmte Bereiche kopieren möchten, z.B. nach Zeit abgegrenzt, dann ist dort der richtige Platz zum eintragen.
Der letzte Teil ist der Tatsache geschuldet, dass dblink anscheinend das Schema , in einfachen Abfragen, nicht mitliefert. Jedenfalls müssen wir definieren wie die Daten zu behandeln sind. Dafür erzeugen wir ein Alias, dass nur für die Zeit der Abfrage existiert, mit:
AS t1(created_at timestamp without time zone);
So ich hoffe, dass war verständlich, denn der Insertbefehl sieht so aus:
INSERT INTO rssanswers (
id,
rssurl_id,
text,
created_at,
updated_at,
language,
confidence,
oldencoding,
etag,
lastmodified
)
select * from dblink(‚myconn‘,
’select id,rssurl_id, text, created_at, updated_at, language,
confidence, oldencoding, etag, lastmodified from rssanswers
WHERE created_at >= date “2023-01-01“ AND created_at < date ''2024-01-01)'' '
)
AS t1(
id bigint,
rssurl_id bigint,
text text,
created_at timestamp without time zone,
updated_at timestamp without time zone,
language character varying,
confidence double precision,
oldencoding character varying,
etag character varying,
lastmodified character varying)
ON CONFLICT (id, created_at) DO UPDATE SET
rssurl_id = excluded.rssurl_id,
text = excluded.text,
updated_at = excluded.updated_at,
language = excluded.language,
confidence = excluded.confidence,
oldencoding = excluded.oldencoding,
etag = excluded.etag,
lastmodified = excluded.lastmodified;
Im ersten Teil sagen wir, was alles eingefügt werden soll, im dblink Teil holen wir die jeweiligen Daten. Dann nochmal das Schema setzen, für die Daten und zum Schluss noch ein ON CONFLICT falls was schief gehen sollte. Easy. /s
Fragt nicht wie lange ich gebraucht habe, um herauszufinden, dass ich bei WHERE created_at >= date “2023-01-01“ das zweimal “ schreiben muss da wir ja schon in dblink escapen und date den Wert auch mit ‚ benötigt.
Ach ja, wenn ihr Schreibrechte habt, dann könnt ihr Daten auch in die remote Datenbank schreiben. Dafür müsst ihr nur die komplette Logik umdrehen …. Hä, ich lass das mal als Hausaufgabe oder Verlinke das hier sobald ich es mal benötigt habe.
In diesem Sinne
Bis später
