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
<pre>
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
</pre>
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
CLS
REM Aus Prodat-Pfad ausfuehren
REM Erstellt einen DUMP aus Live sowie Erstellt Datenbank "DATENBANK_BKP_JJMMDD" oder 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)
REM Was soll erstellt werden
REM 1 für Backup
REM alles ausser 1 für Testdatenbank
SET /P Typ=Eingabe:
SET dumppath="D:\ProdatERP\PostgreSQL\dump\"
SET fartpath="D:\ProdatERP\PostgreSQL\pg96\bin\"
SET original_db=SPEKTRA
IF %Typ% EQU 1 THEN
(
SET neu_zu_erstellende_db=%original_db%_BKP_%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
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_%neu_zu_erstellende_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 = '%neu_zu_erstellende_db%' AND pid <> pg_backend_pid();" template1 postgres
REM eventuell.vorhandene DB loeschen
psql -h localhost -p 5432 -c "DROP DATABASE IF EXISTS ""%neu_zu_erstellende_db%""" template1 postgres
REM neue DB anlegen
psql -h localhost -p 5432 -c "CREATE DATABASE ""%neu_zu_erstellende_db%""" template1 postgres
REM wir muessen im PRODAT-PFAD stehen
psql -f ".\PSQL\0050 System\0 0 pg83-implicit-casts.sql" "%neu_zu_erstellende_db%" postgres
REM searchpath setzen
%fartpath%fart.exe -c "%dumppath%dump_%neu_zu_erstellende_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 "%neu_zu_erstellende_db%" -f "%dumppath%dump_%neu_zu_erstellende_db%.sql" -q -o _db_reload_msglog.txt 2> _db_reload_errorlog.txt
)
ELSE
(
SET neu_zu_erstellende_db=%original_db%-TEST
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_%neu_zu_erstellende_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 = '%neu_zu_erstellende_db%' AND pid <> pg_backend_pid();" template1 postgres
REM eventuell.vorhandene DB loeschen
REM Altest Testsystem Loeschen?
REM 1 für ja
REM alles ausser 1 für nein
SET /P loeschen=Eingabe:
IF loeschen EQU 1 THEN
(
psql -h localhost -p 5432 -c "DROP DATABASE IF EXISTS ""%neu_zu_erstellende_db%""" template1 postgres
)
ELSE
REM alte DB wird nicht geloescht
REM neue DB anlegen
psql -h localhost -p 5432 -c "CREATE DATABASE ""%neu_zu_erstellende_db%""" template1 postgres
REM wir muessen im PRODAT-PFAD stehen
psql -f ".\PSQL\0050 System\0 0 pg83-implicit-casts.sql" "%neu_zu_erstellende_db%" postgres
REM searchpath setzen
%fartpath%fart.exe -c "%dumppath%dump_%neu_zu_erstellende_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 "%neu_zu_erstellende_db%" -f "%dumppath%dump_%neu_zu_erstellende_db%.sql" -q -o _db_reload_msglog.txt 2> _db_reload_errorlog.txt
)
PAUSE
</pres>