An Easy guide to setup PostgreSQL in docker

If you’re looking to set up PostgresSQL in Docker in an easy method you are at the right place In just a few steps, you’ll be able to easily spin up your own Postgress container and access it from anywhere.
Learn how to take advantage of this powerful tool with this handy tutorial!

What is Docker?

Docker is an open-source tool designed to make it easier to create, deploy, and run applications with resources in containers. A container wraps up an application’s code and all of its dependencies so the application runs quickly and reliably from one computing environment to another.
Want to learn more about docker visit our Docker blogs tutorials:

What is PostgreSQL

PostgreSQL (often called “Postgres”) is a powerful, open-source object-relational database management system (ORDBMS). It is designed to handle a wide variety of data types and workloads and is known for its robustness, scalability, and compliance with SQL standards.

Next, we are going to deploy the PostgreSQL container using docker.
Prerequisite a server with root access and docker installed. In this tutorial, I am using the ubuntu 20.04 server.
Visit the previous blog to install docker in ubuntu 20.04 How to install docker in ubuntu 20.04

Pull a PostgreSQL Image from Docker Hub.

After installation of docker in your system We need to pull the latest Postgres docker image from the docker hub.

docker pull Postgres

Here we have pulled the latest version if you want to pull a specific version of PostgreSQL refer to the Postgres docker hub page https://hub.docker.com/_/postgres.

After pulling the Postgres image your will be able to list the image using the docker images command.

Create and Run a PostgreSQL Container.

The next step is to run the PostgreSQL image as a container for that using the docker run command

docker run -it -e POSTGRES_USER=linux -e POSTGRES_PASSWORD=linuxlearninghub -p 5432:5432 -v /data:/var/lib/postgresql/data –name postgresql postgres

Here we are passing the POSTGRES_USER and POSTGRESS_PASSWORD as environment variables to the container
Next using the default Postgres port 5432 to establish a connection to the container also we are mounting the data dir /var/lib/postgresql/data in the directory /data in the host using the bind mounts technique.
As we have already discussed volumes and networking visit our previous blog for more reference
A Comprehensive Guide to Docker Networking and Volumes

Creating a database in postgress

As we already have a running Postgres docker container creating a database in it is super easy first we need to exec into the container using the below command

docker exec -ti postgresql /bin/bash

Here PostgreSQL is my container name you need to replace the name with your container name also you can provide the container id in order to exec into it.
Next switching as a Postgres user for that use the command
su – postgres

After switching as a Postgres user you can enter to SQL using the user which we created earlier

psql -U linux

Also, you can list the current database using \l command which will provide a similar output as below.

After entering into psql you can do various operations like creating a database, user assign permission etc for that use the below commands

To create a new database — CREATE DATABASE db_name;
To create a new user —– CREATE USER my_user;
Connect a database with a user — GRANT CONNECT ON DATABASE db_name to db_user;
Grant all privileges to that user on that database —-GRANT ALL PRIVILEGES ON DATABASE db_name TO db_user;
Creating backups and restoring

Backing up your PostgreSQL database containers is essential, as it allows you to recover from disasters and catastrophic file loss quickly. We can take a backup of the database using 2 methods one is taking a backup without entering into the container.

docker exec -t container_id pg_dump -U username -d database_name > dumpfile.sql

Or else we can take backups by entering into the container using the command

docker exec -ti postgresql /bin/bash, switch to psql user as shown above, and run the command.

pg_dump -U myuser -F t -f backup.sql mydb

-U is used to specify the username to connect to the database.
-F is used to specify the format of the backup, in this case, “t” for tar.
-f is used to specify the filename of the backup file.
mydb is the name of the database you want to backup.

Similarly, we can restore dump files in postgres using 2 methods

We can restore the database without entering it into the container but this case won’t work in many situations if your DB has an active connection you need to try another method.

docker exec -i container_id pg_restore -U username -d database_name < dumpfile.sql

If the database you are trying to restore has an active connection now need to terminate all the connections via psql and then complete the restoration process. It is better to drop the database and create a new one with the same name and complete the restoration.

Also, we can restore the database by entering it into the container and switching to Postgres user and running the below command

psql postgres -U db_user-d db_name -f dumpfile.sql

Summary:
By following this guide, users can easily set up and run a PostgreSQL instance within a Docker container, with the flexibility to customize the configuration as needed.

Leave a Reply

Your email address will not be published. Required fields are marked *