Bash script, remote psql command over ssh, pipe sql file, password fails

Question

I have a bash script. I want to run a postgres command with ssh that pipes a local file. The problem is the psql command prompts for a password, and my sql file gets piped into that. How do I write a command that pipes after I type in the password?

ssh server "psql -W -h db_host -p 5432 -U db_user -d postgres" < staging.sql

hek2mgl · Accepted Answer · 2022-01-03 22:28:38Z

2

I suggest to break it down into multiple steps:

# Transfer the sql file to the server
scp staging.sql server

# Excute the queries in that file with psql over ssh
# Notes:
#   - ssh -t enforces terminal allocation. You may try it without this option and see if it still works.
#   - psql -f FILENAME reads commands from file
# 
ssh -t server \
    'psql -W -h db_host -U db_user -d postgres -f staging.sql; rm staging.sql'

answered Jan 3, 2022 at 22:28

hek2mgl

159k31 gold badges263 silver badges279 bronze badges

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

Collectives™ on Stack Overflow

Bash script, remote psql command over ssh, pipe sql file, password fails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related