Postgres Container Access with a SQL Client

As you may have read I like to use Postgres’ official image in my multi-container Docker environment. Out of the box it needs little configuration. I typically access it through my Rails app using rails db or rails console . The other day, however, I needed to craft some SQL and those tools weren’t cutting it. I wanted to use my SQL client so I could work directly with the schema on something better than the command line. That required some tweaking to my DB container.

By default the Postgres image uses Docker’s EXPOSE instruction to open port 5432 for other Docker containers. EXPOSE does not make the port available to the host that runs your Docker Engine. That’s a good, secure default. It’s definitely what we want on a remote server (e.g. production environment). In development, however, this configuration works against us. It prevents a local desktop SQL client from connecting to the containerized Postgres DB. To work around this problem we need to map the container’s port 5432 to a port on our host. You can do this easily with Docker Compose. My docker-compose.yml sets up my DB container like this:

docker-compose.yml db: image: postgres:9.4.5 volumes: - turks-postgres:/var/lib/postgresql/data 1 2 3 4 db : image : postgres :9.4.5 volumes : - turks-postgres :/var/lib/postgresql/data

Very simple; I specify the official image I want to use and a volume to store the database files in. This is my base database service and it works using the default EXPOSE configuration. For development I extend this service in docker-compose.override.yml:

docker-compose.override.yml db: ports: - "5432:5432" 1 2 3 db : ports : - "5432:5432"

Here I’m using the ports: configuration to map the host’s port 5432 to the container’s port 5432. That overrides the default EXPOSE instruction. I can verify the override by checking the output of docker ps after starting my revised containers with docker-compose up -d . I have an IMAGE named postgres:9.4.5 that used to have a PORTS column value of 5432/tcp . Now it is 0.0.0.0:5432->5432/tcp , which shows the port mapping from the host to the container. Finally, we can confirm what we’re seeing by pointing our SQL client to port 5432 on $DOCKER_HOST (for me $DOCKER_HOST is IP 192.168.99.100 as setup by Docker Machine via Docker Toolbox), and querying until our heart’s content.

Got questions or feedback? I want it. Drop your thoughts in the comments below or hit me @ccstump. You can also follow me on Twitter to be aware of future articles.

Thanks for reading!