Script to backup a PostgreSQL database

I have the following script to backup a PostgreSQL database which works fine on the old server. I upgraded to a new Windows 2012 R2 server and now it does not work as before. It works but it prompts me for the password when I execute the batch file

Any idea why it would not work on the new server?

@echo off
setlocal enabledelayedexpansion
REM The next line sets the following DayTime variables: DT_Day, DT_DayOfWeek, DT_Hour, DT_Minute, DT_Month, DT_Quarter, DT_Second, DT_WeekInMonth, DT_Year
for /f "delims=" %%a in ('wmic.exe Path Win32_LocalTime GET * /value') do (for /f "delims=" %%b in ("%%a") do set DT_%%b)
for %%a in (DT_Month DT_Day DT_Hour DT_Minute DT_Second) do (if !%%a! LSS 10 set %%a=0!%%a!)
set Timestamp=%DT_Year%%DT_Month%%DT_Day%_%DT_Hour%%DT_Minute%%DT_Second%
echo Timestamp: %Timestamp%
set PGPassFile=%APPDATA%\postgresql\pgpass.conf
REM xTuple/PostgreSQL Information
SET PGUSER=postgres
SET PGPASS=xxxxxxxx
SET PGHOST=localhost
SET PGBIN="C:\Program Files\PostgreSQL\9.5\bin"
SET BACKUPDES="C:\file_path\Backups"
REM formats can be custom/plain/tar
SET GLOBALS=globals-%Timestamp%.sql
for %%a in (%PGPASS%) do (>"%PGPassFile%" echo %PG_HOST%:%PG_PORT%:%PRODDB%:%PGUSER%:%%~a)
@ECHO Backing up globals to %GLOBALS%...
%pgbin%\pg_dumpall -U %PGUSER% -p %PGPORT% -g > %BACKUPDES%\%GLOBALS%
%pgbin%\pg_dump -U %PGUSER% -p %PGPORT% --format=%FORMAT% -C %PRODDB% > %BACKUPDES%\%BACKUPFILENAME%
REM del "%PGPassFile%" 

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dean ChafeeIT/InfoSec ManagerCommented:
Perhaps the "old server" had the credentials stored in Credential Manager and the new server does not.   Open Credential Manager on both and compare. I see that you are passing creds in the pg_dump call, but maybe there are credentials required for %BACKUPDES%  ?
GerhardpetAuthor Commented:
Windows Credentials Manager does not interact with PostgreSQL at all. The same script works fine on the old server.
GerhardpetAuthor Commented:
So it was a problem with the ph_hba.conf. I had identical entries on a working server and the new server. It did not work. I copied the pg_hba.conf file from the working server to the new server. The backup/pg_dump script is running now without prompting for a password.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GerhardpetAuthor Commented:
Solved the problem myself
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.