PostgreSQL command Automation in batch script

Question

I need to automate the below commands through batch job, is there a way to do it. Currently command prompt is stopping after executing the first step(psql -U nrgadmin -d enwdb). Inside the enwdb database, I need to manually execute the steps from begin to commit. Any help would be appreciated.

psql -U nrgadmin -d enwdb;

 begin;
 alter schema asset_enw rename to asset_enw_backup;
 alter schema asset_import rename to asset_enw;
 alter table enw.geom rename to geom_backup;
 alter table enw.geom_backup set schema asset_enw_backup;
 alter table import.geom set schema enw;
 commit;

Kaushik Nayak · Accepted Answer · 2018-04-07 14:55:52Z

5

Put your commands in a script file and use the -f option of psql while running it.

psql -U nrgadmin -d enwdb -f myscript.sql

Or, if you are in a Unix environment, you could put it all in a here document in a shell script and run the shell script.

#!/bin/sh
psql -U nrgadmin -d enwdb <<EOF
BEGIN;
alter schema asset_enw rename to asset_enw_backup;
#other statements
#--
COMMIT;
EOF

edited Apr 7, 2018 at 14:55

answered Apr 7, 2018 at 14:40

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anil Kumar Over a year ago

Thanks for you suggestion Kaushik. Amended the shell script based on your suggestion. It worked fine. Thank you.

Collectives™ on Stack Overflow

PostgreSQL command Automation in batch script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related