Working with PostGreSQL and PostGIS in Docker + QGIS

I’ve been wanting to explore more possibilities in databases and spatial data since working on a large data project during my PhD. Since then I really haven’t had data that would warrant the effort needed to design and setup a database. Recently I was reminded about Docker, which I have used to spin up Rstudio instances before. After some internet work I found some handy sources for running databases in containers and so I thought I would try it out as a platform for learning more.

Here is my effort to get a database up and running in a container and then connect to said database with Dbeaver and then QGIS.

Download and install Docker; Download and install Dbeaver Community Edition; Download and install QGIS

1. Get a PostGreSQL database running in a container
   1. At the terminal type: docker container run -d --name=pg -p 5432:5432 -e POSTGRES_PASSWORD=docker -e PGDATA=/pgdata -v /pgdata:/pgdata postgres
      1. This will download (if it doesn’t already exist on your machine) the image from docker and start it up. You will now have running a container with a PostGres database active.
      2. Some explanation of the command
         1. -d means the container will run in the background
         2. -name=pg names the container, rather than docker assigning a random name
         3. -p 5432:5432 assigns port 5432 of the container to 5432 on the host. You will connect to the PostGres database through this port
         4. -e POSTGRES_PASSWORD=docker is an environment variable for PostGres, it sets the password to docker in this case
         5. -e PGDATA=/pgdata sets the data location for the database in the container to the folder /pgdata. This folder will be created in the container.
         6. -v /pgdata:/pgdata sets a volume connection from inside the container to a location on the host machine. This allows you to persist your database data when the container is stopped or even deleted.
         7. postgres is the image we will download, this will be the latest version of PostGreSQL
      3. This comes from www.youtube.com/watch?v=iZDbENJrl4I
      4. Another option for the persistence data is just to assign a folder on the host to the default data location inside the container. It may look like -v /[local folder]:/var/lib/postgresql/data
         1. I found that the /pgdata command above persisted but I don’t know where it lived on the host machine. It persisted between stopping the container but disappeared after a Docker app update, YMMV.
      5. If you open the Docker dashboard you will see the container running, success!
2. Connect from the command line
   1. You can connect to the container and get to command line inside of it by executing a command
      1. docker container exec -it pg bash
         1. This brings up bash inside of the container, which is a Linux machine.
         2. -it makes it interactive so that you can type into it
         3. pg is the name of our container & bash is the command
         4. You can type ls to see the contents, which include the folder /pgdata. Type exit
   2. Use psql to make a table
      1. Access psql inside of the container docker container exec -it pg psql -U postgres
      2. -U postgres is the user that you use to access the database. Since you are already inside of the container to connect, no password is needed.
      3. Create a table at the prompt postgres=#
         1. create table table1(name text, id int generated by default as identity);
         2. Then insert some data
         3. insert into table1(name) values('PostGreSQL'), ('database'), ('Dbeaver');
         4. Now you have a table in your database that is running within a docker container. Confirm it by typing \dt
3. Connect to database with Dbeaver
   1. If you open Dbeaver you can connect to this running database by setting up a PostGreSQL connection. The host will be localhost Database will be postgres Username will be postgres and the password is the one set in the docker command, in this case docker
   2. Test the connection with the button, then Finish
   3. You should see our table1 under postgres>Schemas>public>Tables
   4. To use Dbeaver check out the documentation.
4. Get PostGreSQL + PostGIS running in a container
   1. For this I use
      1. docker run --name=pgdb -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -e PGDATA=/pgdata -d -v gisdata:/pgdata postgis/postgis
      2. This uses a named volume managed by Docker at gisdata It doesn’t already exist so Docker will create it. You can check these with docker volume ls
5. Connect to database with QGIS
   1. This can be done by right clicking on the PostGIS in the Browser. Once connected you can mange the connection in the DB Browser and do things like import data into the database. Probably best to update to 3.10 at least.

There was a fair amount of Googleing to explore all of this. I had issues arise around connecting to volumes. It is nice to be able to persist data so that you could use this process for learning and analysis. Docker is a good way to keep the environment stable and isolated from whatever machine you are on (I was working on Windows 10 and MacOS).