Linux.postgresql

• mit der Version 9.2 hat PostgreSQL den Funktionsumfang von FirebirdSQL wohl überstiegen
• Ich denke über eine Migration der OrgaMon Datenhaltung zu PostgreSQL nach
• "A standby server can also be used for read-only queries", das könnte für Polyzalos interessant sein
• Eigentlich will ich nur den "Main" markieren, und dann weitere "Standby" hinzufügen
  • Optimaler Weise soll der Main darüber Auskunft geben können welche Standby angeschlossen sind
  • Clients können so, wenn sie wollen Ready-Only Sachen auf einen Standby deligieren
  • Ein Standby sollte Auskunft geben können wie groß der Gab zum "Main" ist
  • Es sollte gut dokumentiert sein, wie man einen "Standby" zum Main macht (Phase 1: alter "Main" macht nur noch Read-Only, Phase 2: "Standby" akzeptiert auch "Write" Transaktionen, Phase 3: alter "Main" geht offline)

Installation

Benötigte Pakete

#zypper install postgresql94-server postgresql94-docs pgadmin3
# systemctl enable postgresql
# systemctl start postgresql

apt install postgresql-all

#
# Passwort setzen "Linux"-User
# das Homeverzeichnis des postgres User ist /var/lib/postgresql/13/
#
passwd postgres

#
# Passwort setzen Alternative 2/2
#
pgsql -u postgres
psql -u postgres
sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'masterkey';

Netzwerk freischalten

sshd

• Fraglich: ev. muss bei AllowUsers in der /etc/ssh/sshd_config der User postgres hinzugefügt werden
  • noch testen!!!

postgresql

joe /etc/postgresql/13/main/pg_hba.conf

#
# für das Netzwerk hinzu das zugreifen darf
#

# IPV4
host    all             all   192.168.115.0/24        trust

# IPV6
host    all             all   2003:df:8708:6000::/56  trust

Interface freischalten

joe /etc/postgresql/13/main/postgresql.conf

#
# 
#
listen_addresses = '*'           # what IP address(es) to listen on;


#
# Dienst neu starten
#
systemctl restart postgresql

SQL Know how

SQL - Statements

#
# Einen "generator" erstellen
#
create sequence ARTIKEL_GID;

#
# Einen Datentyp erstellen
#
create domain DOM_REFERENCE as bigint;

#
# Tabelle erstellen
#
CREATE TABLE MWST (
 RID DOM_REFERENCE (NOT NULL?) PRIMARY KEY,
 VON_DATUM TIMESTAMP,
 BIS_DATUM TIMESTAMP,
 SATZ DOUBLE PRECISION NOT NULL,
 NAME VARCHAR(45),
 SYNC SMALLINT DEFAULT 1 );

#
# Trigger erstellen 1/2 
#
CREATE OR REPLACE FUNCTION fu_mwst_insert()
 RETURNS TRIGGER 
 LANGUAGE PLPGSQL
 AS
$$
BEGIN
 IF ((NEW.RID is NULL) OR (NEW.RID<1)) THEN
  NEW.RID = nextval('GLOBAL_GID');
 END IF;
 RETURN NEW;
END;
$$
;

#
# Trigger erstellen 2/2
#
CREATE TRIGGER MWST_TRI BEFORE INSERT ON MWST EXECUTE FUNCTION fu_mwst_insert();

#
# Tabelle mit Daten füllen
#
insert into MWST (RID,SATZ,NAME) VALUES (0,16,'SATZ1');

Admin Know How

#
# Cluster sichern
#
pg_dumpall -f out

#
# Cluster-Pfad neu erstellen
#
sudo -u postgres /usr/lib/postgresql/13/bin/initdb /var/lib/postgresql/13/main

Replikation

• der ganze Cluster wird repliziert
  • ein Cluster ist ein Verzeichnis mit dem eine Instanz des Datenbankservers exklusiv arbeitet
  • ein Cluster kann dann mehrere Datenbanken enthalten
  • das Cluster-Verzeichnis hat viele Unterverzeichnisse, und auch Config-Files, es ist schrecklich
• die "Replication" ist das gleichhalten der Cluster-Verzeichnisse
  • Ausnahme "standby.signal", eine Marker-Datei der nur im Standby existiert (schrecklich!)
