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
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.
