Programme (pg_dump, pg_dumpall, createdb, psql) erreichbar unter ..\PostgreSQL\pg9.6\pg96\bin
1. Umzug einer Datenbank (pg_dump)¶
Backup¶
1. Dump erstellen
pg_dump -h localhost -p 5432 -U postgres -d DATABASE -O -f dump_DB.sql --disable-triggers- Hinweis: Bei Anpassungen unten das Batch-File-Bsp anpassen!
- ErläuterungenErläuterungen
-h -p -U sind Verbindungsoptionen: Host, Port, Benutzer für den dump-d ist die best. Datenbank-O keine Objekteigentümerschaft ausgeben-f Dateiname--disable-triggers Trigger bei Wiederherstellung deaktivieren.
2. search-path in Dump-Datei korrigieren, siehe #9521:
3. ggf. Rollen exportieren (wenn Wiederherstellung auf anderem DB-Cluster erfolgt)
pg_dumpall -h localhost -p 5432 -U postgres -r -f dump_roles.sql
Wiederherstellung¶
1. Datenbank erzeugen
createdb -h localhost -p 5432 -U postgres NEUE-DB
2. implicit casts erzeugen
- implicit-casts werden nicht mit importiert:
..\PSQL\0050 System\0 0 pg83-implicit-casts.sql muss davor oder danach auf der DB ausgeführt werden.
3. ggf. Rollen importieren (wenn Wiederherstellung auf anderem DB-Cluster erfolgt)
psql -h localhost -p 5432 -U postgres -f dump_roles.sql -q
4. Datenbank wiederherstellen
psql -h localhost -p 5432 -U postgres -d NEUE-DB -f dump_DB.sql -q -o db_reload_msglog.txt 2> db_reload_errorlog.txt- ErläuterungenErläuterungen
- Verbindungsoptionen, s.o.
- Vollständiges oben erstelltes Skript wird ausgeführt.
- Datenbankname muss angegeben werden, sonst wird in System-DB "postgres" geschrieben
- -q quiet (ist schneller)
- -o Anfrageergebnisse in Datei (ist schneller)
- 2> Fehler in Datei (ist schneller)
2. Kopie innerhalb des Clusters¶
1. Verbindungen abwürgen
- SQLSQL
-- KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'SOURCE_DB'
AND pid <> pg_backend_pid();
2. direkte Kopie erstellen
CREATE DATABASE "SOURCE_DB_YYYYMMDD" WITH TEMPLATE "SOURCE_DB";
3. Prodat-Dienst neu starten.
3. Umzug des kompletten Clusters (pg_dumpall)¶
Backup¶
1. Dumps erstellen
Schema: pg_dumpall -h localhost -p 5432 -U postgres -O -s -f dumpall_schema.sql --disable-triggers
Daten : pg_dumpall -h localhost -p 5432 -U postgres -O -a -f dumpall_daten.sql --disable-triggers
- Alle Rollen werden geschrieben
- Alle Datenbanken werden mit CREATE und CONNECT ins Skript geschrieben.
- Schema und Daten getrennt, damit implicit-casts eingespielt werden können.
2. search-path in Dump-Datei korrigieren, siehe #9521:
- https://sourceforge.net/projects/fart-it/
fart.exe -c dumpall_schema.sql "SELECT pg_catalog.set_config('search_path', '', false);" "SELECT pg_catalog.set_config('search_path', 'public, TSystem, prodat_languages, z_99_deprecated', false);"fart.exe -c dumpall_daten.sql "SELECT pg_catalog.set_config('search_path', '', false);" "SELECT pg_catalog.set_config('search_path', 'public, TSystem, prodat_languages, z_99_deprecated', false);"
Wiederherstellen¶
1. Strukturen anlegen:
psql -h localhost -p 5432 -U postgres -f dumpall_schema.sql -q -o msglog_schema.txt 2> errorlog_schema.txt
2. implicit-casts erzeugen:
- implicit-casts werden nicht mit importiert:
..\PSQL\0050 System\0 0 pg83-implicit-casts.sql muss davor oder danach auf allen DBs ausgeführt werden.
3. Daten wiederherstellen:
psql -h localhost -p 5432 -U postgres -f dumpall_daten.sql -q -o msglog_daten.txt 2> errorlog_daten.txt- Alle Rollen werden erzeugt
- Alle Datenbanken werden erzeugt
4. Batch-File-Bsp: Dump und Kopie wieder einspielen¶
alte Variante von DSalte Variante von DS
CLS
REM Aus Prodat-Pfad ausführen
REM Erstellt einen DUMP aus Live sowie Erstellt Datenbank "LOLL-TEST" (falls vorhanden vorheriger Drop)
REM Im Standard werden große unnötige Log Tabellen, sowie RecnoKeyword ausgelassen. mit REM auskommentieren (--exclude-table-data=tlog.audit_log)
SET dumppath=F:\ProdatSQL\DELETE\
SET ORIGDB=KUNDE-LIVE
SET TESTDB=KUNDE-TEST
PAUSE
del _db_reload_msglog.txt
del _db_reload_errorlog.txt
REM dump ausführen gemäß Wiki
pg_dump -h localhost -p 5432 -U postgres -d "%ORIGDB%" -O -f "%dumppath%dump_DB.sql" --disable-triggers --exclude-table-data=tlog.auditlog --exclude-table-data=public.lagerlog --exclude-table-data=public.stvtrs --exclude-table-data=public.opreg --exclude-table-data='tcache.tformass*' --exclude-table-data=public.recnokeyword
REM beliebige Taste um fortzufahren: alte DEMO drop und neuen Dump einspielen. Zum Abbrechen (alte DEMO NICHT Droppen) jetzt Schliessen ODER STRG+C
PAUSE
REM Evtl vorhandene Connection killen
psql -h localhost -p 5432 -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%TESTDB%' AND pid <> pg_backend_pid();" template1 postgres
REM vorhandene DB löschen
psql -h localhost -p 5432 -c "DROP DATABASE IF EXISTS ""%TESTDB%""" template1 postgres
REM neue DB anlegen
psql -h localhost -p 5432 -c "CREATE DATABASE ""%TESTDB%""" template1 postgres
REM wir müssen im PRODAT-PFAD stehen
psql -f ".\PSQL\0050 System\0 0 pg83-implicit-casts.sql" "%TESTDB%" postgres
REM nicht mehr nötig ab 19.04
REM fart.exe -c "F:\ProdatSQL\DELETE\dump_DB.sql" "SELECT pg_catalog.set_config('search_path', '', false);" "SELECT pg_catalog.set_config('search_path', 'public, TSystem, prodat_languages, z_99_deprecated', false);"
REM reload dump
psql -h localhost -p 5432 -U postgres -d "%TESTDB%" -f "%dumppath%dump_DB.sql" -q -o _db_reload_msglog.txt 2> _db_reload_errorlog.txt
PAUSE
COLOR 9F
CLS
REM Aus Prodat-Pfad ausfuehren
REM Erstellt Eine Backupdatenbank "DATENBANK_BKP_JJMMDD" oder eine neue Testdatenbank DATENBANK-Test (falls vorhanden vorheriger Drop)
REM Im Standard werden bei Testdatenbanken grosse unnoetige Log Tabellen, sowie die RecnoKeyword ausgelassen. mit REM in der batch auskommentieren wenn nicht gewuenscht (--exclude-table-data=tlog.audit_log)
REM Was soll erstellt werden
SET /P Typ=Eingabe(1 fuer Backup, alles ausser 1 fuer Testdatenbank):
SET dumppath=Y:\temp\dump\
SET fartpath=Y:\ProdatSQL\
SET original_db=PRODAT-DEMO
REM Her nichts anpassen
SET neue_sicherungs_db=%original_db%_BKP_%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
SET neue_test_db=%original_db%-TEST
SETLOCAL EnableDelayedExpansion
REM Sicherungsdatenbank
IF "%Typ%" == "1" (
PAUSE
del _db_reload_msglog.txt
del _db_reload_errorlog.txt
REM Dump der einzelnen Datenbank ausfuehren nach Beschreibung im Wiki.
pg_dump -h localhost -p 5432 -U postgres -d "%original_db%" -O -f "%dumppath%dump_%neue_sicherungs_db%.sql" --disable-triggers
REM beliebige Taste um fortzufahren: ggF eine vorhanden Sicherung mit gleicher Bezeichnung enfernen(drop) zum Abrechen jetzt Schliessen ODER STRG+C
PAUSE
REM eventuell vorhandene Connection killen
psql -h localhost -p 5432 -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%neue_sicherungs_db%' AND pid <> pg_backend_pid();" template1 postgres
REM eventuell.vorhandene DB loeschen
psql -h localhost -p 5432 -c "DROP DATABASE IF EXISTS ""%neue_sicherungs_db%""" template1 postgres
REM neue DB anlegen
psql -h localhost -p 5432 -c "CREATE DATABASE ""%neue_sicherungs_db%""" template1 postgres
REM wir muessen im PRODAT-PFAD stehen
psql -f ".\PSQL\0050 System\0 0 pg83-implicit-casts.sql" "%neue_sicherungs_db%" postgres
REM searchpath setzen
%fartpath%fart.exe -c "%dumppath%dump_%neue_sicherungs_db%.sql" "SELECT pg_catalog.set_config('search_path', '', false);" "SELECT pg_catalog.set_config('search_path', 'public, TSystem, prodat_languages, z_99_deprecated', false);"
REM einspielen des Dumps
psql -h localhost -p 5432 -U postgres -d "%neue_sicherungs_db%" -f "%dumppath%dump_%neue_sicherungs_db%.sql" -q -o _db_reload_msglog.txt 2> _db_reload_errorlog.txt
) ELSE (
REM TestDatenbank
PAUSE
del _db_reload_msglog.txt
del _db_reload_errorlog.txt
REM Dump der einzelnen Datenbank ausfuehren nach Beschreibung im Wiki.
pg_dump -h localhost -p 5432 -U postgres -d "%original_db%" -O -f "%dumppath%dump_%neue_test_db%.sql" --disable-triggers --exclude-table-data=tlog.auditlog --exclude-table-data=public.lagerlog --exclude-table-data=public.stvtrs --exclude-table-data=public.opreg --exclude-table-data='tcache.tformass*' --exclude-table-data=public.recnokeyword
REM beliebige Taste um fortzufahren: ggF eine vorhanden Sicherung mit gleicher Bezeichnung enfernen(drop) zum Abrechen jetzt Schliessen ODER STRG+C
PAUSE
REM eventuell vorhandene Connection killen
psql -h localhost -p 5432 -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%neue_test_db%' AND pid <> pg_backend_pid();" template1 postgres
REM eventuell.vorhandene DB loeschen
REM Altest Testsystem Loeschen?
REM mit ja bestätigen
SET /P loeschen=Loeschen der alten Test-DB mit 1 bestaetigen:
IF "%loeschen%"=="1" (
psql -h localhost -p 5432 -c "DROP DATABASE IF EXISTS ""%neue_test_db%""" template1 postgres
) ELSE (
REM alte DB wird nicht geloescht
)
REM neue DB anlegen
psql -h localhost -p 5432 -c "CREATE DATABASE ""%neue_test_db%""" template1 postgres
REM wir muessen im PRODAT-PFAD stehen
psql -f ".\PSQL\0050 System\0 0 pg83-implicit-casts.sql" "%neue_test_db%" postgres
REM searchpath setzen
%fartpath%fart.exe -c "%dumppath%dump_%neue_test_db%.sql" "SELECT pg_catalog.set_config('search_path', '', false);" "SELECT pg_catalog.set_config('search_path', 'public, TSystem, prodat_languages, z_99_deprecated', false);"
REM einspielen des Dumps
psql -h localhost -p 5432 -U postgres -d "%neue_test_db%" -f "%dumppath%dump_%neue_test_db%.sql" -q -o _db_reload_msglog.txt 2> _db_reload_errorlog.txt
)
PAUSE
</pres>