Running PostgreSQL in a Container for Local Development on Windows

You can install the full version of PostgreSQL, also known as Postgres, and be on your merry way. If you like the idea of not having to install Postgres and having the ability to start and stop Postgres trivially then this article is for you.

Running PostgreSQL using Docker can be accomplished with the following Docker command:

> docker run -e POSTGRES_PASSWORD=postgres postgres

This simple Docker command will launch pull the Postgres image and launch Postgres for you with the superuser password set to "postgres". Running Postgres like this has a problem. If the container is stopped you will lose all of the data in the database.  In order to preserve the data in the database then you will want to use a volume in Docker.

Try the following command to add a volume that will store the databases data.

> docker run -e POSTGRES_PASSWORD=postgres -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data postgres

On Linux this will work just fine and you will now have a Postgres instance running locally that will survive container stop and starts. On Windows this WON'T work! On Windows you will see an error message that looks like the following:

running bootstrap script ... 2020-06-02 21:34:36.577 UTC [81] FATAL:  data directory "/var/lib/postgresql/data" has wrong ownership
2020-06-02 21:34:36.577 UTC [81] HINT:  The server must be started by the user that owns the data directory.
child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/postgresql/data"

This a known issue with Docker for Windows. The solution I have found to workaround this on Windows is to create a volume first.

docker volume create --name=pgdata

Then you can run Postgres with the following Docker command.

docker run -e POSTGRES_PASSWORD=postgres -v pgdata:/var/lib/postgresql/data postgres

You should now see this message, database system is ready to accept connections. Success!

Complete the Docker Command

Now that we have persistent storage for our database, let's finish out the docker command to improve it's usage for local development. The final command should be:

# create a permanent volume
docker volume create --name=pgdata
# run postgres with explicit settings
docker run --rm --name postgres -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v pgdata:/var/lib/postgresql/data postgres

With the latest changes we now have Postgres running with the username, password, exposed port, and container name explicitly defined.

PostGIS Support

In order to get PostGIS support you will want to choose a Docker image that already has it installed. In the code below we use  the base image `postgis/postgis:11-2.5-alpine` to get the PostGIS support we need in our database.

> docker run --rm --name postgres -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v pgdata:/var/lib/postgresql/data postgis/postgis:11-2.5-alpine

There is a notable side effect when using the PostGIS container specified above. The database will start and stop and then start again.

Final PowerShell Script

# PowerShell script
# create a permanent volume
docker volume create --name=pgdata

# run postgres with GIS support
docker run --name postgres `
    --rm `
    -e POSTGRES_USER=postgres `
    -e POSTGRES_PASSWORD=postgres `
    -d `
    -p 5432:5432 `
    -v pgdata:/var/lib/postgresql/data `
    postgis/postgis:11-2.5-alpine

References

https://hackernoon.com/dont-install-postgres-docker-pull-postgres-bee20e200198
https://stackoverflow.com/questions/57192475/postgresql-in-a-docker-container-on-windows-how-to-persist-data-to-a-local-wind