Setup PostgreSQL with PostGIS on Docker

PostgreSQL is a great open source tool to setup a simple SQL database. However, PostgreSQL can be a bit finicky depending on the PostgreSQL version and the operating system of your computer. Hosting a PostgreSQL server on a Docker container makes it easy to setup multiple servers that can easily be turned on or off as well as ported to other workflows like AWS or Google Cloud. I’ll show you how to setup a PostgreSQL server that has PostGIS installed on a Docker Container (you can also watch the video tutorial I made here).

If you’re using a Windows OS and you don’t have UNIX commands enabled for command prompt/powershell then you’ll need to install Cygwin or QEMU to run a UNIX terminal. If you’re on a Mac then nothing other than a terminal is needed.

Launch Docker on your computer. Once Docker is running, open a new terminal and run the following command:

docker run --name my_postgis_server1 -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -d mdillon/postgis

This command builds a Docker container from mdillon’s PostGIS container on Docker Hub. We’re running the container on port 5432 so we can connect to it locally and have set both the username and password to be ‘postgres’, which is typically the default username and password. Change these if you’d like, but make sure these are aligned with the ‘role’ username if you’re backing up a database later. Your terminal window should look like this:

Next we’ll execute and enter the container by running:

docker exec -it 452b560df7e8 psql -U postgres

This will launch the PostgreSQL server. We can list the databases in our server by running:

\l

Your terminal should now look like this:

The server has been initialized with some empty template databases. To create a new database we can run the following command:

CREATE DATABASE my_new_db;

Note, if restoring a .sql dump file of a database you’ll want to make a new database with the same name as the old one. We can restore a dumped database by running:

cat /path_to_where_you_saved_data/my_db_dump_file.sql | docker exec -i <container id> psql -U postgres -d my_db_dump_name

Your terminal might look like this:

Now that our database is restored (this can take some time if we’re restoring a large database) we can access it with PgAdmin or another database GUI like DBeaver. I’ll use PgAdmin in this example.

After launching PgAdmin right click on ‘Servers’ and click Create > Server:

You’ll then see a window that looks like this:

Name the new server whatever you want and then in the ‘Connection’ tab specify 127.0.0.1 as the Host name/address (127.0.0.1 is interchangeable with just writing localhost). The port should be 5432 and you can specify the username you used when setting up the PostgreSQL server (postgres in this case). Hit save and then navigate to your new server and the database you just restored. Here’s an example of what this might look like:

It’s that simple!

Some handy tips and tricks:

• If restoring a large database or writing a lot of data to a database make sure to allocated the proper memory in Docker.

• To pause the docker container and database:

docker stop <container_id>

• To start the docker container and database:

docker start <container_id>

• If you’ve had failed database restores or want to clear out old images, containers, and volumes (where most of the data is stored) then run these commands:

docker image prune
docker container prune
docker volume prune

I hope this was helpful and easy to follow. Thanks for reading and LMK how I can improve these tutorials and or what you’d like me to write about next!