Running PostgreSQL in Docker for local dev

Recently I blogged about how I'm running SQL Server on Mac with Docker and Oracle on a Mac with Docker, so here's how you can run PostgreSQL locally using Docker... more specifically docker-compose.

To start, you'll need to download and install docker, you can check that you have it installed by running docker-compose -v on the command line and it should output a version number.

An example docker-compose.yml for postgres

Create a file docker-compose.yml with the following:

version: '3.7'
services: 
  pg: 
    image: postgres:9.5.20 
    ports: 
      - "5432:5432" 
    volumes: 
      - ./pg:/docker-entrypoint-initdb.d/ 
    environment: 
      - "POSTGRES\_PASSWORD=${DB\_PASS}"

In this case I am exposing port 5432 (the default postgres port) locally, so I'll be able to connect to it via localhost on port 5432.

Running an initialization SQL or sh script

You can initialize this database by creating some files in a folder under your docker-compose.yml file called pg. The docker initialization scripts will run any .sql file or any .sh file it finds in there during startup. So you can create database users or create table schema by placing files in those directories.

The default username will be postgres and the password for that user is defined in the environment variable POSTGRES_PASSWORD which I am defining to be the value of my computers environment variable named DB_PASS. You could hard code it instead if you really wanted to.

Finally the version of PostgreSQL that is used can be tweaked by changing the line image: postgres:9.5.20 to use a different version number.

Once you have customized it as necessary, then you can run the following command to bring up the database server:

docker-compose up

During initialization it will run your scripts in your pg folder to create the database. If you need to re-run the initialization scripts you'll need to remove the docker container (hint: use docker container ls and docker container rm commands)