Skip to content

PostgreSQL Docker

PostgreSQL Docker

An easy way to set up your local environment for developing SQL with Docker containers

Download an image:

docker pull postgres

After downloading the image you can check that is available to use: 

docker images

>>>
REPOSITORY    TAG       IMAGE ID        CREATED        SIZE
postgres      latest    9907cacf0c01    2 weeks ago    314MB

We will create a local folder and mount it as a data volume for our running container to store all the database files in a known location for you. In the “run” command, we will map also the ports from the host to the running container and a password for the Postgres default user.

1. Create a folder in a known location for you

mkdir ${HOME}/postgres-data/

2. run the postgres image

docker run -d \
    --name dev-postgres \
    -e POSTGRES_PASSWORD=pass \
    -v ${HOME}/postgres-data/:/var/lib/postgresql/data \
    -p 5432:5432 \
    postgres

3. check that the container is running

docker ps

>>>
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
dfa570d6e843        postgres            "docker-entrypoint.s…"   27 hours ago        Up 3 seconds        0.0.0.0:5432->5432/tcp   postgres-test

Great, you have a running PostgreSQL instance and you should be able to enter the container from your command line and test the database instance:

docker exec -it dev-postgres bash

Now you are in the container's bash console. Connect to the database 

psql -h localhost -U postgres

Starting the pgAdmin instance

pgAdmin is the most popular and feature-rich Open Source administration and development platform for PostgreSQL. You will use it to manage the DB instance as well as to run your queries against the tables of it.

You will be using this docker image to deploy it in a container. Get the image and run the instance of the image with the following commands: 

docker pull dpage/pgadmin4
docker run \ 
    -p 80:80 \
    -e 'PGADMIN\_DEFAULT\_EMAIL=user@domain.local' \
    -e 'PGADMIN\_DEFAULT\_PASSWORD=SuperSecret' \
    --name dev-pgadmin \ 
    -d dpage/pgadmin4

The parameters that we are passing to the docker run command are:

  • -p 80:80: This parameter tells docker to map the port 80 in the container to port 80 in your computer (Docker host)
  • -e 'PGADMIN_DEFAULT_EMAIL: Environment variable for default user’s email, you will use this to log in the portal afterwards
  • -e 'PGADMIN_DEFAULT_PASSWORD': Environment variable for default user’s password
  • -d: This parameters tells docker to start the container in detached mode
  • dpage/pgadmin4: This parameter tells docker to use the image that we have previously downloaded

Let’s check that the container is up and running, you should also see the previous container running: 

docker ps

Accessing the PostgreSQL from the pgAdmin tool

We haven’t defined any network for these containers so they should be running on the default one, and if you try to access the database or the web portal through their ports, connecting via ‘localhost’ or ‘127.0.0.1’ would work just fine; but if you try connecting from one container to the other, you might encounter some connectivity issues.

We will need to look for the IP address of the PostgreSQL container on our host, you can run this command for it: 

docker inspect dev-postgres -f "{{json .NetworkSettings.Networks }}"
docker inspect return low-level information of Docker objects, in this case, the ‘dev-postgres’ instance’s IP Adress. The -f parameter is to format the output as a JSON given a Go template. The output should look like this: 
{
    "bridge":
 {
           "IPAMConfig":null,
           "Links":null,
           "Aliases":null,
           "NetworkID":"60c21f5cfcaaff424a0e4a22463dc8f9a285993de04e7ac19ce5fd96bba56a47",
           "EndpointID":"be6e45b659c30bd12aa766d7003a2887607053684b68574e426f8823104b18a2",
           "Gateway":"172.17.0.1",
           "IPAddress":"172.17.0.2",
           "IPPrefixLen":16,
           "IPv6Gateway":"",
           "GlobalIPv6Address":"",
           "GlobalIPv6PrefixLen":0,
           "MacAddress":"02:42:ac:11:00:02",
           "DriverOpts":null
 }
}

Copy the IPAddress value into the clipboard, which is 172.17.0.2 in my case, you will need to define the connection in the pgAdmin tool.

The next step is to go to your web browser and type http://localhost:80.

pgAdmin login portal, http://localhost:80. Source: Local

You should type the user email and the password you wrote when running the container.

Once you are in the portal, you will need to add a new server by clicking on the “Add New Server” and adding the right information on the pop-up window, make sure you add the IPAdress that you copied previously in the Host name/address under the Connection tab.

Welcome page from pgAdmin tool. Source: Local

Once you have created the connection you should see the server on the right side of your screen. At this moment you are ready to start building your databases and tables, uploading data and querying for your analysis or applications.

Server dashboard on pgAdmin tool. Source: Local

Source