Using shell script to grab data from Heroku Postgres database

I am new to this. I want to write a script that I can execute from the command line to run a query on a PostgreSQL database hosted in Heroku.

Correctly, I have a script script.sh

with executable rights that looks like this:

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

      

The commands echo

and heroku ...

work fine, however once Heroku starts up the script no longer issues commands. Only after I manually close the Heroku app does it enter the last three lines.

I understand that this is a bash script that is not meant to enter postgreSQL commands when Heroku is open, but is there a way to do this?

It looks to me like it might have something to do with connecting to Heroku and sending the request in one line - I searched Heroku documentation but didn I don't see anything useful.

+3


source to share


1 answer


You can use a flag --command

to pass SQL commands heroku pg:psql

to the server side 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!"

      

+2


source







All Articles