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¶

Install Docker for Mac: https://docs.docker.com/docker-for-mac/install/.

Alternatively, you can install Docker using Homebrew: brew install homebrew/cask/docker

OPTION 1: Run Postgres using a single Docker command ¶

• uses the official docker postgres 13 image
• uses a named volume, my_dbdata, to store postgres data
• exposes port 54320 to the host using -p
• sets the container name to my_postgres
• uses the -d flag to run in the background
• sets the postgres superuser password to "my_password" using -e and the POSTGRES_PASSWORD environment variable.

$ docker run -d --name my_postgres -v my_dbdata:/var/lib/postgresql/data -p 54320:5432 -e POSTGRES_PASSWORD=my_password postgres:13

OPTION 2: Run Postgres using Docker Compose¶

$ mkdir /tmp/myproject
$ cd /tmp/myproject

Create a new file docker-compose.yml:

version: "3"
services:
  db:
    image: "postgres:13"
    container_name: "my_postgres"
    environment:
      POSTGRES_PASSWORD: "my_password"
    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:13 image from hub.docker.com using the image key
• creates a container named "my_postgres" using the container_name key
• sets the postgres superuser password to "my_password" using the environment key and the POSTGRES_PASSWORD environment variable
• connects port 5432 inside Docker as port 54320 on the host machine using the ports key
• uses a named volume, "my_dbdata", for storing the database data using the volumes key. 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

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 -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',
    password='my_password',
)
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')

Errors¶

• docker: Error response from daemon: Conflict. The container name "/my_postgres" is already in use by container "b27594a414db369ec4876a07021c9ea738a55b3bc0a3ad5117158367131b99a2". You have to remove (or rename) that container to be able to reuse that name.

  If you get the above error, you can remove the container by running:

  $ docker rm my_postgres
  

• Error response from daemon: You cannot remove a running container 7e94d205b6f4ef40ff885987f11e825e94eddbcd061481e591e07c87ed7cf86e. Stop the container before attempting removal or force remove

  If you get the above error, you can stop the container by running:

  $ docker stop my_postgres
  

See also¶

• My Postgres Homebrew notes
• My Docker cheat sheet
• postgres image on Docker Hub
• docker run command reference
• Docker Compose file reference
• Documentation on using volumes