1

I have a single batch file that runs various psql and ogr2ogr (spatial/GIS) commands within PostgreSQL databases. I run identical commands across two identical databases (dev & test). I'd like to store my PostreSQL connection info in a single variable and call it to run my psql commands. That way I can simply comment out the connection (dev or test) which I'm not currently using.

The reason I'm using this workflow is because I also run ogr2ogr commands in the same script, which successfully uses a simply batch variable.

Scenario 1: Manually update connections. This works but is tedious to update

::Run commands in DEV Server/DB
psql U UserName -h 99.99.999.999 -d MyDB -c "some sql command1"
ogr2ogr command 1
psql U UserName -h 99.99.999.999 -d MyDB -c "some sql command2"
psql U UserName -h 99.99.999.999 -d MyDB -c "some sql command3"

::Run commands in TEST Server/DB
psql U UserName -h 88.88.888.888 -d MyDB -c "some sql command1"
ogr2ogr command 1
psql U UserName -h 88.88.888.888 -d MyDB -c "some sql command2"
psql U UserName -h 88.88.888.888 -d MyDB -c "some sql command3"

Scenarios 2: I would like to set DB connections in 1 one place. Then I can run all commands and switch between DBs by simply commenting out 1 line and re-running the batch.

\SET dbConnect "-U UserName -h 99.99.999.999 -d MyDevDB"
::\SET dbConnect "-U UserName -h 88.88.888.888 -d MyTestDB"

psql dbConnect -c "some sql command1"
ogr2ogr command 1
psql dbConnect -c "some sql command2"
psql dbConnect -c "some sql command3"

I've tried several options include the -v, set, and /set to no avail. I've researched these cases below, but am still struggling. Ideally I'd like to store and call the variables in one location, in one file.

psql passed variable How do I specify a password to psql non-interactively? PostgreSQL Connection URL

grizzle
  • 11
  • 2
  • It may not be the answer you are looking for but I suggest you might try some simple Python which should provide what you ask, and it would allow advancement in your setup – Slumdog Aug 03 '18 at 00:24
  • Did you try setting environment variables? It is the most upvoted answer in your second reference. – Łukasz Kamiński Aug 03 '18 at 10:32
  • @Slumdog. A python script may be the way to go if I can't stick to a single .bat file with my desired functionality. Thanks for the reply! – grizzle Aug 03 '18 at 16:09
  • @ŁukaszKamiński I did explore that option but wish to run this a standalone, single file instead of having a 2nd file to reference. Thanks for the reply! – grizzle Aug 03 '18 at 16:10

0 Answers0