Install Postgresql In Docker Container

Jun 11, 2018 Connecting to PostgreSQL container inside Docker network. Let’s try to connect to the Postgres container from another container within the same Docker network which we created earlier.Here, we have used psql client to connect to the Postgres. We used the Postgres container name as a hostname, user and password present in the Docker file. Starting PostgreSQL server and pgAdmin: Now, to start the db and pgadmin services, run the following command: $ docker-compose up -d. The services should start in the background. As you can see, the port 8080 and 5432 are opened by the docker-proxy service. $ sudo netstat -tlpn.

I just started this as fun activity and checking remote database connectivity in a single board computing environment and how docker can make single board computing easy and more usable in real IOT projects. I am glad that everything worked as expected and still few things to be explored ! I am not connecting my sensor now, that will be step 2 to log sensor data on PostgreSQL hosted on Pi. So really less hack this time :).

I am just planning SSH (putty or techtia which ever I get) to the Raspberry Pi from another Windows PC for better control on the Raspberry Pi. Once the PostgreSQL container is up and running I will connect the instance from Azure Data Studio and then we will try some basic PSQL bash command from SSH terminal.

I am trying to put every detail step to install PostgreSql on Raspberry Pi running on Debian Linux. Lets create a folder named postgres1:

Create postgres_data folder inside postgres1 folder:


Create the docker-compose.yml using any editor … I am using Nano which come out of box with Debian Linux. VI or VIM anything can be used but VIM require separate installation on the PI..

I created a 'docker-compose.yml' file like below .. There are many things we can change I liked these attributes in my file as I need them.

Now after writing the docker compose file Postgres1 the directory on Pi should look like below:

I prefer a previous postgres installation Config cleanup if there is something already. My approach was finding the config file and just remove that from specific location ..

Install Postgresql In Docker Container Database

Now use “sudo docker-compose up” .. It will take few minutes to create the container as per the docker compose file and should be okay if you get same response like below.

Meantime lets talk about bridge network and volume little bit - the purpose I am using now should work with default network on the compose but in future I want to add new services under same network and keep isolated from other default networks on the docker. As a result I want my networks to work as single bridge network as OSI data link layer component for the PostgreSql instance. My volume is located at '/var/lib/postgresql/data' which we can not read out side of docker process. I am using volume as this is best persistence storage mechanism in docker however I would love to play with this latter for back up, replication and mount (not included in this step 1 document).

Lets come back to the terminal .. Sometime you can expect the terminal frozen after starting the server but just starting a new SSH session should solve the problem.

Now we can see the PostgreSql instance is running in my Raspberry Pi in a docker container.

Connection to Raspberry PI remote Postgres database from Azure Data Studio:

I just installed Azure data Studio on a Windows PC and installed the PostgreSQL plugin for Azure Data Studio. Now I will use Azure Data Studio to remotely connect the PostgreSQL database running on Raspberry PI using TCP port 5432. Let's to verify TCP connection in postgresql.conf file.

Using above command I tracked down the file and and then just a quick check whether the listen_address =”*” or not. I was expecting a * there not 127.0.0.1 (fake) .. and it is *.

Great .. I am connected now. Running a basic SQL query on 'master' database from azure data studio.

Now I will create a new super user 'postgres' which I would use from psql command line .. I just logged in in azure data studio and ran the sql.

Install Postgresql In Docker Container Linux

PSQL Command from SSH terminal:

Install Postgresql In Docker Container

Now I am going to use psql command and list down all the postgres databases present in my raspberry pi. Run this from from you ssh terminal connecting Raspberry pi.

Docker

Then login as the created super user 'postgres'.. My host is localhost for psql because I am just doing ssh and port I opened 5432 as per the docker compose file.

Install Postgresql In Docker Container Store

This should allow me to see the available databases using 'l' or we can create new databases also using psql as we logged in as superuser.

Remove container:

Install Postgresql In Docker Container List

Docker

Finally, I will use same docker compose file to remove the container from my raspberry pi. This just to do clean up what I did now …