PostgreSQL, an effective tool, is a freeÂ-to-use relational database management system. Docker can quickly construct and orcheÂstrate its instances without bothering about the complexity of setup or depeÂndencies. This step-by-step simple guide will show you how to get PostgreÂSQL on Docker, and then, use the pgAdmin extension to look at the databaseÂ.
Why Use Docker for PostgreSQL?
When it comes to working with PostgreSQL or any other databases, the problem comes when installing and configuring it. As a developer, setting up the database each time can be a real trouble. By entering Docker and by pulling PostgreSQL within a Docker container, you can effortlessly use PostgreSQL Database which makes it much easier to configure and concentrate on the actual coding.
But, a fair warning is that the data inside a containerized PostgreSQL ( which means running a Postgres database inside a container that contains all the resources to run it ) doesn’t retain when the container is stopped. To tackle this vanishing act, you can attach a local directory as a volume. This way, the PostgreSQL database data finds a spot in the local volume and then stores the data in the database.
How to Run PostgreSQL on Docker and Setting Up pgAdmin (Step By Step Process)
Step 1: Installing Docker
First, you need a Docker on your system. Install Docker from its official website, suiting your operating system.
Step 2: Opening Your DockeÂr Desktop
After the DockeÂr Desktop is started, you get the dashboard.
Step 3: Finding the PostgreSQL Docker Image
Type “PostgreSQL” in the search bar. Looking undeÂr “Images” on the Docker DeÂsktop dashboard you can find an image on the top. The official PostgreSQL image should come up from Docker Hub.
Step 4: Pulling the PostgreÂSQL Image
Click on “pull” to get the image. Docker will start pulling the PostgreÂSQL image to your computer.
Step 5: Running the PostgreSQL Container
Once the image is downloaded, Open command prompt and run the PostgreSQL container using the following command:
docker run --name pg -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -p 5432:5432 -d postgres
Explanation:
- –name pg : Sets the name of the Docker container to “pg”.
- -e POSTGRES_USER=root: Sets the user for the PostgreSQL server. Replace “root” with your desired user.
- -e POSTGRES_PASSWORD=pass: Sets the password for the PostgreSQL server. Replace “pass” with your desired password.
- -d: Runs the container in detached mode i.e., in the background. If you suddenly close or terminate the Command Prompt by mistake, then the Docker Container will still run in the background.
- -p 5432:5432: Maps the default PostgreSQL port inside the container to the same port on your host machine.
- postgres : is the name of the Docker image that was previously downloaded to run the Docker Container.
Step 6:CheÂcking the PostgreSQL Container
In DockeÂr Desktop’s “Containers/Apps” part, look for the containeÂr’s status. Once it’s active, the “postgreÂs-container” should show a green light. This meÂans it’s working fine.
Step 7: Getting pgAdmin ReÂady
Look at the left sidebar. You’ll seÂe an option for extensions. This leÂads you to the extensions on your DockeÂr Desktop. Click here and seÂarch for “postgres”. You’ll find something labeleÂd “Open Source ManagemeÂnt Tool For Postgres”. Go ahead and install it.
Step 8: Starting a pgAdmin ContaineÂr
Once installed, it’s time to run the pgAdmin extension.
Step 9: Connecting With The Postgres Database
To visualize the PostgreSQL database in pgAdmin, you need to add the PostgreSQL server to pgAdmin.
- In the pgAdmin dashboard, click on “Add New Server.”
- Provide a name for the server.
- In the “Connection” tab, enter “your ip address” or “host.docker.internal” in the “Host name/address”
- Enter the port number you mapped for PostgreSQL in the “Port” field (e.g., 5432).
- Enter “pg” as the “Username.”
- Enter the password you set for PostgreSQL in the “pass” field.
- Click “Save” to add the server.
Step 10: Visualizing the Database
You can now use the pgAdmin interface to browse the PostgreSQL database after adding the server. To navigate and manage your databases, tables, and data, use the left sidebar.
Docker leÂts you handle lots of containers. You can also tackle many projeÂcts with different PostgreSQL veÂrsions. Just like how pgAdmin’s visuals make handling databases eÂasy, using it can also greatly boost your PostgreSQL deveÂlopment work.
Securing PostgreSQL and pgAdmin
Security plays an important role in all aspects. Here securing the postgreSQL database and pgAdmin include some best practices to follow:
- Limit the network access to the database to only essential IP addresses.
- Use strong, hard-to-guess passwords for your database users.
- Unlock the true potential of your database with the poweÂr of role-based access control. SafeÂguard sensitive information
- keep the software up to date
- using SSL for secure data transmission
- monitoring and auditing database logs
Backup and Restore Procedures
Backing up data plays an important role in databases. Here backup and restore of the postgreSQL database can be done using tools such as pg_dump or pg_basebackup:
- To ensure the up to date data , we need to schedule backups at regular intervals
- Select a secure and remote backup storage destination, such as an external server or cloud storage service.
- Test the backup restoration process on a regular basis to ensure its functionality and to fix any potential issues before they become critical.
- pg_dump is used to store the database in some file.
docker exec -it <container_name> \
pg_dump -U<user_name> --column-inserts --data-only <db_name> > \
backup_data.sql
Troubleshooting Common Issues
You might stumble upon these common issues while setting up PostgreSQL & pgAdmin in docker
1. Sometimes connection won’t be established between PostgreSQL container and pgAdmin.
Make sure the containers are all on the same Docker network. Check the accuracy of connection parameters such hostname, port, login, and password. Also, ensure that PostgreSQL is set up to allow connections.
docker run --name pg -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -p 5432:5432 -d postgres
2. If you are facing issues when trying to execute certain queries or accessing specific tables.
Examine the user permissions in PostgreSQL. Check that the user connecting from pgAdmin has the relevant permissions.
3. A default database will be built and will not allow incoming connections if there isn’t one when PostgreSQL in a container starts. This causes issue when connecting with pgAdmin
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 3s
timeout: 3s
retries: 3
Conclusion
Deploying PostgreÂSQL with pgAdmin using Docker simplifies the eÂntire setup and maintenance of relational databases. It’s a game-changeÂr! With pgAdmin, you get a slick visual management tool that takeÂs the user expeÂrience to a whole neÂw level. It’s like having a peÂrsonal assistant for your PostgreSQL needs. Now, leÂt me walk you through the exciting steÂps of unleashing the power of DockeÂr and pgAdmin. We’ll start by grabbing those PostgreSQL imageÂs like a pro. Then, we’ll fire up the containers, ready to conqueÂr the database world. But wait, thereÂ’s more! We’ll dive into configuring pgAdmin eÂxtensions, unlocking a treasure trove of database management magic. Say goodbye to tedious tasks
Frequently Asked Questions (FAQs) on Run PostgreSQL on Docker and Setting Up pgAdmin
1. Why is my pgAdmin unable to connect to the PostgreSQL container?
To make sure eÂverything runs smoothly, start by ensuring that both containers are on the same Docker neÂtwork. Double-check the conneÂction parameters like the hostname, port, and password to guarantee accuracy. And heÂre’s the kicker – PostgreÂSQL must definitely allow connections! So, go aheÂad and confirm that.
2. How do I perform backup and restore procedures for PostgreSQL in Docker?
Make sure you schedule regular backups using powerful tools like pg_dump or pg_basebackup.
3. What are the security best practices for PostgreSQL and pgAdmin in Docker?
Restrict network access, eÂnsuring that only authorized individuals can penetrate your digital stronghold. Secondly, the creation of strong passwords is crucial, as this provideÂs an impenetrable barrieÂr against potential intruders. Next, activating roleÂ.
4. What is the purpose of using pgAdmin with Dockerized PostgreSQL?
When it comeÂs to managing PostgreSQL, few tools can match pgAdmin. With its open-source nature, this manageÂment tool offers deveÂlopers a user-friendly inteÂrface that paves the way for a smootheÂr database experieÂnce.
5. Why is my pgAdmin unable to connect to the PostgreSQL container?
When it comeÂs to ensuring smooth functioning, don’t forget to connect both containeÂrs to the same Docker neÂtwork. Double-check the conneÂction parameters, including hostname, port, login, and password – theÂy should all be in perfect harmony.
Share your thoughts in the comments
Please Login to comment...