Umzug von MySQL auf PostgreSQL klappt nicht problemlos

[Mephisto]

Hallo,

nachdem MySQL beim Import einer Tabelle mit ca. 1.8 Mio Zeilen einige Fehler machte, wurde mir hier der Umstieg auf PostgreSQL nahegelegt, mit der es dann auch fehlerfrei funktionierte.

Jetzt hatte ich aber Schwierigkeiten, mit den anderen DBs (einer Zeitzonen-DB und Semanitc Scuttle) umzuziehen. mit denen ich schon länger unter MySQL arbeite. Leider sind immer sämliche Anleitungen nur für MySQL verfügbar und Postgre wird immer nur beiläufig erwähnt.

Die Zeitzonen DB besteht aus 3 Tabellen, wovon ich gestern eine partout nicht importiert bekommen habe. Für MySQL soll die Tabelle folgendermassen aussehen:

CREATE TABLE `timezone` (
  `zone_id` int(10) NOT NULL,
  `abbreviation` varchar(6) COLLATE utf8_bin NOT NULL,
  `time_start` int(11) NOT NULL,
  `gmt_offset` int(11) NOT NULL,
  `dst` char(1) COLLATE utf8_bin NOT NULL,
  KEY `idx_zone_id` (`zone_id`),
  KEY `idx_time_start` (`time_start`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Diese Tabelle habe ich in PG folgendermassen definiert (mit pgAdminIII):

CREATE TABLE timezone
(
zone_id bigint,
abbreviation character varying(6),
time_start integer,
gmt_offset integer,
dst character(1)
)
WITH (
OIDS=FALSE
);

Die timezone.csv sieht folgendermassen aus:

"1","WET","-2147483648","0","0"
"1","WET","-2147397248","0","0"
"1","CET","-733881600","3600","0"
...

Gestern bekam ich immer eine Fehlermeldung: Zeile 1 Spalte 1 -> keine GANZZAHL.

Nachdem ich heute den Rechner neu gestartet habe, ist der Fehler weg !

Stundenlang bin ich einem Phänomen hinterher gerannt, welches nach Neustart des Rechners kein Thema mehr ist. Bei allem, was ich vorher mit der MySQL-DB gemacht hatte, gab es (zumindestens solche) seltsamen Phänomen nicht ! Natürlich hatte ich mich gestern auch mehrfach mit der PG-DB reconnected, weil ich dachte, dass vielleicht mit meiner Session etwas faul ist.

Ist da vielleicht etwas bekannt, dass mit der PG-Version 9.3.2 etwas nicht stimmt ?

Grüße
Mephisto

 

[akretschmer]

Diese Tabelle habe ich in PG folgendermassen definiert (mit pgAdminIII):

Ich würde nicht anfangen, Zeiten als INT und extra Zeitzonen zu speichern, dafür gibt es korrekte Datentypen: timestamptz.

Die Zeitzonen mit Namen und so sind auch schon in PG da und definiert:

test=*# select * from pg_timezone_abbrevs limit 3;
 abbrev | utc_offset | is_dst
--------+------------+--------
 ACSST  | 10:30:00  | t
 ACST  | -04:00:00  | t
 ACT  | -05:00:00  | f
(3 rows)

Time: 0,323 ms
test=*# select * from pg_timezone_
pg_timezone_abbrevs  pg_timezone_names
test=*# select * from pg_timezone_names limit 3;
  name  | abbrev | utc_offset | is_dst
------------------+--------+------------+--------
 GMT0  | GMT  | 00:00:00  | f
 Brazil/Acre  | AMT  | -04:00:00  | f
 Brazil/DeNoronha | FNT  | -02:00:00  | f
(3 rows)

Da sind übrigens auch Sommerzeiten und so alles mit beachtet - einfach nur anwenden. Du kannst auch zwischen Zeitzonen umrechnen bzw. wenn Du eine Zeit abfragst, kannst sagen, für welche Zeitzone du das brauchst.

 

[Mephisto]

Ok, wenn das PG schon "im Bauch" hat, brauche ich diese Zeitzonen-DB hier natürlich nicht. Ich war nur heute etwas überrascht, dass PG diese Tabelle gestern einfach nicht einlesen wollte und, ohne etwas zu verändern, es heute auf einmal ging.

 

[akretschmer]

Ok, wenn das PG schon "im Bauch" hat, brauche ich diese Zeitzonen-DB hier natürlich nicht. Ich war nur heute etwas überrascht, dass PG diese Tabelle gestern einfach nicht einlesen wollte und, ohne etwas zu verändern, es heute auf einmal ging.

Kann ich nicht nachvollziehen. Was steht im Log von PG?

 

[Mephisto]

Habe gerade den ganzen Rechner nach dem PG-Log abgesucht -> nicht gefunden. Jedenfalls nicht da, wo die Logs "normallerweise" sind.

 

[akretschmer]

Habe gerade den ganzen Rechner nach dem PG-Log abgesucht -> nicht gefunden. Jedenfalls nicht da, wo die Logs "normallerweise" sind.

Nicht suchen - fragen! Die DB weiß das doch ;-)

test=*# select name, setting from pg_settings where name in ('data_directory','log_directory','log_filename');
  name  |  setting
----------------+--------------------------------
 data_directory | /usr/local/pgsql/data-9.3
 log_directory  | pg_log
 log_filename  | postgresql-%Y-%m-%d_%H%M%S.log
(3 rows)

Andreas

 

[Mephisto]

Ja, sicher, wenn man das weiß ! Bei mir ist das Data-Direktory in /var/lib/postgres/data, da gibt es aber kein pg_log-Verzeichnis und in der /var/lib/postgres/data/postgresql.conf sind alle Settings mit "#" auskommentiert (da alle "auskommentiert" sind, glaube ich das nicht so richtig).

Und so grundsätzliche Einstellungen kann man nicht irgendwo (ausser sicherlich in der shell) administrieren (sehen wäre ja auch schon mal nicht schlecht) ? Oder gibt es doch noch ein besseres Management/Administration-Tool ? Muss man bei PG etwa alle Einstellungen per SQL in der postgres-DB und seinen diversen Tabellen vornehmen ?

 

[akretschmer]

Ja, sicher, wenn man das weiß ! Bei mir ist das Data-Direktory in /var/lib/postgres/data, da gibt es aber kein pg_log-Verzeichnis und in der /var/lib/postgres/data/postgresql.conf sind alle Settings mit "#" auskommentiert (da alle "auskommentiert" sind, glaube ich das nicht so richtig).

Ja, da muß man sich erst mal reinarbeiten. Wichtig ist, daß auch logging_collector eingeschaltet ist - vermutlich ist es das bei Dir nicht.

Und so grundsätzliche Einstellungen kann man nicht irgendwo (ausser sicherlich in der shell) administrieren (sehen wäre ja auch schon mal nicht schlecht) ? Oder gibt es doch noch ein besseres Management/Administration-Tool ? Muss man bei PG etwa alle Einstellungen per SQL in der postgres-DB und seinen diversen Tabellen vornehmen ?

Nein. Du brachst nur einen $EDITOR, damit bearbeitest Du die postgresql.conf. Für Zugriffsgeschichten die pg_hba.conf. Beide Dateien sind so, daß alle möglichen Optionen auskommentiert mit deren jeweiligen Default-Werten dastehen. Ich finde das recht praktisch - einfach mal diese Files anschauen. Viele Änderungen greifen schon via Reload, die, die einen Restart brauchen, bei denen ist das auch genannt.
In PG 9.4 kommt evtl. eine Möglichkeit, Werte via SQL (persistent) zu ändern. Vieles kann man aber auch zur Laufzeit via SQL jetzt schon ändern, z.B. Parameter für Planner-Kosten oder Abfragestrategien. Aber das ist halt wie bei vielen komplexen Dingen: da muß man sich reinarbeiten. Wichtig ist, die Parameter erst einmal zu verstehen. Und so doll viele sind das bei PG nicht, fast alle Parameter werden von wohl 99% der Anwender im Leben niemals nie geändert, weil sie auf sinnvollen werden stehen. Anpassen braucht man eigent lich nur:

• Logging-Parameter
• Speicher-Parameter

Eher selten, wenn man z.B. SSD einsetzt, Kosten-Parameter. Letztendlich bleiben weniger als ein Dutzend Dinge, die man normalerweise anpassen muß.

 

[Mephisto]

Mit dem Umzug von Semantic-Scuttle auf die PG klappt das auch irgendwie nicht. Wenn ich mysql runterfahre und alles andere (posgresqld und httpd) neu starte funktioniert es nicht mehr, obwohl ich da eine config.php entsprechend für PG geändert habe. Das muss ich wohl auch erst ganz neu aufsetzen. Nun gut, mit Apache und php kenne ich mich auch noch nicht aus. Und ein DB-Kenner hätte wahrscheinlich auch die Daten aus mysql einfach in die PG exportiert.

Aber vielleicht kennt ja auch jemand hier ein noch besseres DB-basiertes Favoriten-Management. Mit meinen mittlerweile über 2000 Favoriten (meine persönliche Suchmaschine) kommt bislang nur Chromium ohne Probleme klar, aber das kann man unter Linux mittlerweile wegen mittlerweile nicht mehr modifizierbaren winzigen Texten in den Tabs nicht mehr zum Hardcore-Surfen benutzen.

 

[Mephisto]

Ja, da muß man sich erst mal reinarbeiten. Wichtig ist, daß auch logging_collector eingeschaltet ist - vermutlich ist es das bei Dir nicht.

Ok. logging-collector ist abgeschaltet (auch nicht ganz so sinnig, oder ? Gerade bei einer Datenbank !), habe es jetzt so geändert:

# This is used when logging to stderr:
logging_collector = on            # Enable capturing of stderr and csvlog
                                               # into log files. Required to be on for
                                               # csvlogs.
                                               # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'        # directory where log files are written,

1. Muss ich da noch csvlog einschalten ?
2. Kann ich nicht in z.B. /var/log/postgresql loggen ?
3. Es fält mir gerade auf, dass data auf /var ist und somit bei mir auf einer hdd-Partition. Das system ist auf einer ssd. Wie kann man das data-Verzeichnis dort hin verlegen ? Wäre das nicht sinnig ?

Eher selten, wenn man z.B. SSD einsetzt, Kosten-Parameter.

Da müsste ich dann wohl auch noch was machen.

 

[akretschmer]

Ok. logging-collector ist abgeschaltet (auch nicht ganz so sinnig, oder ? Gerade bei einer Datenbank !), habe es jetzt so geändert:

Die Default-Config ist sehr spartanisch und sparsam im Ressourcenverbrauch. Das sollte man wissen. Anpassen sollte man auf alle Fälle shared_buffers. Auf einem dedizierten DB-Server so auf 1/4 RAM, aber auch nicht mehr als etwa 8-10GB. Default sind es IIRC 32MB.

# This is used when logging to stderr:
logging_collector = on            # Enable capturing of stderr and csvlog
                                               # into log files. Required to be on for
                                               # csvlogs.
                                               # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'        # directory where log files are written,

1. Muss ich da noch csvlog einschalten ?

Nö, es sei denn, Du willst CSV-Logs.

2. Kann ich nicht in z.B. /var/log/postgresql loggen ?

Ja. Aber beachte, der User postgres braucht Schreibrechte dort.

3. Es fält mir gerade auf, dass data auf /var ist und somit bei mir auf einer hdd-Partition. Das system ist auf einer ssd. Wie kann man das data-Verzeichnis dort hin verlegen ? Wäre das nicht sinnig ?

Server stoppen, Config anpassen, Daten dahinschaufeln, Server starten.

Da müsste ich dann wohl auch noch was machen.

Bei SSD-Platten hat man faktisch keine Zugriffszeiten wie bei normalen Platten. Daher kann man random_page_cost, was normalerweise auf 4 ist, auf knapp über 1 setzen (probier 1.5). Damit wird er eher Indexe nutzen, weil er so weiß, daß ein random-Read nicht viel teurer als ein sequentielles Lesen ist. Aber sowas wirklich nur bei SSD machen, sonst ballerst Dir selber in den Fuß.

SSD kann man auch nutzen, wenn man sehr, sehr schreiblastig ist: dann legt man da das Transaction Log drauf und entkoppelt das Schreiben der Transaction Logs vom Schreibern / Lesen der eigentlichen Daten. Ein Kunde von uns hat z.B. pro Sekunde einige hundert (kleiner) Inserts, das ist eine enorme Last für die Transaction Logs - daher haben wir die auf SSD. Die Daten sind, wegen der Menge, auf einem schnellen RAID10 mit einer Menge normaler 15K SAS - Platten.

 

[Mephisto]

Die Default-Config ist sehr spartanisch und sparsam im Ressourcenverbrauch. Das sollte man wissen. Anpassen sollte man auf alle Fälle shared_buffers. Auf einem dedizierten DB-Server so auf 1/4 RAM, aber auch nicht mehr als etwa 8-10GB. Default sind es IIRC 32MB.

Kann ich es bei meinen mikroskopisch kleinen Anforderungen nicht einfach so lassen und es erst dann ändern, wenn es mir zu langsam wird ?

Nö, es sei denn, Du willst CSV-Logs.

Brauch ich nicht. Möchte die Logs nur mit ksystemlog (oder Editor) einsehen können.

Server stoppen, Config anpassen, Daten dahinschaufeln, Server starten.

Hört sich einfach an !

...Daher kann man random_page_cost, was normalerweise auf 4 ist, auf knapp über 1 setzen (probier 1.5). ... Aber sowas wirklich nur bei SSD machen, sonst ballerst Dir selber in den Fuß.

Erst umziehen, dann ändern ...

SSD kann man auch nutzen, wenn man sehr, sehr schreiblastig ist

Gut, zu wissen ! Aber schreiblastig ist bei mir nicht ! Eigentlich ist es gar nicht lastig. Es ist nur eine kleine Datenbank auf einem Rechner, der eigentlich vornehmlich andere Sachen macht.

 

[akretschmer]

Kann ich es bei meinen mikroskopisch kleinen Anforderungen nicht einfach so lassen und es erst dann ändern, wenn es mir zu langsam wird ?

Ja, natürlich.

 

[Mephisto]

So, jetzt werde ich wohl doch wieder die MariaDB installieren. Was ich für meinen Rechner brauche, ist eine online-fähige und eine lokale Datenbank.

SQLite werde ich behalten, weil es für meine Programmierung erst mal vollkommen ausreicht. Die geonames-DB (allCountries.txt und timezones.txt) ist drin. Ich hatte es mir schon immer gewünscht, nicht für jede neue Ortschaft auf der Welt erst mal im Internet schauen zu müssen. Das ist aber nur ein schönes neues Feature meines Programms. Und für das, wofür ich die DB eigentlich in meinem Programm benutzen möchte, reicht die SQLite erst mal vollkommen aus und wird auch von Qt hervorragend unterstützt. Wenn das Programm erst mal fertig ist, lässt sich die SQLite-DB sicherlich auch in eine andere (Mehrbenutzer-)DB exportieren.

Was die Online-DB auf meinem Webserver angeht, kann ich mit der PostgreSQL-DB insofern nicht leben, weil ich damit Semantic Scuttle nicht ans Laufen bekomme. Gestern hatte ich noch versucht, ob man die Lesezeichen vielleicht auch statt mit SemanicScuttle mit OwnCloud händeln kann, aber da funktioniert der Import nicht (wie auch anderswo so beschrieben, aber nicht gelöst). Nützt mir also nichts und das andere, was OwnCloud kann, brauche ich nicht. Und andere Lesezeichen-Lösungen scheint es nicht zu geben, jedenfalls habe ich keine gefunden.

Da die Lesezeichen-DB aber mit der MariaDB einwandfrei funktioniert und es auch sicherlich noch andere Webserver-Apps gibt, die ich gebrauchen könnte (die auch alle vornehmlich für MySQL dokumentiert sind), werde ich diese DB auch anstatt von PG verwenden.

Ok, der "Ausflug" in die PostgreSQL-Welt war sicherlich ne gute Erfahrung. Er hat mir aber auch meine Befürchtung bestätigt, dass das keine Datenbank ist, mit der man anfangen sollte, schon deswegen, weil sie im Internet (ausser der sehr guten Referenz) nicht sonderlich Berücksichtigung findet und entsprechende Beispiele fehlen. Und meine Wenigkeit funktioniert halt mit Learning By Doing.

Da ich durch den Datenbank-Exkurs nun sowieso schon den Faden bei meinem Programm-Projekt vollkommen verloren habe, werde ich heute Abend noch mal versuchen, Semantic Scuttle auf der SQLite ans laufen zu bekommen. Man gönnt sich ja sonst nichts !