备份数据库脚本windows版本
@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"
set PGPASSWORD=password123!
SET PGPATH=D:\pg14\bin\pg_dump
SET SVPATH=D:\dbbackup\
SET PRJDB=water_resources_assess
SET DBUSR=postgres
SET DBROLE=postgres
FOR /F "TOKENS=1,2,3 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F "TOKENS=1,2,3 DELIMS=: " %%i IN ('TIME /T') DO SET t=%%i%%j%%k
SET DBDUMP=%PRJDB%_%d%_%t%.backup
@ECHO OFF
%PGPATH% -h localhost -p 5432 -U %DBUSR% --role %DBROLE% -w -F c -b -v -f %SVPATH%%DBDUMP% %PRJDB%
echo Backup Taken Complete %SVPATH%%DBDUMP%
pause
还原数据库命令
D:\pg14\bin >pg_restore.exe -U postgres -d hebwater0129 D:\dbbackup\hebwater_2023-01-29_0100.backup
导出pgsql表结构以及数据
pg_dump -h 192.168.100.60 -U postgres -p 5432 -d ceshidb -t public.dic_region --inserts >D:\dbbackup\dic_region.sql
还原表
psql -h 192.168.100.60 -U postgres -p 5432 -d water_resources_assess_zhengshi -f D:\haisen.sql