PSQL Demo

@ECHO OFF
setlocal enabledelayedexpansion
set backup_folder=D:\backup_data
set file_name_one=%date:~0,4%-%date:~5,2%-%date:~8,2%-DMMKDB.dump
set file_name_two=%date:~0,4%-%date:~5,2%-%date:~8,2%-RDB_ShellGeom.sql
set file_name_thr=%date:~0,4%-%date:~5,2%-%date:~8,2%-RDB_ShellGeom_data
set host_name=localhost
set port_name=5432
set user_name=postgres
set db_name=DMMKDB
set folder_name=%date:~0,4%-%date:~5,2%-%date:~8,2%-shared_attachment

echo BACKUP DMMKDB
pg_dump --inserts -T "\"RDB_ShellGeom\"" -h !host_name! -p !port_name! -U !user_name! -Fc !db_name! > !backup_folder!\!file_name_one!
pg_dump -s -t "\"RDB_ShellGeom\"" -h !host_name! -p !port_name! -U !user_name! !db_name! > !backup_folder!\!file_name_two!
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "copy "\"RDB_ShellGeom\"" to '!backup_folder!\!file_name_thr!' Binary;"
echo BACKUP DMMKDB SUCCESS

echo BACKUP SHARED FOLDER
echo \test\ > !backup_folder!\list_of_exclusion.txt
echo \DeploymentPackage\ >> !backup_folder!\list_of_exclusion.txt
echo \HASPDriver\ >> !backup_folder!\list_of_exclusion.txt
mkdir !backup_folder!\!folder_name!
xcopy D:\shared_attachment !backup_folder!\!folder_name! /s /e /y /exclude:!backup_folder!\list_of_exclusion.txt
del !backup_folder!\list_of_exclusion.txt
echo BACKUP SHARED FOLDER SUCCESS

@ECHO OFF
setlocal enabledelayedexpansion
set backup_folder=D:\backup_data
set file_name_one=%date:~0,4%-%date:~5,2%-%date:~8,2%-DMMKDB.dump
set file_name_two=%date:~0,4%-%date:~5,2%-%date:~8,2%-RDB_ShellGeom.sql
set file_name_thr=%date:~0,4%-%date:~5,2%-%date:~8,2%-RDB_ShellGeom_data

set host_name=localhost
set port_name=5432
set user_name=postgres
set db_name=DMMKDBtest

echo DROPDB DMMKDBtest
dropdb -h !host_name! -p !port_name! -U !user_name! !db_name!
echo DROPDB DMMKDBtest SUCCESS
pause

echo CREATEDB DMMKDBtest
createdb -h !host_name! -p !port_name! -U !user_name! !db_name!
echo CREATEDB DMMKDBtest SUCCESS

echo CREATE EXTENSION
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "create extension "postgis";"
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "create extension ""uuid-ossp"";"
echo CREATE EXTENSION SUCCESS

echo RESTORE FROM DMMKDB
pg_restore -h !host_name! -p !port_name! -U !user_name! -d !db_name! !backup_folder!\!file_name_one!
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -f !backup_folder!\!file_name_two!
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "copy "\"RDB_ShellGeom\"" from '!backup_folder!\!file_name_thr!' Binary;"
echo RESTORE FROM DMMKDB SUCCESS

echo MODIFY TABLE RDB_AttachmentPathConfig
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "DELETE FROM ""RDB_AttachmentPathConfig"";"
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "INSERT INTO ""RDB_AttachmentPathConfig"" (""AttachmentPath"", ""Note"",""AttachmentType"") VALUES ('\\172.16.14.28\shared_attachment\test', 'shared folder','TFWL001');"
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "INSERT INTO ""RDB_AttachmentPathConfig"" (""AttachmentPath"", ""Note"",""AttachmentType"") VALUES ('\\172.16.14.28\shared_attachment\test\img_signature', 'The signature image','TFWL002');"
echo MODIFY TABLE RDB_AttachmentPathConfig SUCCESS

pause

