Run PostgreSQL with Docker

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

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 postgresql
Code 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: bridge
Code language: YAML (yaml)

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

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

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

docker-compose up -d
Code language: Shell Session (shell)

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

PostgreSQL with docker-compose start

The database is started on localhost at 5432 port.

Note

i) I am using postgres db v 13.2,If you want to use different version change the tag against image name. You can get all available image names from docker hub.

ii) When you start the container for the first time, database will be initialized . Later if you change some settings and restarting the containers , the changes will not take effect. In those scenarios you can force reinitialization by stopping containers with following command. Please note that, following command will remove any saved data from database.

docker compose down --volume

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.

Note

port,username and password details are available in docker-compose.yml file

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 down
Code 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: bridge
Code 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.

Docker settings

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

Similar Posts