Notes on looking at DB info on Heroku (postgres)

This assumes you have the Heroku CLI already installed and have the rights/permissions needed to access your project.

This is written for this project (SHF-project), but should work for any project on Heroku that uses Postgres.

Helpful References:

• Heroku Command Line Interface (CLI) Reference
• Heroku PostgreSQL

Log in and Start Postgesql on Heroku

• Log in to Heroku: heroku login (from a local shell)

• Start the Postgres command line interface: heroku pg:psql --app shf-project --app shf-project will use the database associated with the application shf-project You should see heroku connect to postgresql and then psql should show the version and a prompt:

--> Connecting to postgresql-regular-17431
psql (9.5.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

shf-project::DATABASE=>

• Help for SQL: \h

• Help for psql (the postgres CLI): \?

• In general, a psql command starts with a \ (as opposed to a SQL query, which doesn't)

• Type in your query, then follow it with \g to execute it:

shf-project::DATABASE=> SELECT * FROM companies
shf-project::DATABASE=> \g

You'll then see the results of the SELECT * FROM companies query

• If the results are longer than 1 page, you may see a : prompt. This is like vi based editors. Hitting the spacebar will show you another page of results. The return key will show you 1 more line (row). You can type q to quit -- it will stop showing you any more results.

Send the results of all queries to a file, then fetch with git

• In psql, the \o FILENAME option will send all query results to a file named FILENAME. You can then download (via a git fetch) the file to your local machine. Ex:

shf-project::DATABASE=> \o psql-output-101702041700.txt
shf-project::DATABASE=> SELECT * FROM companies
shf-project::DATABASE=> \g
shf-project::DATABASE=> \q
<your local prompt> $ git remote -v
heroku	https://git.heroku.com/shf-project.git (fetch)
heroku	https://git.heroku.com/shf-project.git (push)
<your local prompt> $ git fetch heroku

The above will:

1. send all query output to a file named psql-output-101702041700.txt on your Heroku server
2. execute the query SELECT * FROM companies
3. quit psql (\q)
4. list the remotes associated with your heroku git repo (the remote it typically named heroku)
5. do a git fetch to update your local git repo with what's on your Heroku server (via the remote named heroku)

• After the git fetch, you should have psql-output-101702041700.txt on your local machine, where you can look at the results of the SQL query SELECT * FROM companies

• See the psql commands for Input/Output for more (from psql help via \?):

   Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)
  

Misc

You can look at the logs for you app from your Heroku dashboard. ex: https://dashboard.heroku.com/apps/<your-project-name>/logs