531 lines
29 KiB
PowerShell
531 lines
29 KiB
PowerShell
#Requires -RunAsAdministrator
|
||
<#
|
||
.SYNOPSIS
|
||
Lexware DB Mirror – Vollstaendiges Installations-Script
|
||
Richtet die gesamte Replikations-Infrastruktur ein (Windows + Linux).
|
||
|
||
.NOTES
|
||
Ausfuehren als Administrator:
|
||
powershell -ExecutionPolicy Bypass -File "Lexware-DB-Mirror-INSTALL.ps1"
|
||
|
||
Was dieses Script tut:
|
||
1. Verzeichnisstruktur auf Windows anlegen
|
||
2. SSH-Key schreiben + Berechtigungen setzen
|
||
3. Alle Windows-Scripts schreiben (push-loop, push-dump, hba-watcher)
|
||
4. PostgreSQL: replication.conf (wal_level=logical) anlegen + Neustart
|
||
5. PostgreSQL-User lxreplica + lxdump anlegen
|
||
6. Publications auf allen 8 Datenbanken anlegen
|
||
7. Scheduled Tasks registrieren und starten
|
||
8. Firewall-Regel fuer Port 15432 pruefen
|
||
9. Linux: webhook.py deployen + starten
|
||
10. Linux: restore-latest.sh (mit Replikations-Guard) deployen
|
||
11. Linux: PostgreSQL max_logical_replication_workers erhoehen
|
||
12. Linux: Subscriptions anlegen
|
||
#>
|
||
|
||
Set-StrictMode -Off
|
||
$ErrorActionPreference = "Continue"
|
||
$utf8NoBom = [System.Text.UTF8Encoding]::new($false)
|
||
|
||
function Step([int]$n, [string]$text) {
|
||
Write-Host ""
|
||
Write-Host "[$n] $text" -ForegroundColor Yellow
|
||
}
|
||
function OK([string]$text) { Write-Host " OK: $text" -ForegroundColor Green }
|
||
function WARN([string]$text) { Write-Host " WARN: $text" -ForegroundColor Red }
|
||
|
||
Write-Host ""
|
||
Write-Host "================================================================" -ForegroundColor Cyan
|
||
Write-Host " Lexware DB Mirror - Installation" -ForegroundColor Cyan
|
||
Write-Host "================================================================" -ForegroundColor Cyan
|
||
|
||
# ================================================================
|
||
# KONFIGURATION – hier anpassen falls sich etwas aendert
|
||
# ================================================================
|
||
$baseDir = "C:\lexware-db-connect"
|
||
$pgBin = "C:\Program Files\Lexware\PostgreSql\17\Bin"
|
||
$dataDir = "C:\ProgramData\Lexware\LexwarePG\Data\current"
|
||
$hbaFile = "$dataDir\pg_hba.conf"
|
||
$logFile = "C:\Users\Administrator\Desktop\LexWare-DB-Mirror.log"
|
||
$sshKey = "$baseDir\ssh\id_rsa"
|
||
$linuxIp = "192.168.115.113"
|
||
$linuxHost = "root@$linuxIp"
|
||
$linuxDir = "/opt/lexware-dumps"
|
||
$pgPort = "15432"
|
||
$pgAdmin = "altertillattbruker"
|
||
$databases = @("f1","f2","lexkonto","lexkk","rk","lxoffice","lx","lxcatalog")
|
||
|
||
# ================================================================
|
||
# SCHRITT 1: Verzeichnisse
|
||
# ================================================================
|
||
Step 1 "Verzeichnisse anlegen"
|
||
foreach ($d in @("$baseDir\ssh","$baseDir\dumps","$baseDir\linux-connect")) {
|
||
New-Item -ItemType Directory -Force -Path $d | Out-Null
|
||
}
|
||
OK $baseDir
|
||
|
||
# ================================================================
|
||
# SCHRITT 2: SSH-Key
|
||
# ================================================================
|
||
Step 2 "SSH-Key einrichten"
|
||
|
||
$privateKey = @'
|
||
-----BEGIN OPENSSH PRIVATE KEY-----
|
||
b3BlbnNzaC1rZXktdjEAAAAABG5vbmUAAAAEbm9uZQAAAAAAAAABAAACFwAAAAdzc2gtcn
|
||
NhAAAAAwEAAQAAAgEA0MKbtecz0btBv91Rtsaiov4UYVJTtkM7FVqosiZz6ZeqoXj5wl9b
|
||
Zics60o6kpMM3EeOkLTP39oJWiRxra/9PjqvKT+4TDlIH3R9cBZo8n4u17le7wLLTk/0P6
|
||
iTj05d3TR1tBObRMuMAbFJM1b6YyTfV/kf5NY8t9IwvB2dUMCX7podvdtFjC5T4YM1qarC
|
||
1XaTKrNaj0YU2+IUKAfyDoA1gL45Q8K0lL1OukAq9eTlZiVPWdI5fAjy5z9EKiFxRUqZhE
|
||
hT4sJKGrRYBqYwjdAlwTXxbmdg6B57oarC5KwLb3f5ysVVXwpgsBqd5mB7FnyKPgaGAa7P
|
||
dE+183EbemmiWIO173JBDNArZhfy2LHW7VZkhkUBgZxW6rq3ugU1qDj+J9nmQucpyjUmMe
|
||
rKfFsBUcL4m5vvugEs8R8NiYUG13e8HkiieF4/+G/SRTgFfg8qQrXmz8AOXKzEdTQcfA5I
|
||
T8ps/fid/5KSogtQDJDUsLp8UcCOTG3tmpJjk1uxKjXkCY8hpJnvAHuiHZYsXS9icI+A5g
|
||
G0qMWL27rMdsQvKwiNleV/0sHjpTzwCqXuAUB06G9c1AxN5pS8yhyvl2qSZdy31Sqh5Ur5
|
||
RRPQh3tnDNucFboP1e/fKHbwf7jEb/2k5B3Z2cUYHwSKvfIZZpcO+8XRcBQWZS8fyRwjiA
|
||
EAAAdQ6y/Dcusvw3IAAAAHc3NoLXJzYQAAAgEA0MKbtecz0btBv91Rtsaiov4UYVJTtkM7
|
||
FVqosiZz6ZeqoXj5wl9bZics60o6kpMM3EeOkLTP39oJWiRxra/9PjqvKT+4TDlIH3R9cB
|
||
Zo8n4u17le7wLLTk/0P6iTj05d3TR1tBObRMuMAbFJM1b6YyTfV/kf5NY8t9IwvB2dUMCX
|
||
7podvdtFjC5T4YM1qarC1XaTKrNaj0YU2+IUKAfyDoA1gL45Q8K0lL1OukAq9eTlZiVPWd
|
||
I5fAjy5z9EKiFxRUqZhEhT4sJKGrRYBqYwjdAlwTXxbmdg6B57oarC5KwLb3f5ysVVXwpg
|
||
sBqd5mB7FnyKPgaGAa7PdE+183EbemmiWIO173JBDNArZhfy2LHW7VZkhkUBgZxW6rq3ug
|
||
U1qDj+J9nmQucpyjUmMerKfFsBUcL4m5vvugEs8R8NiYUG13e8HkiieF4/+G/SRTgFfg8q
|
||
QrXmz8AOXKzEdTQcfA5IT8ps/fid/5KSogtQDJDUsLp8UcCOTG3tmpJjk1uxKjXkCY8hpJ
|
||
nvAHuiHZYsXS9icI+A5gG0qMWL27rMdsQvKwiNleV/0sHjpTzwCqXuAUB06G9c1AxN5pS8
|
||
yhyvl2qSZdy31Sqh5Ur5RRPQh3tnDNucFboP1e/fKHbwf7jEb/2k5B3Z2cUYHwSKvfIZZp
|
||
cO+8XRcBQWZS8fyRwjiAEAAAADAQABAAACAFZBwuLjWBb1v5IOWYAjDPo576PSx4IMv3Hw
|
||
Vrndh5FiOH+lo9U7X2GTGE1UC2Wa2vp9mpuSCj5dMfYMDuiMSiAXUV7C1FyyYmmU0Wup5s
|
||
0jdClwj5hEWErQYISZG/dfkwsebO/uFf7T99KPNUbATo7+okYQSqxcFRSDBd4EgobmPSC6
|
||
j0VuP4tPbRtGArtLMlvPNbm0B9whQeckv91Wgx6YvQKoFrM31TOMEOaGMvoNDPgqvGHJqj
|
||
Tk4bDJBFpAHTRbQZlV5UtRqhrkn2aH7pH6Ck+OAWbz0ie2yLChBQxFRihVs2GkLcUqXY8G
|
||
QG37OYCuDtTYDzDM0S0m7nBbMNWgtJ82LdTT6VQMEVgff10q25kbLRmKJbhezUyyXSBfrk
|
||
i2644Udqnyqtai/JY9ubkiREJQSVBjd6VsG2iVPw3a0xoVdyazo9pwS6i44bjbH7qBbcJv
|
||
rTwpcfrOs8hBtVtGVIndm8iEvBdG4rwislS1w8cIJrw3VgAUhnULMUBmqP+kz0gRT7c/JG
|
||
5W6tPxy5nP3YchcOgdCOB5XKK/tmEsa5g1hpSTeEkqz5wAwEu1yCsiXBemoIDvxqHcFk6X
|
||
BWnJMpB6OQxVSzieet1lsWXTX+8AXfQjk1CvuO+Hmd/oRjDj2EACBw/bBjhdA/lELPqMKo
|
||
tfVYBmmJ6TwH1ivenNAAABAQCjMDP5ZW2DfmsswEyg6MEuuJSiEIZkc4hzShqIFlQGbILw
|
||
vOGfrcM2a4PBLT6u0fA8blklbFjVL7BUbIwykbftE9U1R6jMacrdubLB/4QRkD7Yxoa5DB
|
||
91ayK4SguI+0eFw8wDJSTmhxC4NHuVg+I4gRNCwGGdgf+9zP4g0lpA3el0LVDm4rOP1Jc+
|
||
2ZtTGA8f5vhfe98kZCIoWNIvc1e2DYkRvMMbvWMdCf65cfML9g+Ql7/xFMqcvLeyeSZVVN
|
||
FgBfup6EhjDHfVZ93R71+g1Rmph/dmvmbYpYdMrvyIQDy6ZhWrkcoyKYREHEDUtDmIh5dn
|
||
nyNBGXgBmnCG2sp1AAABAQD+ww8qrc66ivWwI7A935axxptpXRRl/cYW2p7PFbU59gMnbH
|
||
Ddw3AwsKqNIElE2mJlxeNm1++sSVI0cQlBi54Cwzcs0J5ic8NpCUmhm7GSZt4B8Yz3PXNV
|
||
UsXFsBpKeU977XBS1RW3JODh3AizjOLGFOLk5IQS8rhlNRlJ/MfN9gmngTPs+JVuupvONf
|
||
1WwBB6Q/pNZakH9HyTi8eX4e9jzj97pZBZlzq7LF9UeALSS0rZWiwjjUzD5kk72c+JYQ+7
|
||
lmnQR5xwk9A+TUzN834XK2MsZmdmzaigkhXIpyRy+7TnY7s/0Hr+myRH8jZWxFSRpvJyVA
|
||
knoPQ4wJOtw17TAAABAQDRxlHbrHanwHO9jjBsEONYtHn73qA6+CzPoo0shCDTivgXFAa3
|
||
31Z8iF6q7shXbwebsPly4vD15g8gD8OYHrETb/v9RgJHXMsil0z7UpdE66FMTeqH4Tmjq/
|
||
qTySjqjHKBK3D1xt9kgz6sO2fztUpoIb9e5Jy8LIx04uGQV6KSeRbglnaFvBuv3RxtlEI8
|
||
3/OyA9I01TfLopFjAgQBP/bNFLAhy+ocbjBwl2MZGx+fCSLQbWPi/DiFOam+a0l7vQTFTS
|
||
1S68vyqMQ2c/xGqz96nzoWs3pox9K7fBWdqbR/EFn1QmZEa7qXDFfMQfLvQGXC/hA/Wrpa
|
||
SwU0SMrbJgFbAAAAE2FkbWluaXN0cmF0b3JAVzJLMjUBAgMEBQYH
|
||
-----END OPENSSH PRIVATE KEY-----
|
||
'@
|
||
$publicKey = "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDQwpu15zPRu0G/3VG2xqKi/hRhUlO2QzsVWqiyJnPpl6qhePnCX1tmJyzrSjqSkwzcR46QtM/f2glaJHGtr/0+Oq8pP7hMOUgfdH1wFmjyfi7XuV7vAstOT/Q/qJOPTl3dNHW0E5tEy4wBsUkzVvpjJN9X+R/k1jy30jC8HZ1QwJfumh2920WMLlPhgzWpqsLVdpMqs1qPRhTb4hQoB/IOgDWAvjlDwrSUvU66QCr15OVmJU9Z0jl8CPLnP0QqIXFFSpmESFPiwkoatFgGpjCN0CXBNfFuZ2DoHnuhqsLkrAtvd/nKxVVfCmCwGp3mYHsWfIo+BoYBrs90T7XzcRt6aaJYg7XvckEM0CtmF/LYsdbtVmSGRQGBnFbqure6BTWoOP4n2eZC5ynKNSYx6sp8WwFRwvibm++6ASzxHw2JhQbXd7weSKJ4Xj/4b9JFOAV+DypCtebPwA5crMR1NBx8DkhPymz9+J3/kpKiC1AMkNSwunxRwI5Mbe2akmOTW7EqNeQJjyGkme8Ae6IdlixdL2Jwj4DmAbSoxYvbusx2xC8rCI2V5X/SweOlPPAKpe4BQHTob1zUDE3mlLzKHK+XapJl3LfVKqHlSvlFE9CHe2cM25wVug/V798odvB/uMRv/aTkHdnZxRgfBIq98hlmlw77xdFwFBZlLx/JHCOIAQ== administrator@W2K25"
|
||
|
||
[System.IO.File]::WriteAllText("$baseDir\ssh\id_rsa", $privateKey, $utf8NoBom)
|
||
[System.IO.File]::WriteAllText("$baseDir\ssh\id_rsa.pub", $publicKey, $utf8NoBom)
|
||
icacls "$baseDir\ssh\id_rsa" /inheritance:r /grant "NT AUTHORITY\SYSTEM:(F)" /grant "VORDEFINIERT\Administratoren:(F)" 2>&1 | Out-Null
|
||
OK "SSH-Key + Berechtigungen"
|
||
|
||
# ================================================================
|
||
# SCHRITT 3: Windows-Scripts schreiben
|
||
# ================================================================
|
||
Step 3 "Windows-Scripts schreiben"
|
||
|
||
[System.IO.File]::WriteAllText("$baseDir\hba-watcher.ps1", @'
|
||
$pgCtl = "C:\Program Files\Lexware\PostgreSql\17\Bin\pg_ctl.exe"
|
||
$dataDir = "C:\ProgramData\Lexware\LexwarePG\Data\current"
|
||
$hbaFile = "$dataDir\pg_hba.conf"
|
||
$ruleAll = "hostssl all lxreplica 192.168.115.113/32 scram-sha-256"
|
||
$ruleRep = "hostssl replication lxreplica 192.168.115.113/32 scram-sha-256"
|
||
while ($true) {
|
||
try {
|
||
$lines = [System.IO.File]::ReadAllLines($hbaFile)
|
||
$existing = $lines | Where-Object { $_ -match "lxreplica" }
|
||
$ok = ($existing.Count -eq 2) -and ($existing[0] -eq $ruleAll) -and ($existing[1] -eq $ruleRep)
|
||
if (-not $ok) {
|
||
$base = $lines | Where-Object { $_ -notmatch "lxreplica" }
|
||
$newLines = [System.Collections.Generic.List[string]]::new()
|
||
foreach ($line in $base) {
|
||
if ($line -match "192\.168\.115\.0/24") { $newLines.Add($ruleAll); $newLines.Add($ruleRep) }
|
||
$newLines.Add($line)
|
||
}
|
||
[System.IO.File]::WriteAllLines($hbaFile, $newLines, [System.Text.Encoding]::ASCII)
|
||
& $pgCtl reload -D $dataDir 2>&1 | Out-Null
|
||
}
|
||
} catch {}
|
||
Start-Sleep -Seconds 10
|
||
}
|
||
'@, $utf8NoBom)
|
||
|
||
[System.IO.File]::WriteAllText("$baseDir\push-dump.ps1", @'
|
||
$pgDump = "C:\Program Files\Lexware\PostgreSql\17\Bin\pg_dump.exe"
|
||
$pgCtl = "C:\Program Files\Lexware\PostgreSql\17\Bin\pg_ctl.exe"
|
||
$dataDir = "C:\ProgramData\Lexware\LexwarePG\Data\current"
|
||
$hbaFile = "$dataDir\pg_hba.conf"
|
||
$dumpDir = "C:\lexware-db-connect\dumps"
|
||
$linuxHost = "root@192.168.115.113"
|
||
$linuxDir = "/opt/lexware-dumps"
|
||
$sshKey = "C:\lexware-db-connect\ssh\id_rsa"
|
||
$logFile = "C:\Users\Administrator\Desktop\LexWare-DB-Mirror.log"
|
||
$lockFile = "C:\lexware-db-connect\push-dump.lock"
|
||
$maxLines = 500
|
||
$databases = @("f1","f2","lexkonto","lexkk","rk","lxoffice","lx","lxcatalog")
|
||
$utf8NoBom = [System.Text.UTF8Encoding]::new($false)
|
||
function Write-Log { param([string]$Message,[string]$Level="INFO")
|
||
$entry = "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] [$Level] $Message`n"
|
||
[System.IO.File]::AppendAllText($logFile,$entry,$utf8NoBom)
|
||
$c=[System.IO.File]::ReadAllLines($logFile); if($c.Count -gt $maxLines){[System.IO.File]::WriteAllLines($logFile,($c|Select-Object -Last $maxLines),$utf8NoBom)}
|
||
}
|
||
if(Test-Path $lockFile){$age=(Get-Date)-(Get-Item $lockFile).LastWriteTime;if($age.TotalMinutes -lt 10){exit 0};Remove-Item $lockFile -Force}
|
||
New-Item -ItemType File -Path $lockFile -Force | Out-Null
|
||
try {
|
||
New-Item -ItemType Directory -Force -Path $dumpDir | Out-Null
|
||
$ts=$( Get-Date -Format "yyyy-MM-dd_HH-mm"); $sw=[System.Diagnostics.Stopwatch]::StartNew()
|
||
Write-Log "===== Sync-Lauf gestartet ($ts) ====="
|
||
$tr="host all lxdump 127.0.0.1/32 trust"
|
||
$lines=Get-Content $hbaFile
|
||
if($lines -notmatch "lxdump"){
|
||
$nl=[System.Collections.Generic.List[string]]::new();$ins=$false
|
||
foreach($l in $lines){if(-not $ins -and $l -match "127\.0\.0\.1/32"){$nl.Add($tr);$ins=$true};$nl.Add($l)}
|
||
[System.IO.File]::WriteAllLines($hbaFile,$nl,[System.Text.Encoding]::ASCII)
|
||
& $pgCtl reload -D $dataDir 2>&1|Out-Null; Start-Sleep -Seconds 2
|
||
}
|
||
$env:PGSSLMODE="disable";$env:PGPASSWORD="";$env:PGCLIENTENCODING="LATIN1"
|
||
$sql=@'
|
||
DO $$BEGIN IF NOT EXISTS(SELECT 1 FROM pg_roles WHERE rolname='lxdump') THEN CREATE USER lxdump WITH SUPERUSER LOGIN; END IF; END$$;
|
||
'@
|
||
& "C:\Program Files\Lexware\PostgreSql\17\Bin\psql.exe" -h 127.0.0.1 -p 15432 -U altertillattbruker -d postgres -c $sql 2>&1|Out-Null
|
||
$errs=@();$ok=0
|
||
foreach($db in $databases){
|
||
$f="$dumpDir\${db}_${ts}.dump"
|
||
& $pgDump -h 127.0.0.1 -p 15432 -U lxdump -d $db -Fc -f $f 2>&1|Out-Null
|
||
if($LASTEXITCODE -ne 0){Write-Log " $db -> DUMP FEHLGESCHLAGEN" "ERROR";$errs+=$db;continue}
|
||
$sz=if(Test-Path $f){"{0:N1} MB" -f ((Get-Item $f).Length/1MB)}else{"?"}
|
||
& scp -i $sshKey -o StrictHostKeyChecking=no $f "${linuxHost}:${linuxDir}/${db}_${ts}.dump" 2>&1|Out-Null
|
||
if($LASTEXITCODE -eq 0){
|
||
try{Invoke-WebRequest -Uri "http://192.168.115.113:9055/restore?db=$db" -Method POST -UseBasicParsing -TimeoutSec 5|Out-Null}catch{}
|
||
Write-Log " $db -> OK ($sz)";$ok++
|
||
}else{Write-Log " $db -> SCP FEHLGESCHLAGEN ($sz)" "ERROR";$errs+=$db}
|
||
Remove-Item $f -Force
|
||
}
|
||
$cl=Get-Content $hbaFile|Where-Object{$_ -notmatch "^host\s+all\s+all\s+127\.0\.0\.1/32\s+trust"}
|
||
[System.IO.File]::WriteAllLines($hbaFile,$cl,[System.Text.Encoding]::ASCII)
|
||
& $pgCtl reload -D $dataDir 2>&1|Out-Null
|
||
& ssh -i $sshKey -o StrictHostKeyChecking=no root@192.168.115.113 "for db in f1 f2 lexkonto lexkk rk lxoffice lx lxcatalog; do ls -t /opt/lexware-dumps/\${db}_*.dump 2>/dev/null|tail -n +6|xargs -r rm -f; done" 2>&1|Out-Null
|
||
$sw.Stop();$el="{0:mm\:ss}" -f $sw.Elapsed
|
||
if($errs.Count -gt 0){Write-Log " Ergebnis: $ok/$($databases.Count) OK | Fehler: $($errs -join ', ') | Dauer: $el" "WARN"}
|
||
else{Write-Log " Ergebnis: $ok/$($databases.Count) OK | Alle synchronisiert | Dauer: $el"}
|
||
Write-Log "===== Sync-Lauf beendet ====="
|
||
} finally {
|
||
$fl=Get-Content $hbaFile -ErrorAction SilentlyContinue|Where-Object{$_ -notmatch "lxdump"}
|
||
if($fl){[System.IO.File]::WriteAllLines($hbaFile,$fl,[System.Text.Encoding]::ASCII);& $pgCtl reload -D $dataDir 2>&1|Out-Null}
|
||
Remove-Item $lockFile -Force -ErrorAction SilentlyContinue
|
||
}
|
||
'@, $utf8NoBom)
|
||
|
||
[System.IO.File]::WriteAllText("$baseDir\push-loop.ps1", @'
|
||
$script=$( "C:\lexware-db-connect\push-dump.ps1")
|
||
$logFile="C:\Users\Administrator\Desktop\LexWare-DB-Mirror.log"
|
||
$utf8NoBom=[System.Text.UTF8Encoding]::new($false)
|
||
$wait=120
|
||
function WL([string]$m){[System.IO.File]::AppendAllText($logFile,"[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] [LOOP] $m`n",$utf8NoBom)}
|
||
WL "Loop gestartet (Interval: ${wait}s nach Lauf-Ende)"
|
||
while($true){
|
||
$ok=$false
|
||
try{
|
||
$r=& ssh -i "C:\lexware-db-connect\ssh\id_rsa" -o StrictHostKeyChecking=no -o ConnectTimeout=5 root@192.168.115.113 "sudo -u postgres psql -d f1 -tAc 'SELECT COUNT(*) FROM pg_stat_subscription WHERE received_lsn IS NOT NULL;' 2>/dev/null"
|
||
$ok=([int]($r.Trim()) -ge 4)
|
||
}catch{}
|
||
if($ok){Start-Sleep -Seconds $wait;continue}
|
||
WL "Replikation inaktiv - starte Dump-Fallback"
|
||
$sw=[System.Diagnostics.Stopwatch]::StartNew()
|
||
& powershell.exe -NonInteractive -ExecutionPolicy Bypass -File $script
|
||
$sw.Stop();$el=[int]$sw.Elapsed.TotalSeconds
|
||
$sl=[Math]::Max(10,$wait-$el)
|
||
WL "Lauf beendet nach ${el}s - naechster Start in ${sl}s"
|
||
Start-Sleep -Seconds $sl
|
||
}
|
||
'@, $utf8NoBom)
|
||
|
||
OK "hba-watcher.ps1, push-dump.ps1, push-loop.ps1"
|
||
|
||
# ================================================================
|
||
# SCHRITT 4: PostgreSQL replication.conf + Neustart
|
||
# ================================================================
|
||
Step 4 "PostgreSQL WAL-Level auf logical setzen"
|
||
$replConf = "$dataDir\conf.d\replication.conf"
|
||
[System.IO.File]::WriteAllText($replConf, "wal_level = logical`nmax_replication_slots = 30`nmax_wal_senders = 10`n", [System.Text.Encoding]::ASCII)
|
||
Write-Host " Starte PostgreSQL neu (Lexware ~5 Sek nicht erreichbar)..." -ForegroundColor Gray
|
||
& "$pgBin\pg_ctl.exe" restart -D $dataDir -w 2>&1 | Out-Null
|
||
Start-Sleep -Seconds 6
|
||
OK "wal_level = logical in conf.d\replication.conf"
|
||
|
||
# ================================================================
|
||
# SCHRITT 5: Datenbankbenutzer + Publications
|
||
# ================================================================
|
||
Step 5 "Datenbankbenutzer und Publications anlegen"
|
||
|
||
$lines = Get-Content $hbaFile
|
||
$newLines = [System.Collections.Generic.List[string]]::new(); $ins = $false
|
||
foreach ($line in $lines) {
|
||
if (-not $ins -and $line -match "127\.0\.0\.1/32") { $newLines.Add("host all all 127.0.0.1/32 trust"); $ins = $true }
|
||
$newLines.Add($line)
|
||
}
|
||
[System.IO.File]::WriteAllLines($hbaFile, $newLines, [System.Text.Encoding]::ASCII)
|
||
& "$pgBin\pg_ctl.exe" reload -D $dataDir 2>&1 | Out-Null; Start-Sleep -Seconds 2
|
||
$env:PGSSLMODE = "disable"
|
||
|
||
& "$pgBin\psql.exe" -h 127.0.0.1 -p $pgPort -U $pgAdmin -d postgres -c "DO `$`$ BEGIN IF NOT EXISTS(SELECT 1 FROM pg_roles WHERE rolname='lxreplica') THEN CREATE USER lxreplica WITH REPLICATION LOGIN PASSWORD 'LxRepl2026!'; END IF; END`$`$;" 2>&1 | Out-Null
|
||
& "$pgBin\psql.exe" -h 127.0.0.1 -p $pgPort -U $pgAdmin -d postgres -c "DO `$`$ BEGIN IF NOT EXISTS(SELECT 1 FROM pg_roles WHERE rolname='lxdump') THEN CREATE USER lxdump WITH SUPERUSER LOGIN; END IF; END`$`$;" 2>&1 | Out-Null
|
||
|
||
foreach ($db in $databases) {
|
||
$r = & "$pgBin\psql.exe" -h 127.0.0.1 -p $pgPort -U $pgAdmin -d $db -c "CREATE PUBLICATION lx_pub FOR ALL TABLES;" 2>&1
|
||
Write-Host (" {0,-12} {1}" -f $db, (if ($r -match "already exists") {"bereits vorhanden"} else {"Publication angelegt"})) -ForegroundColor Green
|
||
}
|
||
|
||
$cl = Get-Content $hbaFile | Where-Object { $_ -notmatch "^host\s+all\s+all\s+127\.0\.0\.1/32\s+trust" }
|
||
[System.IO.File]::WriteAllLines($hbaFile, $cl, [System.Text.Encoding]::ASCII)
|
||
& "$pgBin\pg_ctl.exe" reload -D $dataDir 2>&1 | Out-Null
|
||
OK "lxreplica, lxdump, Publications"
|
||
|
||
# ================================================================
|
||
# SCHRITT 6: Scheduled Tasks
|
||
# ================================================================
|
||
Step 6 "Scheduled Tasks registrieren"
|
||
$principal = New-ScheduledTaskPrincipal -UserId "NT AUTHORITY\SYSTEM" -LogonType ServiceAccount -RunLevel Highest
|
||
$settings = New-ScheduledTaskSettingsSet -ExecutionTimeLimit (New-TimeSpan -Hours 0) -RestartCount 5 -RestartInterval (New-TimeSpan -Minutes 1) -MultipleInstances IgnoreNew
|
||
|
||
foreach ($task in @("LexwarePG-HBA-Watcher","LexwarePG-Push-Loop")) {
|
||
Unregister-ScheduledTask -TaskName $task -Confirm:$false -ErrorAction SilentlyContinue
|
||
}
|
||
|
||
$a1 = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-NonInteractive -ExecutionPolicy Bypass -WindowStyle Hidden -File `"$baseDir\hba-watcher.ps1`""
|
||
Register-ScheduledTask -TaskName "LexwarePG-HBA-Watcher" -Action $a1 -Trigger (New-ScheduledTaskTrigger -AtStartup) -Settings $settings -Principal $principal | Out-Null
|
||
Start-ScheduledTask -TaskName "LexwarePG-HBA-Watcher"
|
||
|
||
$a2 = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-NonInteractive -ExecutionPolicy Bypass -WindowStyle Hidden -File `"$baseDir\push-loop.ps1`""
|
||
Register-ScheduledTask -TaskName "LexwarePG-Push-Loop" -Action $a2 -Trigger (New-ScheduledTaskTrigger -AtStartup) -Settings $settings -Principal $principal | Out-Null
|
||
Start-ScheduledTask -TaskName "LexwarePG-Push-Loop"
|
||
OK "LexwarePG-HBA-Watcher + LexwarePG-Push-Loop gestartet"
|
||
|
||
# ================================================================
|
||
# SCHRITT 7: Firewall
|
||
# ================================================================
|
||
Step 7 "Firewall Port 15432"
|
||
if (-not (Get-NetFirewallRule -DisplayName "*15432*" -ErrorAction SilentlyContinue)) {
|
||
New-NetFirewallRule -DisplayName "PostgreSQL Lexware 15432" -Direction Inbound -Protocol TCP -LocalPort 15432 -Action Allow | Out-Null
|
||
OK "Regel neu angelegt"
|
||
} else { OK "Bereits vorhanden" }
|
||
|
||
# ================================================================
|
||
# SCHRITT 8: Linux – webhook.py deployen
|
||
# ================================================================
|
||
Step 8 "Linux: webhook.py deployen"
|
||
|
||
$webhookPy = @'
|
||
#!/usr/bin/env python3
|
||
import http.server, subprocess, threading, urllib.parse, logging, sys, os
|
||
PORT = 9055
|
||
RESTORE_SCRIPT = "/opt/lexware-dumps/restore-latest.sh"
|
||
LOG_FILE = "/var/log/lexware-restore.log"
|
||
VALID_DBS = {"f1","f2","lexkonto","lexkk","rk","lxoffice","lx","lxcatalog"}
|
||
logging.basicConfig(level=logging.INFO, format="%(asctime)s [webhook] %(message)s", datefmt="%Y-%m-%d %H:%M:%S",
|
||
handlers=[logging.FileHandler(LOG_FILE), logging.StreamHandler(sys.stdout)])
|
||
def run_restore(db):
|
||
env = os.environ.copy()
|
||
cmd = [RESTORE_SCRIPT]
|
||
if db: env["RESTORE_DB"] = db
|
||
logging.info(f"Starte Restore: db={db or 'alle'}")
|
||
result = subprocess.run(cmd, env=env, capture_output=True, text=True)
|
||
if result.stdout:
|
||
for line in result.stdout.strip().splitlines(): logging.info(line)
|
||
if result.returncode not in (0,1): logging.error(f"Restore exit {result.returncode}")
|
||
class H(http.server.BaseHTTPRequestHandler):
|
||
def log_message(self, fmt, *args): logging.info(f"{self.address_string()} {fmt%args}")
|
||
def do_POST(self):
|
||
p=urllib.parse.urlparse(self.path); q=urllib.parse.parse_qs(p.query)
|
||
if p.path!="/restore": self._r(404,"Not found"); return
|
||
db=q.get("db",[None])[0]
|
||
if db and db not in VALID_DBS: self._r(400,f"Unbekannte DB: {db}"); return
|
||
self._r(200,f"OK: db={db or 'alle'}")
|
||
threading.Thread(target=run_restore,args=(db,),daemon=True).start()
|
||
def do_GET(self):
|
||
if self.path=="/health": self._r(200,"OK")
|
||
else: self._r(404,"Not found")
|
||
def _r(self,code,body):
|
||
d=body.encode(); self.send_response(code)
|
||
self.send_header("Content-Type","text/plain"); self.send_header("Content-Length",str(len(d))); self.end_headers(); self.wfile.write(d)
|
||
if __name__=="__main__":
|
||
s=http.server.ThreadingHTTPServer(("0.0.0.0",PORT),H)
|
||
logging.info(f"Webhook-Server laeuft auf Port {PORT}")
|
||
s.serve_forever()
|
||
'@
|
||
|
||
$tmpWebhook = [System.IO.Path]::GetTempFileName() + ".py"
|
||
[System.IO.File]::WriteAllText($tmpWebhook, $webhookPy, $utf8NoBom)
|
||
& scp -i $sshKey -o StrictHostKeyChecking=no $tmpWebhook "${linuxHost}:${linuxDir}/webhook.py" 2>&1 | Out-Null
|
||
Remove-Item $tmpWebhook -Force -ErrorAction SilentlyContinue
|
||
|
||
# Webhook als Hintergrundprozess starten (falls nicht schon laufend)
|
||
& ssh -i $sshKey -o StrictHostKeyChecking=no $linuxHost "pkill -f 'webhook.py' 2>/dev/null; sleep 1; nohup python3 ${linuxDir}/webhook.py >> /var/log/lexware-restore.log 2>&1 &" 2>&1 | Out-Null
|
||
OK "webhook.py deployed und gestartet"
|
||
|
||
# ================================================================
|
||
# SCHRITT 9: Linux – restore-latest.sh deployen
|
||
# ================================================================
|
||
Step 9 "Linux: restore-latest.sh deployen"
|
||
|
||
$restoreSh = @'
|
||
#!/bin/bash
|
||
DUMP_DIR="/opt/lexware-dumps"
|
||
STAMP_DIR="${DUMP_DIR}/.last_restore"
|
||
LOG_FILE="/var/log/lexware-restore.log"
|
||
DATABASES="${RESTORE_DB:-f1 f2 lexkonto lexkk rk lxoffice lx lxcatalog}"
|
||
KEEP_DUMPS=5
|
||
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"; }
|
||
psql_c() { sudo -u postgres psql -v ON_ERROR_STOP=1 -c "$1" 2>>"$LOG_FILE"; }
|
||
exec 9>/var/lock/lexware-restore.lock
|
||
if ! flock -n 9; then log "SKIP Restore laeuft bereits"; exit 0; fi
|
||
mkdir -p "$STAMP_DIR"
|
||
|
||
restore_one() {
|
||
local db="$1" dump="$2" shadow="${1}_shadow"
|
||
psql_c "DROP DATABASE IF EXISTS \"${shadow}\";" 2>/dev/null
|
||
if ! psql_c "CREATE DATABASE \"${shadow}\" ENCODING 'UTF8' LC_COLLATE 'de_DE.UTF-8' LC_CTYPE 'de_DE.UTF-8' TEMPLATE template0;"; then
|
||
log "ERROR $db Shadow anlegen fehlgeschlagen"; return 1; fi
|
||
local tmplog=$(mktemp)
|
||
sudo -u postgres bash -c "PGCLIENTENCODING=LATIN1 pg_restore -d '${shadow}' '${dump}'" 2>"$tmplog"
|
||
local rc=$?; cat "$tmplog" >> "$LOG_FILE"
|
||
if [[ $rc -eq 1 ]]; then
|
||
local errs=$(grep "^pg_restore: error:" "$tmplog" | grep -v "encoding\|COPY failed\|does not exist.*role" | wc -l)
|
||
rm -f "$tmplog"
|
||
if [[ $errs -gt 0 ]]; then log "ERROR $db $errs kritische Fehler"; psql_c "DROP DATABASE IF EXISTS \"${shadow}\";" 2>/dev/null; return 1; fi
|
||
log "WARN $db Restore mit harmlosen Warnungen"
|
||
elif [[ $rc -gt 1 ]]; then rm -f "$tmplog"; log "ERROR $db exit $rc"; psql_c "DROP DATABASE IF EXISTS \"${shadow}\";" 2>/dev/null; return 1
|
||
else rm -f "$tmplog"; fi
|
||
log "SWAP $db"
|
||
psql_c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='${db}' AND pid<>pg_backend_pid();" 2>/dev/null
|
||
sleep 0.2
|
||
if ! psql_c "ALTER DATABASE \"${db}\" RENAME TO \"${db}_old\";"; then log "ERROR $db Rename fehlgeschlagen"; return 1; fi
|
||
if ! psql_c "ALTER DATABASE \"${shadow}\" RENAME TO \"${db}\";"; then log "ERROR $db Shadow-Rename fehlgeschlagen"; return 1; fi
|
||
( psql_c "DROP DATABASE IF EXISTS \"${db}_old\";" 2>/dev/null ) &
|
||
return 0
|
||
}
|
||
|
||
for db in $DATABASES; do
|
||
latest=$(ls -1 "${DUMP_DIR}/${db}_"*.dump 2>/dev/null | sort | tail -n 1)
|
||
[[ -z "$latest" ]] && { log "SKIP $db kein Dump"; continue; }
|
||
stamp_file="${STAMP_DIR}/${db}"; last_restore=""
|
||
[[ -f "$stamp_file" ]] && last_restore=$(cat "$stamp_file")
|
||
[[ "$latest" == "$last_restore" ]] && { log "SKIP $db bereits eingespielt"; continue; }
|
||
|
||
# Logical Replication Guard: ueberspringen wenn WAL aktiv
|
||
repl=$(sudo -u postgres psql -d "$db" -tAc "SELECT COUNT(*) FROM pg_stat_subscription WHERE subname='lx_sub_${db}' AND received_lsn IS NOT NULL;" 2>/dev/null | tr -d ' \t\n')
|
||
if [ "${repl:-0}" -gt "0" ]; then log "SKIP $db Logical Replication aktiv"; continue; fi
|
||
|
||
log "START $db $latest"
|
||
if restore_one "$db" "$latest"; then echo "$latest" > "$stamp_file"; log "OK $db"; fi
|
||
done
|
||
|
||
for db in $DATABASES; do
|
||
ls -t "${DUMP_DIR}/${db}_"*.dump 2>/dev/null | tail -n +$((KEEP_DUMPS+1)) | xargs -r rm -f
|
||
done
|
||
'@
|
||
|
||
$tmpRestore = [System.IO.Path]::GetTempFileName() + ".sh"
|
||
[System.IO.File]::WriteAllText($tmpRestore, $restoreSh, $utf8NoBom)
|
||
& scp -i $sshKey -o StrictHostKeyChecking=no $tmpRestore "${linuxHost}:${linuxDir}/restore-latest.sh" 2>&1 | Out-Null
|
||
& ssh -i $sshKey -o StrictHostKeyChecking=no $linuxHost "chmod +x ${linuxDir}/restore-latest.sh" 2>&1 | Out-Null
|
||
Remove-Item $tmpRestore -Force -ErrorAction SilentlyContinue
|
||
OK "restore-latest.sh deployed"
|
||
|
||
# ================================================================
|
||
# SCHRITT 10: Linux – PostgreSQL Worker-Config
|
||
# ================================================================
|
||
Step 10 "Linux: PostgreSQL max_logical_replication_workers"
|
||
& ssh -i $sshKey -o StrictHostKeyChecking=no $linuxHost @"
|
||
mkdir -p /etc/postgresql/18/main/conf.d
|
||
echo 'max_logical_replication_workers = 16' > /etc/postgresql/18/main/conf.d/replication.conf
|
||
echo 'max_worker_processes = 32' >> /etc/postgresql/18/main/conf.d/replication.conf
|
||
systemctl restart postgresql
|
||
sleep 5
|
||
"@ 2>&1 | Out-Null
|
||
OK "max_logical_replication_workers = 16"
|
||
|
||
# ================================================================
|
||
# SCHRITT 11: Linux – Subscriptions anlegen
|
||
# ================================================================
|
||
Step 11 "Linux: Subscriptions anlegen"
|
||
|
||
$createSubsSh = @'
|
||
#!/bin/bash
|
||
WINHOST="192.168.115.111"
|
||
echo "Droppe alte Subscriptions..."
|
||
for db in f1 f2 lexkonto lexkk rk lxoffice lx lxcatalog; do
|
||
sudo -u postgres psql -d "$db" -c "ALTER SUBSCRIPTION lx_sub_${db} DISABLE;" 2>/dev/null
|
||
sudo -u postgres psql -d "$db" -c "ALTER SUBSCRIPTION lx_sub_${db} SET (slot_name=NONE);" 2>/dev/null
|
||
sudo -u postgres psql -d "$db" -c "DROP SUBSCRIPTION IF EXISTS lx_sub_${db};" 2>/dev/null
|
||
done
|
||
echo "Lege Subscriptions neu an (copy_data=false)..."
|
||
for db in f1 f2 lexkonto lexkk rk lxoffice lx lxcatalog; do
|
||
echo "=== $db ==="
|
||
sudo -u postgres psql -d "$db" <<EOF
|
||
CREATE SUBSCRIPTION lx_sub_${db}
|
||
CONNECTION 'host=${WINHOST} port=15432 user=lxreplica password=LxRepl2026! sslmode=require dbname=${db}'
|
||
PUBLICATION lx_pub
|
||
WITH (copy_data = false);
|
||
EOF
|
||
done
|
||
echo ""
|
||
echo "=== Status ==="
|
||
sudo -u postgres psql -d f1 -c "SELECT subname, subenabled FROM pg_subscription ORDER BY subname;"
|
||
'@
|
||
|
||
$tmpSubs = [System.IO.Path]::GetTempFileName() + ".sh"
|
||
[System.IO.File]::WriteAllText($tmpSubs, $createSubsSh, $utf8NoBom)
|
||
& scp -i $sshKey -o StrictHostKeyChecking=no $tmpSubs "${linuxHost}:${linuxDir}/create_subs.sh" 2>&1 | Out-Null
|
||
Remove-Item $tmpSubs -Force -ErrorAction SilentlyContinue
|
||
|
||
Write-Host " Fuehre Subscriptions-Script auf Linux aus..." -ForegroundColor Gray
|
||
$subsResult = & ssh -i $sshKey -o StrictHostKeyChecking=no $linuxHost "bash ${linuxDir}/create_subs.sh 2>&1"
|
||
$subsResult | ForEach-Object { Write-Host " $_" -ForegroundColor Gray }
|
||
|
||
# ================================================================
|
||
# ABSCHLUSS + PRUEFUNG
|
||
# ================================================================
|
||
Write-Host ""
|
||
Write-Host "================================================================" -ForegroundColor Cyan
|
||
Write-Host " Pruefung" -ForegroundColor Cyan
|
||
Write-Host "================================================================" -ForegroundColor Cyan
|
||
|
||
Start-Sleep -Seconds 12
|
||
|
||
# HBA-Regeln
|
||
$hbaCheck = Get-Content $hbaFile | Select-String "lxreplica"
|
||
Write-Host (" pg_hba.conf lxreplica-Regeln: {0}" -f $hbaCheck.Count) -ForegroundColor (if ($hbaCheck.Count -eq 2) {"Green"} else {"Red"})
|
||
|
||
# Tasks
|
||
Get-ScheduledTask | Where-Object { $_.TaskName -like "LexwarePG*" } | ForEach-Object {
|
||
Write-Host (" Task {0,-30} {1}" -f $_.TaskName, $_.State) -ForegroundColor Green
|
||
}
|
||
|
||
# Replikation
|
||
Start-Sleep -Seconds 15
|
||
$replCheck = & ssh -i $sshKey -o StrictHostKeyChecking=no -o ConnectTimeout=10 $linuxHost `
|
||
"sudo -u postgres psql -d f1 -tAc 'SELECT COUNT(*) FROM pg_stat_subscription WHERE received_lsn IS NOT NULL;' 2>/dev/null" 2>&1
|
||
$replCount = [int]($replCheck.Trim())
|
||
Write-Host (" Aktive Subscriptions mit WAL: {0}/8" -f $replCount) -ForegroundColor (if ($replCount -ge 7) {"Green"} else {"Yellow"})
|
||
|
||
# Webhook
|
||
$webhookOk = & curl.exe -s --max-time 5 "http://${linuxIp}:9055/health" 2>$null
|
||
Write-Host (" Webhook /health: {0}" -f $webhookOk) -ForegroundColor (if ($webhookOk -eq "OK") {"Green"} else {"Red"})
|
||
|
||
Write-Host ""
|
||
Write-Host "================================================================" -ForegroundColor Cyan
|
||
Write-Host " Fertig!" -ForegroundColor Cyan
|
||
Write-Host " Log: $logFile" -ForegroundColor White
|
||
Write-Host " Doku: C:\Users\Administrator\Desktop\Lexware-DB-Mirror-Dokumentation.md" -ForegroundColor White
|
||
Write-Host "================================================================" -ForegroundColor Cyan
|
||
Write-Host ""
|