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

Date: 2019-01-09 | Modified: 2019-03-09 | Tags: linux, mac, sql
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

$ docker run -d —name my_postgres -v my_dbdata:/var/lib/postgresql/data -p 54320:5432 postgres:11

OPTION 2: Run Postgres using 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
  • creates 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

bug解决
https://codeday.me/bug/20180523/168404.html
https://my.oschina.net/liuyuanyuangogo/blog/497239
http://www.it1352.com/646510.html