postgres, psql and a bat file

Question

I'm going around in circles and so need some help.

I want to be able to run an .sql against a database which is a scheduled task via a bat file. However when i run the bat file manually I get prompted for a password. I enter the password and then get told that....

"psql: FATAL: password authentication failed for user "(my windows login)"
'-h' is not recognised as an internal or external command, 
operable program or batch file.

At the moment my bat reads...

@echo off
"D:\Program Files (x86)\PostgreSQL\9.1\bin\psql.exe"
-h localhost -U postgres -d database_name -f D:/scripts/SQL/test.sql
pause

First thing, what cmd do i need to add to populate the password request

What am I doing wrong with the rest of the statement to get it to load the .sql

Thanks

This question is older, so in fact the other one is a "duplicate", but both have a goog answer here: http://stackoverflow.com/a/15593100/1801588 — Pavel V., Jun 04 '14 at 10:34

score 2 · Accepted Answer · edited Jun 13 '12 at 14:23

2

by adding this line to your config file (pg_hba.conf), you can tell postgres to allow local connections without authentication

local      <database>  <user>  trust

http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

edited Jun 13 '12 at 14:23

answered Jun 13 '12 at 14:18

bpgergo

15,669
5
44
68

local connections do not work in Windows ("local" means a connection through a unix domain socket) – Jun 13 '12 at 14:24
oops, I havent been on a Windows box for a while. And thanks for updating my link! – bpgergo Jun 13 '12 at 15:32
local is only for UNIX domain sockets! AFAIK it still won't work over TCP/IP – Janus Troelsen Sep 18 '12 at 14:37

score 1 · Answer 2 · answered Jun 13 '12 at 14:17

All that needs to go into a single line in your batch file (it seems you have two lines):

@echo off
"D:\Program Files (x86)\PostgreSQL\9.1\bin\psql.exe" -h localhost -U postgres -d database_name -f D:/scripts/SQL/test.sql
pause

To avoid the password prompt, set the environment variable PGPASSWORD before calling psql:

@echo off
setlocal
set PGPASSWORD=my_very_secret_password
"D:\Program Files (x86)\PostgreSQL\9.1\bin\psql.exe" -h localhost -U postgres -d database_name -f D:/scripts/SQL/test.sql
pause
endlocal

The setlocal/endlocal commands are there to make sure the variable is cleared after the batch file was executed.

To avoid having the password in plain text in the batch file you can create a pgpass.conf file that contains the password. For details please see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html

I'm getting the following error message when I run the batch as a windows administrator user "psql: FATAL: password authentication failed for user "postgres2. If i run the batch file under my normal windows login then the bat/sql runs. any ideas? — geomiles, Jun 14 '12 at 07:40

postgres, psql and a bat file

2 Answers2