File . bat to generate backup and Restore in Postgresql

Question

I am creating . bat files to backup and restore a database in Postgresql using windows 10 and pg 9.4

I perform the following command to perform the backup

set PGUSER=postgres
set PGPASSWORD=postgres123

"C:/Program Files/PostgreSQL/9.4/bin\"pg_dump.exe --host localhost --port 5432 --format custom --blobs --verbose --file "D:\bkp.sql" "dbsibcom"

it works perfectly.

And to perform the Store I have the following command

set PGUSER=postgres

set PGPASSWORD=postgres123
"C:/Program Files/PostgreSQL/9.4/bin\pg_restore.exe -i -h localhost -p 5432 -c -d "testrestore" -v " D:\bkp.sql"

I create the bat file and try to restore, it just opens and closes the dos screen quickly and doesn’t work.

There is something wrong or another method of doing Restore ?

I have to give some permission for pg to have access to my backup file or something like that?

Editing

---

I also tried these commands to try to perform Restore and it doesn’t work

set PGUSER=postgres
set PGPASSWORD=postgres123 

C:/Program Files/PostgreSQL/9.4/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "testrestore"   --verbose "D:\bkp.sql"

Answer 1

Considering:

• The name of the database as: minha_database
• The backup file as: C:\bkp\backup_database.dump
• Installation of Postgresql 9.1 in C:\Program Files (x86)\PostgreSQL

To back up:

C:\Progra~2\PostgreSQL\9.1\bin\pg_dump -h servidor -p 5432 -U postgres --inserts -c -f C:\bkp\backup_database.dump minha_database

To do the Store:

C:\Progra~2\PostgreSQL\9.1\bin\psql -U postgres -d minha_database -f C:\bkp\backup_database.dump

• You can keep command set PGPASSWORD=postgres123 not to ask for the password and automate the execution.

• You can put a commando pause at the end of .bat to see which error occurs.

• In your environment, the bin folder path will be: C:\Progra~1\PostgreSQL\9.4\bin\. Use this path to place commands.

• The dump file will be generated in sql format, and in plain text, without any protection / encryption.

Answer 2

Backup script

Windows 

@Echo Off
 @For /F "tokens=1,2,3,4 delims=/ " %%A in ('Date /t') do @(
 Set DayW=%%A
 Set Day=%%B
 Set Month=%%C
 Set Year=%%D
 Set All=%%A_%%B_%%C_%%D
 )
 @For /F "tokens=1,2,3 delims=:,. " %%A in ('echo %time%') do @(
 Set Hour=%%A
 Set Min=%%B
 Set Sec=%%C
 Set Allm=%%A.%%B.%%C
 )
 @For /F "tokens=3 delims=: " %%A in ('time /t ') do @(
 Set AMPM=%%A
 )
set datestr=%All%%Allm%%AMPM%
echo datestr is %datestr%

set BACKUP_FILE=E:\BACKUP\WMSBACKUP_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=suasenha
echo on
cd "\Program Files\PostgreSQL\10\bin\"
pg_dump.exe -h localhost -U postgres -p 5432 wms > %BACKUP_FILE% 

pause

Answer 3

@echo off

rem Coloca o nome do banco aqui

set banco=

REM Senha da base de dados

SET PGPASSWORD=

rem Caminho da pasta do backup

set pastabk=D:\backup_sistema

rem Caminho do pg_dump

set pg_dump=C:\Program Files\PostgreSQL\9.3\bin

rem Unidade do HD EXTERNO

set pendrive=G:\

rem Nome do Servidor

set servidor=

if not exist %pastabk% (mkdir %pastabk%)

color 1f

Title BACKUP SISTEMA

echo ----------------------------------------------------------------------------

echo -----------------------Backup de banco de dados-----------------------


echo ----------------------------------------------------------------------------

echo.

set dia=%date:~0,2%%date:~3,2%%date:~6,4%_%time:~0,2%%time:~3,2%

set dia=%dia: =%

echo Banco de Dados.: %banco%

echo.

cd\

CD %pg_dump%

pg_dump.exe -h %servidor% -p 5432 -U postgres -F c -b -v -f 
"%pastabk%\%banco%%dia%.backup" %banco%

echo.

echo ----------------------------------------------------------------------------

echo ----------------------------------------------------------------------------

echo.


if exist %pastabk%\%banco%%dia%.backup (

    echo Backup Concluido... 

    echo Criado no arquivo: "%pastabk%\%banco%%dia%.backup".

    echo.

    if exist %pendrive% (

        echo Copiando arquivo para dispositivo removivel.

        if not exist %pendrive%\BancosBk\ (md %pendrive%BancosBk\)
 
        copy "%pastabk%\%banco%%dia%.backup" 
"%pendrive%\BancosBk\%banco%%dia%.backup"

        echo.

        echo Backup copiado para dispositivo removivel.

        echo Copiando em %pendrive%\BancosBk\%banco%%dia%.backup

    ) else (echo Dispositivo removivel nao encontrado.)

) else (echo Não foi possivel concluir o backup.)


echo.

pause