Use Postgresql from docker

To me - it's always painful to install and configure Postgresql on local machine. As a developer, it kills much time. Another problem is to manage a list of servers when I've to work with multiple postgres version at the same time. It's better to use Docker to setup database rather than manually installing the DB server. It's super easy to install any database version with just a few steps.

In this post I'm going to note down the steps I've followed to install Postgresql 9 inside a docker container. Also I'll import from an existing database to this one.

 

Step one: Install docker

There are plenty of tutorials on this so I'm skipping it. At the end of this step you should be able to run the docker command from terminal. If you are new to docker, I'll recommend to play with it a bit so that you are familiar with the keywords: container, image, docker hub etc.

 

Step two: Pull the docker image

We need to pull the image from docker hub to our local machine for postgresql. To install latest postgresql, run the following command:

# docker pull postgres 
 
If you need a specific version of postgresql e.g. 9.6.20, you need to provide a tag with the image name. Here is how:

# docker pull postgres:9.6.20

You'll find the available tags for postgresql in their docker hub page: https://hub.docker.com/_/postgres Go to the "Supported tags" section.

After this command, docker will download the image to your local machine. To make sure the image is there, run this command:

# docker image ls

 

Step three: Create a volume for your container

It's recommended to create a volume for the container. It's directory on your host machine which will be accessed by the postgresql that'll run inside the docker container. You data will persist event if you delete the container.

I'm going to create the following directories inside my HOME:

# mkdir -p $HOME/docker/volumes/postgres-9

So postgres-9 will be the directory aka volume which will be used by docker.

 

Step four: Initialize and run the container

This is the most important step: we will create and run the container from the image. We'll also pass some additional arguments. Here is the command:

# docker run --rm --name postgres_9 -e POSTGRES_PASSWORD=1234 -d -p 5050:5432 -v $HOME/docker/volumes/postgres-9:/var/lib/postgresql/data postgres:9.6.20

We need to understand what's going on here.

--rm: tells docker to remove the image when we stop it. Since we're using using a volume to persist the data, it'll be better to remove the image when we don't need the container running.

--name postgres_9: we're giving a name to our container. This will help us to identify containers easily.

-e POSTGRES_PASSWORD=1234: We're passing an environment variable POSTGRES_PASSWORD with value 1234 to the container. This will set the password of the default DB user - postgres.

-d: Tells docker to run this container as daemon. So it'll run in background

-p 5050:5432: It's the pair of ports. It means postgresql will use the 5432 port inside the container. We'll access it through 5050 port from the host machine.

-v $HOME/docker/volumes/postgres-9:/var/lib/postgresql/data

This is the volume parameter. The first part is the location inside the host machine. the second part is the location inside the container. So here we are telling docker to persist the /var/lib/postgresql/data location of the container in the $HOME/docker/volumes/postgres-9 directory. We are doing so because by default postgresql stores it's data in the /var/lib/postgresql/data location.

postgres:9.6.20: It's the name of the image and tags that we pulled in the step two.

After running this command, the postgresql-9.6.20 will run inside a container. We'll be able to access it from the host machine with 5050 port.

To check our container is running, run the following command:

# docker container ls
 
The output will be a list of container. We'll need the container ID later in this post. 
 
 
We've successfully finished the first part.

Now we'll create a database inside our container and import a existing database dump in it.

Let's connect to the container from our terminal with this command:

# docker exec -it postgres_9 /bin/bash

Here, postgres_9 is the name of the container we set in step four. Lets connect to the database with:

# su postgres

Now we're inside the postgres terminal. We need to create a database, an database user and any other tasks here. I'm skipping the details of these here assuming we can do it easily.

Our database is ready. We can access our new postgresql at port 5050 from localhost. We can use any DB client to connect to the databse. 
 
Now I want to export an existing database from my host machine and import it inside the container. Here are the steps I'm going to follow:
  • Export my local database
  • Transfer it inside the container
  • Import within the container

Export my local database:

The following command will export the structure and data to a file named my_dump.sql

# pg_dump src_db_name > my_dump.sql

Transfer it inside the container

# docker cp my_dump.sql 1281bacd35fb:/var/ 
 
Here, 1281bacd35fb is my container id. After this command, my_dump.sql file will be copied to /var directory inside the container

Import within the container

The following command will import the dump file to a database

# su postgres
# psql dest_db_name < /var/my_dump.sql 

 
The above command will import the tables and data from my_dump.sql file to a database named dest_db_name

You may need to add user, grant permissions and some other to the imported db, tables and sequences.

That's it. From now, I'm not going to install any database without docker.

Comments

Post a Comment

Popular posts from this blog

Run tasks in background in Spring

Why background task? There are tons of examples where we often need to run a task in background e.g. sending an email, sms or notification, writing some log in file etc. If a task is not required to maintain atomicity and can be executed after some delay, it's a good candidate to run in background.   How to run tasks in background in Java? In Java, the easiest way to run a task in background is to create a new thread and execute it. It's quite simple. Here is how to do this: new Thread(new Runnable(){ @Override public void run(){ // your background task } }).start(); It's easy! But there are some maintenance problem with the above code. The major problem is - there is no way to control how many background tasks we want to run simultaneously. For example, if 1000 users registers in your application at a time and you create 1000 background threads to send them a confirmation email, maybe you'll be in trouble. So you ne
Read more

Conditional field inclusion in Jackson and Spring Boot

When we write JSON API with spring boot, we often need to customize which fields should be included in our response JSON and which should not. For example, suppose we've a Model like the following: public class User {     private Long id;     private String name;     private String password;     private List<String> children;     // getters and setters here } Now we've the following controller: @RestController @RequestMapping("/api/users") public class UserController {     @GetMapping     public List<User> userList(){         User user = new User();         user.setId(10L);         user.setName("Rafiqunnabi Nayan");         user.setPassword("abcd");         user.setChildren(Arrays.asList("Child 1", "Child 2"));         return Arrays.asList(user);     }     @GetMapping("{id}")     public User userDetails(@PathVariable Long id){         User user = new User();         user.set
Read more

How to configure Wildfly 10 to use MySQL

Follow these steps to configure MySQL in Wildfly 10 Step 1 : Download MySQL connector jar from mysql downloads page . Step 2 : Go to modules\system\layers\base\com of your Wildfly home Step 3 : Create folder like mysql\main and go to mysql\main Step 4 : Place the downloaded connector jar in the main folder  Step 5 : Create a file named module.xml in the main folder and put the following content in it. Set the path variable so that it matches the name of the jar file. <?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.1" name="com.mysql">     <resources>         <resource-root path="mysql-connector-java-5.1.39-bin.jar"/>                  </resources>     <dependencies>         <module name="javax.api"/>         <module name="javax.transaction.api"/>     </dependencies> </module> Step 6 : Go to standalone\configuration dir
Read more