SaltyCrane Blog — Notes on JavaScript and web development

How to run PostgreSQL in Docker on Mac (for local development)

These are my notes for running Postgres in a Docker container for use with a local Django or Rails development server running on the host machine (not in Docker). Running in Docker allows keeping my database environment isolated from the rest of my system and allows running multiple versions and instances. (I previously had a problem where Homebrew upgraded Postgres when I didn't expect it to and my existing database became incompatible. Admittedly, I didn't know Homebrew well, but it was frustrating.) Disadvantages of Docker are it's another layer of abstraction to learn and interact with. We use Docker extensively at work, so from a mental overhead point of view, it's something I wanted to learn anyways. Currently I use the Homebrew Postgres for work, and Postgres in Docker for personal projects. I also wrote some notes on Postgres and Homebrew here.

Install Docker

OPTION 1: use a single Docker command

Run a postgres container
  • uses the official docker postgres 11 image
  • uses a volume to store postgres data
  • exposes port 54320 to the host
  • sets the container name to my_postgres
  • uses the -d flag to run in the background
$ docker run -d --name my_postgres -v my_dbdata:/var/lib/postgresql/data -p 54320:5432 postgres:11

OPTION 2: use Docker Compose

Create a docker-compose.yml file
$ mkdir /tmp/myproject
$ cd /tmp/myproject

Create a new file docker-compose.yml:

version: "3"
services:
  db:
    image: "postgres:11"
    container_name: "my_postgres"
    ports:
      - "54320:5432"
    volumes:
      - my_dbdata:/var/lib/postgresql/data
volumes:
  my_dbdata:
  • uses docker compose file version 3
  • sets up a service named "db" (this name can be used with docker-compose commands)
  • uses the postgres:11 image from hub.docker.com
  • will create a container named "my_postgres"
  • connects port 5432 inside Docker as port 54320 on the host machine
  • uses a volume named "my_dbdata" for storing the database data. Even if the container and image are deleted, the volume will remain unless explicitly deleted using docker volume rm
  • for more information, see the Docker Compose file reference
Start Postgres

Pull the postgres image from hub.docker.com, create a container named "my_postgres", and start it in the background:

$ docker-compose up -d

See that it's working

See the logs:

$ docker logs -f my_postgres

Try running psql:

$ docker exec -it my_postgres psql -U postgres

hit CTRL+D to exit

For other commands such as starting, stopping, listing or deleting, see my Docker cheat sheet.

Create a database

$ docker exec -it my_postgres psql -U postgres -c "create database my_database"

Connect using Python and psycopg2

$ python3.6 -m venv myenv
$ source myenv/bin/activate
$ pip install psycopg2-binary

Create a new file named myscript.py

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=54320,
    dbname='my_database',
    user='postgres',
)
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS test (id serial PRIMARY KEY, num integer, data varchar);")
cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abcdef"))
cur.execute("SELECT * FROM test;")
result = cur.fetchone()
print(result)
conn.commit()
cur.close()
conn.close()

Run it

$ python myscript.py
(1, 100, 'abcdef')

See also