Install PostgreSQL on Mac OS X - Tooling. SQLPro Studio - A macOS, Windows and iPad/iPhone database client. I would like to know if exists a TP monitor for postgres. If it exists, I would like to know if there is a support for JDBC and if there is a support.
In the past when I’ve wanted to explore production data for a Heroku-hosted Ruby on Rails app, I’ve primarily used heroku console and rake tasks. Each method has limitations though: heroku console makes easy to answer simple questions about your data, but makes it difficult to perform complicated analyses that take more than a few lines of code. Rake tasks let you perform complex analyses, but make it difficult to explore data because each time you tweak your task to do something new, you need to commit, push to production, run the task, and wait for it to execute. Neither option makes it easy to quickly explore the data.
Wouldn’t it be nice if you could quickly query your database and explore the results?
Fortunately there is a way using a combination of Heroku’s
pg:pull
feature and a Mac app called SQLPro for Postgres. Here’s how it works:Step 1: Pull your production data into a local Postgres database
Heroku makes this fairly easy using the pg:pull command:
Where
mylocaldb
is the name of a local Postgres database, sushi
is the name of your Heroku app, and HEROKU_POSTGRESQL_MAGENT
is the name of your database which you can obtain by running:If your local Postgres instance requires a user name and password, you can provide them via the command line as well:
In order for this command to work,
mylocaldb
can’t exist when you run this command. To delete it beforehand, you can run:For my own workflow combine them and use a Bash alias to make it easier to run:
Then I can just run
prdb
(my short hand for “Preceden Database”) from the command line to drop the old copy and grab the latest production data:How to set google as your default browser on safari. Step 2: Explore the data using SQLPro for Postgres
Sqlpro For Postgres 2016
SQLPro for Postgres is a fantastic Mac app for exploring Postgres databases. You can also query the data other ways but for quickly exploring, querying, and exporting the data, SQLPro for Postgres is hard to beat.
Postgresql App
Here’s what the UI looks like along with an example query to display the first 10 people to sign up:
In future posts we’ll see how to query Postgres with R to analyze the data and gain insights about how people use our products.
If you’re interested in learning more, sign up for my new Data Science for Product Analytics newsletter to get notified when there are new posts.
Sqlpro For Postgres 2019
Update: check out the follow up post, How to Schedule Cloning your Heroku Postgres Database Locally.