Using Heroku's pg:pull with Docker
PG_HOST=127.0.0.1 when running Heroku's
pg:pull to a local target database that is running in a Docker container. For a more detailed explanation, read on...
I recently starting using Heroku to deploy a side project built with Rails and Postgres. Heroku is a PaaS (Platform as a Service) that makes it really easy to deploy web applications, and other related services such as a database. Since I'm not currently monetizing my side project, I selected the free tier, together with the Hobby Dev package for the database, which provides a limited amount of storage.
One thing you might like to do once there's some significant amount of production data in the database, is to make a copy of it to import into a locally running database. For local development you'd usually be using fake or test data (i.e. seeds in Rails). However, it can sometimes be useful to develop against real data. For example, when working on a data visualization app, having real data can help to determine if the data viz is providing any insight.
Heroku provides a convenient
pg:pull command to pull data from a production database on Heroku to a locally running database. It's run from a terminal (given that you've already authenticated via
heroku login). The basic structure of the command is:
heroku pg:pull your-heroku-db-name target-local-db-name --app your-heroku-app-name
Note that this command requires a postgres client to be installed locally, the easiest way on a Mac is via Homebrew:
brew update && brew cleanup && brew install postgresql (actually installs both client and server but only client portion is needed).
The value of
your-heroku-app-name is found on your Heroku dashboard, for example
mystic-wind-83. The value of
your-heroku-db-name can also be found from the Heroku dashboard by clicking on the app, the database name will be listed in the "Installed add-ons" section, for example
target-local-db-name is the name of a new database that will be created on your local Postgres, for example
my_prod_copy. You can name this any valid database name but it must not already exist on your system. Putting this all together, the command would look like:
heroku pg:pull postgresql-sushi-123 my_prod_copy --app mystic-wind-83
However, since I'm running Postgres in a Docker container for my app, the
pg:pull command returned the following error message:
heroku-cli: Pulling postgresql-sushi-123 ---> my_prod_copy createdb: error: could not connect to database template1: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
According to the Heroku docs, this error can result from not having the Postgres binaries in the
pg:pull is essentially a wrapper around
pg_dump. But this was not the root cause of this error in my case as
pg_dump was indeed in the PATH. This can be verified as follows:
> type pg_dump pg_dump is /usr/local/bin/pg_dump > echo $PATH ...:/usr/local/bin:...
When running Postgres in a Docker container, the
PG_HOST environment variable must be specified to tell Heroku where the running Postgres instance is (otherwise it's assuming localhost). For Docker, the value is
127.0.0.1. Also, if you've specified a Postgres root user and password, those must also be specified as environment variables for the
For example, my Postgres container is started via
docker-compose up using the following
version: "3.8" services: database: image: postgres:13 volumes: - db_data:/var/lib/postgresql/data - ./init.sql:/docker-entrypoint-initdb.d/init.sql ports: - "5432:5432" environment: POSTGRES_PASSWORD: somethingSomething POSTGRES_USER: postgres volumes: db_data:
So the final working command for Heroku to pull the production database to a local copy when using Docker is:
PGUSER=postgres PGPASSWORD=somethingSomething PGHOST=127.0.0.1 heroku pg:pull postgresql-sushi-123 my_prod_copy --app mystic-wind-83
Now you can connect to your newly created local database
my_prod_copy. Note that the
pg:pull command will not copy the production user/role, so you'll have to connect with your