PostgreSQL with Docker – Quick Start

Introduction

Docker is changing how we distribute and install software and it is a very popular tool in many areas of software development as well. In this post, I will show you how to quickly get started with docker and postgreSQL.

I will try to keep this post very simple and will not cover complex workflows (those will be covered in later posts) and this will keep the discussion focus and help in easy learning. Now the idea is that you are gonna get, I don’t know, lights up the old neurons and creates one of those aha moments.

I am assuming that you already have installed docker on your machine and it is running.

Getting the Image

The following command will pull down the latest stable release Postgres image from the official Postgres docker hub repository.

>>docker pull postgres

Persist Data

Container data is gone once it is stopped and this is useful for certain situations (e.g. if you are running some database/integration testing and want to get rid of test data then its great). But, If we want to persist data generated by the Postgres instance running inside a container beyond the container’s lifecycle, we need to map a local mount point as a data volume to an appropriate path inside the container.

Running the Container

>>docker run –name pg-docker -e POSTGRES_PASSWORD=docker -e PGDATA=/tmp -d -p 5433:5432 -v ${PWD}:/var/lib/postgresql/data postgres:11

The following command variation uses another environmental variable to setup the database as well:

>>docker run --name pg-docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=sampledb -e PGDATA=/tmp -d -p 5433:5432 -v ${PWD}:/var/lib/postgresql/data postgres:11

Connect and use PostgreSQL

Once the container is up an running, connecting to it from an application is no different than connecting to a Postgres instance running outside a docker container. For example, to connect using psql we can execute

>>psql -h localhost -U postgres -d postgres

or u can use Azure Data Studio and make the connection as shown below:

We can also run psql inside the container by using the following command:

>>docker exec -it pg-docker psql -U postgres

We can issue SQL commands via docker cli as follows:

>>docker exec -it pg-docker psql -U postgres -c "CREATE DATABASE testdb;"

we can also run sql-script in a similar manner as shown below:

>>docker exec -it pg-docker psql -U postgres -f /opt/scripts/test_script.sql

Docker File

You can run above mentioned commands individually but typically you will create a docker file to build an image. Lets create a docker file to structure our setup requirements.

Notice the Copy command which is copying the script files from host directory to container.

I have two simple sql script files as follows:

and here are the content of these script files, feel free to adjust as per your requirements:

Giving the file names numeric ascending value helps in controlling the execution order.

Now having all this in place, we can now build an image using this docker file:

>>docker image build -t postgresbasic .

>>docker run --name pg-docker -e PGDATA=/tmp -d -p 5433:5432 -v ${PWD}:/var/lib/postgresql/data postgresbasic

and finally we can now check the database using psql or Azure Data Studio.

Summary

This was a basic introduction of how to use docker for running postgreSQL database. We also saw simple configuration related to environment setup and how to execute scripts as well. Docker file and scripts can be downloaded from this git repository. If you want to know more about Azure Data Studio or SQL, you can check my other articles as well. Let me know if you have some questions. Till next time, Happy Coding.

References

• https://hexquote.com/fun-with-sql-using-postgres-and-azure-data-studio/
• https://hub.docker.com/_/postgres
• https://stackoverflow.com/questions/26598738/how-to-create-user-database-in-script-for-docker-postgres
• https://hackernoon.com/dont-install-postgres-docker-pull-postgres-bee20e200198