How to Connect PowerBi to Postgres Database

Below is a step-by-step documentation on how to export the port of PostgreSQL in Docker Compose, configure IP tables to create an external connection, and then connect Power BI to the Postgres Database:

• SSH into your VM where Docker is running.
• Open your docker-compose.yml file for editing (you can use any text editor you are comfortable with).
• Find the section for your PostgreSQL service in the docker-compose.yml file and add the ports. It should look something like this:

db:
    image: postgres
    environment:
      POSTGRES_USER: my_user
      POSTGRES_PASSWORD: my_password
    ports:
      - "5432:5432"

• The ports section maps the port 5432 of the container to the same port on the host (VM). By default, PostgreSQL listens on port 5432. Ensure that this port is not already in use on the host.
• Save and close the docker-compose.yml file.
• Now, run the following command to apply the changes

$ docker-compose up -d

To create an external connection, you need to allow traffic to the PostgreSQL port in the IP tables of your VM. Follow these steps:

• SSH into your Proxmox server where the VM is running
• Run the following command to allow incoming traffic to the PostgreSQL port (5432):

$ iptables -t nat -A PREROUTING -i vmbr0 -p tcp --dport 7*** -j DNAT --to-destination <VM_IP>:5432

• Replace the Asterisks (*) in 7* with the last 3 digits of your Vm ip address for technicality

Now that you have exported the port and configured IP tables, you can connect Power BI to the PostgreSQL database:

• Open Power BI on your local machine.
• Click on “Get Data” from the Home tab.
• In the “Get Data” window, select “Database” and then “PostgreSQL” from the list.
• Enter the IP address of your Server in the “Server” field followed by :7* which is the port you chose earlier.It should look like this

 Server_IP:7*** 

• In the “Database” field, enter the name of the database you want to connect to (or leave it blank to connect to the default database).
• Enter the PostgreSQL username and password you defined in the docker-compose.yml file.
• Click on “Connect” to establish the connection.
• Power BI will display a navigator window showing the available tables in the PostgreSQL database. Select the tables you want to import, and click on “Load” to import the data into Power BI.