• fraglich: muss es eine Rolle replication geben auf dem "Main"? Oder kann ich den "postgres" nehmen zum replizieren
• Um einen Standby in Betrieb zu nehmen muss man erst das Cluster-Verzeichnis auf den Standby übertragen
  • fraglich: warum ist das nicht der erste Schritt des Standby selbst?

• In den Default Einstellungen war es so, dass ein Standby wegen dem Fehler ...

2022-03-18 12:40:39.466 CET [342697] FATAL:  database system identifier differs between the primary and standby
2022-03-18 12:40:39.466 CET [342697] DETAIL:  The primary's identifier is 7076100876600299037, the standby's identifier is 7076381400005816800.

• ... nicht replizieren konnte, der Main jedoch brachte seine Transaktionen nicht zu ende (ich habe 10 Min. gewartet!)

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

• das ist natürlich ein NoGo, die Replication darf niemals das Risiko erhöhen dass auf dem Main etwas nicht gelingt
• Diese Synchrone Replikation gefährdet (unnötig) die Stabilität des main, in der Dokumentation steht "asynchron" ist der default! Komisch!
  • asynchron scheint es nur zu sein wenn der Standby online ist, das ist irre!!
• Vielmehr muss NACH der Transaktion das WAL dem Standby zur Verfügung gestellt werden
• Sonst müsste ja Netzwerk und Standby immer schneller und besser sein als der Main, was eine unglaubliche Ernergieverschwendung wäre
• Standbys sollten eigentlich ziemlich komplett schlafen wenn es keine load gibt
• Standbys sollten durchaus 15 Min brauchen dürfen um wieder uptodate zu sein

• bin kurz davor den Test mit postgresql 13 abzuberechen, da es zu viele Enttäuschungen gibt

Standby Inbetriebnahme

pg_basebackup -vR --format=tar --compress=9 -D backup3

touch /var/lib/postgresql/13/main/standby.signal

pg_basebackup -C

http://www.pgadmin.org/

Die Tabellen einer Datenbank findest du in

 Schemata
   public
     Tabellen

erste Schritte

su postgres
$ create user orgamon1 with password 'OrgaMon1';
$ create database orgamon1;
$ grant all on DATABASE orgamon1 to orgamon1;

Administration

über phpPgAdmin

pgAdmin

apt install build-essential libssl-dev libffi-dev libgmp3-dev virtualenv python-pip libpq-dev python-dev python3 nano

virtualenv -p python3 ~/pgadmin4
cd ~/pgadmin4
mkdir var
mkdir var/storage
mkdir var/sessions
source bin/activate
wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.30/pip/pgadmin4-4.30-py3-none-any.whl
pip3 install pgadmin4-4.30-py3-none-any.whl
joe lib/python3.9/site-packages/pgadmin4/config_local.py
deactivate
pip3 install pytz flask_babelex flask_login flask_mail flask_paranoid flask_security flask_sqlalchemy

Migration der Datenbank

• erst mit der Metadaten-Datei des OrgaMon eine UTF8-Datenbank erstellen
• mit einem selbst erstellten 'cp' Util erst alle Rohdaten mit 'insert', dann alle Referenzen per 'update'

Zugriff mit OrgaMon Desktop

über Zeos

Laden der Client Bibliothek unter Windows

• Den Pfad auf C:\Program Files(x86)\PostgreSQL\9.1\bin zu setzen brauchte keinen Erfolg.
• Den .exe zunächst mal in PostgreSQL\bin speichern, alle anderen Referenzen mussten durch den Developer-Install befriedigt werden
• Die Referenzen lauten im Einzelnen:

* LIBPQ.dll. 
* SSLEAY32.dll. 
* LIBEAY32.dll.
? WS2_32.dll. 
? NSI.dll. 
R MSVCR90.dll. 
* libintl-8.dll. 
* libiconv-2.dll. 
? WSOCK32.dll. 
? MSWSOCK.dll. 
? WSHTCPIP.dll. 
? WSHIP6.dll. 
? DNSAPI.dll. 
? WLIDNSP.dll. 
? IPHLPAPI.DLL. 
? WINNSI.DLL. 
? rasadhlp.dll. 
? fwpuclnt.dll.