Run PostgreSQL with Docker

This tutorial will show you how to setup PostgreSQL with Docker.

PostgreSQL is a powerful, open-source database system. It is one of the most popular database systems in the world. PostgreSQL is designed to be reliable, robust, and scalable.

In general we can install PostgreSQl on our systems by downloading corresponding installation files from official website.

Instead of installing as native application you can also run PostgreSQL with Docker as container. By running PostgreSQl as container you can easily experiment with different versions of database.

Required Software

• Docker
• pgAdmin

Now Let’s look at the process to run postgreSQL as docker container

Step 1) create a new directory for the project and navigate into it

mkdir postgresql && cd postgresqlCode language: DOS .bat (dos)

Step 2) Create a docker compose file (docker-compose.yml) with following configuration

version: '3.3'


services:  
  postgres:
    # Official Postgres image from DockerHub
    image: 'postgres:13.2'
    
    ports:
      - 5432:5432
    networks:
      - app_net    

    environment:
      POSTGRES_USER: postgres # The PostgreSQL user (useful to connect to the database)
      POSTGRES_PASSWORD: postgres # The PostgreSQL password (useful to connect to the database)
      POSTGRES_DB: employee # The PostgreSQL default database (automatically created at first launch)

networks:
  app_net:
    driver: bridgeCode language: YAML (yaml)

Step 3) Open command prompt and navigate to postgresql folder run following command

docker-compose upCode language: Shell Session (shell)

If you want to run in detached mode, you can run in the following command

docker-compose up -dCode language: Shell Session (shell)

Above command will download the image ( for the first time) and start the database in container.

The database is started on localhost at 5432 port.

Connecting to Database with pgAdmin

Step 4) Now lets open the pgAdmin application and connect to the newly started database server.

Right click Servers menu , choose Create option and Server sub option.

Give name to connection and click on Connection tab

Enter hostname , port, username and password on click on save button.

Now you can access postgres database server started with docker container.

Now you can create schemas and tables in the database but once you stop the container all the data will be lost.

You can stop the container with following command

docker-compose downCode language: Shell Session (shell)

Persisting the Data

If you are looking for temp database above configuration is fine but if you want data to persist so that it is available when ever we start the container then we need to create a volume and store the data in it.

docker-compoe file with volume configuration

version: '3.3'


services:  
  postgres:
    # Official Postgres image from DockerHub
    image: 'postgres:13.2'
    volumes:  # volume
      - ./postgres-data:/var/lib/postgresql/data    
    ports:
      - 5432:5432
    networks:
      - app_net    

    environment:
      POSTGRES_USER: postgres # The PostgreSQL user (useful to connect to the database)
      POSTGRES_PASSWORD: postgres # The PostgreSQL password (useful to connect to the database)
      POSTGRES_DB: employee # The PostgreSQL default database (automatically created at first launch)

networks:
  app_net:
    driver: bridge
Code language: YAML (yaml)

Now if we start the container again we can see the postgres-data folder and following folder structure inside it.

Now all the changes will be persisted across container restarts.

Adminer

Adminer is a full-featured light weight database management tool written in PHP.

Instead of using the pgAdmin client, we can bundle Adminer in the docker-compose file.

version: '3.3'


services:  
  postgres:
    # Official Postgres image from DockerHub
    image: 'postgres:13.2'
    volumes:
      - ./postgres-data:/var/lib/postgresql/data    
    ports:
      - 5432:5432
    networks:
      - app_net    

    environment:
      POSTGRES_USER: postgres # The PostgreSQL user (useful to connect to the database)
      POSTGRES_PASSWORD: postgres # The PostgreSQL password (useful to connect to the database)
      POSTGRES_DB: employee # The PostgreSQL default database (automatically created at first launch)

  adminer: # adminer db client
    image: adminer
    restart: always
    ports:
      - 8900:8080
    networks:
      - app_net

networks:
  app_net:
    driver: bridge
Code language: YAML (yaml)

After starting the containers, we can adminer client from from browser.

Open the browser and navigate to localhost:8900.

To access the database, enter the details like below. In server field you need to enter the service name defined in the docker compose file( not localhost)

Starting DB @ Startup

With above docker-compose configuration, we have to start the container whenever we want use the database. If you want to start the database container when ever you login into the system we need to set the restart policy to always in compose file.

version: '3.3'


services:

  postgres:
    # Official Postgres image from DockerHub
    image: 'postgres:13.2'
    restart: always #restart policy
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

    ports:
      - 5432:5432
    networks:
      - app_net

    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: employee

  adminer:
    image: adminer
    restart: always #restart policy
    ports:
      - 8900:8080
    networks:
      - app_net

networks:
  app_net:
    driver: bridgeCode language: YAML (yaml)

Now if you start the container once even of you restart the system containers will be automatically started again.

For containers with restart policy always to start after reboot, Docker daemon thread should also start at startup.

In Windows system, please check the Start Docker Desktop when you log in option to start Docker daemon at startup.

Conclusion

In this tutorial we have seen how to setup PostgreSQl database with docker, how to persist the data and start the database during System startup.

You can download docker-compose file from GitHub