0

I'm trying to schedule a batch file to backup a database on postgres. However, the job gets stuck on status "r" with no indication of what's going on, however a 0kb SQL file is created. When I run the batch file manually, it executes perfectly fine. A normal insert statement runs fine

OS: Windows 10 postgres v11.4 pg admin v4.9

I'm running this on the host itself with superuser postgres

Contents of batch file:

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"

SET PGPATH=C:\"Program Files"\PostgreSQL\11\bin\
SET SVPATH=D:\pgsqlbak\
SET PRJDB=xxx
SET DBUSR=postgres
SET HOUR=%time:~0,2%%time:~3,2%%time:~6,2%
SET dtStamp1=0%time:~1,1%%time:~3,2%%time:~6,2% 
SET dtStamp2=%time:~0,2%%time:~3,2%%time:~6,2%
FOR /F "TOKENS=2,3,4 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%k%%j%%i
if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp1%) else (SET dtStamp=%dtStamp2%)

SET DBDUMP=%PRJDB%_%d%_%dtStamp%.sql
@ECHO OFF
%PGPATH%pg_dump -h 127.0.0.1 -p 5432 -U postgres %PRJDB% > %SVPATH%%DBDUMP%

pgpass:

127.0.0.1:5432:yyy:postgres:password
127.0.0.1:5432:xxx:postgres:password
127.0.0.1:5432:postgres:postgres:password
localhost:5432:yyy:postgres:password
localhost:5432:xxx:postgres:password
localhost:5432:postgres:postgres:password

Also would like to know how to kill a job that is running apart from restarting server

Any help on this will be greatly appreciated

Xlsx
  • 165
  • 1
  • 12
  • Hi Bankar. I had the same problem some time ago and it turned out that the status was showing `r` but in fact pgAgent wasn't running at all. It happens when the pgAgent application is unexpectedly terminated, which does not change the current job status. Check any memory failure and make sure that the database did not restart.. it also terminates pgAgent without warning ;) good luck! – Jim Jones Nov 21 '19 at 10:13
  • Thanks Jim, but for this case since everything is running locally, don't think that would be the case... I tried few times and same issue, it was a 1 min job only – Xlsx Nov 21 '19 at 10:15

0 Answers0