@ECHO OFF
setlocal enabledelayedexpansion
set file_name=%date:~0,4%-%date:~5,2%-%date:~8,2%-DMMKDB_backup
set host_name=localhost
set port_name=5432
set user_name=postgres
set db_name=DMMKDBtest

echo BACKUP DMMKDB
pg_dump -h !host_name! -p !port_name! -U !user_name! !db_name! > F:\BACKUP_DMMKDB\!file_name!
echo BACKUP DMMKDB SUCCESS
pause

echo DROPDB DMMKDBtest
dropdb -h !host_name! -p !port_name! -U !user_name! !db_name!
echo DROPDB DMMKDBtest SUCCESS
pause

echo CREATEDB DMMKDBtest
createdb -h !host_name! -p !port_name! -U !user_name! !db_name!
echo CREATEDB DMMKDBtest SUCCESS
pause

echo CREATE EXTENSION
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "create extension "postgis";"
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "create extension ""uuid-ossp"";"
echo CREATE EXTENSION SUCCESS
pause

echo RESTORE FROM DMMKDB
psql -h !host_name! -p !port_name! -U !user_name! !db_name! < F:\BACKUP_DMMKDB\!file_name!
echo RESTORE FROM DMMKDB SUCCESS
pause

echo MODIFY TABLE RDB_AttachmentPathConfig
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "DELETE FROM ""RDB_AttachmentPathConfig"";"
psql -h !host_name! -p !port_name! -U !user_name! -d !db_name! -c "INSERT INTO ""RDB_AttachmentPathConfig"" (""AttachmentPath"", ""Note"") VALUES ('\\localhost\shared_attachment\test', 'shared folder');"
echo MODIFY TABLE RDB_AttachmentPathConfig SUCCESS
pause

@ECHO OFF
setlocal enabledelayedexpansion
set folder_name=D:\ftp_data\07_DrillMonthlyStatistics
set file_name=%date:~0,4%-%date:~5,2%-%date:~8,2%-DrillMonthlyStats.csv

set y=%date:~0,4%
set m=%date:~5,2%
set d=%date:~8,2%
set current_day=%y%-%m%-%d%
set /a m-=1
if %m%==0 set m=12&set /a y-=1
set last_day=%y%-%m%-%d%
rem echo !current_day!
rem echo !last_day!

psql -h localhost -p 5432 -U postgres -d DMMKDB -c "copy (select "\"RDB_DrillCollar\""."\"Name\"", "\"t_bi_worksite\""."\"sitename\"", "\"RDB_DrillCollar\""."\"X\"", "\"RDB_DrillCollar\""."\"Y\"", "\"RDB_DrillCollar\""."\"Z\"","\"RDB_DrillCollar\""."\"Length\"", "\"RDB_DrillCollar\""."\"Diameter\"", "\"RDB_DrillCollar\""."\"OpeningDate\"","\"RDB_DrillCollar\""."\"CompletedDate\"", "\"RDB_DrillCollar\""."\"Cataloger\"" from "\"RDB_DrillCollar\"", "\"t_bi_worksite\"" where "\"RDB_DrillCollar\""."\"IsDesign\"" = false and "\"RDB_DrillCollar\""."\"ApprovalFlag\"" = 1 and ("\"RDB_DrillCollar\""."\"VerTo\"" is null or "\"RDB_DrillCollar\""."\"VerTo\"" = 0) and "\"RDB_DrillCollar\""."\"VerFrom\"" >= (select min("\"VerNo\"") from "\"RDB_Version\"" where "\"Time\"" >= '!last_day!') and "\"RDB_DrillCollar\""."\"VerFrom\"" <= (select max("\"VerNo\"") from "\"RDB_Version\"" where "\"Time\"" <= '!current_day!')and "\"t_bi_worksite\""."\"siteid\"" = "\"RDB_DrillCollar\""."\"WorkPlaceID\"") to '!folder_name!\!file_name!' (format csv, delimiter ',', header true);"

rem pause