Using shell script to grab data from Heroku Postgres database

Question

I'm a newbie at this. I want to write a script that I can execute from command line to run a query on a Heroku-hosted PostgreSQL database.

Right I have a script script.sh with executable permissions that looks like:

echo "Starting pull from postgres ..."
heroku pg:psql <db> --app <app-name>
\copy (<query>) to 'file.csv' WITH CSV
\q
echo "Done!"

The echo and heroku ... commands runs fine, however, once Heroku launches, the script no longer injects the commands. Only after I manually close out the Heroku app does it inject the last three lines.

I understand that this is a bash script that isn't intended to input postgreSQL commands once Heroku is open, but is there a way to do this?

I get the sense that it might involve connecting to Heroku and submitting the query in one line -- I searched around Heroku's documentation but didn't see anything that would be helpful.

ryanbrainard · Accepted Answer · 2017-04-06 23:54:07Z

5

You can use the --command flag to pass SQL commands to heroku pg:psql along with the server-based COPY and then redirect the output to a file:

echo "Starting pull from postgres ..."
heroku pg:psql <db> --app <app-name> --command "COPY (<query>) TO STDOUT WITH CSV" > file.csv
echo "Done!"

answered Apr 6, 2017 at 23:54

ryanbrainard

6,0871 gold badge39 silver badges43 bronze badges

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

1 Comment

VxJasonxV Over a year ago

pg:psql is an interactive command. It requires you to enter commands directly. The --command flag changes that by providing the commands upfront to be executed automatically.

Collectives™ on Stack Overflow

Using shell script to grab data from Heroku Postgres database

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